[SOLVED] Using the comparison between date&time field and NOW()

[Vadim_Ciobanu]

Hi there!

I want to send reminders on collaborator emails. I build a table where they create reminders for themselves. The email has to be sent at the selected time.

I try to use automation trigger “When record matches condition” or “When record enters view”.
In the “Reminder” table I have a “Date&Time” field and a field with formula: IF({Date&Time} <= NOW(), 1, 0).

I noticed what the formula field is not updated instantly when the condition is true.

QUESTION: How often is it updated? Is it a best practice to use such solutions?

[Justin_Barrett · Accepted solution]

Formula triggers based on the NOW() function are doable (I’ve been using one for about eight months to refresh my planning system daily) but the documentation for that function states:

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.

In other words, the timing might be precise, or it might be delayed. In my experience, it’ll be on the nose for a few days, then it’ll be nearly an hour late on one day. It’s impossible to predict. It’s also worth noting that NOW() is calculated based on GMT, not your local timezone, so you might need to adjust the formula. In my case, I want my daily automation to trigger around midnight. My timezone is GMT-8, so my formula is:

HOUR(NOW()) >= 8

You can also nix the IF() function around your date comparison because the comparison itself will return 1 or 0 (true or false) appropriately.

{Date&Time} <= NOW()

Vadim_Ciobanu:

Is it a best practice to use such solutions?

If you want precise timing, no. Unfortunately that’s not currently possible when using the NOW() function to drive the automation. Even when using something like Integromat, it won’t check more frequently than every 5 minutes, but it might be more reliable than an automation solution for now.

[ScottWorld]

Justin_Barrett:

Even when using something like Integromat, it won’t check more frequently than every 5 minutes

Integromat can check every 1 minute, depending on what pricing plan you are on.

Justin_Barrett:

but it might be more reliable than an automation solution for now.

Yes, this is true. Of course, it comes with an extra cost (unless you’re on their free plan, which has a limited number of runs).

Although the bigger problem with Airtable is that if you run out of runs, you’re completely stuck until the next month. You can’t purchase more, so all your automations are dead until the next month.

[ScottWorld]

BTW, the screenshot below exemplifies how “all over the place” Airtable’s NOW() function is. Although this IS working as advertised.

Check out the creation times of each of the records below. All of these records were scheduled to be created at 12:00am, and they kicked in anywhere between 18 minutes and 45 minutes later.

However, they DID all kick in within the 1-hour time frame that Airtable promised for a closed database, so this DOES work as advertised.

But yeah, for absolutely precise scheduling down to the minute, you’d have to go with Integromat.

[Justin_Barrett]

ScottWorld:

Integromat can check every 1 minute, depending on what pricing plan you are on.

Thanks for the follow-up on this. The last plan I was on (before reverting back to free) had a five-minute interval, but I forgot that higher plans allow more frequent checks.

Here’s a look at my most recent runs of the daily update automation that I mentioned above:

I usually leave Airtable (the desktop app) open overnight, which may be why the times tend to be closer to the target.