Alert automation triggered by a Symbol column which uses flags not finding all rows

John Knipper

There's a column formula (on columnX) which sets a flag to red if certain conditions are met. The formula is setting the flag correctly.

Automation has been created which sends an alert to contacts on the row based on the Flag value for columnX. The automation runs every day at 2am. The alert which is sent doesn't include all of the rows which it should. For example, yesterday's alert contained 3 rows, but when the sheet was opened at 7am, there were 5 rows flagged. As a test, the automation was run once at this time, and the alert contained all five rows.

Does the sheet need to be opened to cause the column formula to update the flag value? If so, is there a suggested work around.

Thank you, John

Sameer Karkhanis

Your alert is triggered based on date/time so it will only include those rows which have been updated anytime before the notification goes out. Any update that happen after the workflow run will be included in the next day's run.

John Knipper

Thanks for your response, Sameer.

By examining the cell version history, I've confirmed that the rows missing from the report were not changed between the two automated alerts. That wasn't the cause.

Part of the column formula compares a [Promised Date] < Today(). Am I correct that this value will update when the automation runs (i.e. no one needs to open the sheet to cause the update)?

Paul Newcome

Anything with the TODAY function needs to be triggered to stay up to date. To trigger the TODAY function, you need to activate the sheet. Activating the sheet can be done by opening it, update request, form entries, and automations that change cell values (record a date/change cell/copy row recipient/move row recipient, etc.).

My recommendation would be to insert a date type column called "TODAY". Then you can set up a Record Date automation to run every day at 1am and record the date in this column. You can set whatever condition you want so long as the condition will definitely trigger daily such as the primary column being blank or the primary column being not blank.

This will change the date in the column every day at 1am which will activate the sheet and update the TODAY function so that all rows are actually flagged when the 2am automation runs.