Insert row every X spaces

(0) Share

Report

Posted on by Kepcla

Hi Experts,

I´m trying to achieve something a little hard.

I inserted a column named "Code" in the gallery FINAL, I made that with "sequence",

I have 3 controls "Supports", "Beams" and "Booster", and these controls should be inserted every 10, 10, 15 records in the FINAL collection respectively.

But I don´t know how to insert them (See the table below how it should look)

I appreciate a lot your help!

Categories:

General Questions

I have the same question (0)

All responses (10)

Answers (0)

Fardy_MADI_2 332 Super User 2024 Season 1 on at

Like (0)

Report
Re: Insert row every X spaces

Hi @Kepcla,

To solve your problem you can use the modulo function with AddColums.

Here in my exemple bellow, I create a sequence from 100 to 2000 and add a "Control" column where I put "Supports" every 10 records starting from line 2, "Beams" every 10 records starting from line 7 and "Booster" every 15 records starting from line 14 :

AddColumns(
Sequence(2000;100;1);
"Control";
If(
Mod(ThisRecord.Value;10) = 2;"Supports";
Mod(ThisRecord.Value;10) = 7;"Beams";
Mod(ThisRecord.Value;15) = 14;"Booster"
)
)

Was this reply helpful? Yes No
Kepcla on at

Like (0)

Report

Re: Insert row every X spaces

My apologies! but I inserted the wrong example of tables, and the idea is that when inserting the control, the code increases downwards.
(i.e., Original Collection de last code is "50970032", and the Final Collection is "50970046", since when inserted it runs downwards. It is not so simple, because when you insert the "control", and at the same time you increase the code number downwards, and the insertion must also be maintained every 10,10,15 spaces.
See the Final Collection how it should look.

Was this reply helpful? Yes No
Fardy_MADI_2 332 Super User 2024 Season 1 on at

Like (0)

Report

Re: Insert row every X spaces

If I understand correctly, you want the "Control" column to be automatically calculated when you add a row in the collection ?

Was this reply helpful? Yes No
Kepcla on at

Like (0)

Report

Re: Insert row every X spaces

By calculated you mean that the control is inserted every 10,10, 15 spaces, so yes, that's right.
And the code field should be incremented downwards sequentially.

Was this reply helpful? Yes No
Fardy_MADI_2 332 Super User 2024 Season 1 on at

Like (0)

Report
Re: Insert row every X spaces

Try the formula below in which I use Collect to add a row in my collection named 'Final' and I use First and LastN to check the 10th and 15th last values.

Collect(Final,
{
Code:Last(Final).Code+1,
Control:If(
First(LastN(Final,10)).Control="Supports","Supports",
First(LastN(Final,10)).Control="Beams","Beams",
First(LastN(Final,15)).Control="Beams",
"")
}
)

Was this reply helpful? Yes No
Kepcla on at

Like (0)

Report

Re: Insert row every X spaces

Maybe I explained myself wrong, the right column is the one I need to obtain from the data in the left column.
Insert controls (support, beam and booster) every 10,10,15 respectively, and increase the number of the code column at the end of the table each time a record is inserted (see image below)
Thanks for advance!!

Was this reply helpful? Yes No
Fardy_MADI_2 332 Super User 2024 Season 1 on at

Like (0)

Report
Re: Insert row every X spaces

So you want two things :

First create a new collection in which you insert the Control column but you must know where you want to start counting :

ClearCollect(FinalCollection,
AddColumns(
OriginalCollection,
"Control",
If(
Mod(ThisRecord.Code,10) = 2,"Supports",
Mod(ThisRecord.Code,10) = 7,"Beams",
Mod(ThisRecord.Code,15) = 14,"Booster"
)
)
)

Then you can add rows in this new collection with the second formula :

Collect(Final,
{
Code:Last(FinalCollection).Code+1,
Control:If(
First(LastN(FinalCollection,10)).Control="Supports","Supports",
First(LastN(FinalCollection,10)).Control="Beams","Beams",
First(LastN(FinalCollection,15)).Control="Beams",
"")
}
)

It suppose to work like this image below :

Was this reply helpful? Yes No
Kepcla on at

Like (0)

Report

Re: Insert row every X spaces

Great approach by inserting control first and code later!
But the from-to columns need to scroll down for each control entered (see image, the yellow spaces)
The collection on the left is the one generated with your formula, and to the right as it should look.
Appreciate your help!!

Was this reply helpful? Yes No
Fardy_MADI_2 332 Super User 2024 Season 1 on at

Like (0)

Report

Re: Insert row every X spaces

This may work if you reverse operations, create rows first then add the column.

Was this reply helpful? Yes No
Kepcla on at

Like (0)

Report

Re: Insert row every X spaces

...but it didn't work as it should, the inserted control didn't generate a space in the from-to, (yellow space in the image)

Was this reply helpful? Yes No