Delay email automation

[James_Carringto]

Does anybody have a good workaround for delaying email automations for (example) 10 minutes?

Our situation:

• When a project goes over budget, an email is sent to the project manager

But:

• the email will go immediately, which is a problem, because the project manager might simply be in the process of updating the record, and once he’s finished with his updates, the project might not actually be over budget. Therefore, we are getting tons of false notifications.

What’s needed:

• Some sort of pause before the notification is sent out, where Airtable checks to see if the situation is still true for that record, before sending the notification.

[Databaser]

Hi @James_Carrington

Maybe use a “last modified time” field + formula that adds 10 minutes and gives a “send email” message?

[Alyssa_Buchthal]

Yeah this has been a pain for me. I typically use a formula field like @Databaser suggests, but I have it act as a bool that returns true when the allotted buffer time has passed. Then the automation fires once that bool is true (and if the budget condition you described still matches). If not then nothing is sent! I use this in a couple scenarios to ensure no false notifications go out since AT has no built in delay-and-re-check feature.

[Karlstens · Accepted solution]

I have a view that filters for a Date Time field, and also a calculation field that sets to “1” ten minutes after the Date Field time, using this formula;

IF((DATEADD({setDateTime}, 10, 'minute')) <= NOW(), 1
, BLANK())
, BLANK())

If setDateTime isn’t utilised, the calculation remains blank. When the calc strikes one, the record enters a view that is monitored by an Automation. Records remain in the view for a week for staff to review, then they are filtered out.

The one bug with this method, is that if the user enters in the current Date/Time - but then, back-spaces to alter the time, it will unintentionally trigger the calculation. I’m not too sure how to prevent this problem… not without another field. :thinking:

The delay isn’t perfect however, and my experience is inline with the discussion here;

NOW() function is not refreshing properly Formulas

My nightly update automation ran as planned, so I don’t think anything has changed.

[kuovonne]

Karlstens:

if the user enters in the current Date/Time - but then, back-spaces to alter the time, it will unintentionally trigger the calculation. I’m not too sure how to prevent this problem… not without another field. :thinking:

Automations trigger as soon as the record meets the conditions, even if it met the conditions only briefly due to users entering partial, incomplete, or immediately changed data. This is why when an automation depends on user input, I recommend having a separate manual trigger, such as a single select field that the user sets to indicate that data entry is over.

You might be able to get away with adding a dateTime comparison with the LAST_MODIFIED_TIME({setDateTime}) however, that just leaves you guessing how fast the user types combined with how long it takes for NOW() to update, and that is a loosing battle.

BTW, your formula looks a little funky. Did you accidentally paste the line with BLANK() twice?

[Justin_Barrett]

kuovonne:

You might be able to get away with adding a dateTime comparison with the LAST_MODIFIED_TIME({setDateTime}) however, that just leaves you guessing how fast the user types combined with how long it takes for NOW() to update, and that is a loosing battle.

I’ve used LAST_MODIFIED_TIME() comparisons on multiple occasions to build automation triggers, and have never run into issues. My trigger formulas typically look like this:

AND({Field To Watch}, NOW() > DATEADD(LAST_MODIFIED_TIME({Field To Watch}), 5, "minutes"))

This ensures that the field actually contains data, and that data was last updated more than five minutes ago. If both conditions are true, the formula will output a 1; otherwise it outputs zero. The datetime returned by LAST_MODIFIED_TIME() updates with every saved change to the field, which means multiple changes for even the simplest thing the user might type. That constantly pushes out that five-minute endpoint, so I don’t even think about how often NOW() is updating or how fast the user types. Both are irrelevant because the target is five minutes away from their last edit.

[kuovonne]

Justin_Barrett:

I’ve used LAST_MODIFIED_TIME() comparisons on multiple occasions to build automation triggers, and have never run into issues.

I’m glad you have never run into issues. Your system makes a lot of sense.

Also, thank you for typing up the formula for the comparison with LAST_MODIFIED_TIME()

