Skip to main content

I’ve got a base with an “Event Finished” formula field that works like this:

IF(DATETIME_DIFF({End}, NOW()) < 0, TRUE(), FALSE())

It resolves into a checkbox, and I use it to filter out finished events from admin list views. My base currently sees about ~15-20ish events a day, which have times attached, and the folks that need to monitor the views have told me they find it really annoying when finished events aren’t filtered out of a given list view.

And it works fine! I could be using IS_AFTER rather than DATETIME_DIFF, but otherwise, it does the job. However, I know NOW is a function to avoid. Its inaccuracies aren’t a big deal to me (if the formula clears out an event 15 minutes later than it should have, that’s not the end of the world), but its computational cost is becoming more of an issue. I assume NOW continues to recompute for every record, even those that are well beyond completed at this point. I’m trying to optimize things so NOW gets called less, so here are some questions:

  • Does NOW not compute if it’s behind a conditional branch in a formula that isn’t reached? If, once an event completes, I were to automate setting a single select to “Completed,” and then change the formula with IF(Status = “Completed”, TRUE(), {{ existing formula }}), would that mitigate how much NOW is called?
  • Is TODAY just as much of a process hog? If TODAY isn’t the same calendar day as the event record yet, I’d prefer to be able to say IF(IS_BEFORE(TODAY, {End}), FALSE(), {{ existing formula}}), so that NOW is only in use when checking the time on the day of.
  • Other ways of doing this I haven’t considered? Would an hourly (half-hourly?) automation that checks events for done-ness and marks them as such be less process-intensive than NOW? Manually flagging things to clear off the list is possible, but not what I’d prefer.

re: Is TODAY just as much of a process hog? If TODAY isn’t the same calendar day as the event record yet, I’d prefer to be able to say IF(IS_BEFORE(TODAY, {End}), FALSE(), {{ existing formula}}), so that NOW is only in use when checking the time on the day of.

 

According to the docs TODAY’s better?  Not sure how much better tho

https://support.airtable.com/docs/troubleshooting-airtable-performance?utm_source=forethought-chat#can-i-resolve-or-prevent-base-performance-issues

re: Other ways of doing this I haven’t considered? Would an hourly (half-hourly?) automation that checks events for done-ness and marks them as such be less process-intensive than NOW? Manually flagging things to clear off the list is possible, but not what I’d prefer.

 

Yeap that’d make sense.  I don’t think you have hourly granularity within an automation though, so I think you’d be doing a daily one with ‘Event date is before today’ maybe?

Hey ​@thelus,

If you are concerned about base performance, then you might want to assess having the “heavy-lifting” done by a third party tool such as Zapier, Make or n8n (more on these automation tools here).

As I see it, you could have an n8n workflow running every five minutes, and fetching those events which meet certain condition, or all events and then filtering for those which match the Now() condition. But in this case, the assessment of  IF(DATETIME_DIFF({End}, NOW()) < 0, TRUE(), FALSE()) would be done by n8n and not by Airtable. You would then update, using the n8n workflow, those records which match that condition.

Would this make sense? I might be missing smth!

If you need any help setting this up, feel free to grab a slot using this link. I’d be happy to go through it together.

Mike, Consultant @ Automatic Nation 
YouTube Channel

​@TheTimeSavingCo I wound up doing option #3, after realizing the automation to do it is way easier than I thought. With an “At a scheduled time > 30 min interval” action, I pull all the records in a view that’s filtered to show records for today only that don’t have an “Is Completed” checkbox checked. Looping through those records and running them against this Script action is just about the easiest Script action there could be:

const config = input.config()

const done = config.dateEnd < config.now
output.set("Is Done", done)

...where “now” is the “Expected trigger time” of the automation, and “dateEnd” is the end of the event. Finally, update the “Is Completed” checkbox of each record with whatever the value of “Is Done” in the script is.

Terms & Conditions