Time Related Email Notifications

Bolton

Hi fellow Smartsheet users,

I have a Smartsheet setup where it will send an email notification when items are coming due. This will trigger when a value hits 30 days in a specific column. I went and opened this sheet for the first time in a while today and it sent me a notification that the item was coming due but the column was not 30 days it was -7. While this is accurate based on todays date it did not trigger when it should have.

My sheet seems to not be updating on any normal periodicity and only when I open it. Does anyone know how I can address this? If I could even get the sheet to update once a day that would be sufficient.

Thanks so much,

-Bolton

Matt Lynn-PCG

@Bolton You are correct. The "=today()" function is awesome, but it only knows what the day is when the system is triggered to update. So if you don't update the sheet for 10 days, it doesn't change the date, it stays on the date 10 days ago. But when you (or the system) looks at that sheet it will update the date.

However there's a common/simple to do to fix this. I have it on my template sheet and add it to everything I build even if I don't need it (yet).

1.) Add a hidden helper column, that is a date format.

2.) Build a simple automation to record a date in that hidden column on any row that the primary isn't blank. Schedule that automation to run every day at 4 or 5am for example. Hide the column and from then on that sheet will update the date every day on that schedule.

Matt Lynn-PCG

@Bolton You are correct. The "=today()" function is awesome, but it only knows what the day is when the system is triggered to update. So if you don't update the sheet for 10 days, it doesn't change the date, it stays on the date 10 days ago. But when you (or the system) looks at that sheet it will update the date.

However there's a common/simple to do to fix this. I have it on my template sheet and add it to everything I build even if I don't need it (yet).

1.) Add a hidden helper column, that is a date format.

2.) Build a simple automation to record a date in that hidden column on any row that the primary isn't blank. Schedule that automation to run every day at 4 or 5am for example. Hide the column and from then on that sheet will update the date every day on that schedule.

Bolton

https://community.smartsheet.com/discussion/comment/450434#Comment_450434

Matt,

This is a great idea! Going to try this tonight and see if it runs correctly tomorrow.

Thanks so much!

-Bolton