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.
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 !
Don’t hesitate if you have questions (as I’m a bit too short on time to give more explanations at the moment )
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! 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?
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
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 …
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.
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 … but it might still give you an idea :
I used, once again, 2 different tables here and the formula in the Update button is this one :
( 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 .
Don’t hesitate if you have question
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.
I’ve just slightly modified the Action within the button Condense and commented the changes I’ve made .
[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 completely forgot about 
!
…
!