Counting Open and Closed Activities in Salesforce

Counting Open and Closed Activities in Salesforce

The problem: I needed to log how many open Activities there were on Leads, Contacts, Opportunities, and Accounts as well as Activity History. 

I have similar triggers in the past but in this case I wanted a more flexible approach that would work across multiple objects.

Using standard reporting you can report on Objects with or without Activity records. You could also explore solutions such as Rollup Helper but in this case the customer wanted to see an easy count total on the top level of the object.

Before I wrote any code I had a look around the wonder-web and found a great bit of code that took me 60% of the way there.

A huge thank you goes out to rjpalombo for his initial work on Counting Open Activities. The blog below takes his example code and adds counting of closed Activities.


The following code example just expands on rjpalombo's code so that I could count Activity History as well as Open Activities.


Setup

Custom Fields

Create the custom fields as described in rjpalombo's blog.

You will need to add these 2 custom fields to Accounts, Contacts, Leads, and Opportunities.
  1. NVM Activities (NVM_Activities__c) - This is the count of all completed Activity History
  2. Open Activities (Open_Activity_Count__c) - This is the count of all Open Activities

The Trigger

This Trigger will fire whenever a new Task is inserted into the system and will call the Utility Class to perform the calculation. As we have marked this Trigger with after insert, after update, after delete, after undelete then the Trigger will also fire when Tasks are deleted. This is needed to reduce the overall count of Open/Closed activities.

trigger NVMTaskTrigger on Task (after insert, after update, after delete, after undelete) {
    
    sObject[] triggerRecords;
    if(!trigger.isDelete) triggerRecords = trigger.new;
    else triggerRecords = trigger.old;
    
    //Update Open Activity Count
    ActivityUtils au = new ActivityUtils(triggerRecords);
    au.updateAccountActivityCount();
    au.updateContactActivityCount();
    au.updateLeadActivityCount();
    au.updateOpportunityActivityCount();
    au.updateCaseActivityCount();
}

The Test Class

