Add any number of rows from any row within a table

Steve
Participant V

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

Step-by-step guide

Show More

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
2X_b_beab09b778ff8823a60ea0dcb231d3cb2622e84e.png

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
2X_3_3eeea9af35166aa2b1886da534b4bbb311ad5b70.png

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
2X_0_0bfd5790c9a522571b7ffa8dbef0f41958198fd9.png


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
2X_c_c97c3bbe4871f43c13cb59f95ce92e31c3428e13.png
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...
2X_7_746cfc168ac6390f3f19f88968d027053b661bae.png

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
2X_1_154b494c41078fc06d2649e55243d2a5e372899f.png

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
2X_1_1bd8d5ff3378aa3092e3555f2baf8caac0f359df.png
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...
2X_4_491212251223fd3e53c55c97acfbb5156ece8840.png

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
2X_d_dabdff74b31144169dc26429f2537912b56bdfdd.png

Quick reference

Show More
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

 

75 50 13.3K
50 REPLIES 50

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?

Look here!

2X_4_41722811d102cfda6e5de327897a9f11a5b8f568.png

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!

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]

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.

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:

2X_9_99e7b1b2ff734d57fab0923a16b94364ce5441fc.jpeg

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?

Yes, but it’s not trivial.

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!

khuslid
Participant V

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!

khuslid
Participant V

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…

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!!

@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

@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.

Riazsg
Participant II

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

Ami
Participant V

@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.

@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.

That might do it i will update, thanks

Ami
Participant V

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

If you use an action of type Grouped: execute a sequence of actions, each of the listed actions will be preformed in the order listed.

No need to worry about that.

Thanks, i will update

reggieneo
Participant V

@Steve, how can I change the Original to be a dynamic items/ values from the list. say, extract from the payroll table? the list is contracting or expanding depending on who is on the payroll of the week. thanks

I recently had a similar issue and solved it by using a dynamic count (computed and stored in a real column but could be a VC) and an increment counter (real column) to exit the loop. You can check this post for some ideas - Action chain sequencing is failing

reggieneo
Participant V

Thanks, I will play with my little experience to make it work.

furkizio
Participant IV

Thank you very much Steve for this post. It is what i was seaching for a long time

I created the same set up as you did to insert values from BoM table to Production Details table.

There are 2 lines in BoM table. First line is inserted to Production Details table but later it is updated with second line.

What can be reason? I changed couple of things in Add one row action but did not help. Please see 1st and 2nd line.


Thank you very much in advance

I would guess that Production ID is the key column for the Production Details table. If you add another row with the key column value of a row already there, the row already there will be replaced!

Make sure the key column for the Production Detail table will have a unique value for every row in the Production Detail table.

I am very very thankful. Spent almost 3 hours but did not think of keys. You are right. I forgot to set both Production ID and Detail ID as keys.

Have a good weekend Steve. Thank you again

Dave2
Participant IV

@Steve what an awesome solution! Works just as advertised.

Question: The table I deployed this on has column for sequentially numbered “steps”. To this point, the user was inputting the step numbers manually. How would I set it up so that when a new row is added it gets assigned a serial number? I’ve played around with initial values in the column and changing the column value in the actions, but nothing has worked so far.

Also, this is a child table, so the the serial numbering needs to restart at 1 for the first new child record, then increment to 2 for the second, and so on…

Hopefully, there’s something in common to the rows you’re adding. You say you’re adding steps, so I can imagine these might be part of a larger “process”, and so have a Ref column with the ID of that project. In the Initial value for the new step rows, you could use something like this:

(
  MAX(
    SELECT(
      step-table[step-number],
      ([project-id] = [_THISROW].[project-id])
    )
  )
  + 1
)

replacing step-table with the name of the table the row are being added to; step-number with the name of the column of that table containing the step numbers; and project-id with the name of the column containing the reference back to the parent project. Note that you may need to add to the SELECT() expression to limit what existing rows it looks at.

See also:

I discovered the post you linked after I posted my question and was (slowly) on my way to builiding that very expression! Nice to know I was on the bright track It works perfectly! Thanks for all your help, here and over the years. You appsheet wizards have saved the day more times than I can count. If this app I’m developing comes together the way I envision it it will save me having to invest 20k/year in off the shelf practice management software. Constantly impressed with this platform and community!

AlexM
Participant V

It works great!

How would you prevent duplicates?

I have 3 tables:

Workers
BridgeTable
Availability

The BridgeTable has an EnumList column [Available Dates] and based on this, rows are added in the Availability table.

The problem is that I can have duplicates.
I am thinking at a column to Concatenate [Worker] and [Date] but I am stuck.

I tried to have a condition on action ADD ONE ROW:

NOT(
ANY(SELECT(Availability[ConcatColumn],true))=
ANY(LIST(CONCATENATE([Worker],ANY([Available Dates]))))
)

But no success so far.

Thank you!

Please post a screenshot of the configuration screen for your Add one row action.

Will do shortly.
Don’t think that is going to help though.

I asked the wrong question.

How would I prevent the user to enter duplicate availability records (different ID, same worker, same date) on a later attempt?

On a later stage, availabilities will be assigned to day jobs.
Having a worker available twice in a day is not an option.

ISBLANK(
  FILTER(
    "Availability",
    AND(
      ISNOTBLANK([Worker]),
      ([Worker] = [_THISROW].[Worker]),
      ISNOTBLANK([Date]),
      ([Date] = [_THISROW].[Date])
    )
  )
)

Thisrow Date is EnumList in bridge table.

I need to compare all the combinations Worker+Every date from EnumList (Bridge table) with worker+date from availability table.

(I used the formula as condition for add one row)

Please start a new topic for this problem, as it has nothing to do with this topic. Please include a fuller description of what you’re trying to accomplish and of the three involved tables.

On a single attempt, there are no duplicates.

It works like a dream.

What I am asking is to take your solution a bit further.

Sitalia
Participant III

Thanks Steve for this post…very innovative…I tried your example with the following differences

  • In the “Add one Row” action I put another new table as the target as my intent is to add rows to another new table

  • Instead of initializing the count through the Form, I put it as the “Initial Value” in the Count Column of the table - as the number of rows I want to add is always fixed. Of course I have a form but just to trigger the “Add more Rows” action

Everything works fine except that the row in the source table (that I am creating the set of new rows) gets copied or duplicated in the source table…so now I see 2 rows in the source table - one the original and another an exact copy…I don’t believe looking at the action definitions that this should happen - not able to figure out why. Please do help

The second requirement I have is to add a set of rows in the new table for each row in the source table automatically…right now I believe it is set up to create a set of additional rows only for one row in the source table - List(_THISROW) - so how do I achieve this - kind of a recursive action within a recursive action…

Look forward to hearing from you…

Please post screenshots of the action configurations.

Top Labels in this Space