9

I have a custom object where I need to track data changes to a lot of fields for regulatory reasons, far more than the maximum of 20 fields for standard history tracking. (Actually Salesforce increased this limit to 35 for our org, but that's far from enough.)
So I thought about building a history mechanism myself. I guess that a couple of people have already done that: How did you do it?

I thought about:
- an after update trigger
- that browses the object's schema, gets all the fields (at least for reasonable field types, not for formula fields etc.),
- compares the old and new value of each field and
- stores a record of a custom history object for each field where there is a difference, plus the current time, user and the old and new value.

What do you think about this approach?

My problem is how I can store the values for fields with different field types. AnyType (http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_primitives.htm) cannot be used. Do I need to provide fields for each field type in my custom history object (e. g. oldStringValue, newStringValue; oldIntegerValue, newIntegerValue etc.)? This makes it cumbersome to show the history records in a related list and to report on the history data.

Edit: This custom object documents due dilligence checks and similar compliance-related data. Regulators want us to have a very detailed documentation on this and we have to make sure that any changes get tracked.

4
  • 2
    Wouldn't you be able to store all the values in a string? Numbers and dates are easily converted to a string and back. Jul 30, 2013 at 9:54
  • Wow. This sounds really painful. What is the record and why does it have so many fields? Jul 30, 2013 at 10:31
  • Lex, storing all values as strings is a good idea. As the history is primarily for tracking changes, I don't need to calculate with integers or dates.
    – georg w.
    Jul 30, 2013 at 10:38
  • user320, I updated my post.
    – georg w.
    Jul 30, 2013 at 10:42

3 Answers 3

4

Here is my solution. It works dynamically on all fields of certain types (I have tested with String, Picklist, Date, DateTime, Integer, Boolean). You could extend it so that not only updates, but also inserts are tracked. Edit: For some weeks, this code has been working in production without problems.

Be aware that it can compromise security because changes to fields are tracked even if the user cannot see the field on the page layout.

The object for which the history information will be kept is called Compliance__c; the history is stored in ComplianceHistory__c.

This is inside the after update trigger for Compliance__c:

    //get all fields from compliance that we want to check for changes
    Map<String, Schema.SObjectField> allComplFieldsMap = Schema.SObjectType.Compliance__c.fields.getMap();
    complFieldsToTrack = new Map<String, Schema.DescribeFieldResult>();
    for (Schema.SObjectField complField : allComplFieldsMap.values()) {
        Schema.DescribeFieldResult describeResult = complField.getDescribe();
        //choose which fields to track depending on the field type
        if (describeResult.getType() == Schema.DisplayType.Boolean ||
            describeResult.getType() == Schema.DisplayType.Combobox ||
            describeResult.getType() == Schema.DisplayType.Currency ||
            describeResult.getType() == Schema.DisplayType.Date ||
            describeResult.getType() == Schema.DisplayType.DateTime ||
            describeResult.getType() == Schema.DisplayType.Double ||
            describeResult.getType() == Schema.DisplayType.Email ||
            describeResult.getType() == Schema.DisplayType.Integer ||
            describeResult.getType() == Schema.DisplayType.MultiPicklist ||
            describeResult.getType() == Schema.DisplayType.Percent ||
            describeResult.getType() == Schema.DisplayType.Phone ||
            describeResult.getType() == Schema.DisplayType.Picklist ||
            describeResult.getType() == Schema.DisplayType.String ||
            describeResult.getType() == Schema.DisplayType.TextArea ||
            describeResult.getType() == Schema.DisplayType.Time ||
            describeResult.getType() == Schema.DisplayType.URL) 
        {
            //don't add standard fields that are not necessary
            if (describeResult.getName() != 'CreatedDate' &&
                describeResult.getName() != 'LastModifiedDate' &&
                describeResult.getName() != 'SystemModstamp' &&
                //only add fields that are visible to the current user
                describeResult.isAccessible() &&
                //do not add formula fields
                !describeResult.isCalculated()
                )
            {
                complFieldsToTrack.put(describeResult.getName(), describeResult);
            }
        }
    }

The following is done for every object in Trigger.new inside a loop:

    Compliance__c oldCompl = (Compliance__c)oldSo;
    Compliance__c newCompl = (Compliance__c)so;

    for (Schema.DescribeFieldResult fieldDescribe : complFieldsToTrack.values()) {
        if (oldCompl.get(fieldDescribe.getName()) != newCompl.get(fieldDescribe.getName())) {
            ComplianceHistory__c complHistory = createUpdateHistory(fieldDescribe, oldCompl, newCompl);
            historiesToInsert.add(complHistory);
        }
    }

Here is the method to populate the history object referenced in the loop:

private ComplianceHistory__c createUpdateHistory(Schema.DescribeFieldResult field, Compliance__c oldCompl, Compliance__c newCompl) {
    ComplianceHistory__c complHistory = new ComplianceHistory__c();
    complHistory.Compliance__c = newCompl.Id;
    complHistory.Event__c = 'Edit';
    complHistory.Field__c = field.getLabel();
    complHistory.User__c = UserInfo.getUserId();
    // shorten strings that are longer than 255 characters (can happen if the field has the type textArea)
    if (complHistory.OldValue__c != null) complHistory.OldValue__c = complHistory.OldValue__c.abbreviate(255);
    if (complHistory.NewValue__c != null) complHistory.NewValue__c = complHistory.NewValue__c.abbreviate(255);
    complHistory.EditDate__c = System.now();
    return complHistory;
}

In the end, the history records are inserted:

    if (!historiesToInsert.isEmpty()) {
        //remove duplicate history entries
        List<ComplianceHistory__c> historiesToInsertWithoutDuplicates = new List<ComplianceHistory__c>();
        Set<ComplianceHistory__c> historiesSet = new Set<ComplianceHistory__c>();
        historiesSet.addAll(historiesToInsert);
        historiesToInsertWithoutDuplicates.addAll(historiesSet);

        //insert the rest
        insert historiesToInsertWithoutDuplicates;
    }
3
  • 1
    This seems to track every possible field change by default, which is fine, but could be more than you need and may end up filling your Compliance History table with unnecessary records. Another approach would take a static list of field names (defined within the Class or in a Custom Setting) and only record history of those fields, instead of any field on the entire record. Sep 16, 2013 at 18:02
  • In this lines: Compliance__c oldCompl = (Compliance__c)oldSo; Compliance__c newCompl = (Compliance__c)so; Are you mean the new and old record in the trigger?
    – Edgar
    Apr 22, 2014 at 2:06
  • Yucel - Yes, these are the new and old records from Trigger.newMap and Trigger.oldMap. "so" and "oldSo" are variables of type SObject that are cast to Compliance__c. Passing the objects as SObject is part of the generic trigger structure I use.
    – georg w.
    Apr 23, 2014 at 15:36
2

This is a common task in database design in general. There are two approaches, depending on your needs. It sounds like you probably just need the first option.

  1. If you just need to record the value changes, then a table with Old Value, New Value, Modified By, Modified Date, and a primary key is the simplest way to go. The values stored can just be their string (or serialized equivalent) version, as suggested by Lex.

  2. If you need a complete picture of the record at a point in time then a full history object may be needed. In this case you would have a full shadow object that gets populated whenever something is changed.

Fr more ideas and best practices I would suggest searching for "SQL history table best practices".

0

I would like to suggest the generic implementation of feature Audit Trail for your requirement . Where through configuration settings you provide the database Tables and Fields for which history needs to maintain. Implementation of database trigger technique do it work of identifying value change to capture on Audit Trail.

These history will be maintained in separate optimized table with Primary Key, Foreign Key, Modification By & Date, Others fields, Delimited encoded information Name, Type, Value pair containing historical values for configured fields. Decode encoded information to make use of it when needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.