Alert Someone on every day a Task is marked as happening

Milton Rodas

@Ella @Paul Newcome ... so, today the notification went out at 5 AM, and it's funny:

• In the email alert, it did include continuous dates, but it also included a date from yesterday 1/27/20 and it did not include any date of 1/28/20 (which is today).
• Whereas, when I checked the smartsheet, the checkboxes were only checked for dates of 1/28/20 or dates that were continuous, and the task that had 1/27/20 was NOT checked (and yet, it was included in the report).

So, I think the formula works well inside the smartsheet itself, it does what it's being told to do, which is check the box if any date range includes today.

But the workflow is NOT working in sending out those lines that have a checkbox TODAY. Why did it send a task dated yesterday in today's report and did not send any task dated today?

SEE HERE:

This is what we received today. Notice the 1/27/20 date included in the report, whereas the date range of 1/27/20 - 1/31/20 is there. and no dates of 1/28/20 are anywhere to be found (which there was 1 in the smartsheet that had 1/28/20).

Now, this is how the actual smartsheet looks... Notice, the line for 'Salvador Carbajal (1/27/20)' is NOT checked off. But the line that is 'Amy Chap (1/28/20)' is checked off, and yet, did not make it to the report.

Any thoughts, anyone? Thank you so much, in advance, for any feedback.

Ella

@Milton Rodas The test on my end worked fine, so Paul was correct that the alert start date would not limit the workflow as I got tasks dated 1/24 and 1/23 and 1/28 (today) as long as the check box was checked.

Please provide details/screenshots of your alert set up.

Milton Rodas

Hi @Ella -- at what time in the day are you running your workflow? I have it set to 5 AM. Do you think the time influences the output?

Here's my alert setup, per your request:

Ella

Thanks @Milton Rodas. I had one set for 9 am and another one for 10 am. Both worked great.

Your alert set up is like mine so the time is the only difference. I will set up one for tomorrow at 5 am to see what happens.

@Paul Newcome , @Andrée Starå , do you have any other thoughts on this?

Paul Newcome

@Ella and @Milton Rodas

I feel that the issue may be arising from using the TODAY() function. The TODAY() function will only update when the sheet is activated (cross sheet reference/cell link update/form entry/update request/opening the sheet).

The automation pulled what was checked yesterday because at the time of the trigger, the TODAY() function hadn't been updated from yesterday's date yet. When you opened the sheet to look, it updated the TODAY() function and made it appear as if something had gone wrong.

So we need to figure out a way for the sheet to become "activated" BEFORE the trigger so that the TODAY() function stays current.

I personally use Zapier couple with cell linking for this. Basically I have a sheet called "zzzTODAY" (the z's make it easier to find). This sheet has a date column that simply has =TODAY() in it. Then I set up Zapier to add a new row everyday just after midnight. Zapier automatically adding the row updates the TODAY() function, then all of my sheets where I need TODAY(), I just cell link from zzzTODAY to whatever sheet I want the reference in then replace my TODAY() functions with referencing that new cell.

This ensures a constantly updating TODAY() reference even if the sheet stays closed for days, weeks, months, years...

Ella

This issue is with the TODAY formula as my mock alert was incorrect today since I have not been into the sheet yesterday, so this solution unfortunately won't work for @Milton Rodas.

Paul Newcome

@Ella

That is correct. The issue with the TODAY function as well as a possible solution using Zapier is in my post above.

Milton Rodas

Interestingly, today the Notification at 5 AM included every instance where the date had a range of TODAY (1/30/20). I think it is a bug by Smartsheet and think the Formula is good.

Basically, if today started at 12:01 AM and my notification is going out at 5 AM, it should include instances that have TODAY's date in it. Very simple. And yet, sometimes it catches the instances (like today) and sometimes it doesn't.

I am continuing to test.

Paul Newcome

@Milton Rodas I do think it has to do with the TODAY function and when the sheet is activated relative to when the notification is sent.

Here is a way to test it. If you view a PUBLISHED version of a sheet, it will not update the TODAY function.

Publish the sheet as Read Only. Tomorrow, do NOT open the sheet itself. Instead go to the published URL. Compare this to the data received in your email and see if it matches up.