Let’s say I have the following columns Name, Age, Job, I also have a formula column called Change
When any of the first three columns are changed, I want the “Change” field to contain the data that has been changed. E.G I have changed “Builder” to “Teacher”, with this change, I want the formula the show “Teacher”
If the “Age” was changed to “36”, I’d like the “Changed” field to show “36”. Is this possible with a trigger formula in the “Change” field and how would I go about doing it?
This is tricky but here is a possible approach: https://public.getgrist.com/ndNH3bMWLZJD/Show-Which-Value-Changed/m/fork
The key to it is a History column with a Trigger Formula which uses its own previous value to maintain the current and the previous value of another cell. We encode the value into JSON so that it can be stored in a data column (of type Text):
import json
history = json.loads(value) if value else []
json.dumps([$Value] + history[0:1])
It’s reevaluated whenever $Value changes: each time it places the current $Value as a first item in an array, and moves the previous first item to be the second item.
The Value columns is a formula that collects all the cells of interest.
With this History column in place, the Change column like you want can be a regular formula column that compares the last two values in $History:
import json
history = json.loads($History) if $History else []
if len(history) < 2:
return None
[current, previous] = history
for key in current:
if current[key] != previous[key]:
return (key, previous[key], current[key])
It still needs to decode using JSON and also to check that History contains two values that could be compared. Then it goes through all keys, and returns info about the first difference.
That is great! I’ve taken a copy and am going through the fine details. Thank you for the time spent on it!