How to pick up ALL changes to a manual entry field via automation
UPDATE 09/05/2020 - The new “updated record” trigger for automations makes this workaround unnecessary (more notes in a comment farther down in this thread), but I’ll leave the thread here anyway.
Using Airtable’s new (but still technically in beta for Pro-plan users and up at the time I’m writing this) automation features, how can an automation pick up all changes to a field? It wouldn’t be a new record trigger, and the only other option right now is a new record in a view. If the field in question was simply going from empty to not-empty, the triggering view—and any connected automation—would pick it up after the first few characters, and wouldn’t operate on the full contents because the record would need to leave the view and re-enter it again to re-trigger the automation.
So how can a lengthy change to a single field re-trigger an automation multiple times so that its full contents are processed?
As with many things, it’s a matter of time.
In the early days of Airtable’s automations (in its early private beta phase, if I remember correctly), I ran a test that a) taught me that repeated triggering was possible, and b) taught me some other things about some of Airtable’s fields.
For this demo, I’ll enter some text into a field named {My Stuff}, and use the automation to copy it to another field named {My Stuff Copied}.
In my test, I added three new fields:
- a Last modified time field (I’ll call this
{Last Updated}), targeting only the desired manual entry field ({My Stuff}in this case) - a date field (I’ll call this
{Captured Time}). Make sure this is set to record time as well, not just the date. - a formula field (I’ll call this
{Time Mismatch})
The formula in {Time Mismatch} returns a 1 if {Last Updated} does not match {Captured Time}, and a 0 when they match.
{Last Updated} != {Captured Time}
I’ll add a view named “Trigger: Time Mismatch”, and add a filter to only show records where the {Time Mismatch} field equals 1. The automation I’m going to build will trigger when there’s a mismatch, copy the contents of {My Stuff} to {My Stuff Copied}, and update the date in {Captured Time} with what’s in {Last Updated}, forcing the record to fall back out of the view.
At first this might not seem like much, but that’s because we’re slightly biased by what we see in date fields of any type, including Last updated time fields. We don’t see anything more precise than hours and minutes. However, my original test taught me that the data stored in any date field—including a Last updated time field—isn’t just recorded as pure hours and minutes, but much more precisely, possibly down to the millisecond (I haven’t done exhaustive tests on the precision, but that’s my theory as of now).
I also learned that when you are editing a field, the Last updated time field doesn’t just update with the first characters you type. It repeatedly updates as the data in that edited field is synced with Airtable’s servers. We just don’t see those micro-changes most of the time because, again, the field only shows hours and minutes.
With that in place, here’s my sample setup:
Make a new automation, and set it up to trigger when a record enters the “Trigger: Time Mismatch” view:
The only action we need will be an “Update record” action, to copy the date from {Last Updated} to {Captured Time}, and the text from {My Stuff} to {My Stuff Copied}:
After testing the step and activating the automation, you might need to delete the record used for the test, as it might not behave as expected. Start fresh with a new record, and start typing in the {My Stuff} field. After a brief delay, you’ll see what you type slowly echoed step-by-step in the {My Stuff Copied} field.
Obviously a pure data copy isn’t the best example, but I hope it gets your gears turning on the possibilities.
