(maybe bug) "When record matches condition" trigger works wrong

[Alexey_Gusev]

Hi,

(TL;DR "it triggered on all new records, no matter if conditions are met, when they include formula field)

I have following issue:

table1 (near 1500 records) has button field “Confirm match”. when a key pressed, the record partly copied to table2 (by my script, ~500 records), linked to it’s “parent” in table1

table2 contains formula field “Control”, relying on some data including lookup from linked record in table1, result is “OK” or “_error”
“_error” records supposed to be removed, from time to time. I can’t filter at this step, because sometimes “OK” record in table2 may become “_error” after table1 update.

it’s usual process, and it worked as expected.

Now I decided to automate cleanup for table2 and setup new automation “When record matches condition”, second step is Remove record, where Control contains “error” (by way, I was surprised that Remove action is not available, i did that by scripting)

The problem is that trigger works on records, which supposed to be “OK”, while my condition is for “_error”. Automation log shows these records as “_error”.

in short:

When i disable automation, and press “Confirm”(my script, creating single record by updateRecordAsync), the record appears in table2 as “OK”

When I enable automation and press “Confirm”, record created for a few seconds (with “OK”), and then removed by automation, supposed it’s “error” record.

My point: that happens because formula calculation has a little millisecond lag, at first it counts as “error” and then “OK”. but during these milliseconds automation got it as target.
I understand many possible workarounds. Maybe i just query record again inside the removal script and check “error” or “OK”, but i think such automation behavior may cause serious issues for someone else.

[Justin_Barrett · Accepted solution]

Alexey_Gusev:

My point: that happens because formula calculation has a little millisecond lag, at first it counts as “error” and then “OK”. but during these milliseconds automation got it as target.

I don’t believe that this is a bug. The automation is triggering as designed based on the data that it sees. Because there is that slight period where the output is “_error” before it moves to “OK,” I recommend redesigning the automation that deletes those “_error” records.

One option is to do as you mentioned and double-check the record status before deleting, though that still means that you’re eating up automation runs for records that don’t need to be removed.

Another approach would be to add a formula field that outputs the number of minutes since the last change to the record.

DATETIME_DIFF(NOW(), LAST_MODIFIED_TIME(), "minutes"))

Adjust your automation trigger to look for two conditions: “_error” in the {Control} field, and a value higher than 1 in that formula field. Because the NOW() function only refreshes roughly every 15 minutes when your base is open (longer if it’s closed), this will introduce a sufficient delay so that records that eventually become “OK” will never accidentally trigger the automation.

Yet another approach is to avoid the instant trigger, and instead run a single automation on a schedule (daily/weekly/???) that runs a script to look for and delete all records with “_error” as the status. This will use fewer automation runs, which could be helpful if you’re looking to use automations for more things.

[Alexey_Gusev]

Well, i thought about such option - to perform some delay, as my case doesn’t require immediate action. I haven’t much experience working with date fields and formulas, so your suggestion is very helpful, thank you, that should work perfect for me.
I just thought about if somebody will try to implement kind of input data validation using regex formula with additional automation - if new record is “not OK”, remove it and inform creator. They might get into situation where all new records will be deleted, even if formula supposed to evaluate them as “OK”.