Help

Re: Schedule Automation to wake up base

Solved
Jump to Solution
2952 8
cancel
Showing results for 
Search instead for 
Did you mean: 
Karen_Williams
4 - Data Explorer
4 - Data Explorer

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?

3 Solutions

Accepted Solutions

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:

Screenshot 2024-03-20 at 11.11.06 AM.png

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

 

See Solution in Thread

Yea this appears to work I think.  I don't think we can get it to do 7am exactly though

What if we had an automation that'd run every day at 7am instead?  That automation's action would be to find records where Countdown < 11, and we'd use a repeating group to send one email per found record?

See Solution in Thread

Ah, I think you're talking about the order of the automations?  If so, nope, it doesn't matter!

See Solution in Thread

14 Replies 14

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

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

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.

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?

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:

Screenshot 2024-03-20 at 11.11.06 AM.png

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

 

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?

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'
  )
)

Screenshot 2024-03-21 at 10.59.43 AM.png

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?

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!