How to schedule an automation?

[LukaszWiktor]

I wanted to share what I figured out looking for a way to run an automation at a scheduled time.

First, create a helper table named Automation Trigger with 3 columns:

1. Name (type: Single line text)
2. Run At (type: Date, with time field included)
3. Record (type: Formula, expression: RECORD_ID())

For each automation you want to schedule:

1. Add a row with a unique descriptive name
2. Add a view with a filter matching only the one record added above (Name is …)

Add an automation:

1. Choose trigger: When record updated
2. Select table: Automation Trigger
3. Select view: the one matching your automation intent
4. Select the Run At field.
5. Run test
6. Add an action according to your needs

Finally, the scheduling piece. You’ll need Integromat or Zapier. Zapier allows to schedule a task only at a selected hour. Whereas Integromat is much more flexible. For example, you can schedule an action at regular intervals.

1. Connect Integromat to your Airtable account
2. Select Update a record action
3. Select your base and the Automation Trigger table
4. Copy a Record Id from the row in the Automation Trigger table that is matching your automation intent
5. In the Run At field, add a formula now

That’s it. Integromat will update the record in the Automation Trigger table at the specified schedule what in turn will trigger your automation in Airtable.

I’m looking forward to your feedback. Is there a way to simplify this solution?

[Justin_Barrett]

Thanks for sharing! To answer your question, this can be simplified quite a bit. I have a couple daily scheduled automations that run every day at midnight. The setup only require a single formula field, and no extra services. The formula is:

HOUR(NOW()) = 7

That will return a 1 every day at midnight (my timezone is GMT -7, and NOW() calculates based on GMT, so 7am GMT is midnight for me), and 0 at all other hours. That triggers an automation that uses the “When record matches conditions” trigger, with the condition being that this formula field contains a 1. At midnight, the automation triggers and does its thing. An hour later, the formula output changes to 0, and it’s effectively reset until the next day.

With some minor changes, and maybe an extra field or two depending on how complex you want to get, this could be altered to run on any other desired interval, including having a different interval for each record, and it could still be done completely inside of Airtable.

[LukaszWiktor]

Thank you so much @Justin_Barrett for your suggestion! Indeed, using NOW() eliminates the need of an external system to schedule an automation.

Although, I noticed that the output of NOW() isn’t updated every minute, but rather every 5 minutes or so. It’s fine when you want to run an automation at a specified hour, but it doesn’t work for a more fine-grained scheduling. For example, I wanted to run an automation every 15 minutes so I used the following formula MOD(HOUR(NOW()) * 60 + MINUTE(NOW()), 15), but it never produced 0 as result.

[kuovonne]

I’ve created a proof-of-concept automation based off NOW that ran approximately every 15 minutes. My control table had a date/time field for the {desired run time}, and a trigger field that says whether the desired run time is on or after NOW. When the trigger field changes, I ran an automation that included a script that updated the {desired run time} to the next quarter hour after the current time.

When I tested the automation for a few hours with the base, it rarely ran exactly on the quarter hour but usually ran within 10 minutes of the quarter hour. I don’t remember how close to the quarter hour the automation was when the base was closed. It also appears that Airtable may be tweaking how NOW works behind the scenes.

Keep in mind that this will eat up a lot of automation runs. Running the automation every quarter of an hour would be close to 3K runs per month. This doesn’t account for any other automations that you might want for changes to actual data.

[Justin_Barrett]

kuovonne:

I’ve created a proof-of-concept automation based off NOW that ran approximately every 15 minutes.

I did a similar test a week or two ago, and it averaged closer to 10 minutes for me. I’m not sure why there would be a difference between users, but I’m sure there’s some technical explanation for it.

kuovonne:

My control table had a date/time field for the {desired run time}, and a trigger field that says whether the desired run time is on or after NOW .

@LukaszWiktor That’s the system I would lean toward if you want more precise(ish) timing. Instead of looking for an exact match (which will be tricky with update intervals that can vary widely), design the formula to output 1 when NOW() is later than the target time. You can probably still get 4 triggers per hour, even though they won’t be precisely on increments of 15 minutes.

To set the 15-minute interval for comparison, I would add a formula field named {Next Run Update}, using this formula:

DATEADD({Desired Run Time}, 15, "minutes")

Add an “Update record” step to your automation that copies that formula’s output into the {Desired Run Time} field.

However, @kuovonne makes a good point about possibly burning through your monthly automation run allotment that way. See the bottom of this page for the quotas for each workspace level:

Airtable - Support

Automations Overview

Join a live training! Power up your workflow   Overview Automations allow you to configure custom trigger-action workflows directly within your Airtable base. This will allow you and your team ...

One possible way to save some of those automation runs is to only trigger the automation during certain hours of the day (if that would work for your use case). For example, to only have the automation trigger between 9am and 5pm, I could use this (with the hours again adjusted by 7 hours for my local time):

AND(
    HOUR(NOW()) >= 16,
    NOW() >= {Desired Run Time}
)

16:00 in GMT is 9am here. Because 5pm here equates to midnight in GMT, I can leave off the second condition because the hour will roll back to 0 at midnight, and that formula will only return a 1 when both conditions are true.

[kuovonne]

Justin_Barrett:

To set the 15-minute interval for comparison, I would add a formula field named {Next Run Update} , using this formula:

I considered this method, but decided to opt for the scripting method of calculating the next update time instead. Suppose NOW takes longer than 15 minutes to update? Then the next automation won’t ever get triggered because the field value won’t be updated.

Example:
The desired run time is 10:00, and NOW is 9:59, so the trigger field is blank.
NOW updates only periodically, and for some reason, NOW doesn’t update until 10:16.
The trigger field realizes that the desired run time of 10:00 is before 10:16, so it updates its value to 1.
The automation runs and does its thing, including copying over the next desired run time, which is 10:15.
However, the trigger field remains 1 because it is still after the new desired time. It never flips back to blank.
Because the trigger field never flips back to blank, it never is triggered again.

With a script, the script can calculate the next desired run time to ensure that there is enough time for the trigger field to become blank before triggering again.

---

I might be overengineering things, and this option might be out of reach of those who are not comfortable with scripting (or hiring someone to write a script), but I thought it was worth mentioning.

[Justin_Barrett]

kuovonne:

Suppose NOW takes longer than 15 minutes to update? Then the next automation won’t ever get triggered because the field value won’t be updated.

My earlier test ran for several hours, and the longest delay between NOW() updates was 12 minutes. That said, having the automation set the next update time would definitely be safer.

[Shae_Redding_Ro]

Here I am trying to figure out what to automate and you guys are automate your butts off :rofl:

[Mariusz_S]

I’m experiencing some weird behaviour.
In my table I have a “time diff” field like this:
IF({Date}!="",DATETIME_DIFF(Date,NOW(),'minutes'),"")

and a second formula to pull the records into a specific view that should trigger the automation
IF({Date}!="",IF({Time Diff [mins]}<1,"✅","❌"),"")
But the it only runs when I go into that view. Not really sure what’s causing this?

[kuovonne]

@Mariusz_S How long are you waiting when testing your system? If you were waiting only a few minutes, you might not have been waiting enough. The NOW() function does not update continuously. Per the documentation
:

This function updates when the formula is recalculated, when a base is loaded, or otherwise roughly every 15 minutes when a base is open. If the base is closed, it will update approximately every hour only when the base has time-dependent automation triggers or actions, or sync dependencies.

Going into the view may have triggered the formula with NOW() to recalculate.