I’m using Grist for my business CRM. I’m often updating a customers status in the spreadsheet and would love to have a log of events.
An example - a customer applies to upgrade their account, so I set the customer status field from “Basic member” to “Applying for Professional”. After they have been authorised, I set the field to “Professional Member”.
Is there any way for Grist to keep track of the fact that the field was “Basic”, then “Applying”, finally “Professional”? Maybe having a field that keeps track of the changes - something like “Member field was changed from Basic to Applying”.
You can use trigger formulas and a “History” field to implement something like your idea for a field that tracks changes. The following trigger formula (when applied only on changes to a “Status” field) should keep a newline-separated history of all changes to the Status field with the date that the change was made. Feel free to tweak the formula to match your preferred formatting, or add/remove additional data.
old_values = value
new_value = $Status
if not old_values and not new_value:
return ""
return "{}\nChanged to {} on {}".format(
old_values, new_value or 'Blank', TODAY()).strip()
Grist does maintain some history of recent changes to tables. The ‘Document History’ link in the lower left corner of the Tools section has an activity tab that shows recent changes, but it’s not as searchable and not as well-suited for looking at older changes.
Looking ahead, would you say your use case would be better supported if Grist had a way to automatically create records in a table when a record in a different table is modified (say a separate History table that gets populated anytime a Status is modified)? Or is the trigger formula field closer to what you were looking for?
Thank you for your reply!
I understand that this formula needs to be a trigger formula set to Apply on Record Changes. The issue I am having is with the two values “old_value” and “new_value”.
If the field that is triggering the formula is called “$Member_Status” and the possible member status values are “A”, “B” and “C”, what should the formula say? (Marked by a ? in the code below)
old_values = ?
new_value = ?
if not old_values and not new_value:
return ""
return f"{old_values}\nChanged to {new_value or 'Blank'} on {TODAY()}".strip()
The reason I ask this, is that when the field “$Member_Status” has been changed triggering the event code, the “$Member_Status” variable has already the new value and therefore I have no reference to the original value to add to the “old_value” variable.
You’re correct, there isn’t currently a way for a trigger formula to access its previous value. I can’t think of a workaround that doesn’t involve keeping status change history in a separate trigger formula field that listens to changes to the original status field. Having something like a prev_value variable available to trigger formulas does sound useful, and would work well in your example.
Also, I did make a small update to the last line of the formula for compatibility with Python 2 documents. You can disregard it if the original formula was working ok (it used a feature that was only available in Python 3 documents).
Would you be opposed to maintaining a table of historical statuses? It might solve for your needs just as cleanly, and with the right dashboard, have a nice workflow, too.