The changes tracking log is a frequently asked question. That’s why we decided to describe three approaches that allow the organization of a changes tracking log.
The common idea is that changes are registered as related details that are kept in a separate table.
In this blog post we’ll describe three different approaches to implement changes tracking:
• One column is changed – One record is registered as a related detail
• Multiple columns are changed at a time – One record containing all columns’ values is added as a related detail
• Master record can’t be modified by a user – Changes are added as related details and can be displayed via summary columns in the Master record
Here is a prebuilt Tracking Column History example in the database library. This template includes all three approaches to let you test all of them and choose the one that meets your needs better.
Below is a detailed description of each approach. Please choose your favorite approach and implement it in your online database.
Approach #1. One Column – One Change
Let’s assume that you have “Company Profiles” table containing a “Name” column. When this column is changed and saved by a user, this changed value should be registered as a separate record in the “Name History” related details table.
Implementation of this method includes the following steps:
First, create a record change trigger with the record create action in the “Company Profiles” table:
Secondly, configure the “New Name History Record Create” action.
Please note that the assignments include the “Company” column. This is a single-reference column referring to the “Company Profiles” table.
As a result, when the “Name” column value is added or changed in the “Name” column of the Company Profile record, this Name value is getting registered in the “Name History” table.
Approach #2. Many Columns – One Change
Say, any column of the “Address” section can be modified via the “Change Address” custom button. Moreover, all changed and unchanged address values must be registered as one record in the related “Address History” table.
For example, the “Change Address” custom button should allow editing the Street, City, Zip, State columns. Please review the custom button’s settings on the screenshot below:
As you may have already noticed, there is the “New Address History Record Create” action added to the button. This action generates a new record in the “Address History” table when new address data is added or if some of the address-related columns are modified.
Please note that this action is also used in the “New Record Added” trigger. This trigger creates an Address History record, when a new record is added to the “Company Profiles” table and when any or none of the Address section fields are populated.
So, you should create a “New Record Added” trigger in the “Company Profiles” table and add the existing “New Address History Record Create” action to this trigger.
Approach #3. Related Details and Summary columns
With this approach, a user can’t edit “Account” data in the “Company Profile” master record; instead he/she can add changes tracking as related details. However, the summary columns can display changes in the master record automatically.
Implementation details of this changes tracking approach are described below.
Let’s consider an “Account” section of the Company Profile record. To change any value in this section, a user should add a new Account History record as a related detail under the “Company Profile” form. These new values will be displayed in the “Company Profiles” table via summary columns.
To implement this changes tracking approach, create a view in the “Account History” table that will be used as the details view. Let’s name this view “Company Details”. It will include the “Ownership”, “Annual Revenue”, “Employees”, “Created By”, “Date Created” columns.
Now, you should set this view as Details View in the relation settings:
After that you can add summary columns to this relation. They will allow the display of the last Account History changes data from the related details. This becomes possible due to the Index function included in the summary column settings.
An example of the summary column settings including the Index function are displayed on the screenshot below:
Similar settings should be added to other summary columns.
The third approach is now implemented as well.
You may find more details about the described Changes Tracking approaches in the Tracking Column History database template.