I’m more careful in my use of the NOW() formula after reading that it takes a lot of processing power, since it is constantly running to update the value of now. But when you do use it, does it matter how many times it is used in a formula? For example, to trigger a status change within a 5-minute range after a record is modified, you could evaluate when
DATETIME_DIFF(NOW(),LAST_MODIFIED_TIME(),'minutes')>0 and DATETIME_DIFF(NOW(),LAST_MODIFIED_TIME(),'minutes')<6
This uses two NOW() functions. Or you could use a Switch statement which calls NOW() once, and outputs the same outcome:
NOW() is so far off from being accurate that these formulas aren’t going to be useful. Even when a base is open, NOW() can be off by 5-10 minutes, which makes such small intervals very precise but not very accurate.
In a large table, I would try very hard to avoid use of NOW() at all. And for the formulas that you’ve shown, I don’t think they will be useful at all.
Why do you need to know what has been modified in the last five minutes?
Thanks @Kuovonne. I’m setting up a Slack notification for specific changes to a record. One of the fields that I’m watching is a text field, so I need to delay the trigger. Otherwise, the notification could go out while someone is in the middle of typing. Is there another way to do this? Airtable actually has a page on delayed notifications and they use the NOW() formula in their suggested system. It also compares NOW() with operators, which I think I saw you or Bill flag as very processing intensive too…
Yeah, it’s a total guessing game to try and figure out when someone is done typing. I wouldn’t use NOW() for that. I would use a workflow when the user does something else to indicate that data entry is complete, like selecting a checkbox, setting a single-select, or pressing a button.
I agree with @Kuovonne’s assessment of this. The way that I do this for my clients is to give them a single-select field that is right next to the text field, with a list of options to choose from. For example:
Send Slack Notification Send Email Notification Create Proposal in Microsoft Word Create Invoice in Microsoft Word etc.
Sometimes I create multiple different single-select fields, depending on the client’s workflow.
Now granted, this DOES require the user to manually be involved with triggering their own automation, but it prevents all the other problems from happening.
In this case, users making edits are not the same users who care deeply about getting a notification when there’s a change, and could easily leave a checkbox unchecked. So you’re up against the unreliability of a user vs. the unreliability of the NOW() function. You could require a checkbox in an interface or a form, but not in a grid
I think of these single-selects as “Action” single selects. I typically have a single automation that watches for when the single select has a value. The automation has a conditional actions for each choice. The automation also resets the value of the single-select to blank so that a new action can be taken later.
Without relying on a user, I guess you could have a scheduled automation run a Find Records action once every half hour. Then you still might catch someone in the middle of typing though.
For example, one workaround is to trigger the automation when the field has a value, but then put the delay in the automation itself. Then do a fresh read of the text value with a “Find records” action, and send the result of the find.
To get the day, you use scripting to put in a delay of up to 30 seconds, and chain as many of these delays as you need. So, for a five minute delay, you would need 10+ scripting actions, probably with a deal of 29 seconds to avoid erroring out.
Of course they are different people. The people making the change don’t need a notification because they already know there is a change, since they are the ones making it.
On the other hand, do they care about doing their jobs right? If they do care, then usually training is okay. If they don’t care (and plenty of them don’t care), you have to resort to other tricks.
Yeah, it is a matter of how quickly do the people need to get the notifications, and how many notifications will they need to get? If they would likely get only a few notifications, polling every half hour is a waste of automations. If they will get lots of notifications, triggering individual might result in an overabundance of notifications. Workflow also matters if they want to keep each issue in its own thread.
This seems like potentially the best solution in terms of reliability, if the scripting action can accurately time it. What’s the function that you would use to do this?
The script that introduces the delay is pretty accurate. It can be done in a few short lines, and there are a few different versions. I sell one version in my Automation helpers. You can also google and find various versions. It’s a pretty simple script.
I might add this technique to my Guide to Scripting, if you are willing to wait.
Yes. I actually take this one step further. The single-select will change to the word “Done” (in green) if it was completed successfully, and it will change to the word “Error” (in red) if there was a problem.
These options are at the very bottom of the single-select list, separated from the actions by a blank option that is colored in grey.
Not to derail Julian’s thread, but I like to have a separate text field for reporting errors or other feedback from the automation so that I can be more descriptive. I usually have a datestamp as part of the text field that is set by the automation so people can see the result of the most recent automation run. Depending on the situation, I either clear the message field for each run, or I just tack on to the top of the text field so it is an ongoing log.
Um, isn’t there a third option? Event trigger? I assume this is for a customer with an enterprise instance? If so, isn’t this exactly what you need to avoid all this crap?
As I read through the threads, I assume you are jousting with the definition of a fully changed field - i.e., a field that was changed and committed such that no more changes are being typed, right? It’s a timing issue and it’s because Airtable doesn’t expose a method for knowing the field edit has been committed.
The Enterprise events API does support that definition, making it possible to trigger a webhook when, and only when, the timing is not in question; the record has been changed and committed.
