Creating a record change log table displaying the old/new values of fields from an updated record.

Question

I'd like to track changes made to a record in my `Clients` table by writing the before and after values to a `Change History` table. For instance, if I updated the "Name", "Phone Number" and "Email" field in the `Clients` table , the Pipeline would create a new record for each field updated, showing the old/new values for that field, when it was updated, etc. The Change History records would contain the following fields:

`Field Name`
`Old Value`
`New Value`

Is there any way to do this in Pipelines?

------------------------------
Matt Makris
------------------------------

&nbsp;`Field Name`
`Old Value`
`New Value`

Is there any way to do this in Pipelines?&nbsp;------------------------------Matt Makris------------------------------

markshnier__you · Answer

Just adding two more comments on this. The first is that an alternative is simply logging the old value and the new value any time any of the field to your tracking changes. This will result in many records, which have no change and you were just purchased them out overnight.

Just adding two more comments on this. The first is that an alternative is simply logging the old value and the new value any time any of the field to your tracking changes. This will result in many records, which have no change and you can just purge them out overnight. &nbsp;You would have an embedded report on the table to show the change log, but you would have a filter on that report to not show records where there was no change in the field &nbsp;

The other tip is to include the table name in your logging table, and that way you can use the same table to log changes in many different tables and with the report link field, it would only include logging for say customers on the customers table and parking for orders on the orders table.
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

chayceduncan · Answer

You can use the $prev syntax in pipelines to handle it. If you're looking to track each field individually you might try something like:&nbsp;
Trigger: When the record changes on any of those fields
IF condition
if a.client_name != a.$prev.client_name
&nbsp; &nbsp; &nbsp;Log record to change history table logging phone as the field and the a.client_name as value and a.$prev.client_name as old
IF condition
if a.phone != a.$prev.phone
&nbsp; &nbsp; &nbsp;Log record to change history table logging phone as the field and the a.phone as value and a.$prev.phone as old------------------------------Chayce Duncan------------------------------

markshnier__you · Answer

I think you will find it problematic to construct your Pipeline branching, so that's why I suggest logging the old and new value for each field you are tracking, knowing that the vast majority of those log records will end up getting purged overnight as there is no change in that particular field.

------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------

mattmakris · Answer

Is there a way to write the actual field name to a column in the table so that we can see the field that was changed in the row? For instance, if there are 100 fields in the form and only the phone number was changed the row/record would reflect something like this:Field Changed | Old Value | New Value
"Phone" | "1234567890" | "0987654321"------------------------------Matt Makris------------------------------

markshnier__you · Answer

Suggest that in your log table you have separate fields for the table name, the field name, the old value and the new value and who made the change.&nbsp;&nbsp;
For the pipeline step that changes the field phone, you would just literally put Phone into the box for the field name.
------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

Forum Discussion

Creating a record change log table displaying the old/new values of fields from an updated record.

5 Replies

Related Content

Create a formula duration over 5 years

Old Form/New Form Builder/editor

Change Notification with Old and New Values

Create Record, pause, then display record

Bulk create child records from selection list

Recent Discussions

New Forms - Mobile support

Conditional formula url

To capture an XML response from an API in Pipelines

How to handle error in Upload step in Pipeline

Date value changes when copied to new table with pipeline.