Replacing Formula Fields with Triggers

Question

I'm working to clear out some formula fields of a MainObject. Due to some Salesforce limitations, we decided to use triggers instead.

Great, so the first formula field I tried to replace was Formula_Field__c: Custom_Object__r.Related_Object__r.Name & " - "& Custom_Object__r.Account__c

All we need to do is to create another text field and populate it using the same formula, right? Nope, it’s not that simple:

If you try inserting/updating a Object and look at the debug logs, you will see that Custom_Object__r evaluates to null. However the field Custom_Object__c (from that MainObject) does hold the Id of the related Custom_Object__c.

That does seem a bit odd when you come across it for the first time. It isn’t exactly intuitive, right? The reason this happens is because in before / after insert / update triggers, relationship fields are not reference-able directly, as there is no way for Salesforce to know which fields of the related object it should load and make available. It cannot load all Custom_Object__c and make them available, for reasons of efficiency (just a guess). So, if you need to access fields on related objects, you will first have to query for them and then use the references.

So, I created the field Formula_Field_Trigger__c, and this is the trigger that will populate it:

trigger ReplaceFormulaFields on MainObject__c(before insert, before update) {

        List<Id> scIds = new List<Id>();
        List<Id> cIds = new List<Id>();

        // loop to load the Ids of Custom_Object__c       
        For(Tag_Order__c toTrig :trigger.new)
        {
           scIds.add(toTrig.Custom_Object__c);
        }

        // loop to load the Ids of the respective Related_Object__c
        List<Custom_Object__c> scList = [SELECT Id, Name, Related_Object__c, Account__c FROM Custom_Object__c where Id in :scIds];
        For(Custom_Object__c sc :scList)
        {
           cIds.add(sc.Related_Object__c);
        }

        // Now we map the results in order to make it available to get and concatenate it into the new field

        Map<Id,Custom_Object__c> scMap = new Map<Id,Custom_Object__c>([SELECT Id, Name, Related_Object__c,Account__c FROM Custom_Object__c where Id in :scIds]);
        Map<Id,Related_Object__c> cMap = new Map<Id,Related_Object__c>([SELECT Id, Name FROM Related_Object__c where Id in :cIds]);


        // Finally, we get the values and add it to the new field:  
        For(Tag_Order__c toTrig :trigger.new)
        {
          toTrig.Formula_Field_Trigger__c = cMap.get(scMap.get(toTrig.Custom_Object__c).Related_Object__c).Name + ' - ' + scMap.get(toTrig.Custom_Object__c).Account__c;
        }

}

OK. It works like a charm. But now I wonder if this is the most efficient solution. All suggestions are very welcome.

Thanks!

Answer 1

That's entirely too complicated. Here's an easier version:

trigger ReplaceFormulaFields on MainObject__c(after insert, before update) {
    if(Trigger.isInsert) {
        // After insert, we perform a recursive update.
        // We need to clone to avoid error about DML on Trigger.new.
        update Trigger.new.deepClone(true);
    } else {
        // Query for our values here
        for(MainObject__c record: [SELECT Custom_Object__r.Related_Object__r.Name, Custom_Object__r.Account__c FROM MainObject__c WHERE Id IN :Trigger.new]) {
            // And populate the field value.
            // Thanks to how SOQL records work, no NullPointerException
            // should occur if the lookup is empty.
            Trigger.newMap.get(record.Id).Formula_Field_Trigger__c = record.Custom_Object__r.Related_Object__r.Name + ' - ' record.Custom_Object__r.Account__c;
        }
    }
}

You could have also accomplished this with a simple workflow rule, which can copy data across records well.