Add any number of rows from any row within a table

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

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

Step-by-step guide
  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

    image

  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

    image

    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

Data >> Tables >> Rows

  • Are updates allowed?: Adds, Updates (at least)

Data >> Columns >> Rows >> Count

  • Type: Number (or any other numeric type)
  • Minimum value: 0 (recommended)

Behavior >> Actions >> Rows >> Decrement count

  • Do this: Data: set the values of some columns in this row
  • Set these columns:
    • Count: ([Count] - 1)

Behavior >> Actions >> Rows >> Add one row

  • 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)

Behavior >> Actions >> Rows >> For each row to add, do this…

  • Do this: Grouped: execute a sequence of actions
  • Actions:
    • Add one row
    • Decrement count

Behavior >> Actions >> Rows >> Add more rows

  • 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
29 Likes

Thanks a lot @Steve!! This will help a lot of people.

Now your next guide should be, how did you get those expandable/collapsible sections in your post?

6 Likes
Look here!

image

10 Likes

An additional suggestion that I’ve got would be to add another column that has a count, starting at zero or 1, which is incremented on every iteration. This would allow looping through any list via INDEX(), while remaining in the initial table’s context.

EDIT: Actually that’s not necessary, just do COUNT([list]) - [count]

4 Likes

Hi @Steve,

Thank you very much. The app idea, use of recursive action and the accociated documentation post above are all very useful and ingenious.

6 Likes

This is very cool @Steve
So a user could built his own table and then via Enable QuickEdit (beta) he could work in the Table like in EXCEL.
This reminds me of how you can insert a Table in Google Docs:

image

In your example we can add as many ROWS we like. But could we also define how many COLUMNS we want to use? I would say NO, but perhaps someone knows a workaround?

1 Like

Great!!

Now that Steve has demonstrated the utility of the hide/show toggle, I hope those of you who have not already done so will vote for this:

ありがとうございます!

Thanks! :smiling_face_with_three_hearts:

Ages ago I learned to program in basic, which had loops. I didn’t image that something similar (e.g. recursion) could be done in AppSheet. Very cool!

2 Likes

Yes, but it’s not trivial.

This was so good timing for me!
I’m working on a heavy desktop APP where I need to import an Item description (SOW) for contractors.
Hundreds of rows with items, pieces, unit, unit price, toltal.
Then they can easily type either percent or actual pieces of performed work for a period to invoice.
Now you showed me how to import these rows easily.
Thanks a lot!

1 Like

Hmmm… I wonder…
To import a number of rows from another table (Data_to_import-table), how can I import multiple rows from here with different data on each row?
Can I in my “Data_to_import-table” some how have a “reversed” column [Count] or an [NumberID] and make an action to Decrement count these? (just thinking loud here)
My question is really:
How can I add multiple rows AND get uniqe data on each row from another table (My “Data_to_import-table”)?

I guess I could first just add empty rows, and then set values to those based on a number ID matching from my import table. Not sure how yet, since it’s hundreds of rows. But would be beautiful to do it all at once…:slight_smile:

Not sure if this will help. @Steve 's approach outlined here was the succinct answer to a couple similar problems I had been working on.

  1. Pick from a list of Technicians to assign to a work order and then place them into an Assigned Technicians table where each Tech can later record their personal job stat info (e.g. Start and Stop times on the job).

  2. Pick from a list of Products or Materials or Labor Charges to apply to a Work Order - populate them into their respective tables and then allow table editing to adjust Quantity, etc.


I adjusted @Steve 's approach to use picked list items from an EnumList rather than a count column. Then modified the following actions defined in Steve’s outline.

  • In the action to add the single row, I assigned the Technician column to the FIRST item from the picked list using the INDEX() function like this:
    INDEX([Picked Technicians], 1)

  • In the action that normally decrements count, I instead REMOVED the first item from the picked list like this:
    [Picked Technicians] - LIST(INDEX([Picked Technicians], 1))

The result is that I have a set of Assigned Technicians each with a different Technician assigned.

CAUTION: If you decide to allow editing through the same EnumList, there will need to be some additional work. For example, pre-populate with already picked items and identify rows already added and skip them. Both easily accomplished.

THANKS @Steve!!

5 Likes

@Steve Thank you very much. Your Sample not only gave me the inspiration but also saved me a lot of time. Thank you for taking the time and sharing this Tip :slight_smile:

4 Likes

@Steve

I implemented this tips and tricks to one of my app and pleased to inform you Steve, it is working perfectly. I twisted tricks a bit to add number of rows onto ANOTHER table rather than same table.

I also tested another way to achieve the same, but they failed. For instance, I separated the actions, add first row, and action to add more rows, but the secondary action (to add more rows) are not triggered even after the first row are added.
I tried to place event listener to trigger action through the workflow based on add new row event, but apparently appsheet is not listening such an EVENT to fire the action.

All all, combine multiple actions like Steve introduced into single action could be only workable solution for now.

Again thank you Steve for sharing brilliant tricks.

6 Likes

Hi Steve

 Thanks

But implementation of

  1. while() or Loop() function and
  2. Add rows to a table
    will definitely make life easy and increase efficiency

It is a workable but cumbersome solution

Thanks Again, Riaz

1 Like

@Steve Thank you for this.
I have used it to add rows to another table based on enumlist values added by the user in a parent table. The only issue i had was that i still need my user to manualy add a number to the Count column. I use the count with indexing and split for the list. Gteat idea.

1 Like

@Steve is there a way to avoid the manual adding the count value? It seems i cant use data chang with an action to set the value of column that has a function in it.
Thanks

You use an expression to create the initial count value? Try putting that expression into initial value field, instead of app formula.

3 Likes

That might do it :grinning: i will update, thanks

1 Like

Hi
So i use your solution to add a new row to another table, however, i do the process several times in one row of data. The problem im facing is how to trigger. When used once, i simply change as you showed the on save do this action. I have at least for of these actions in one row. My question therefore, are actions sequenced in the order they are on the editor? Im afraid that all actions trigger the same time and crash the app. My alternative is using a report change data type and trigger actions in a sequnce with that.
Thanks