LinkedIn
Twitter
Definitely a frequently-asked question: how do I add some number of rows to a table automatically? It’s possible, but it’s not easy.
This article details an approach that can be accomplished entirely within AppSheet (no API use), within the app itself (no workflow use), and accommodates an arbitrary number of rows (no built-in count maximum).
While I’ve tried to make the guide accessible for less-experienced app creators, make no mistake: this is an advanced technique.
I hope you find this useful.
Sample app
Show More
Add Rows 1
I’ve also posted a follow-on app that simplifies the looping method used here and offers more examples:
Looping with actions
I’ve also posted a follow-on app that simplifies the looping method used here and offers more examples:
Looping with actions
Step-by-step guide
Show More
5. Of course, the whole point here is to add rows, so we need an action (e.g., Add one row) for the Rows table of type Data: add a new row to another table using values from this row to add one row, to be used repeatedly as many times as needed.
Screenshot: Behavior >> Actions >> Add one row
The action to add a new row must set at least one column value of the new row. A common approach is to set the row’s key column value (commonly using UNIQUEID()), but if the key column has an Initial value expression, you could choose to set another column value instead. In fact, you can set any column values of the new row with this action.
6. Above, we identified two actions that need to occur with the addition of each row: the row addition itself, and lowering the count of rows still needed. We need a third action to perform those other two actions together.
1. In this guide, the table from which and to which rows will be added is named Rows. For your app, choose an existing table to which you want the user to be able to add multiple rows. Remember, though, to use your own table’s name wherever the Rows table is referenced in these instructions.
2. The Rows table must allow at least Adds and Updates: Adds to add rows, and Updates to keep track of how many more rows are needed.
Screenshot: Data >> Tables >> Rows
3. To add multiple rows, we need to keep track of how many more are needed. For this, add a column (named, e.g., Count) of a numeric type (e.g., Number) to the Rows table.
Screenshot: Data >> Columns >> Rows
4. The Count column tracks the number of rows still needed. As each new row is added, the count should go down. Create an action (e.g., Decrement count) for the Rows table of type Data: set the values of some columns in this row to lower the value of the Count column by one: ([Count] - 1).
Screenshot: Behavior >> Actions >> Decrement count
5. Of course, the whole point here is to add rows, so we need an action (e.g., Add one row) for the Rows table of type Data: add a new row to another table using values from this row to add one row, to be used repeatedly as many times as needed.
Screenshot: Behavior >> Actions >> Add one row
The action to add a new row must set at least one column value of the new row. A common approach is to set the row’s key column value (commonly using UNIQUEID()), but if the key column has an Initial value expression, you could choose to set another column value instead. In fact, you can set any column values of the new row with this action.
6. Above, we identified two actions that need to occur with the addition of each row: the row addition itself, and lowering the count of rows still needed. We need a third action to perform those other two actions together.
Create an action (e.g., For each row to add, do this…) for the Rows table of type Grouped: execute a sequence of actions to perform both of the actions above: Add one row and Decrement count.
Screenshot: Behavior >> Actions >> For each row to add, do this...
7. The magic in this process is finding a way to repeat the row addition process an arbitrary number of times. We can accomplish this repetition using a process called recursion. Recursion is an advanced topic in computer programming, so I won’t try to detail it here. Happily, though, AppSheet can do it, and it’s reasonably easy to setup.
Create an action (e.g., Add more rows) for the Rows table of type Data: execute an action on a set of rows to perform the For each row to add, do this… action above only on the current row (set Referenced Rows to LIST([_THISROW])).
Screenshot: Behavior >> Actions >> Add more rows
8. This new Add more rows action should only do anything if the number of wanted rows expressed by the current row’s Count column value is more than zero. To enforce this, set the Add more rows action’s Only if this condition is true property to the expression, ([Count] > 0).
Screenshot: Behavior >> Actions >> Add more rows >> Only if this condition is true
This step is absolutely critical! If not done, the row addition process will repeat continuously (a condition called “infinite recursion”) until the app crashes or is force-stopped.
9.To complete the recursion setup that provides the repetition needed to add multiple rows, add the Add more rows action to the list of actions in the For each row to add, do this… action.
Screenshot: Behavior >> Actions >> For each row to add, do this...
10. At this, point, the Add more rows action is ready for use. When performed for a row of the Rows column that has a Count column value more than zero, the action will add the number of rows and reset the count to zero.
To see the Add more rows action work, add the action as the Form Saved event action for the Rows_Form view. Then, add a new row or edit an existing row, and include a value for Count.
Screenshot: UX >> Views >> Rows >> Rows_Form
Quick reference
Show More
Data >> Tables >> Rows
- Are updates allowed?: Adds, Updates (at least)
- Type: Number (or any other numeric type)
Minimum value:0(recommended)
- Do this: Data: set the values of some columns in this row
- Set these columns:
Count:([Count] - 1)
- Do this: Data: add a new row to another table using values from this row
- Table to add to: Rows
- Set these columns:
- Key:
UNIQUEID()(commonly) - (others as desired)
- Key:
- Do this: Grouped: execute a sequence of actions
- Actions:
- Add one row
- Decrement count
- Do this: Data: execute an action on a set of rows
- Referenced Table: Rows
- Referenced Rows:
LIST([_THISROW]) - Referenced Action: For each row to add, do this…
Behavior >> Actions >> Rows >> Add more rows
- Only if this condition is true:
([Count] > 0)
Behavior >> Actions >> Rows >> For each row to add, do this…
- Actions:
- Add more rows (add to existing actions list)
UX >> Views >> Rows >> Rows_Form
- Event actions:
- Form Saved: Add more rows
Have finally used the recursion example. And now this has opened doors for a lot of things.
Nonetheless, have tried to summarize the thing in a diagram. Hopw this will be of help.
