Forum Discussion

MattMakris's avatar
MattMakris
Qrew Cadet
10 months ago

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

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
------------------------------

5 Replies

  • 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: 

    Trigger: When the record changes on any of those fields

    IF condition

    if a.client_name != a.$prev.client_name

         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

         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's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      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.  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  

      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
      ------------------------------

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend

        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
        ------------------------------