Workflow Automation - problems with alerts

DawnS

Hi and Happy New Year. I'm having some issues with workflow alerts.

I have set up several columns using formula to set a '1' in specific columns if a date has passed and there is no data in certain columns. This appears to be working ok. I am then trying to use the automation workflow to send an alert based on the column value changing from a 0 to 1. The alert is set using the 'when a row changes' and when the column value changes from 0 to 1. I set some dummy data over the xmas period when I wasn't logged in to Smartsheet, expecting to see a number of alerts generated when I returned. When I logged in this morning, one alert generated today but for data that should have been triggered over the christmas period. Does this mean I have to be logged in to Smartsheet for any alerts to trigger? Is anyone else successfully using alerts without being logged in to the system? Many thanks for any help.

Paul Newcome

That's actually where the problem lies then.

The TODAY() function only updates when the sheet is activated whether it be through directly opening the sheet, a new form entry, an update request, cell links updating, and various other activities that can happen.

If there is no activity at all on the sheet, then the TODAY() function will maintain the last date that the sheet was active. There are a few different ways to account for this, but the best fit for you would depend on your exact setup. The workaround I personally use involves a third party app called Zapier that inserts a new row onto a sheet every day at midnight. This sheet has a basic =TODAY() formula in a date type column.

This means that just a little after midnight, the sheet is made active which updates my TODAY function. Then I use a cell link onto every sheet that I need the TODAY function for to pull the cell from this "zapped" sheet. Since cell links update even when a sheet is not active, I can reference this linked cell in my formulas instead of using the TODAY() function.

This means that my formulas and alerts are always accessing the correct dates even if the sheet is inactive.

Another option for your setup could be to only enter 1 if

[Actual Payroll Input & Interfaces Time]1 = ""

Then you could set up your alert to send based on a date and set the criteria for when the date in your [Scheduled Payroll input & Interfaces] column is Today or is In The Past.

This should allow you to maintain your sheet and alerts with a lot less hassle than compared to setting the above up through Zapier, but if you regularly use the TODAY() function, I would definitely look into setting something up.

DawnS

Thank you Paul - that is so useful to know. I don't use the Today function an awful lot so will look at your suggestion of alerts based on the blank data - I think that will work fine for me. Thank you again. Regards, Dawn

Paul Newcome

You should not have to be logged in. Are you able to provide screenshots of the alerts that are setup? Are you using the TODAY function in line anywhere with the trigger?

DawnS

Hi Paul - thank you for answering - the formula I am using in a various columns called Alerts has the Today function - see below:

=IF(AND(TODAY() >= [Scheduled Payroll input & Interfaces]1, [Actual Payroll Input & Interfaces Time]1 = ""), 1, 0) - the workflow is then as follows:

Paul Newcome

That's actually where the problem lies then.

The TODAY() function only updates when the sheet is activated whether it be through directly opening the sheet, a new form entry, an update request, cell links updating, and various other activities that can happen.

If there is no activity at all on the sheet, then the TODAY() function will maintain the last date that the sheet was active. There are a few different ways to account for this, but the best fit for you would depend on your exact setup. The workaround I personally use involves a third party app called Zapier that inserts a new row onto a sheet every day at midnight. This sheet has a basic =TODAY() formula in a date type column.

This means that just a little after midnight, the sheet is made active which updates my TODAY function. Then I use a cell link onto every sheet that I need the TODAY function for to pull the cell from this "zapped" sheet. Since cell links update even when a sheet is not active, I can reference this linked cell in my formulas instead of using the TODAY() function.

This means that my formulas and alerts are always accessing the correct dates even if the sheet is inactive.

Another option for your setup could be to only enter 1 if

[Actual Payroll Input & Interfaces Time]1 = ""

Then you could set up your alert to send based on a date and set the criteria for when the date in your [Scheduled Payroll input & Interfaces] column is Today or is In The Past.

This should allow you to maintain your sheet and alerts with a lot less hassle than compared to setting the above up through Zapier, but if you regularly use the TODAY() function, I would definitely look into setting something up.

DawnS

Thank you Paul - that is so useful to know. I don't use the Today function an awful lot so will look at your suggestion of alerts based on the blank data - I think that will work fine for me. Thank you again. Regards, Dawn

Paul Newcome

Happy to help! 👍️

Feel free to revisit this thread if you have additional questions!