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!
- Create a configuration table
This defines what fields should be tracked.
- 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.”.
- 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.
- 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
- 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
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.