Re: How to keep original row of record and add ano...

ecisze · 04-27-2020 11:10 AM

Hi every community member,

I am new to AppSheet and like to keep audit trail records on my app. I have been keeping the original row but changes status from 1 to 0 when save changes. Then create a new row of updated record with status 1. It has been done using Excel vba. Appreciate your advise on how to handle it in AppSheet or suggest better method.

Thanks.

Steve

Mixing VBA and AppSheet is very likely to cause considerable problems with your app. I would advise working to avoid using VBA to modify any worksheets used by AppSheet.

To keep an audit trail of changes to rows, consider using a workflow to copy a row to a log table on any row add or update.

ecisze

Thank you Steve.

What are the possible problems with vba and Appsheet sharing same worksheets?

Thanks.

Steve

AppSheet has no awareness of VBA. AppSheet will not be aware of any changes VBA makes until the app syncs. There’s nothing to stop AppSheet from overwriting changes made by VBA. Likewise, VBA could make changes that AppSheet doesn’t see. AppSheet is not designed to share its data store with non-AppSheet writers.

But, to clarify, if your VBA only reads the data and doesn’t modify it, you should be fine.

Austin

I can attest to Steve’s points. I have a MySQL database as the back end of my apps and using triggers in database can produce some odd behaviors. It produces very odd results especially if you have server caching enabled in your appsheet app.

WillowMobileSys

There is an Action type “copy the row and edit the copy”. I have not used this before so I don’t know anything about how it works. I have never read any posts about it so it is likely a seldom used feature.

I would think that this simple opens a Form with a new row (new key) but has copied all columns from the original row. The changes are made to this copy. Upon Save the copy is added as a NEW row leaving the original row untouched.

A cancel would simply throw the copy away.

So instead of “Normal” edit actions, you would replace them with this as the edit action. I have no idea what that might take.

ecisze

I am thinking two ways to handle it:

On Update action creates a New row based on data on the Form just like a pure Add New record transaction. Then change the original record status field from 1 to 0. Both original and new record store in same Current table. The Slice filters out status 1 record and display on Form.

Another option is to have 2 tables: Current, Log tables. An Add New action creates an exact transaction record in both table except the Log table also include the record ID (say 1) of the Current table. For later Update action to the record, the data in Form will be updated to existing record ID (1) in Current table. This Update action will also create a New record in Log table base on the Form data and also update the original Record ID (1) transaction to status 0. The current table is the source for Form display.

Is AppSheet able to handle? Should this be done on workflow? Please point me to right AppSheet documentation that would help me. This is my very first try on AppSheet.

Thank you very much,

WillowMobileSys

In hind sight my suggested Action above will not work because you do want to link the rows together.

Yes AppSheet can handle but you do want to be cognizant of the table size. If you have lots of users making lots of edits, your tables (whether a single table or two tables) will grow quickly.

If the Log tables will be used by only a select few people, it would be better to track the audits in a separate table. This keeps the “current” data clean, efficient and performant for the majority of the system users.

So, assuming the second option of two tables, I think the workflow can be much cleaner than you are initially thinking. No need to manage row states with 0’s and 1’s.

Workflows can be triggered off of ADDS, UPDATES or both. So, you can create a Workflow to trigger for any ADD or UPDATE of a Current row

There is a perfect Action type to use in the Workflow, “add a new row to another table using values from this row”. With this Action you would add a NEW row - each time - to the Log table copying all the columns from the Current row - as well as the current row ID to link the two together.

As you say, the Current row is the source for the Form view and always the row the users interact with.

ecisze

Thank you everyone advise. Very helpful. I will study the docs and try it. Certainly more challenges coming but would be fun. I’m sure.

Hopefully one day I’ll complete my first App and capable to help others.

How to keep original row of record and add another row of updated record in same table