I still don’t know when I’d feel comfortable using this system.

• I still occasionally get interrupted when I am in the middle of something and have to immediately drop everything for several minutes. (This actually happened as I was typing this post. Maybe this is a weird personality trait that other people don’t have.)
• I hate having to wait 5-15+ minutes for an automation to run. (Again, this might be a personality flaw.)
• NOW() is a resource intensive function and can slow down a base.

[Justin_Barrett]

kuovonne:

I still occasionally get interrupted when I am in the middle of something and have to immediately drop everything for several minutes. (This actually happened as I was typing this post. Maybe this is a weird personality trait that other people don’t have.)

I have that happen on occasion. I suppose that if this system was looking at a field that required so much typing that one could be interrupted and taken away for an unknown amount of time, I might not use it. However, most cases where I do end up using this are for simpler data points like dates. I don’t want the automation to trigger in the middle of entering a date, and a date can be typed or selected in just a few seconds so its entry wouldn’t likely conflict with an interruption of unknown length.

kuovonne:

I hate having to wait 5-15+ minutes for an automation to run. (Again, this might be a personality flaw.)

I’m somewhat the same. If I need the result of the automation right away, then I might be inclined to look for a different solution. I typically only use a system like this in cases where the delay doesn’t stop me from moving forward with other things; i.e. I need the automation to run, but not necessarily right away.

kuovonne:

NOW() is a resource intensive function and can slow down a base.

Perhaps my use cases so far haven’t included the right criteria, but I have yet to see a case where I can point to the NOW() function as the reason that a base isn’t sufficiently performant. Considering that NOW() updates so infrequently, I’m not sure how it can be considered resource intensive. I’m curious to know how you came to this conclusion. I think I’ve seen you mention this before, but I don’t recall if it’s coming from specific tests that you’ve run or from an outside source.

[kuovonne]

Justin_Barrett:

Perhaps my use cases so far haven’t included the right criteria, but I have yet to see a case where I can point to the NOW() function as the reason that a base isn’t sufficiently performant. Considering that NOW() updates so infrequently, I’m not sure how it can be considered resource intensive. I’m curious to know how you came to this conclusion. I think I’ve seen you mention this before, but I don’t recall if it’s coming from specific tests that you’ve run or from an outside source.

My comments about how formula fields affect base performance are not based on specific tests that I have done. I don’t do much benchmarking personally.

This community post has a link to a blog post that about testing. Those tests have interesting results relating to using the API and complex chains of computer fields.

The NOW() function isn’t mentioned in that article, but I had a client with a very large base that told me that they had done some testing and discovered that formula fields that used NOW() were responsible for far more computation time than any of their other formulas. This also makes sense. Most formulas only recompute when their inputs change. However formulas with NOW() recompute repeatedly every day, even for very old records that have no other inputs changing. If a table has several tens of thousands of records and poor filtering, that formula with NOW() is going to be doing a lot of recalculating.

If your base is small, you probably wouldn’t notice the resources that NOW() is using. But that little bit can eventually add up. It’s a death by a thousand cuts.

I still use NOW() and TODAY() when needed, but I am cautious with their use.

I have also created some very long chain formulas that take a very long time to compute. Most formulas update results instantly, but changing the inputs to these formula chains (lots of text processing and back and forth rollups) would take several seconds to completely update, and I could watch the updates trickle in.

Formula performance certainly can affect base performance. It might take a large base with complex formulas for the effects to be obvious, but the effects are there even if they are not obvious.

[Karlstens]

That’s a great example, thanks for sharing. I like it a lot.

I’m on the fence as to whether I’ll implement NOW() formulas, instead opt’ing for a staged solution to send an email automation, where 3 fields need to line up as true and then the email is sent.

Perhaps an improvement for Automations, is Airtable devs could create a “Wait” action - where the automations could trigger, then Wait for a designated amount of time (5 minutes), and then re-check if a condition (either the original trigger or a new condition) is true and the trigger subsequent actions, such as an email.