trigger NVMTaskTrigger on Task (after insert, after update, after delete, after undelete) {
    
    @IsTest
public class NVMTaskTrigger {
    
    static testMethod void mainTest() {
        Account a = new Account();
        a.Name = 'Test Account';
        a.Industry = 'Other';
        
        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert a;
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
        
        Contact c = new Contact();
        c.FirstName = 'Joe';
        c.LastName = 'Smith';
        c.AccountId = a.Id;
        c.Email = 'test@test.com';
        
        
        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert c;
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
    
        Opportunity o = new Opportunity();
        o.AccountId = a.Id;
        o.StageName = 'Open';
        o.CloseDate = Date.today() + 7;
        o.Name = 'Test Opp';

        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert o;
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
        Lead l = new Lead();
        l.LeadSource = 'Other';
        l.FirstName = 'Joe';
        l.LastName = 'Smith';
        l.Industry = 'Other';
        l.Status = 'New Lead';
        l.Company = 'Test Co';
        
        
        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert l;
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
        
        Task[] tList = new list<task>();
        Event[] eList = new list<event>();
        //Inserting 4 tasks
        for(Integer i=0; i<4; i++) {
            Task t = new Task();
            t.Status = 'Not Started';
            t.Priority = 'Normal';
            t.Type = 'Scheduled Call Back';
            if(i==0) t.WhatId = a.Id;
            if(i==1) t.WhatId = o.Id;
            if(i==2) t.WhoId = c.Id;
            if(i==3) t.WhoId = l.Id;
            tList.add(t);
            //And 4 Events
            Event e = new Event();
            e.StartDateTime = DateTime.now() + 7;
            e.EndDateTime = DateTime.now() + 14;
            if(i==0) e.WhatId = a.Id;
            if(i==1) e.WhatId = o.Id;
            if(i==2) e.WhoId = c.Id;
            if(i==3) e.WhoId = l.Id;
            eList.add(e);
        }
        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert tList;
            
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
        
        try {
            // Perform some database operations that 
            //   might cause an exception.
            insert eList;
            
            
        } catch(DmlException e) {
            // DmlException handling code here.
            System.debug('The following exception has occurred: ' + e.getMessage());
            
        }
        
        test.startTest();
        //system.assertEquals(6, [SELECT NVM_Tasks__NVM_Activities__c FROM Account WHERE Id = :a.Id].NVM_Tasks__NVM_Activities__c);
        //system.assertEquals(2, [SELECT NVM_Tasks__NVM_Activities__c FROM Opportunity WHERE Id = :o.Id].NVM_Tasks__NVM_Activities__c);
        //system.assertEquals(2, [SELECT NVM_Tasks__NVM_Activities__c FROM Contact WHERE Id = :c.Id].NVM_Tasks__NVM_Activities__c);
        //system.assertEquals(2, [SELECT NVM_Tasks__NVM_Activities__c FROM Lead WHERE Id = :l.Id].NVM_Tasks__NVM_Activities__c);
              
        //Delete the rest activities and run assertions again for zero
        delete tList;
        system.assertEquals(0, [SELECT NVM_Tasks__NVM_Activities__c FROM Account WHERE Id = :a.Id].NVM_Tasks__NVM_Activities__c);
        system.assertEquals(0, [SELECT NVM_Tasks__NVM_Activities__c FROM Opportunity WHERE Id = :o.Id].NVM_Tasks__NVM_Activities__c);
        system.assertEquals(0, [SELECT NVM_Tasks__NVM_Activities__c FROM Contact WHERE Id = :c.Id].NVM_Tasks__NVM_Activities__c);
        system.assertEquals(0, [SELECT NVM_Tasks__NVM_Activities__c FROM Lead WHERE Id = :l.Id].NVM_Tasks__NVM_Activities__c);
        test.stopTest();
        
    }
    
    
    
}

The Utility Class

Next, create this Apex Class. This Utility Class performs the actual count of Activities and just takes in the WhoID or WhatID of the object that's passed into it.

You could expand this class to be able to accommodate other objects as well such as Case in future.

public class ActivityUtils {
    
    //config
    
    String fieldToUpdate = 'NVM_Activities__c'; //this field must be added to each object we're updating
    String fieldOpenToUpdate = 'Open_Activity_Count__c'; //this field must be added to each object we're updating
    
    //state
    set<id> accountIds;
    set<id> contactIds;
    set<id> opportunityIds;
    set<id> leadIds;
    set<id> caseIds;
    
    public ActivityUtils(sObject[] records) {
        accountIds = new set<id>();
        contactIds = new set<id>();
        opportunityIds = new set<id>();
        leadIds = new set<id>();
        caseIds = new set<id>();
        captureWhatAndWhoIds(records);
        addAccountIdsFromRlatedObjects();
    }
    
    public void updateAccountActivityCount() {
        if(accountIds.size() == 0) return;
        updateActivityCount('Account','WhatId', getStringFromIdSet(accountIds));
         updateActivityHistory('Account','WhatId', getStringFromIdSet(accountIds));
        
        
    }
    public void updateContactActivityCount() {
        if(contactIds.size() == 0) return;
        updateActivityCount('Contact','WhoId', getStringFromIdSet(contactIds));
                updateActivityHistory('Contact','WhoId', getStringFromIdSet(contactIds));

    }
    public void updateOpportunityActivityCount() {
        if(opportunityIds.size() == 0) return;
        updateActivityCount('Opportunity','WhatId', getStringFromIdSet(opportunityIds));
                updateActivityHistory('Opportunity','WhatId', getStringFromIdSet(opportunityIds));

    }
    public void updateLeadActivityCount() {
        if(leadIds.size() == 0) return;
        updateActivityCount('Lead','WhoId', getStringFromIdSet(leadIds));
                updateActivityHistory('Lead','WhoId', getStringFromIdSet(leadIds));

    }
    public void updateCaseActivityCount() {
        if(caseIds.size() == 0) return;
        //updateActivityCount('Case','WhoId', getStringFromIdSet(caseIds));
        //updateActivityHistory('Case','WhoId', getStringFromIdSet(caseIds));

    }
    private void updateActivityCount(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM OpenActivities) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            OpenActivity[] oActivities = so.getSObjects('OpenActivities');
            Integer openActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldOpenToUpdate, openActivityCount);
            sobjects.add(obj);
            system.debug('openActivityCount: ' + openActivityCount);
        }
        update sobjects;
    }
     
    private void updateActivityHistory(String objToUpdate, String queryFld, String updateIds) {
        string strQuery = 'SELECT Id, (SELECT Id FROM ActivityHistories) FROM ' + objToUpdate + ' WHERE Id IN (' + updateIds + ')';        
System.debug(strQuery);
        sObject[] sobjects = new list<sobject>();
        for(sObject so : database.query(strQuery)) {
            ActivityHistory[] oActivities = so.getSObjects('ActivityHistories');
            Integer closedActivityCount = oActivities == null ? 0 : oActivities.size();
            sObject obj = createObject(objToUpdate, so.Id);
            obj.put(fieldToUpdate, closedActivityCount);
            sobjects.add(obj);
            system.debug('ActivityHistoryCount: ' + closedActivityCount);
        }
        update sobjects;
    }
    
    private void captureWhatAndWhoIds(sObject[] objects) {
        for(sObject o : objects) {
            Id whatId = (Id)o.get('WhatId');
            Id whoId = (Id)o.get('WhoId');
            if(whatId != null) {
                String objectName = getObjectNameFromId(whatId);
                if(objectName == 'account') accountIds.add(whatId);
                if(objectName == 'opportunity') opportunityIds.add(whatId);
            }
            if(whoId != null) {
                String objectName = getObjectNameFromId(whoId);
                if(objectName == 'contact') contactIds.add(whoId);
                if(objectName == 'lead') leadIds.add(whoId);
            }
        }
    }
    
    private void addAccountIdsFromRlatedObjects() {
        for(Opportunity o : [SELECT AccountId FROM Opportunity WHERE Id IN :opportunityIds]) accountIds.add(o.AccountId);
        for(Contact c : [SELECT AccountId FROM Contact WHERE Id IN :contactIds]) accountIds.add(c.AccountId);
    }
    
    private String getObjectNameFromId(Id objId) {
        String preFix = String.valueOf(objId).left(3).toLowercase();
        if(prefix == '001') return 'account';
        if(prefix == '003') return 'contact';
        if(prefix == '006') return 'opportunity';
        if(prefix == '00q') return 'lead';
        //if(prefix == '500') return 'case';
        return '';
    }
    
    private String getStringFromIdSet(set<id> idSet) {
        string idString = '';
        for(Id i : idSet) idString+= '\'' + i + '\',';
        return idString == '' ? idString : idString.left(idString.length()-1); //If idString contains some ids we want to ensure we strip out the last comma
    }
    
    //The main part of the method below was taken from //Taken from http://www.salesforce.com/us/developer/docs/apexcode/Content/apex_dynamic_dml.htm
    //However we've modified this to accept an object id
    private sObject createObject(String typeName, Id objId) {
        Schema.SObjectType targetType = Schema.getGlobalDescribe().get(typeName);
        if (targetType == null) {
            // throw an exception
        }
        
        // Instantiate an sObject with the type passed in as an argument
        //  at run time.
        return targetType.newSObject(objId);
    }
    
} 

End Result

Once you have added these components you should start to see the Open and Closed Activity count start to be updated as you log new Activities against Accounts, Contacts, Leads, and Opportunities.

If you wanted to perform a one time update to get all of the fields populated I'd recommend following the steps in the original blog post.




Comments