Hi All,
I wanted to do a general inquiry with the community on strategies or approaches to detailed logging of user changes in tables & affiliated child tables. I know when you have just a few tables, this can be relatively easy. I’m moreso focused on when you have a say 12 main tables, that each have say 5 child tables, and you want to have a changelog as a child table for each main table to view user history of updates to the parent table and all child tables. And just what values were changed. For example what were the values in an enumlist column that were added or removed from the previous version. The main tables having 100 columns, and some child tables having 20+.
What comes to mind for me is how complex this would be to build out automations for these types of updates.
What strategies would you recommend for something like this? Appscript? Just keeping a full copy of all the columns consolidated into a single column in a changelog that could be manually compared? Etc.
Also want to consider public apps with usersettings used for user access and how this would impact the best strategy for this situation.
AppSheet is a ROW processing system - meaning that it detects row changes and not column changes. It would be incredibly complex to try to detect and log before/after changes for each column.
The alternative is log each new row or row change into the log table. This is easy to do with Update Bots. Then when you need to analyze a row, pull up that row’s history and compare each prior row to each subsequent row o see which columns changed and what were the before/after values.
I would do this with each Child table as well.
The downside is that you have double the tables for each of your significant data tables (obviously don’t need to log utility tables).
Also, each log table will have 1 or more additional copies of each data row. This could make the log tables quite large. You can minimize the impact to your apps in two ways:
-
First set the log tables so ALL rows are filtered out - there is a setting for it on each table. This allows rows to be written into the tables but they are never downloaded to devices.
-
Purge, or at least archive, older entries. In lots of cases, change logs are only needed for short periods of time. They then become unchangeable and no need to keep log history. In cases where log changes are desired to be kept indefinitely, then I would recommend an archival process that removes the older rows from the day-to-day table (for performance) along with some process that allows reading/retrieval of the archived entries.
I hope this helps!!
1 Like