Best Way to Capture and Record Field Value Changes

[Ish_Rojas]

I have a table with a column titled Status. It contains four statuses New, Answered, Rollover, Completed.

I want to capture the result when a Status changes, for example, from New to Answered, from New to Rollover, etc. I also want to report it compactly.

Further, I want to capture and count the Status changes in a Pivot Table or similar. I have looked at nested IF/SWITCH as well as Automation timestamp.

But being new to Airtable I am unsure which is simplest and most efficient. The Automation solution seems like many columns and not suited to a Pivot Table.

Any help appreciated.

[Justin_Barrett]

Welcome to the community, @Ish_Rojas! :grinning_face_with_big_eyes: I’m guessing the repeated “Rollover” in your status list in the first line is a typo? (You said there are four status options, but listed five.)

Ish_Rojas:

I want to be able to capture and count the Status changes in a Pivot or similar.

Are you looking to do this per record, or for the whole table? My gut says the latter, but I just want to check to be sure.

I’ve got an idea for how to capture the changes in a single record, and a script could be written to build a summary for the whole table based on that saved data. Would that work for your use case?

[Ish_Rojas]

Hi Justin… yes, the repeated “Rollover” in the first sentence is a typo resulting from multitasking… I see an edit pencil so will correct it.

[Ish_Rojas]

When are you available to pursue your idea and script?

[Hannah_Wiginton]

Hey @Ish_Rojas,

To keep a running log of your Status changes, you can use the Field Diff Log function in the On2Air Actions app.

It uses a Long Text field to keep a running log of changes to the field, the before and after, plus a timestamp.

Field Diff Log Function

On2Air | Accomplish Anything with Airtable – 8 May 21

On2Air: Actions | On2Air

On2Air: Actions is an Airtable automation and workflow tool to integrate and connect your Airtable data with any other software tools used in your business. Create powerful business processes with almost 20 additional action options beyond the...

[Justin_Barrett]

Here’s how to track the status changes using an automation. In addition to your existing {Status} field, you’ll need:

• A long text field to contain the list of all status changes. I named mine “Status Progress”
• A single line text field that named “Current Status” that will be part of the automation setup.
• A formula field named “Status Changed” containing this formula: Status != {Current Status}. This will output a 1 when the status has been changed, and will drive the automation.
• A formula field to create the text of each new line added to {Status Progress}. I named this “Next Status”, and used the following formula:

IF({Current Status}, "\n" & {Current Status} & " to ") & Status

The last three fields above can be hidden, leaving only the {Status} and {Status Progress} fields visible.

To set up the automation, set {Status} to any option, then create an automation using the “When record matches conditions” trigger, using the following conditions:

Test the trigger and it should test successfully.

Add an “Update record” action after it. Choose the table and record ID from the trigger step, then set the field updates as follows, pulling all values from the trigger step:

To clarify, the {Status Progress} field will receive its own contents plus the output of the “Next Status” field, with no space between them. The {Current Status} field will receive the name of the currently-selected option from the {Status} field.

Name the automation as you wish, and turn it on. It should now behave like this:

If you wish to also capture a timestamp for each change, use something like this for the {Next Status} formula:

IF({Current Status}, "\n" & {Current Status} & " to ") & Status & " - " & DATETIME_FORMAT(SET_TIMEZONE(LAST_MODIFIED_TIME(Status), "timezone_specifier_here"), "l LT")

If you’d like a script to break down how many times each status change occurred across the entire table, contact me directly. While I’m happy to help with formulas and automation solutions here, script development is part of my business.

[Ish_Rojas]

Hi Justin… I followed your instructions but as you can see from the images below only one record updated successfully. I tested your solution by cycling through all the selections in the Status field for several records.

What might account for only one record showing successfully updated? Notice too that for the sole updated record shows the jb_Status Changed field value of 0, not 1. My formula shows Status != {jb_Current Status}, yet the 0 appears to have driven the automation (?).

Clearly, I’ve missed somewhere just not sure where but you may see my error. Thanks—

[Justin_Barrett]

Ish_Rojas:

What might account for only one record showing successfully updated?

My gut says that you had changed those records’ status options before turning on the automation. Remember that the automation only picks up on relevant triggers that occur after it is activated.

Also remember that for any record to trigger more than once, the triggering condition must first be un-met before it can trigger again. In this case, the value in one of the fields being watched—{jb_Status Changed}—must reset to zero. To make this happen in the records that didn’t do anything in your tests, simply clear the {Status} field. Now if you set a new status, the formula output will return to 1, which will trigger the automation.

This ties in to your next question:

Ish_Rojas:

Notice too that for the sole updated record shows the jb_Status Changed field value of 0, not 1. My formula shows Status != {jb_Current Status}, yet the 0 appears to have driven the automation (?).

To be clear, the 0 did not drive the automation. That’s part of the trigger reset mechanism that I explained above. When you change the {Status} field value, the difference between that field and {jb_Current Status} causes the formula to output a 1, which triggers the automation. The automation then copies the status to {jb_Current Status}, which causes the formula to output a 0, effectively resetting the trigger.

[Ish_Rojas]

You’re right as rain. I cycled through the various statuses and your solution shows working as you described it.

See my updated results below. This is a great help and I appreciate your indulgence.

Now on to the other to-do’s of this base including making a decent Pivot Table or similar to report these results going forward. Thank you.

[Le_Sans_Blague]

This is awesome and something I’ve been trying to solve for my base as well. One question… for my use case rather than {Status Progress} showing “old status” to “new status”, I want it to show “new status: start date”. I have a field for Start Date next to the equivalent of the {Status}.

I’ve modified the automation so that it returns what I want in Status Progress, but I can’t figure out how to make it wait for me to update the start date before running the automation. I tried adding fields for {Current Start Date} and {Start Date Changed} that work the same as the status fields, but if I try to add {Start Date Changed} = 1 as an additional condition to the trigger, the test fails.

Am I making sense? Any suggestions?