Summarizing Salesforce fields with Triggers

Roll Up Summary fields are a Salesforce Admin’s best friend. But they’re only available on Master-Detail relationships. Understandable, but what to do when you need a summary of data when the master-detail relationship isn’t there? Triggers to the rescue In this example, I’ll be counting the number of activities related to an Opportunity. This one’s for you @Amber9904.

This example is a bit trickier since activities are made up of Tasks and Events…so we need to deal with each one. To do this, we’ll create 3 elements:

• A class called OpportunityActivityCount
• A Trigger on Task that fires After Insert, After Update, After Delete, and After Undelete
• A Trigger on Event that fires After Insert, After Update, After Delete, and After Undelete

Some requirements: You’ll need a new field on the opportunity table. Name it whatever you want, but for the attached code, it’s expecting Activity_Count (Activity_Count__c)

First off, the class:

(Note that we’re including the unit test in here as well. It depends on the triggers to be loaded later in order to work. Whether the unit tests belong here, or in a central test class is another topic)

public with sharing class OpportunityActivityCount {

    public static Boolean didRun = false;
    public static String oppPrefix =  Opportunity.sObjectType.getDescribe().getKeyPrefix();

    /*
    * Takes a set of opportunity IDs, queries those opportunities, and updates the activity count if appropriate
    */
    public static void updateOpportunityCounts(Set<ID> oppIds) {

        if (didRun == false) { //We only want this operation to run once per transaction.
            didRun = true;

            //Query all the opportunites, including the tasks child relationships
            List<Opportunity> opps = [SELECT ID, activity_count__c, (SELECT ID FROM Tasks), (SELECT ID FROM Events) FROM Opportunity WHERE ID IN :oppIds];
            List<Opportunity> updateOpps = new List<Opportunity>();

            for (Opportunity o : opps) {
                Integer count = o.tasks.size() + o.events.size();

                if (o.activity_count__c != count) {
                    o.activity_count__c = count;
                    updateOpps.add(o); //we're only doing updates to opps that have changed...no need to modify the others
                }
            }

            //Update the appropriate opportunities
            try {
                update updateOpps;
            } catch (Exception e) {
                //This is controversial. Anything could happen when updating the opportunity..validation rule, security, etc. The key is we don't
                //want the event update to fail...so we put a try catch around the opp update to make sure we don't stop that from happening.
            }
        }
    }

    /*
    * Test method for this class and TaskUpdateOpportunity and EventUpdateOpportunity
    */
    public static testMethod void testCountTask() {
        //Setup
        Account a = new Account(name='Test');
        insert a;

        Opportunity opp = new Opportunity(accountId = a.id, name='Test Opp', StageName='New', CloseDate=System.today());
        insert opp;

        //Insert our first task
        Task t = new Task(subject='Test Activity', whatId = opp.id);
        insert t;

        //Verify count
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(1,opp.activity_count__c);

        //Disconnect task from the opportunity
        didRun = false; //Reset
        t.whatId = null;
        update t;
        //Verify count = 0
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(0,opp.activity_count__c);

        didRun = false; //Reset
        //Add an event
        Event e = new Event(subject='Test Event', whatId = opp.id, startDateTime = System.Now(), endDateTime = System.now());
        insert e;

        //Verify count = 1
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(1,opp.activity_count__c);

        //Relink the task to the opportunity
        didRun = false; //Reset
        t.whatId = opp.id;
        update t;

        //Verify count = 2
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(2,opp.activity_count__c);

        //Disconnect the event from the opportunity
        didRun = false; //Reset
        e.whatId = null;
        update e;

        //Verify count is back down to 1
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(1,opp.activity_count__c);

        //Delete the task
        didRun = false; //reset
        delete t;
        //Verify count is back down to 0
        opp = [SELECT ID, activity_count__c FROM Opportunity WHERE ID = :opp.id];
        System.assertEquals(0,opp.activity_count__c);

    }

}

Next, the Task Trigger.

Notice that we’re capturing all of the “After” events.  This is important since deleting a task affects the count just as much as adding a task. Also note that we need to do a little be extra legwork when it comes to Tasks…the relationship from Task to Opportunity is a “soft” relationship…since tasks can be related to all sorts of record types. Given that, we’re doing a check on the task to see if it’s linked to an Opportunity to prevent trying to do a bunch of unneeded stuff on non-opportunity-related tasks.

trigger TaskUpdateOpportunity on Task (after delete, after insert, after undelete, after update) {

    Set<ID> oppIds = new Set<ID>();
    //We only care about tasks linked to opportunities.
    String prefix =  OpportunityActivityCount.oppPrefix;

    //Add any opportunity ids coming from the new data
    if (Trigger.new != null) {
        for (Task t : Trigger.new) {
            if (t.WhatId != null && String.valueOf(t.whatId).startsWith(prefix) ) {
                oppIds.add(t.whatId);
            }
        }
    }

    //Also add any opportunity ids coming from the old data (deletes, moving an activity from one opportunity to another)
    if (Trigger.old != null) {
        for (Task t : Trigger.old) {
            if (t.WhatId != null && String.valueOf(t.whatId).startsWith(prefix) ) {
                oppIds.add(t.whatId);
            }
        }
    }

    if (oppIds.size() > 0)
        OpportunityActivityCount.updateOpportunityCounts(oppIds);

}

Finally, the Event Trigger.

This should look familiar. It’s just like Task..but with event.

trigger EventUpdateOpportunity on Event (after delete, after insert, after undelete, after update) {

    Set<ID> oppIds = new Set<ID>();
    //We only care about tasks linked to opportunities.
    String prefix = OpportunityActivityCount.oppPrefix;

    //Add any opportunity ids coming from the new data
    if (Trigger.new != null) {
        for (Event e : Trigger.new) {
            if (e.WhatId != null && String.valueOf(e.whatId).startsWith(prefix) ) {
                oppIds.add(e.whatId);
            }
        }
    }

    //Also add any opportunity ids coming from the old data (deletes, moving an activity from one opportunity to another)
    if (Trigger.old != null) {
        for (Event e : Trigger.old) {
            if (e.WhatId != null && String.valueOf(e.whatId).startsWith(prefix) ) {
                oppIds.add(e.whatId);
            }
        }
    }

    if (oppIds.size() > 0)
        OpportunityActivityCount.updateOpportunityCounts(oppIds);

}

That’s it. Give it a try and let me know how it works out for you.

Here’s some items to keep in mind. The triggers are “bulkified” to account for large numbers of Tasks and Events. I would still recommend testing multiple task inserts…especially as they interact with add-on services like Outlook Connector, Conga, and mass email marketing tools.