Using time-based formulas to trigger automations

[Nils]

I am collecting survey results into an airtable base.

This includes two tables:

1. The index for all surveys (and including key information such as deadline).
2. The responses for the various surveys (each response is linked to a survey)

Each survey has a deadline (time field deadline), when it should stop accepting responses. A this point, the survey and its results should be transfered into a new view of each table that then syncs to another base.

I have created an IF formula, that (all dates are set into the same timezone: CEST) switches to "transfer", once NOW() is larger than the deadline date (which is CEST is 2am). For the responses, I add 5 minutes to the deadline to ensure that the survey has already been synched.

If I look at the CREATED_TIME() for the entries in the synched table in the other base, all entries have been created at the same time (i.e. the delay added to responses does not seem to have been accounted for).

Can anyone advise how airtable recalculates time-based formulas, if a base is not accessed at this time? I would want to rely on this automation to trigger throughout the night and not manually.

Thank you for your advise!

[ScottWorld · Accepted solution]

Unfortunately, this is not very well-suited for Airtable’s native automations.

Airtable recalculates time-based formulas approximately every 10 minutes or so, but it stops evaluating formulas altogether if your base isn’t being actively used by anybody. Then, the base will go to sleep. I’m not sure how long it takes before a base goes to sleep.

You can “wake up” a base at a particular time by creating some meaningless automation that triggers at a particular time (such as 9am every day or every 15 minutes), but the problem is that the base will go to sleep again shortly thereafter, and you could end up using up a lot of your monthly automation runs to keep waking up your base.

My top recommendation for creating automations that need to fire reliably on a schedule — with no additional fear of running out of automation runs — would be to use Make’s automations instead of Airtable’s automations.

In Make, you would start your automation with a “Search Records” module, and put it on a schedule.

There can be a bit of a learning curve with Make, which is why I created this basic navigation video for Make, along with providing the link to Make’s free training courses. There are also many Make experts hanging out there who can answer other Make questions. 

[Nils]

Two "wake up" automations per day = 60 per month should be definitely feasible. 

Thank you!