A 30 day reset switch for notifications

jblunda

I have a sheet used to keep track of auditing a tool on a manufacturing line. That tool must be looked at within 30 days, reoccurring forever. If I'm getting close to that 30 day deadline, I want SS to alert me. If I were to re-audit that tool before the deadline, then there's no need to alert me. Basically, I need a reset switch that resets that 30 days every time I re-audit.

I want the following flow to happen, but don't know how to implement:

1. Look at the rows
2. Look for a tool name in those rows
3. Once identified, locate the date of the latest entry
4. If the tool has not been re-audited within 28 days, send alert.
5. If tool was audited within 27 days, cancel alert

Discussion: The alert conditions don't seem to have this level of complexity. I could code an alert to be triggered on a cell change within a page, but if that would only work for the row as it would ignore the rest of the sheet. I want to know when I need to revisit an audit, and do it before the 30 days is up.

Any ideas? Is this too complicated for SS? Does anyone have suggestions for a better method to remind me?

ker9

Hi @jblunda

Is there a column that contains the date of the last audit and is it always the same column? You could potentially use a helper column that calculates when the next reminder should be and use automation triggered on when a date is reached on a Date Field using that helper column. The conditions could include Tool Name = "XXX".

Hope that helps!

ker9

Hi @jblunda

Is there a column that contains the date of the last audit and is it always the same column? You could potentially use a helper column that calculates when the next reminder should be and use automation triggered on when a date is reached on a Date Field using that helper column. The conditions could include Tool Name = "XXX".

Hope that helps!

jblunda

Its not the answer I was looking for, but I can make that work. I created that helper column by building a function that takes the original audit date and adds XX days. On THAT date in the helper column, I've set up a notification workflow to notify me when that date is reached. It's not the reset switch I wanted, but I think this will work just fine.

Thank you!

https://community.smartsheet.com/discussion/comment/374833#Comment_374833

ker9

Hi @jblunda

Good that it gets you going. You might be able to use when a date is reached, every day, and use conditions like x days in the past to get closer to what you need.