Automating alerts based off date modified

Sarah McKew

Hi all,

I have read a number of posts about creating alerts/reminders based on a modified date column, but I haven't found the answer I am looking for. Maybe you have it? *crosses fingers*

I met with Pro Desk on Monday and it was suggested that I use the "Within the last days" option, however this doesn't work because it will trigger anything within the last number of days, whereas the escalation needs to occur only after 7 days.

How the sheet works:

There is an approval workflow that is set up to send reminders twice a week when a request is left "pending". We need to be able to escalate the pending requests to the staff members line manager to follow up (ie the staff member is on leave therefore not receiving the reminders but because the alerts are sent from Smartsheet, no autoreply explaining that detail is known to the Project Office).

The issue: Because there is no way to calculate the number of reminders sent and then automate an alert to the line manager, I thought that I may be able to use the date modified column to trigger a notification once the sheet has not been updated in the last 7 days. The "greater than" option only allows a date field, not a number of days field.

To complicate things: There are 6 steps in the approval workflow (therefore 6 approval columns), so I need to be able to add a condition so that it's only reporting the staff member who has not responded to the approval request.

Please help! Thank you!

Sarah

Mark Cronk

Hi @Sarah McKew ,

Sounds like you need to be creative. To use the date modified column you'll need a helper column [Dateonly] with =DATEONLY([Modified]@row). That will remove the time stamp and return just the date.

Add a checkbox column [Escalate] with the formula =IF([dateonly]@row+7 = today(), 1, 0) This will check the box if it is 7 days after the last modification.

The set an alert or update request to run each day if [Escalate] is checked. To work it has to run on the weekends too.

You'll also need to figure out which line manager needs to be notified. Again, I'd use a helper column with a formula that returns the line manager contact or email. Then use that column in your alert logic. If part 1 seems like something you want to try I'll be happy to help with part 2 - the line manager.

Mark

Sarah McKew

Hi Mark,

Thank you for your suggestion.

Just to make sure I have this correct, the formula will tick the check box if last modified (whether or not the row is sitting as pending/approved/declined), then I would set the conditions related to pending requests in the automation workflow?

Sarah

Mark Cronk

Ah good catch. Yes, you could use automation logic to filter out those that are complete. Otherwise, you could change the Escalate formula so that it doesn't Check if the process is complete.

You'll have to do some trial and error but I think this is the right path.

Mark

Sarah McKew

Thanks Mark, I have set the workflow with conditional paths for each column to only trigger when pending. Because I had added the columns and formulas (and converted to column formula), it has repopulated the modified date for the existing rows to today's date, so I will have to wait for the 7 days since last modified to occur before I will discover whether it's worked.

I have confidence though!

Now to try to and see if I can auto populate approvals when the same staff member is listed in the workflow more than once, so they don't have to approve the same thing three times.

Mark Cronk

Hmm, that's a problem. So you can test, remove the column formula and change a couple test cells to today. Trigger the automation. Once you know it all works you can reactivate the 7 day formula.

Happy to help more.

Mark

Sarah McKew

Hi Mark,

I have just realised that when I click on View Update Requests, it actually updates the "Modified" auto-date column, therefore starting the escalation workflow from day 1 again. I am not sure why simply viewing the requests is considering a modification as none of the data in the row has been changed at all. Do you know if this is fixable?

Thanks again.

Sarah