How can I create a viewable change/audit log for when a column value is changed?

[DougSample]

I've got an Inventory app to help keep track of my construction company's tools and what job site they're located. How can I create a bot that'll autogenerate an entry into a change log for each tool?

Being able to track when a tool changes sites and who's moving it is the ultimate goal. I have all the info in their various columns, I just can't get them to talk to one another. Any help with this would be amazing.

[TeeSee1]

Logging Changes

Hi

I do not know an easy/pretty way to implement this but there is a way to do this as shown above.

It involves multiple steps and you can check it out if you are interested.

The gist of how it is implemented is,

• Create a temp table to copy the current values
• Change the temp table values and save them
• When saved, the log is updated using the current as well as the newly modified values in the temp, and the main table is updated with the temp table values.

[kirkwongkk]

HI TeeSee1, this is exactly what I wish to learn but not quite sure the setting mentioned above. Can you elaborate in more details please? Many thanks.

[TeeSee1]

Hi

I have already deleted the app..

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/How-to-create-change-logs/m-p/436379#M173293

is probably a better solution using a bot.

[TeeSee1]

Another idea which is much simpler.

If you don't need before and after values in one row, you can just copy the new values whenever the row is saved.

All you need is an add a new row using this tables data action, set it in the form's Behavior >> Trigger Actions.

[gaixixon]

This is exactly what I have been doing for my app.
After many working around, I find below solution is most suitable for me:

Example, you have an inventory app that has a table named "INVENTORY"; and you want to track all changes of INVENTORY table.

Step 1: Decide the columns that you need to track changes (ie. [Item Name]; [Qty]; [Buying Price]; [Notes] so on...).

Step 2: Make a NEW table named INVETORY.bak with 1st colum named [UniqueID]; 2nd column named [Inventory Id]; and rest columns as listed in Step 1.

Step 3: Add the table INVENTORY.bak to your app.

Step 4: Create a NEW bot, that run everytime the data in "INVENTORY TABLE" changed.
See below, and make sure to change table "currentstatus" to "INVENTORY", then click "Add a step"

Step 5: Continue following 
- Click new step / Run data action/New  Action/Add New Row/
- At  the option "Add row  to this table", make sure to select "INVENTORY.bak", that is the table that you added earlier.

Now it come to the tricky parts:
Under "With this value", COLUMN ON THE LEFT is the column in your INVENTORY.bak table; that you want to store the  changes.
COLUMN ON THE RIGHT is the value in your INVENTORY table.

As the sample picture below is from my app, so you should read carefully

First row: UniqueID => uniqueid()
2nd row: Change "COUNTY" to "Inventory ID"; and Change [_THISROW] to [_THISROW].[name of the ID colum of your INVENTORY TABLE]; example: [_THISROW].[UniqueID] (UniqueID is the ID column of your inventory table).

Hit Save; and all is set.

Now, you want to reflex changes to your INVENTORY TABLE right?

In your INVENTORY table, create a virtual colum name REVISION HISTORY;
App Formula: FILTER("INVENTORY.bak" , [Inventory ID] = [_THISROW].[ID])
Type: List
Element type: Ref
Reference Table name: INVENTORY.bak

Hope this help you!

[Caolanmcd]

This was very helpful! 

Although I noticed on my end that the way this Revision History is displayed a bit weird

When I click into those I get a Details view. Is there any way of changing the Revision History view to a table or deck?

Thanks!

[Caolanmcd]

Never mind, I fixed this issue. 

It displayed this way because an image was one of the values I had added. Once I got rid of the the image from the table and Automation it displayed as expected. 

Everyday is a learning day