User Change Audit Trail

Hi,

I’m building an inspection app for work that is going to require some level of audit logs/trail to data changes, and was wondering if someone could help me out a little or even point me in the right direction. My data source is called “Inspections”. The information I want to record is

  1. Data changed (before/after values)
  2. Data changed by Whom
  3. Data changed When

I’ve got a post that pointed me in the right direction here ([_THISROW_BEFORE] / [_THISROW_AFTER] Template Generator - #2 by GreenFlux) I believe, and I was hoping someone could weigh in on this.

It seems this is how this would need to happen.

  1. Monitor table “Inspections” for changes. I think this is done with “Events” (changes to table).
  2. I need to Set the Data in a Table called “Audit”. I believe I would do this with “Actions”
  3. I need to somehow pass the data from the “Event” ThisRow to the Action.

Potential Problems:

  1. I’m not sure I can pass data from Events to Actions like before thisrow, after, etc. If I can’t then there’s no way for me to use actions.
  2. Maybe I should use a webhook to connect back to Appsheet’s API to make the change in the background.

I’m hoping someone with more experience can take a peek at this, and perhaps offer some knowledge on and point me in the direction of which route would actually work to make this happen. Perhaps save me some time of trying to create these with some frustration and running up against problems I’m unaware of.

Thanks so much!

This is a potentially substantial requirement and the approach could drastically change depending on the extent or depth of the “audit trail” you wish to have. Below are some general thoughts.

1. Data changed by Whom: You could track it relatively easily by having a column called say 'Record Changed By" with app formula as USEREMAIL() . You could even have another column called say “Record Created By”, with Editable_if as FALSE and Initial value as USEREMAIL()

2. Data changed When : You could use ChangeTimeStamp type columns that listen to columns 'Record Changed By" , “Record Created By” and other columns where you wish to track the data change has taken place.

3. Data changed (before/after values) : This could get trickier based on how extensive you wish to have audit trail

a) if you wish to track just one or two columns such as say who changed the inspection status, you could add a concatenate column with a date-time stamp and the column value that keeps concatenating say last 5 changes to that status column. This can be done by a column data change event action on inspection form save.

b) If you wish to track every small change in all the columns, perhaps a better option will be to create another Audit Trail table where in the event action on every edit in any Inspection record saves a record in the audit trail table. However if an inspection record goes through many changes in its lifetime, this audit trail table could soon become a very large table.

Again, above are just some general approach thoughts. The exact configuration will depend on many other factors of the app configuration and requirements. Hope this helps.

3 Likes

An Event is part of a Bot. Another part of a Bot, the Process, includes Steps to run when the Event is triggered. One kind of Step is to run an Action. That Action runs in the context of the record that triggered the Event.

In particular, you should be using an Action type of “add a record to another table…”.

Your desired functionality is easily solved without the API.

2 Likes