I have a handful of automations that run when a cell is changed (i.e. calculate a commission based on a commission table). It is extremely slow, sometimes up to 10 seconds to display the output in the result field. It seems that while typing the initial number in the first field, it instantly beings to run calculations in the automation. If someone types values in very quickly in multiple rows, it seems it can’t keep up, and we get some rows that don’t save correctly. AS if it’s calculating on every number they type in, regardless of whether they’re finished (tab to next row). For example, I type in Field 1) 100, 1000, 2000 (in three separate) rows to calculate a commission of 10, 100 or 200 in Field 2) in the corresponding row. Row 1 may show $100 in Row 1, but it calculates commission base on only $10 (missing the last zero). Row 2 may work, but row 3 may calculate 20 in Field 2 instead of $200. I can’t figure out how to get it to ONLY calculate once the entry in Field One is “saved” or completed. I don’t want the automation to trigger 3 times if the number value is 3 numbers long, which seems to happen.
It sounds like you are using the “when updated” trigger. According to Airtable, the record is updated for each character that is entered, not just when you are done typing in the field. You can see this same behavior when a formula field recalculates with each key press.
You need to adjust your trigger. For example, you can trigger the automation with a check box field that you click when you are done typing the number.
If you are comfortable with scripting, you can move the automation to a script that is triggered by a button field. This will help avoid running out of automations.
A script won’t work because our users can access them, making it insecure, and then I’d have to rewrite every automation which took days and days (I just got this system a few weeks ago). It doesn’t seem convenient for our users to click a checkbox for every field they enter, unless I can link a single checkbox to all of the 20 individual fields and then have that be the trigger for each of the separate automations that work to calculate the different field values. Is there a way within an automation script to manually call any of the other automation scripts via a function call?
I also struggled with this. I wanted a number field to ‘round to nearest 3’ on .blur, but it would round ‘13’ to ‘0’ because it starts when the field is ‘1’ (first character).
I ended up checking last modified time like @Olpy_Acaflo mentioned, but this column is fatally flawed. It starts in the negatives (as if it were modified in the future) and only updates every 5 mins. Quite useless!
That was exactly my problem, I thought of the lastModified immediately, but after using date difference calculations, the delay made it useless.
They say something about changing the trigger method, but none of them worked any differently, So we have to type slowly to allow the data to process correctly or sometimes the automation calculates based on the wrong value.
It is possible to trigger the automation based on a comparison of the last modified time and NOW(), even with its quirks, but the delays involved are usually too long for a seamless user experience. This is why I usually favor a button field that launches a script.
A script cannot call other scripts, but you can include all of the logic spread across other scripts/automations in a single script and have the script examine the record values to decide what to do.
A script cannot call an automation directly. A script can set field values that trigger an automation. However, at that point, you might as well have the script do everything. (There are a few exceptions, such as having an automation send emails or other third party interactions that Scripting cannot do.)
As for security, yeah, that is a weird difference between Scripting app and scripting automations. But considering that any collaborator can duplicate the base and see everything anyway, it isn’t much of a security difference.