Record Edits in Column

Nassim · 06-19-2021 07:57 PM

Hi, I have a column in my app and sheet where its only purpose is to record the edit made by the user.
Every time an entry is edited by one of the allowed users. I would like it to record the last record with the useremail, timestamp and what column was edited and what was the previous entry.
Currently, I made my app record the whole previous entry and i do not know an easier way to just record the column that got changed.

Here is an example of one of my smaller formulas in the “edited by” column.

IF(
OR(
[_THISROW_BEFORE].[Date] <> [_THISROW_AFTER].[Date],
[_THISROW_BEFORE].[Operator] <> [_THISROW_AFTER].[Operator],
[_THISROW_BEFORE].[Cars Drained] <> [_THISROW_AFTER].[Cars Drained],
[_THISROW_BEFORE].[Operating Hours] <> [_THISROW_AFTER].[Operating Hours],
[_THISROW_BEFORE].[Downtime Hours] <> [_THISROW_AFTER].[Downtime Hours],
[_THISROW_BEFORE].[Notes] <> [_THISROW_AFTER].[Notes]),
CONCATENATE(USEREMAIL()," , ",NOW(),
", Date ",[_THISROW_BEFORE].[Date],
", Operator ",[_THISROW_BEFORE].[Operator],
", Cars Drained ",[_THISROW_BEFORE].[Cars Drained],
", Op Hrs ",[_THISROW_BEFORE].[Operating Hours],
", Down hrs ",[_THISROW_BEFORE].[Downtime Hours],
", Notes ",[_THISROW_BEFORE].[Notes]),
" ")

How would I make it where it would only Concatenate the useremail, timestamp and whatever entry was changed, instead of output all the previous values.
Any help would be greatly appreciated. Thank you in advance
Nas

PS: also how can I make the formula not record the first empty entry (when thisrow_before is blank) without making the formula huge.

MultiTech

Perhaps instead of going this route, you might instead think about creating another record to replace the old one, this way you’ve got a complete “record” of what happened, who changed what, etc.

There’s an action “Create a copy of this row and edit” - I would think this would be a perfect fit.

Nassim

Hi, unfortunately I don’t think I will able to go that route as I have multiple users using the app. The users simply go in and edit the input (row) to adjust a value, date or comment, if need be and I would like to keep the previous entry as a record so that I can eventually create a monthy report of all the edits done for upper management.
I cannot really add an action button (that will copy row and edit) for edits since it would just confuse the users. Unless that action is done in the background when a user edits the entry and somehow hides the previous entry and shows the new row instead?

Steve

Use a bot to copy the row to a history table every time the row is updated.

MultiTech

Actually, if you continue along your current route you run the chance of user’s overwriting each others data.

If you ever had two people editing the same row at the same time, whoever was last to get their edits synced wins
- meaning you’ll only see that user’s edits, the others will be lost.

Whenever you have a multi-user system, it’s advisable to not have users editing the same data source, instead they should each be working with their own data.

and by that I mean they should create their own records in data collection tables, that way they’re only playing with their data and not some other users.

This might help.

Separating these data collections out into their own records makes reporting pretty easy, you can use a <<Start>> expression to simply list off the records you want to show - essentially printing out a small table of data for your report.

You’ll also want to investigate references: