Accsys Consulting logo
About Accsys Consulting

Accsys Consulting is a renowned Canberra-based company, established in 1992, offering specialised financial management and information technology consulting services to valued public and private sector organisations. Our enduring commitment is to assist clients in developing their customer relationship management (CRM), enterprise resource planning (ERP), and financial management information systems (FMIS). We achieve this through a highly skilled team proficient in the implementation and support of leading software solutions like Sage Intacct, Sage 300 and Sage CRM.

With an impressive track record spanning over 30 years, the directors and staff of Accsys Consulting have continually demonstrated their expertise in successfully implementing and supporting Sage Intacct, Sage 300 and Sage CRM solutions. Our team includes Chartered Accountants who possess the expertise to aid clients in financial statement preparation, auditor liaison, and grant acquittal statement review.

Our exceptional reputation has been built upon delivering effective solutions, ensuring prompt implementation, and providing reliable personal service. Today, our respected clientele encompasses public sector organisations, listed public companies, not-for-profit organisations, and private companies.

The exceptional quality of our service makes us proud to have successfully implemented and supported Sage Intacct, Sage CRM and Sage 300 across sites throughout Australia, with a significant number of our clients within the Canberra region.

Partner with Accsys Consulting for comprehensive financial management and information technology consulting services that drive success and ensure optimal efficiency and growth for your valued organisation.

Follow Us On Social
 

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.