Need to test every row against TODAY() to set field value - HOW?

I have a field, ACTIVE, that I want to set to YES if TODAY() is between START_DATE and END_DATE.

Workflow rules seem to be run only when data is ADDED, UPDATED or DELETED.

I need to change the ACTIVE field in every row based on the expression above. How would I do that?

AppSheet itself does not have a way to “schedule” an update to the data. Typically these kinds of updates are best left to backend processing anyway such as with Google Scripts in the sheets or procedures in a database. Front end resources, i.e. users displays, are then not bogged down with this processing effort.

However, I would like to offer this.

I presume you want to update the ACTIVE column in order take action on rows that are “active”? Wouldn’t it be almost as easy to test that:

AND([Start Date] >= TODAY(), [End Date] <= TODAY)

This is equivalent to [Active] = "Yes" but without the need to create and maintain a process to keep [Active] updated.

Thanks for the thorough explanation. Sounds like a great idea.

Minor fixes. AND([Start_Date] <= TODAY(), [End_Date] >= TODAY()

start_date less than today
end_data greater than today

Just testing you!! :slight_smile: I think I had it correct when I first typed it and then changed it!

1 Like