Delay email automation

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.

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

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.

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

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.

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

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.

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

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!

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

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.

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

---

@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! 

@Justin_Barrett thank you man! This has been one I have been trying to solve forever. 

Hi - thanks for this.  Been having the same issues and racking my brain.  What would the formula look like if I need to monitor a change to more than one field? Not all the fields would necessarily be changed, but only if something is modified in one or all of them?  So if it takes one of my team a few minutes to update each field, then the automation waits 5 minutes to kick off the email notification?

@TWarwick The LAST_MODIFIED_TIME() function can accept more than one field reference. Changes in any or all of the listed fields would cause it to output a new modification time.

thanks Justin.  I think I figured out the multiple field reference, but the automation is only firing sometimes. If a new entry in recorded in those fields (we submit through a "form" interface) the automation runs but if a modification is made in an existing entry, no email is sent.

So here's the formula I have

@TWarwick The problem that I see is that all of your logic is wrapped up in the OR() function. Here's why that's not doing what you want.

OR() will output True or False if any of its expressions is equivalent to true; in this case, any of those two fields being filled or the time comparison being true. The problem with this design is that as long as one of those two fields has data, the function will always output True, regardless of the time comparison. Most OR() function logic is designed to immediately return True the moment that it finds a single expression (sequentially, first to last) that is equivalent to True, and ignore the rest. Therefore, if either of those first two fields contains data, the function will return True and not even bother with the time comparison.

The reason that this always-True output is a problem is because fields used as automation triggers need to "reset"—output some value that does not meet the condition of the trigger—in order to trigger the same automation again later. In your case, the field needs to output False—or some false-equivalent value—to reset the trigger. However, if that OR() function always outputs true because either of those fields contain data, then it will never possibly output False until both of those fields are cleared, at which point the output would be based on the time comparison.

The way to make this work is to have the time comparison be the only thing driving the output value. You can still require those fields to be filled, but it just needs to be structured differently:

That formula basically reads like this: if either of those fields is filled, output True/False depending on whether the current time is 3 minutes past the most recent modified time of either field.

thanks very much Justin.  This worked!   Additionally, AT Support confirmed they requested this feature based on our community feedback.