FAQ: Add rows for a date range

Add a set of rows to a table, one row for each date, starting with a given start date and ending with a given finish date.

Try the sample app!

For this example, the table from which the row-addition process will be initiated is called Control Table. The table to which new rows will be added is called Target Table. Use your own names as you see fit. Note that they could even be the same table. Control Table must allow updates; Target Table must allow adds.

Control Table must have at least three columns for this process: Start Date (type Date; the first date in the series; may be virtual); Finish Date (type Date; the last date in the series; may be virtual); and Add Date (type Date; the date corresponding to the next added row; may not be virtual). Column names may be changed as desired.

Start Date and Finish Date should have Required? set to ON. Add Date should be blank.

Action 1: Add Rows for Remaining Dates

This is the action that you should invoke to add the rows: it does everything.

  • For a record of this table: Control Table
  • Do this: Grouped: execute a sequence of actions
  • Actions: (added below, after creating actions 2, 3, and 4)
  • Only if this condition is true: OR(ISBLANK([Add Date]), ([Add Date] < [Finish Date]))

Action 2: Advance Date

This manages the Add Date column value, which contains the next date within the indicated date range. It’ll start at Start Date and increment to Finish Date.

  • For a record of this table: Control Table
  • Do this: Data: set the values of some columns in this row
  • Set these columns:
    • Add Date: IF(ISBLANK([Add Date]), [Start Date], ([Add Date] + 1))
  • Only if this condition is true: TRUE (or blank)

Action 3: Add Row For This Date

This action adds one row to the Target Table table for the current value of the Control Table row’s Add Date column.

  • For a record of this table: Control Table
  • Do this: Data: add a new row to another table using values from this row
  • Table to add to: Target Table
  • Set these columns:
    • (columns as desired)
  • Only if this condition is true: TRUE (or blank)

Action 4: Add More Rows

This action implements an action loop by recursively performing action (1) for each successive date in the indicated date range, from Start Date to Finish Date.

  • For a record of this table: Control Table
  • Do this: Data: execute an action on a set of rows
  • Referenced Table: Control Table
  • Referenced Rows: LIST([_THISROW])
  • Referenced Action: (action 1)
  • Only if this condition is true: TRUE (or blank)

Action 1

Return to action (1) and add actions (2), (3), and (4) to its Actions list.

  • Actions:
    • (action 2)
    • (action 3)
    • (action 4)
10 Likes

Very cool, @Steve! And I love the way your post is laid out. :+1:

I’ve been watching this technique evolve in your posts. I think there was one about looping through child records, or some other kind of list.

This one is particularly interesting because the StartDate and EndDate don’t have to exist as sheet columns! You can just calculate a date range and create X number of rows! Awesome work!

3 Likes

yes, super cool.
Appsheet unique way “How to loop and deal with array dynamically”

1 Like

This a really great post! I have been using this idea for a while in my apps. But I have a question. I have Products table with at least 10 Production steps. So if i add 10 Products it shows approx. 100+ rows to add in sync button. That takes atleast 10 Mins for me to sync the entire app for 1 Order. Is there any way we can do it differently to avoid this slow sync ?

Thanks.

My process does everything directly within the app, on the assumption the app user wants immediate access to the added rows. If that immediate access isn’t needed, you could instead add the rows using a workflow, which requires a sync, but should go much faster.

Great! How do we do the looping action with a workflow ? It would be great if you have any reference on how to do that.

It’s exactly the same thing, except rather than attaching action (1) to the row’s form view, attach it to a workflow for add and update events in the table.

1 Like

Have you tested that. Because it works perfectly for me for actions and never worked when I did the same with Workflow just like you mentioned above.

I have not tested it. Does the workflow log show the workflow firing?

Workflow log was showing but it was stopping somewhere after once and was not looping. I had a valid if formula in key column of child table that was not letting the workflow trigger. I removed that and it Works Perfect now.

2 Likes