Schedule Automation to wake up base

[Karen_Williams]

I have two automations set up that send me reminder emails when my records hit a specific date condition.  It is a "days remaining" type of condition.  I want to wake the base up every morning so that it can update the records and send the appropriate reminder emails.  Do I need to have the schedule automation first in the list so that it performs that first or does it not matter?

[TheTimeSavingCo]

I've got a couple of automations that run off of a formula field using DATETIME_DIFF and they work fine without needing another automation to wake up the base, so it shouldn't matter I reckon

[ScottWorld]

Hi @Karen_Williams,

If the trigger for your automations is "At A Scheduled Time", then your Airtable base should wake up on its own.

Otherwise, all other automations will need your base to be woken up in order for automations to run.

If you or another team member is actively using your base, that would be enough to wake it up. Otherwise, you can just create a dummy automation that doesn't do anything significant that is set to run "At A Scheduled Time". You can also wake it up by making an API call to your base by using an external automation tool like Make.

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Karen_Williams]

The two separate automations I have are set to send emails when two date fields get under 10 days from deadline but I only get the first email, I don't get successive emails as the days get closer.  So I thought, maybe I need a dummy automation that is a "at a scheduled time" trigger with the condition to check at 7:00am if the field "Company Name" is not blank but it didn't work today.  Does it matter that this automation is third in the list?  and if so, can it be moved or do i need to recreate the others after this scheduled wake up automation? Here is a screen shot of what I mean.

[Karen_Williams]

These two automations that I have run off of DateTime_Diff but I only get the email once, at its creation.  It doesn't send me one as the days progress to zero.  Here is the formula in the field:

IF(DATETIME_DIFF({Deadline to Acknowledge}, TODAY(), 'days')>0, DATETIME_DIFF({Deadline to Acknowledge}, TODAY(), 'days'))

I attached the automation. Did I set something up wrong?

[TheTimeSavingCo · Accepted solution]

Ahh, thanks for the details!  I take it that you want one email per day the countdown is <=10?  If so, you're going to need to set up your automation differently, and I would try using "When record updated" instead of "When record matches conditions", and make it focus on the "Countdown" field:

And I would update the countdown field formula so that it would only output a number if it was >0 and <= 10 like so:

IF(
  AND(
    DATETIME_DIFF(
      {Deadline to Acknowledge}, 
      TODAY(), 
      'days'
    ) > 0, 
    DATETIME_DIFF(
      {Deadline to Acknowledge}, 
      TODAY(), 
      'days'
    ) < 11
  ),
  DATETIME_DIFF(
    {Deadline to Acknowledge}, 
    TODAY(), 
    'days'
  )
)

(You might want to make a new formula field which has a sole purpose of powering that automation so that you can see the normal countdown)

---
The reason you only got the email once is because "When record matches conditions" only triggers once for every true value it gets.  And so the formula field going to 9, 8, 7 etc has no effect since they're all <=10, i.e. true.  If you updated the deadline to > 10 days, and then updated it again so that it was <= 10 days, then the automation would trigger again, does that make sense?

And so to get around that we just create a formula field that will only ever update itself if the countdown is >0 and <=10, and trigger the automation whenever that formula field updates

Link to base

[Karen_Williams]

That is perfect.  But one question, the countdown needs to stop once another date field is filled in.  For example, I have a deadline of 10 days to complete a task.  I want a reminder each day until I can complete that task with 10 days.  Five days before the deadline, I complete the task and enter the date of Task Completed.  I no longer want to receive emails because the task is completed. Is that an easy add to the formula?

[TheTimeSavingCo]

Oh yeah for sure, just add a check into your "AND" to see whether that date field is empty:

IF(
  AND(
    DATETIME_DIFF(
      {Deadline to Acknowledge}, 
      TODAY(), 
      'days'
    ) > 0, 
    DATETIME_DIFF(
      {Deadline to Acknowledge}, 
      TODAY(), 
      'days'
    ) < 11,
    {Task completed on} = ""
  ),
  DATETIME_DIFF(
    {Deadline to Acknowledge}, 
    TODAY(), 
    'days'
  )
)

[Karen_Williams]

I was able to get the email reminder when the number of days remaining changed but it changed at 5:00pm PST and I found out that AirTable uses GMT time zone.  How do I add to it that I want the update to happen at 7:00am PST?

[TheTimeSavingCo]

Hmm...I think you could try calculating the hour difference between 12am GMT and 7am PST, and using DATEADD in your formula?  Should work theoretically and I've just set up my own version of this automation to test it out, will let you know!