A 30 day reset switch for notifications

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?

Best Answer

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    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!

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Answer ✓

    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
    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!


  • ker9
    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.