Trigger an automation when a cell goes from blank to filled

635 2
Showing results for 
Search instead for 
Did you mean: 

I want to trigger an automation based on the first instance of a cell, not on any follow up variables. Is there a way to do this?

2 Replies 2

What do you mean by ‘first instance’ and ‘follow up variables’? Generally here you can find all automation triggers in Airtable:

Welcome to the community, @DashiJador! :grinning_face_with_big_eyes: The kind of trigger you want is doable, but it’ll require some parts besides just the field itself.

First off, the quick-and-dirty way around this problem is to a) use a form for all initial data input, and b) use either the “When record created” or “When form is submitted” trigger. That will only trigger the automation once per record, and because it won’t fire until the record is fully created—which only happens after form submission—you’re all set. If that’s not an option, then it gets more complicated.

When it comes to direct data entry in something like a grid view, there isn’t an easy way to tell Airtable, “Trigger when I change the value of this field, but wait until I’m done typing.” By default Airtable considers the very first character that you type into a field to be a change to the record, meaning that the “When record updated” trigger on its own will trigger once for the first character, and then again every few characters after that (depending on how quickly you type vs how quickly it updates your data on the Airtable server).

The typical way around this is to use a formula field. This formula would compare the last time that the desired field was updated (using either a last updated time field or the LAST_MODIFIED_TIME() function) against the current time (using the NOW() function), create a specific trigger value once the difference between those times is over a certain threshold, and use that to trigger an automation that uses the “When record matches conditions” trigger. However, using the NOW() function introduces another wrinkle.

Per the documentation, the output from NOW() doesn’t constantly update. It only refreshes once every 15 minutes or so if your base is open (roughly every hour if the base is closed). In other words, if you write a formula that effectively says, “Create this output no sooner than 10 seconds after the last update to the field,” you could be waiting anywhere from 10 seconds to 15 minutes before it creates the desired output and triggers the automation.

If you’re cool with that, we then move to this criteria: only trigger the first time that the field is changed, and ignore all subsequent changes. If we’re using a “When record matches conditions” trigger for the automation, we can’t make this test part of the trigger formula mentioned above. Formulas recalculate with every data update. If the formula essentially says, “Create this output no sooner than 10 seconds after the last update to the field, but only when the field first becomes non-empty” it would never generate the first trigger (the one that you do want) because the very first character typed would make the field non-empty.

Making this work will require adding another field to the process. It could be as simple as a checkbox field, but whatever it is, the automation will need to set this field to a specific value when it runs for a given record. The formula could then include this field’s value into the calculation to determine whether or not to generate the output that triggers the automation. The formula logic then becomes, “Create this output no sooner than 10 seconds after the last update to the field, but only when this other field isn’t set to this specific value.”

With these options in place, the logic then flows like this (generically-speaking):

  • The first time you enter data into the desired field (I’ll call this {My Field}), the formula begins comparing that update time against the current time.
  • As long as the current time is at least (insert delay amount here) after the last update AND this other checkbox field (I’ll call this {Run Once}) is empty, the formula field will output a 1.
  • The automation is set to trigger when that formula outputs a 1. When this happens, the automation runs and does its thing.
  • As part of the automation execution, the triggering record is updated to put a check in the {Run Once} checkbox field.
  • Because {Run Once} is no longer empty, the formula field will never output a 1 again, no matter how many times {My Field} is updated.