Help with automation

stuartr_66

Hi. I need some help with automations based on upcoming expiry date of contracts.

I want to send an email alert when a contract id within 3 months of expiry date.

Initially I created a helper/formula field using a formula field called DUCE (days until contract expiry) by using Contract date - Today() and set condition DUCE equals 90 days but it was inconsistent and seemed to only generate a value once I had opened the sheet.

So I have since progressed to using a condition based on contract date field. where contract date is within the next 90 days - this seems to work. However it keeps sending the same notification (because the condition is still being met)

So I just wanted to know what my options were to make the automation send only one email alert on the day the criteria is met.

If it helps, this is how the trigger is currently set up. This may may/not be part of the problem. It could run every week instead of daily but this still doesnt solve the problem of the alert being resent every week (as opposed to every day)

Any help appreciated

Elska19

I think for the automation you should have it run every single day like you have it now.

What I would do is change the helper formula and then the conditioning for your automation. The problem with the TODAY() formula is that it won't update automatically in the sheet unless you or someone else views it and saves or changes something else and saves the changes.

If you change your helper column DUCE to DOND (Day of Ninety Days, feel free to change) and the column type to Date you can instead do a formula where its =Contract Date - 90. This will give you a date that is 90 days before contract expires. For the automation condition, you can have a condition where DOND is Today since it's in a date format. This way the automation will run every day and if it meets all criteria and the 90 day out date is equal to that date it'll run and should only email once (and not daily or inconsistently).

Automations can also be set up to lock/unlock rows which will update formulas but I don't think that's needed for this unless you want the countdown days visible.

stuartr_66

Thanks Elska. I didnt know if I would get an early response so I have already set up another test.

So avoiding today() I used where DUCE is within next 90 days

1. created a "notification sent" checkbox
2. amended my automations to include a condition where Notification Sent is blank then send the alert
3. after the alert action, I then added a cell update action to check Notification sent box
4. then the following day, the alert shouldnt repeat as it wont meet the new condition in 2. above?

then will need to wait until the following day to see if it works (ie only 1 notification is sent)

Let me try yours after that (it looks a bit cleaner tbh) but ideally I like to see them both work

Elska19

Hi Stuart, your amended version sounds like it should work since it's another condition to halt further notifications after the first one and with the cell update the sheet will save.

How did you change the DUCE column set up if you're not using TODAY()?

stuartr_66

I didnt change the DUCE column

What I did instead is add the smartsheet condition that says if Contract Expiry date (fixed future date) is within next 90 days. So its not reliant on any formulas calculating anything using Today() , its just using standard SS condition against a fixed contract expirry date. Hope that makes sense. lets see tomorrow

Elska19

I'm following! So your automation is just using that one date column, makes sense and good luck with it!