Detailed Change Tracking in Sage CRM

26 Sep Detailed Change Tracking in Sage CRM

Posted at 10:40h in Blog, Sage CRM by

A requirement that we’ve come across a few times now is to track when a particular field value changed and what the previous value was (e.g. when did the Assigned User change for Company record X?).  A co-worker of mine came up with a method of achieving this, that involved adding only two custom tables and triggers on triggers; I still remember my feeling of admiration at such an elegant solution!

Detailed setup steps are included below, but the gist of it is:
  1. Create a configuration table
    This defines what fields should be tracked.
    Image
  2. Create a tracking table
    This will contain each recorded change.  In the screenshot below, you can see that on 30/08/2013 the Admin user (trac_CreatedBy = 1) changed the Salutation on Person 1791, from “Mr.” to “Dr.”.
    Image2
  3. Create a master trigger on the configuration table
    This trigger runs whenever the configuration table is updated, has an insert or a delete action performed on it.  This master trigger will loop through the configuration rows and create update/insert triggers on the required entities (e.g. the Company table), that will add insert/update changes to the tracking table.

Detailed Setup

I’ve bundled all the SQL commands to set this up, in this script (it’s a 7z file, so you can use something like 7-Zip to extract it):  crm-detailed-tracking-creation-sql
To start tracking fields, just edit the configuration table in SQL Server and specify:
  • Tbl_Name:  The entity table name to track
  • Action: If insert, update or both (insert,update) actions should be tracked
  • Updated_Collist:  The comma-separated list of fields that should be tracked
  • Is_Enable: Set to ‘1’ to turn it on
Once you commit your changes to the table, the master trigger will pick them up and create or re-create the defined triggers.

Next Steps

  • The configuration table could be exposed in CRM via a regular custom page, allowing CRM admins to maintain the tracking definitions
  • Create CRM reports allowing managers to see what changes have been made, to records managed by their team
 

Update

02/01/2014: This setup assumes that your CRM database has defined primary key constraints on the table that you want to track.  This assumption is correct for new installs of Sage CRM 7.2, but if you have upgraded from an older instance, the database will not have those keys defined, and you will need to set them up manually.