Button to make X number of copies of row

Can any button gurus help out here? I have a table with rows that look like this:
| Name | Quantity | Price | Schematic Symbol |
| 10k Ohms | 5 | .10 | R1, R2, R3, R4, R5 |

I would like to make a button for that row, that will basically look at the Quantity column, and make 5 copies of that row, each with a quantity of 1 instead of 5.
I’d also like it to split up the Schematic Symbol column and assign one of the symbols to each of the new rows.
Finally, I’d like it to delete that row if possible

Is this doable? Sorry I’m VERY green on using buttons so I’m not sure where to even start.

Thanks!

1 Like

Hi @Michael_Forsman :blush: !

Here you go :blush:

I’ve done it in 2 slightly different ways and depending on your current setup this might need some adjustments…

The Action formula (which is commented) of the 1st button (the blue one) is:

RunActions(
  // Step 1: Create a sequence (a list) going from 1 to the desired Quantity and ...
  Sequence(1,thisRow.Quantity).ForEach(
    // Action 1: For each number in the list (each number being represented as CurrentValue)
    // add a row in [Table 2] where :
    [Table 2].AddRow(
      // for the field Name
      [Table 2].Name,
      // input thisRow.Name
      thisRow.Name,
      // for the field Quantity -> 1
      [Table 2].Quantity,
      1,
      // for the field Price -> thisRow.Price
      [Table 2].Price,
      thisRow.Price,
      // for the field Symbol
      [Table 2].[Schematic Symbol],
      // the Nth(CurrentValue) (CurrentValue being a number from the sequence) from the list of text 
      // values returned by thisRow.[Schematic Symbol].Split(",")
      // I.e.: when CurrentValue = 1 -> "R1", when CurrentValue = 2 -> "R2", etc ... 
      thisRow.[Schematic Symbol].Split(",").Nth(CurrentValue).Trim()
    )
  ),
  // Action 2: Delete thisRow 
  thisRow.DeleteRows()
)

And the Action formula (which is also commented) of the 2nd button (the green one) is :

RunActions(
  // Step 1: Create a list of text values from thisRow.[Schematic Symbol] and ...
  thisRow.[Schematic Symbol].Split(",").ForEach(
    // Action 1: For each Symbol in the list (each symbol being stored as CurrentValue)
    // add a row in [Table 2] where :
    [Table 2].AddRow(
      // for the field Name
      [Table 2].Name,
      // input thisRow.Name
      thisRow.Name,
      // for the field Quantity -> 1
      [Table 2].Quantity,
      1,
      // for the field Price -> thisRow.Price
      [Table 2].Price,
      thisRow.Price,
      // for the field Symbol
      [Table 2].[Schematic Symbol],
      // Input the CurrentValue from the list of text values...
      // I.e.: For the 1st row -> CurrentValue = "R1", For the 2nd row CurrentValue = "R2" ...
      CurrentValue.Trim()
    )
  ),
  // Action 2: Delete thisRow 
  thisRow.DeleteRows()
)

I hope this helps :innocent: !

Don’t hesitate if you have questions (as I’m a bit too short on time to give more explanations at the moment :blush: )

2 Likes

Ooh thanks @Pch I’ll check this out ASAP and see if it will work!

1 Like

OK this is great and gets me really close.

But one problem I ran into (since I’m new to formulas and buttons) is that my actual table has slightly different columns than I gave in my example (to simplify things) and one of them (the Name) is actually a formula. It looks like the AddRow function expects the column values in order, so it tries to put the first value into the Name column and then errors because it’s a formula obviously.

What do I need to put as a placeholder for a formula column so this doesn’t happen?

Thanks!

Would you mind sharing a sample / mock-up doc ? :innocent:

Oh I think I may have misunderstood your syntax. Let me play with it some more here. I think I may have just been dumb haha

You can also simplify your formula using the new DuplicateRows() formula.

Loop through a list of sequences numbers like @Pch demonstarted, and then simply set the quantity to the CurrentValue

2 Likes

Ok, good to know! I wondered if I could do that. I’ll play with that a bit here soon!

Old habits die hard :sweat_smile: … I completely forgot about DuplicateRows() :blush:
Thanks for the reminder :blush:

Here’s practically the same sample but using DuplicateRows() instead, just to illustrate :blush:

Just in case @Michael_Forsman, this topic helped me a lot when I was struggling with FormulaMap() / ForEach() :

Thanks! So just to be clear, the difference with DuplicateRow is that by default, all the columns will have the exact same values as the source row unless specifically changed. Versus the other solution you had with adding new rows, if you didn’t specify a column, it would just end up blank?

Do I have that straight?

1 Like

OK so I have updated my formula on my Doc and it’s working great! Here’s what I ultimately came up with:

RunActions(
  Sequence(1, thisRow.Quantity)
    .ForEach(
      thisRow
        .DuplicateRows(
          thisTable.[Schematic Symbol],
          thisRow.[Schematic Symbol].Split(",").Nth(CurrentValue)
            .Trim(),
          thisTable.Quantity,
          1
        )
    ),
  thisRow.DeleteRows()
)

