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:

Screen Shot 2020-08-27 at 11.22.02 AM

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.

2 Likes

Wow, this is INCREDIBLY clever, @Justin_Barrett! And a fantastic workaround to the problems that are inherent with Airtable’s “New Record In View” trigger!

In essence, what we’re seeing in that final video of yours is the result of MULTIPLE TRIGGERED “New Records in View” in short succession, one after another! :smiley:

Very very clever!! Thank you so much for posting this!! :smiley:

Murphy’s Law has kicked in. Little did I know when I posted this that a new “updated record” trigger would be added a little over a week later. :slight_smile: A quick test last night confirmed that it works the exact same way, all without needing extra fields and a special view. Just target a single field, and the automation will re-run as needed to pick up all changes to that field.

HOWEVER…

I also noticed in my testing that the “updated record” trigger also fires for a watched field when a record is first created. I reported this via the automations feedback form, as I don’t believe that the default empty state of a field in a new record should trigger anything. I’ve had to work around this issue in some of my updated automations that use the new trigger, but overall it has been more of a blessing than a burden.

3 Likes

Wow, I didn’t realize that this new trigger was silently added to the product with no fanfare! Thanks for heads-up on this, Justin, and thanks for the continued analysis of the pros & cons of the automation behaviors.

1 Like

Another trigger was silently introduced recently (no idea when, but I discovered it a few minutes ago):

When record matches conditions

This could prove to be even more useful than the update record trigger, because it lets you set conditions for the trigger in the same manner that they’re used elsewhere in Airtable: looking for specific contents, or maybe driven by whether a specific field is/isn’t empty, etc. Lots of great possibilities opening up!

1 Like

WOW! Really nice!!! That wasn’t there yesterday, so they must have added that today! :slight_smile:

One huge problem that I discovered yesterday with automations is that lookup fields can NOT be used in the message body (or any other field) of outgoing emails. Airtable makes it SEEM like you can use a lookup field in your email message body by allowing you to select the value of the lookup field when creating your email automation, but it actually doesn’t work properly. Airtable only lets you select the value of the lookup field FOR THE TEST RECORD THAT YOU USED FOR THE AUTOMATION — and that’s the value that remains when you go to send your email. Airtable never substitutes the CURRENT VALUE of the lookup field for whatever record you’re CURRENTLY working with at the time of automated email sending. Airtable always defaults back to the test record’s lookup value. The only way to workaround this problem is to create multiple formula fields — each one which is equal to a separate lookup field. Then, you need to use these formula fields in your outgoing emails. This really clutters up the table with unnecessary fields, and is extremely unintuitive as well.

That sounds like a bug. If you haven’t done so already, I suggest reporting this to Airtable support.

Thanks, @Justin_Barrett. Yeah, I thought it might be a bug as well — I reported it on the Automations Feedback Page. Hopefully, they’re still monitoring submissions from that page.

By the way, I totally & completely agree with you about this. An “updated record” trigger should NEVER trigger on a newly-created record. That makes absolutely no sense at all. Did you report this to the automations team through their Automations Beta Feedback page or through Airtable support? I submitted it myself on their feedback page, but I actually don’t know if they’re monitoring the responses on that page anymore.

As I said in the comment you quoted, I reported it via the feedback form. :slight_smile: That was about a week before it came out of beta and went live for everyone, so I’d like to think they were still checking the form feedback then.

Lol! Oops, I missed that! :crazy_face: I missed that because I violated my own rule about posting late at night when I’m too tired to think straight!! :rofl::crazy_face::joy:

1 Like