Create rows for fixed dates between a start date and end date

Hi Team,

I want a row entry to be created in one of my tables, basis the following:

Below are the columns:

1) Start Date
2) End Date
3) Date 1
4) Date 2

So I want rows to be created for Date 1 & Date 2, between the start date & end date.

How can I achieve this?

0 5 129
5 REPLIES 5

Aurelien
Google Developer Expert
Google Developer Expert

Hi @Aditya_K1999 

In the valid_if section of Date 1 and Date 2, use this expression:

AND(
  [_THIS]>=[Start_Date]
  [_THIS]<=[End_Date]
)

 

Aurelien_0-1747640073184.png

 

Sorry, I think I have not explained it well.

Its similar to how @Steve made the articles on looping with number or enumlist. But the condition is different.

For example, if in my control table I enter the start date as 1st May 2025 & End date as 30 June 2025. And if I mention date/or a number as 4th. Then on every 4th between the start & end date I want a row entry to be created.

Hope I was able to explain better?

Hello, 

In AppSheet, formulas within columns are typically used to calculate values for that specific row, rather than to create new rows in a table. To achieve the creation of new rows based on conditions, you'll generally use Actions, which can then be triggered manually or by an Automation (Bot).

You would create two separate "Add a new row to another table..." actions on your SourceTable.

Action 1: Create a row if [Date 1] is within the range

For a record of this table: SourceTable
Do this: "Data: Add a new row to another table using values from this row"
Table to add to: TargetTable
Only if this condition is true (Prominence: Do not display or set in Behavior section):

AND( ISNOTBLANK([Date 1]), [Date 1] >= [Start Date], [Date 1] <= [End Date] )ISNOTBLANK([Date 1]): Ensures [Date 1] has a value.
[Date 1] >= [Start Date]: Checks if [Date 1] is on or after [Start Date].
[Date 1] <= [End Date]: Checks if [Date 1] is on or before [End Date].

 Set these columns (for the new row in TargetTable):
[RelevantEventDate]: [_THISROW].[Date 1] (This copies the value of [Date 1] from the SourceTable row to the [RelevantEventDate] column in the new TargetTable row).
You can set other columns in TargetTable as needed, for example, copying an ID from the source row: [SourceRowID_Ref] = [_THISROW].[KeyColumnOfSourceTable]

Action 2: Create a row if [Date 2] is within the range

 For a record of this table: SourceTable
Do this: "Data: Add a new row to another table using values from this row"
Table to add to: TargetTable
Only if this condition is true (Prominence: Do not display or set in Behavior section):

AND(

ISNOTBLANK([Date 2]),

[Date 2] >= [Start Date],

[Date 2] <= [End Date]

)


Set these columns (for the new row in TargetTable):
[RelevantEventDate]: [_THISROW].[Date 2]

Just for my clarification.

If the data is as below:

Start date: 1 May 2025
End date: 30 July 2025
Number/Date1: 20

So what you have suggested above, will it create a row for me in my target table for 20 May, 20 June & 20 July?

Here is what you can try..

Source Table

  • [start.date]
  • [end.date]
  • days: ENUMLIST of type number, stores the days you want (1, 15, 30)
  • current.date: type date, initial value = [start.date], reset on row edit
  • other fields you want to copy

Action 1

  • Add row to the destination table along with [current.date] and whatever fields you want to copy
  • Condition: IN(DAY([current.day]),[days])

Action 2

  • Update [current.date] with [current.date] + 1

Action 3

  • Execute an action on set of rows. reference rows: LIST([_THISROW])
  • Referenced Action: Action 4
  • Condition: [current.date] <= [end.date]

Action 4

  • Group execute Action 1 -3

After you set the required parameters, making sure that [current.date] is rest to [start.date], execute Action 4.

Hope this works for you.

Top Labels in this Space