The only issue I’m having is that it fails if the Schematic Symbol column doesn’t have enough “parts” to split it up right. For example if I said quantity was 5, but Schematic Symbol only has “R1, R2, R3” then it will fail.

Is there a way to configure it so that it will handle this gracefully and just fill as many as it can with the split values, and fill the rest with blanks?

Basically it needs to check if the index is empty in the split first, and if it is, just put an empty string. I just don’t know how to check if the index exists or not

I think I cracked it! This seems to work reliably so far:

RunActions(
  Sequence(1, thisRow.Quantity)
    .ForEach(
      thisRow
        .DuplicateRows(
          thisTable.[Schematic Symbol],
          If(
            thisRow.[Schematic Symbol].Split(",").Count() < CurrentValue,
            "N/A",
            thisRow.[Schematic Symbol].Split(",").Nth(CurrentValue)
              .Trim()
          ),
          thisTable.Quantity,
          1
        )
    ),
  thisRow.DeleteRows()
)
2 Likes

Congrats @Michael_Forsman :raised_hands: !

Using an If() to tell the formula what to do in case there were less symbol than the quantity was pretty much what I was going to suggest if I saw your question earlier :grin:

But just to answer this …

As far as I know, yes, you got that straight :smile: !

OK, so this is all working great. It splits out the rows nicely.

Now I have a new challenge. Any chance there’s a way to do the opposite of this?

Basically click a button on a row, and it will look for other rows where specific columns have the same value as the row the button was clicked on, and combine them into that row, updating the quantity of the button row to reflect how many, and combining the Schematic Symbol column into a CSV string? Then delete the other duplicated rows.

Hopefully this makes sense.

Thanks in advance to anyone who can help!

Hi @Michael_Forsman :blush: !

Without knowing a bit more about your actual setup, I guess the suggestion I’m going to make won’t solve everything and will require some adjustments :innocent: … but it might still give you an idea :blush: :

I used, once again, 2 different tables here and the formula in the Update button is this one :
( :information_source: WithName())

// Creates the appropriate list of rows from [Table 2] using Filter().
// Then store the list of rows within the named value Rows.  
// This is done to avoid having to re-use Fitler() redundantly 
// in ModifyRows() 
[Table 2].Filter(Name = thisRow.Name).ListCombine().WithName(Rows,
 thisRow.ModifyRows(
   thisRow.Quantity,
   // Count how many rows in Rows
   Rows.Count(),
   thisRow.Price,
   "0.1",
   thisRow.[Schematic Symbol],
   // Join() each Schematic Symbol in the list of rows stored in Rows 
   // with ", " (for the CSV format)
   Rows.[Schematic Symbol].Join(", ")
 )
)

I hope this helps a little :innocent:.
Don’t hesitate if you have question :blush:

Thanks for this! Hopefully it gets me going in the right direction at least! I’ll give it a try soon!

Hokay, here we go. So first off, here’s a sample Doc where I’m working with this formula: Project Merging Rows

So, I got something going that is very close to what I need. It’s the “Condense” button in that doc.

It does pretty much everything I want it to, except I want it to delete the other “duplicates” that got combined when it’s done. And I’d like it to sum the quantities instead of count the rows.

The big difference between your example and mine is that I’m working within the same table, so at the end I get all but one of the duplicates still there (the one I clicked the button on has been updated to the new combined values). I tried to use that “WithName” formula to get a named “variable” of the original row where the button was clicked so that I could use it to find the duplicates again later to delete them, but I didn’t have luck with that.

The other piece I haven’t quite figured out is for Quantity, I’d like it to actually sum the quantities of individual rows if possible, rather than simply counting them. Is that doable? I tried to figure out how to work in the Sum formula but couldn’t quite make it work.

Any tips?

Here you go @Michael_Forsman :blush: !

I’ve just slightly modified the Action within the button Condense and commented the changes I’ve made :blush: .

[tbl.Parts]
  .Filter(
    And(
      Value = thisRow.Value,
      Rating = thisRow.Rating,
      Type = thisRow.Type,
      Status = thisRow.Status,
      Project = thisRow.Project
    )
  )
  .ListCombine()
  .WithName(
    RowsVar,
    // Added RunActions() to be able to perform multiple actions ^^
    RunActions(
    thisRow
      .ModifyRows(
        thisRow.Quantity,
        // Access the Quantity of the rows in RowsVar and then Sum() the values
        RowsVar.Quantity.Sum(),
        thisRow.[Schem. ID],
        RowsVar.[Schem. ID].Join(", "),
        thisRow.Notes,
        RowsVar.Notes.Join(LineBreak())
      ),
      // Excludes thisRow from RowsVar to only keep the row where the button is pushed
      // Then deletes what's left 
      RowsVar.Filter(CurrentValue != thisRow).DeleteRows()
  )
)

I hope this helps :innocent:

1 Like

That’s perfect!

Thanks! I’m a software engineer but these Coda formulas are taking me a bit to get the hang of. It’s like learning another programming language.

My pleasure @Michael_Forsman :grin: !

As for the rest, I have no doubt you’ll get there :raised_hands: !