This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Privacy Policy. Click Preferences to customize your cookie settings.
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
Monitor table “Inspections” for changes. I think this is done with “Events” (changes to table).
I need to Set the Data in a Table called “Audit”. I believe I would do this with “Actions”
I need to somehow pass the data from the “Event” ThisRow to the Action.
Potential Problems:
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.
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.
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.
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.
Thanks for pointing me in the right direction. I’m having trouble and I’m hoping you guys can maybe help me understand what’s going on. I’m testing this out with just one column at the moment, and not having any luck. Here’s my setup
Two Tables - Jobs and History. I’m trying this on the column “Job Number” in the Jobs Table.
Bot has an event, watching table jobs, for Adds and Updates. The process I’m running has one step.
Run Data Action > Add New Rows To History > With These Values
HistoryID = UNIQUEID()
HistoryBefore = [_THISROW_BEFORE].[JobNumber] <<< Job Number is a Jobs Table Column as Mentioned Above
HistoryAfter = [_THISROW_AFTER].[JobNumber]
I make some changes to the job number, and when I change the Job Number on the Jobs Table, it records BOTH HistoryBefore and HistoryAfter as the after value. No before value is captured. Is this running too far after the update, that those value are not capturable? What am I doing wrong here?
Maybe missing something here, but have you considered following.
You seem to be wanting to add a new row to the history table on every change in the Jobs table. If so you could add a new row to the history table with BOT on every add or chage in the Jobs record. With this you do not need to deal with “Before” and “after” values through expressions [_THISROW_BEFORE] etc., because every change in the Jobs table anyway adds a row in the history table, so all “Before” and “After” value are anyway captured.
Simply add all the columns that you wish to have “audit trail” for, from the Jobs table into History table with BOT on every change in the Jobs table.
So my original idea for an audit trail was to have it log something like this:
Greg changed the value of [Column Name] job number from (before value) to (after value)
That would make it super easy to track down the relevant people/data/changes . Additionally I thought of adding two more columns for each this for each actual data column. So for example I could have three columns for job number
Job Number, Job Number Modified By, Job Number Mod Recorded
Second column would record the changes, third column if it’s been recorded in history (1 means loop needed)
But there must be an easier way to do this. I just want a comprehensive easy audit trail of changes made. I mustn’t be the only one.
Job Number Modified By: This can be captured by UserEmail() in each History record added.
Job Number Mod Recorded: You may wish to clarify what this is?. If this is timestamp, a NOW() can be added against this column while adding the record in History table through BOT
This you could do in the history table itself. You could add an ORDERBY() column on the history table for a specific job, ordered by record save timestamp in history table. So this column will create a reference of all history records for a specific job,
Then with the help of this ORDRBY() list column, you could show an entire list of certain column changes for that particular job.
So what I would like to have as the final output ideally is something like this, for each change.
UserEmail changed (Column Name) value from (Previous Value) to (Current Value).
I’m fine saving this in a history table, in fact I’m successfully kinda doing that at the moment.
Useremail is simple, this is done.
Column Name - I just realized that I may not be able to access column names (instead of values) programatically, so this might also be an issue with how I’m approaching this.
Previous Value - I still haven’t seen/been able to demonstrate I can access this with a bot. It’s not strictly necessary, but would be really nice to be able to show the before/after value for changes in a log. Has anyone demonstrated this is possible with a bot?
Current Value - I can access no problem.
It seems like I’m only half way there. Orderby() once I capture the data is easy, this is down the road from actually capturing the history log. The issues are
can I access before values of a field, via a bot or are those no longer accessible at that point? It seems possible/accessible via email/PDF templates. Unsure via bots.
I believe this is not possible, thus suggestions so far were for some workarounds to achieve “nearly” the desired result. On your specific query on before/after column values, I tried to search the community and came across the posts below with guidance by senior community colleagues on similar requirements. Hope these help.
You may wish to consider the workardound suggested by @tsuji_koichi
Your requirement seems to be exactly similar to the one in the post below.
Another post and guidance by expert community colleagues
So I’ve got some progress on this, and wondering if there’s a way to make this more userfriendly. I’m only testing on one column at the moment. Call it VerticalOrienation. The column immediately following VerticalOrientation is VerticalOrientationRecord. Here’s how it looks
VerticalOrientation - data recording.
VerticalOrientationRecord (hidden) - Appformula that records [_THISROW_BEFORE].[VerticalOrientation]
I then have a bot that watches VerticalOrientation for a change and if it changes, then it writes a record to a NEW table, that says
USEREMAIL() has changed the value of VerticalOrientation from [VerticalOrientationRecord] to [VerticalOrientation] on NOW()
This works great so far for this column. I’ll throw in a little more jazz like looking up the USEREMAIL’s initials or full name, and then putting this in place of the useremail, but I have a log for a column change like I’m wanting.
The next question is, can anyone think of a more efficient way to do this for every column in my table other than creating duplicate columns with the BEFORE values and then monitoring them all with a separate bot fro each column? I can do that but it sounds extraordinarily inefficient.
A separate bot is not required for each column change. One could assign a ChangeCounter type column to listen to all columns that are being monitored for change and a BOT can be triggered based on a change in ChangeCounter or ChangeTimeStamp column.
For avoiding creating a new column for storing each monitored column’s before value, a workaround will be to concatenate the monitored columns into a couple of columns and then assign them to audit log records. However, any workaround comes with some giveaways. With concatenation, the user will need to look a bit closer in the message to know what exactly has changed. This is so because, with concatenation, one cannot easily identify which field out of concatenated multiple fields has changed.
LinkedIn
Twitter
