Mar 08, 2022 05:11 AM
Does anybody have a good workaround for delaying email automations for (example) 10 minutes?
Our situation:
But:
What’s needed:
Solved! Go to Solution.
Oct 06, 2022 06:39 PM
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;
Mar 08, 2022 06:32 AM
Maybe use a “last modified time” field + formula that adds 10 minutes and gives a “send email” message?
Mar 10, 2022 07:48 AM
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.
Oct 06, 2022 06:39 PM
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;
Oct 06, 2022 07:22 PM
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?
Oct 06, 2022 09:58 PM
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.
Oct 06, 2022 11:41 PM
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.
NOW()
is a resource intensive function and can slow down a base.Oct 07, 2022 07:12 PM
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.
Oct 07, 2022 08:09 PM
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.
Oct 20, 2022 02:22 PM
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.