Automation to send reminder 5, 3, and 1 day before event

[Karen_Williams]

I am new to Airtable.  I have a base of speaking engagements.  I want to send an automated reminder to my boss when the engagement is 5, 3, and 1 day away.  I am able to get it to work the first time but I can't get it to know that the time is getting closer.   From what I read, DATETIME_DIFF used with NOW() doesn't update daily.  How to get this to work?

[Alessio_Monino]

Hi @Karen_Williams ,
The formula you can use to solve your problem is indeed that one. It will be DATETIME_DIFF({Date_Of_Event}, TODAY(), 'days'), where it calculates the difference in days between the "event" day and today. Then, based on the number of days that it outputs, you can create three automations that trigger when the record matches a specific condition - i.e. when the formulas is 5, 3 or 1.

A more complex automation that combines everything in one without the need of occupying three automations' worth of space, would use nested IFs in the formula so that the field is only filled in when the are 5, 3 or 1 days missing - if done this way, you would only need one automation that gets triggered when the field is "not empty" and then has conditions inside it checking the number of days before of the event date and sending the corresponding email

Hope this helps!

Website: https://alessiomonino.com
Calendly: https://alessiomonino.com/contact
Email: alessio.monino@gmail.com

[pressGO_design]

Create a field called "Days Until Engagement" (or whatever) that calculates the number of days between today and the engagement: DATETIME_DIFF({engagement date}, TODAY(), 'days').

If you want to get fancy, you can use

IF(DATETIME_DIFF({engagement date}, TODAY(), 'days')>0, DATETIME_DIFF({engagement date}, TODAY(), 'days'))

^^ will clear the cell if the Engagement Date has passed

Then you can set up Send Email automations that use "when a record matches conditions" as the trigger. Or you can create views of 5 days out, 3 days out, 1 day out that filter for the Days Until Engagement and use "when a record enters a view" as the triggers for the automations. Or you can use "when a record is updated" and watch the Days Until Engagement field and then use acondition to evaluate whether the value is 5, 3, or 1 and then send emails when the condition is true.

You're also going to need another automation that "wakes up" the base every hour or so.

I hate using TODAY() and NOW(), so I have engineered a workaround that I use. It's not super pretty, but if you want I can share that with you.

[Karen_Williams]

Can you tell me how to wake up the base every hour or so? Can you also send me the workaround you came up with?  I am still learning.

[pressGO_design]

To wake up the base, create an automation that runs at a scheduled time and set it for every x number of hours starting on y date/time. Then do a find record action for a condition, like where the Engagement Name contains “Company” or something like that, and then turn it on.

I will try to get you the workaround later today. 

[Karen_Williams]

For this automation, does it need to come before the other automations I created?  Because I got the email reminder to work for my need but I would like it remind me early than it does.  Will this do that?