I’m trying to track some specific changes to rows in a table.
Currently, I have an automation watching for changes to rows in that table and then logging the changes that I’m interested in. So, when a row is opened for editing, and a column value is changed, the automation fires and I make an entry in my “audit log” table to capture the new values. If I’m interested in, say, 3 different columns, and those 3 column values are changed, the automation will result in 3 entries in my “audit log” table because the row has effectively changed 3 times. This results in a lot of unnecessary entries in my “audit log”.
What I would like to happen is, when a row is changed by updating multiple column values, the automation fires once when the row is closed for editing, or the user moves to a different row if editing directly in the table.
There is no way to detect when the user closes a row dialog for editing, nor when they move to another row in the table after editing.
But there are ways to achieve what you are looking for.
First, note that EVERY column has its own Modified() formula which returns the date/time of the last time it was modified, and its own Modifiedby() formula that returns the user-id who made the change. This can be used to detect which columns have just been changed on any given row.
By checking these time-stamps, your automation can see what columns have been changed and combine those into a single NewRow() on your audit log.
The automation will also set a time-stamp column to Now() at the end of the process, so you can figure out what columns have changed the next time the automation is triggered for this row.
Thank you for the insight into the column Modified() and ModifiedBy() formulas.
The approach you are suggesting is, I think, on the same path as one I was about to take, but I think I landed on a slightly different solution.
I’ve changed the action of my automation to leverage the AddOrModifyRows() formula. Here I check the “Change Date” column I have in my audit table to track when the change happened. My expression for the fomula is to look for rows that match the row that is changing (also captured in my audit table) and for a “Change Date” less than 5 minutes old. Per the formula, it will either modify those rows (there will very likely only be 1), capturing the values of the columns of interest, and updating the “Change Date” column to be Now(). Of course, if it doesn’t find a recent change (less than 5 minutes old), it will add a new row to my audit table.
This seems to be working fine, but might be a little slow to fire the automation and capture the change. I may have to expand the age check to 10 minutes as it seems to capture changes within the last 3-4 minutes and that’s cutting it close if the user is slow in their row edits due to whatever circumstances.
I hope this makes sense as I’d appreciate any feedback on the approach I took here as it did not leverage the column formulas you were suggesting and I don’t want to miss a more optimal solution.