I'm working on an auditing solution and could use some advice. We've got Record Type Events set up and they're working great for tracking basic stuff - like who updated a record and when. The Event History table captures all that perfectly.
But now we need to go deeper and track what actually changed in each field. Like if someone updates a customer's email from "old@email.com" to "new@email.com", we want to capture both the old and new values, not just that the record was updated.
old@email.com
new@email.com
We really don't want to create separate audit tables for each record type because we have quite a few and it would need maintenance.
I'm wondering if anyone has figured out how to use Record Type Events to capture the before and after values when fields change? Do the events give you access to both the old and new record data? And if so, what's the best way to compare the fields and store just the ones that actually changed?
Has anyone built something like this before? I feel like this should be doable with events since they're already tracking the changes, but I'm not sure about the implementation details.
Any tips or examples would be awesome!
Thanks!
Discussion posts and replies are publicly visible
My Suggestion is to add a column in each Event History table which stores field, old value and new value for all the fields that were modified in a text/string/json data type. The column should have data type to store large text.To populate this field you will need to compare a specific record before and after changes. You can configure expression rules to compare both and return text/json. Important thing to manage is system should keep a copy of old record in a variable in interface and whatever changes user makes should be in different variable. These two should be used for comparing and creating data for the new audit column. In your process model have this column updated with data before the write to records node and after user input task.
Lastly, when showing in front end, you can show the updated data with minor transformation like expressions to transform text into bulleted list etc.
Give it a try!
Harsha Sharma said:You can configure expression rules to compare both and return text/json
Attaching the expression I am using which can be stored in event history table's new column.
This returns semi colon separated string for each modified field. And while showing we are using split() and replace() to show it appropriately to users.