Tracking and plotting the 'history' of a numerical field

[Camilla_Blasi_F]

Hi all,

I have a couple fields in my base where the values are reviewed every few months (i.e. risk score increases or decreases according to mitigation measures).

I would be interested in plotting how for example the risk score for a particular risk has changed over the course of the project. I know I can expand view and see how that record has been edited over time but I was wondering if there is a way to monitor changes to a particular field so that the data can be extracted automatically for graphs and tables etc…

The only other thing I can think of is creating a new field every time the values are reviewed and then hiding them from view but that would just create so many fields over time.

Thanks in advance for your help,

Camilla

[Bill_French]

Hi Camilla, and welcome to the community!

Camilla_Blasi_Foglie:

plotting how for example the risk score for a particular risk has changed over the course of the project.

Yes, this is a critical concept and it can be done.

One approach is to use the new Actions feature to track when changes meeting your requirements occur and do something. Like - when a record is updated.

When a record is updated, you don’t really know if the risk score has changed, but you do know that something changed. This is not ideal because you will be firing the action for every record change, whereas, the risk score is perhaps changed less frequently. Actions do not grow on trees; they do have limits, so you need a less aggressive approach.

Here’s how I do it - note, I am biased toward an integrated, low impact solution that is elegant.

1. Add a new field - Previous Risk Score.

2. Create a script action that tests if the field Risk Score is different from Previous Risk Score; it should run nightly unless you need real-time tracking (that’s a different solution).

3. If the values tested are different, update the Previous Risk Score with the new value; if the values are the same, nothing has changed and the script action can stop without processing #4 below.

4. If the value has changed, in the same script action create an “analytic” record in a new table we’ll call “Risk Tracking Analytics” that tracks all Risk Score changes. This new record will be added every time the action sees the score change and it will include the previous value and the new value as well as a date/time.

5. Create a chart using the Chart block to plot the changes over time using the new Risk Tracking Analytics table.

6. Have a fine beverage while you watch it update from the beach.

[Camilla_Blasi_F]

Hi Bill,

Thank you so much for getting back to me so promptly.

Your approach sounds great. Unfortunately I think running a script action automation is only available for Airtable Pro. Could this be replicated using the scripting block do you think? Any suggestion on how to do this would be greatly appreciated :slightly_smiling_face:

All the best,

Camilla

[Bill_French]

Camilla_Blasi_Foglie:

Could this be replicated using the scripting block do you think?

Yep, but two key challenges emerge using a script block:

1. The block would need to evaluate risk score changes for every record; the scale of the table might make this slow down if the record count grows but it would be seconds if < 5,000 records.

2. The block would need to be run manually at an interval that matches the granularity for which you hope to gather analytics - I.e. every night perhaps.

Manual must-dos are not fun.

Another approach is to create an external process in Integromat or Zapier which would both likely run in the free tiers. I don’t specialize in these tools but there’s probably a pathway to achieve your goals in a fully automated fashion. @ScottWorld might know if this is possible.

Lastly, use the API with something like Google Script - this would also be free and automated but does require some effort to implement.

[kuovonne]

Bill.French:

When a record is updated, you don’t really know if the risk score has changed, but you do know that something changed

You can set the trigger to only fire when a specific field has been updated. This would eliminate the need for the last value field. It would also eliminate the need for a script action.

Note however, if this field is manually updated by a person typing in the number, the trigger might fire on partial input, resulting in incorrect values being stored. (The problem occurs even if you aren’t watching a specific field.)

If you know that updates will never occurred more than several hours apart, another option would be to use a system of formula fields for the automation. This is my favorite approach.

An alternative is to have a button that must be clicked every time the user enters new value to trigger a script.

[kuovonne]

Camilla_Blasi_Foglie:

only available for Airtable Pro

If you do not have a pro subscription, you should make sure that you have enough automation runs in your workspace for all automations across all bases each month. If you are on the free plan, the limit is very low.

[ScottWorld]

Bill.French:

@ScottWorld might know if this is possible

Yes, Integromat could easily handle this. @Camilla_Blasi_Foglie: Integromat is a no-code automation platform.

[Bill_French]

kuovonne:

This would eliminate the need for the last value field. It would also eliminate the need for a script action.

How so?

She needs to compare yesterday’s value with today’s change. How would you do that without a script or without a field that snapshots yesterday’s value?

UPDATE: I see - so you are saying, just log the latest value when – and only when – that field changes, right?

But doing so would require comparisons between two analytics rows to determine the delta between the current state and the prior state. This is probably not possible in the Chart block, but it should show score changes over time, so that’s probably useful.

Brilliant!

ps - Sorry @ScottWorld; I like @kuovonne’s approach - it’s elegant.

[kuovonne]

Bill.French:

I see - so you are saying, just log the latest value when – and only when – that field changes, right

Yes, that is what I was proposing. It wouldn’t calculate the delta, but you could still get a graph out of the data.

However, if the data will be entered manually, it will still need a delay to make sure that incremental changes aren’t recorded. That’s when a formula field would need to be used to introduce the delay. The formula field could make sure that the last modified time is acceptably long ago to make sure that the automation doesn’t trigger prematurely. This would also change the trigger from a “when updated” trigger to a “when meets conditions” trigger based on the formula field.

There is also the slight issue if getting the very first value. However, that will depend on how the data is being entered. If the initial value is set upon record creation, a trigger on record creation could do it. If the value is initially blank, then only triggering on change would work.

[Camilla_Blasi_F]

Thank you. We have the Pro trail version for a year and then will transition to Pro so shouldn’t have this issue