I’ll probably make a video for my channel about this one of these days, but here’s the basic setup that I worked out after some testing.
In my task tracking base, I run an update every night at midnight that refreshes certain things on certain records. I originally had this running in Integromat, but wanted to see if the new Airtable automation could be set up to run on a schedule. It can, though it won’t run precisely at midnight. Since I’ve been using this new setup, it tends to run 6-8 minutes after midnight most of the time. Only three times has it run precisely at midnight, but I’m not picky, so the delay is acceptable in my situation.
The first time I set it up, it was running once on every record that needed an update. However, my once-a-day update also needs to reset certain things in a couple of other tables, so I changed it so that only one record actually triggers the automation, and the others are found and updated within the automation script itself.
Anyway, here’s the setup. I have a formula field with this formula, comparing the task’s due date against NOW(), and only on that single triggering record:
IF(Name = "Task Name", {Due Value} < {Now Value})
I use {Due Value} and {Now Value} in some other calculations as well, which is why they get their own fields. If those were expanded, the full formula would be this:
IF(Name = "Task Name", VALUE(DATETIME_FORMAT(Due, "YYYYMMDD")) < VALUE(DATETIME_FORMAT(SET_TIMEZONE(NOW(), "America/Los_Angeles"), "YYYYMMDD")))
In short, I’m formatting both the task’s due date and NOW() in the same way, converting them to numbers, and checking to see if the NOW() value is larger (i.e. later). By looking for the specific task name, this formula will only output a 1 or 0 on that single record: 1 if the record’s due date is earlier than NOW(), and a 0 otherwise.
I then have a view with a filter that will only show records where that field contains a 1. Around midnight-ish, the formula is recalculated behind the scenes, spits out a 1 because NOW() is later than the due date, and that one record pops into that view, which triggers the automation. The automation script does its thing, including updating the due date of that trigger record (it’s a repeating daily task, so it’s a good choice for a repeating daily trigger), which takes it out of that view until the next day.
With a little tweaking, this setup could be modified to trigger at other times. Want it to run hourly? Add “HH” to the end of both formatting strings. Want it to run hourly but only on weekdays? Add logic to the formula so that it only compares the values on weekdays. Ultimately, the frequency is set by the formula logic when comparing the date/time in a field against NOW(), and the automation script must update that field date/time when it runs in order to properly set up the next interval. However, as I pointed out above, it’s not going to be super precise because there’s no telling how frequently the formula will refresh. If you want a 9am trigger, it might not kick off until 9:07. Best to set the trigger time a little earlier.
