Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Delay email automation

Topic Labels: Automations
Solved
Jump to Solution
1322 16
cancel
Showing results for 
Search instead for 
Did you mean: 

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.
1 Solution

Accepted Solutions

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;

See Solution in Thread

16 Replies 16

Hi @James_Carrington

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

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.

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;

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?

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.

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.

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.

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.

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.

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.

Hi @Justin_Barrett 

Can you please help me creating this:

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.

Right now I have an email automation that fires too quick, so the emails that are being sent, are w/o information. How would I do the above mentioned? Do I need to make a new Field in the table? How does the automation look like? 

Thanks a lot in advance!

Of course if anybody else can help me out, they are more than welcome 🙂 

@kuovonne @Karlstens 

I'm not sure I understand the updated problem at hand - you've mentioned that the 5 minute timeout formula is working - doesn't' this then trigger the automation that contains the record data that was entered 5 minutes ago?

Hello @Karlstens 

Thanks for getting back. 

I haven't done anything yet. I just read the reply from Justin, but don't really understand the formula - and how the Automation should look like. 

Fx. in the formula; does anything comes before 'AND'? And which field should I insert in 'Field to watch'? 

You'll be best to step back from these forums and have a crack at finding your solution with the tips and advice already provided.

As with all things programming, break bigger problem down into lots of smaller but easier problems - and repeat that until you start solving those smaller problems, and once you've solved all the smaller problems you'll find that the big problem is also solved.

Post some screen shots of your progress in a couple of days so we can check in with your progress.


@plyske wrote:

Right now I have an email automation that fires too quick, so the emails that are being sent, are w/o information. How would I do the above mentioned? Do I need to make a new Field in the table? How does the automation look like? 


You didn't specify how your automation is currently designed, but my guess is that you're using the "When a record is updated" trigger, which is likely the core of the issue. Even the smallest change to any field counts as an update as far as that trigger is concerned, and this is likely why the email is being sent without any info.

First off, you'll need to add a formula field and add that formula that I mentioned, changing it to reference the specific field that you want to use to drive the automation. For example, if your table contains a "Comments" field and you want to wait at least 5 minutes after that field has been edited, you'll replace both instances of {Field to Watch} in the example formula with Comments.

Now change the trigger on your automation to "When a record matches conditions," with the condition being that the value from the formula field you just created equals 1. That will only be true 5 minutes after you finish editing the chosen field; the rest of the time it will be 0. (NOTE: the value will stay at 1 long after the 5-minute delay has passed, but that's not a problem. The automation will only re-trigger on a given record when the value returns to 1 after being reset to 0 when the chosen field is edited again.)

With those changes made, your automation should wait at least 5 minutes after the last edit in the specified field before it fires and executes your desired actions.

@Justin_Barrett Thanks so much for your very helpful and detailed explanation! It works like a charm!