Notification without changes in row or column

llambert39581

We have a sheet that track onboarding activity for new clients.  Two date columns in the sheet are Follow-up Due and Follow-up Actual. I would like to send a notification or reminder to the assigned employee on the Follow-up due date if the Follow-up Actual is blank so they are only receiving notifications about open items. There is no change to a row or column to initiate a notification and a reminder does not allow for an 'if' statement.  Any thoughts on how to accomplish this?

Mike Wilday

I use a helper column. I create a hidden date column and use the formula formula to produce. =IF(ISBLANK([Actual Due Date]@row), [Follow Up Due]@row, "Completed")

See if that works for you. Then you build your reminder around the helper column. if there is no Actual Due Date then you will have a date to pull a reminder from. If there is an actual date the word completed populates the date field and the reminder will not fire.  

Hope that helps! 

J. Craig Williams

It is unfortunate that we still do not have the Notification features for Reminders. Very.

That said, to accomplish this, I create a [Reminder Date] column. The column uses a formula to populate the date. For example:

=IF([Open Item]@row, MIN([Due Date]@row, DATEONLY(Created@row) + 14), "")

This will be blank if the item not considered open (there are more than "complete" in this system).

If [Open Item] is checked, then this reminder occurs the earlier of the [Due Date] or 14 days after the task is created.

The Reminder then keys off that date.

If the Reminder is based on some formula using TODAY(), then you'll need to account for that. I used to use Zapier for that:

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

I hope that helps.

Craig

UPDATE: Looks like Mike and I were typing at the same time and that he types faster than I do.

llambert39581

Thank you!  I created a new hidden column and it works!

 

Mike Wilday

Glad the solution works for you. 

@Craig. Two heads are better than 1!