FAQ: Add rows for a date range

[Steve]

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)

[GreenFlux]

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

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!

[Koichi_Tsuji]

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

[Rifad]

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.

[Steve]

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.

[Rifad]

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.

[Steve]

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.

[Rifad]

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.

[Steve]

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

[Rifad]

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.

[Salmex]

Hi Steve, I tried to implement in my project and It didn't work, but When I copied your sample It works,. I don't know if I Need to activate or desactivate something in my project, thank you in advance.

[pablopoma]

Hi !

I had the same issue and it took me 10 hours to find the solution ..

You have to go to Views -> System generated -> Control Table_form -> Behavior -> Event Actions -> Form Saved -> select "Action 1"

[AlexM]

Exactly what I needed.

I have an app where we keep records of employee's availability.

Normally, it is done day by day but the issue was when a holiday needed to be booked, then every record had to be added one by one.

In addition to your (complete) solution, I have added Action 3.1 in order to update those days that already had an availability set.

For example, 7'th of Nov | John | Available

Holiday from 6Th Nov to 15Th of Nov would not add a row for the 7th and will update the 7Th from Available to Holiday

[Roderick]

Agreed! Very well laid out and easy to follow! 

[SkrOYC]

Btw, if someone is looking for a way to ignore weekends (and also holydays), you could use this on the Advance date step:

IF(
 ISBLANK([Add Date]),
 [Start Date],
 WORKDAY(
  [Add Date],
  1
 )
)

WORKDAY() - AppSheet Help

[Saddam]

Very good idea

[dennyfw1]

Greetings Sir steve,
I have been helped by your guidance many in making app in appsheet, in this case of "add rows for date range" my question is "how about if I want to add rows for date range, for many subjects?" in the example you have shown, we input one comment (name) for date between start and finishing date.... what if I want to add date range for a list?

thx in advance