Re: Task Alerts

Ben W

I am wanting to use smartsheet alerts to email me each day with tasks that are due and not completed. However they don't always come through each day despite it having the right set up

To do this I use a form to add new tasks and then included the following columns in my sheet so;

• a date column for when activity is due (future dated when added)
• a formula column that does Today-date column (I've done this so that this column changes each day which I think is required to prompt the alert which detects when it is 0 or negative)
• a done tickbox column so that done items are not included in the alert each day.

Has anyone done something similar - I have included the alert as image below - my problem is that some days it comes through but other days it doesn't and this causes problems.

Thanks in advance for your help.

MattH

On rows where you expected an email: have you inspected these rows to ensure the row met the above criteria? My hunch is there's something off about your formula. Can you use the alert condition where the "Due Date" is "In the past" instead of relying on the formula within the column? (Or "Today", depending on the desired result.)

Steve L

If you have a formula that is calculating a date based on Today(), I believe the sheet has to be opened before the formula recalculates.

I typically use a "Reminder" instead of a "Notification". I would configure the Sheet with columns for a Checkbox, Due Date (Date), and a Reminder (Date).  I use a formula for the Reminder date column, such as "=IF(Checkbox1,"",Due1)".  You can lock the Reminder column, since it's a formula that you won't need to manually change.

When setting up the Reminders, instead of Notifications, you can click the "on" choice to run the reminder, say 3 days after the Due date.

I usually take it a step further in the Reminder column calculation, such that I set the date of the reminder either in the formula or based on a reference to another cell.  For example, I set the Reminder date value to "=IF(Checkbox1,"",(Due1-3))", and set the Reminder itself to run on the Reminder date (Due1 - 3 days).  Doing this allows me to adjust the Reminder dates without having to change the Reminders themselves.

Finally, I typically wrap these types of formulas with an "IFERROR", just in case there's any problems with the calculations:

=IFERROR(IF(Complete1,"",(Due1-3)),"")

CanadaJim

I would also suggest removing the "added or updated" condition.  Even if it hasn't been updated, you probably want to still report on whether it is overdue or not.

Jim

J. Craig Williams

I think CanadaJim is pointing to the issue. If there is no change to the row, it won't send a Notification. It also won't send one to the person making the change unless specifically configured to do so (in your Personal Settings| Settings | Communication Preferences)

Lastly, you mentioned having TODAY() formulas. I use Zapier to force refreshes when there are not too many sheets needing this functionality. See my post here for more on that.

http://ronin-global.com/2017/03/15/forcing-today-refresh-in-smartsheet/

UPDATE: 2017-0917 - my solution has changed slightly and the post updated to reflect new method.

Craig

FEspo

Why not use a report on a dashboard instead that includes a RYG column to display Red when the date is missed? This will collect the data from any sheets you select to include and will be easily accessible on screen any time you look at the dashboard. You just need to add the RYG column to your sheet and incorporate it in the report. Would look something like below but with Red status indicators.

FEspo

If you don't want to build the dashboard you can select to have the report emailed to yourself each day at a certain time.