How to schedule an automation?

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.

HOUR(NOW()) = 7

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.

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.

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.

@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:

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.

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

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

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.

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.

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

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?

@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.

Thank you @kuovonne.
Wasn’t aware of the hourly updates when the base is closed.

HOUR(NOW()) = 7

Hi Justin,
I’m looking to do something similar, I want to run an automation every 2 hours, where every record that matches the conditions within the last 2 hours will be emailed as a grid.

Details are:
When field ‘notification type’ is ‘non-urgent’ (this is a single select field)
Send email including a grid of all non-urgent records.
I want this to run every 2 hours, and only send records that match the condition since the automation last ran. If no records match the condition

Are you able to help with that?

At the moment if I try run at a scheduled time, it will send an email of all records that match the condition, not just the ones that have been updated to match it since the automation last ran.

You didn’t mention what workspace plan your base is in, so I’m not sure if you can use scripting in your automations. I’ve got an idea for a way to pull this off without scripting, but it’s imprecise because it relies on comparing against the NOW() function. That function’s output only updates on certain intervals—roughly every 15 minutes if the base is open, and every 60 minutes if it’s closed—so it’s possible that some records would be missed.

If your base is in a Pro-plan workspace or higher, you could modify your “Find records” action condition to find records that are not marked a certain way—e.g. with a check in a {Notified} checkbox field—insert those records into the email, and then use a “Run a script” action to mark them so that they’re ignored the next time around:

const {recordIds} = input.config()
const table = base.getTable("Table Name")
const updates = recordIds.map(id => ({id, fields: {"Notified": true}}))
while (updates.length) await table.updateRecordsAsync(updates.splice(0, 50))