How to count Activities/Tasks for a record

on

How to count Activities/Tasks for a record


A few months ago, I posted some Apex that counted and summarised the number of Tasks associated to your Opportunity/Account/Contact/Lead et cetera.

You can visit the old post by clicking this link.

When this class ran you would have a new field visible on your Object page showing you how many activities were logged against it.

Opportunity Screen showing new All Tasks count (bottom right)



So, why do the code again?

The first post proved really popular and several people commented that they have used it.

A few people said that they got an error in production environments when adding the code to databases with many thousands of Task records. This was the error some people were getting:
System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop: ()

What's different?

I took a look at the code again and using a few great blog posts on Apex Sets and Maps I've created a v2 version that shouldn't hit a Salesforce limit when used in an environment with many thousands of records.

Try it out and let me know!

Configuration

This script has been written for the Opportunity object but can be easily amended for any object.

To start, create 3 new fields on the Opportunity Object.

Example of new field
  1. All Tasks - AllTasks__c  Number(18, 0)
  2. Closed Tasks - Closed_Tasks__c  Number(18, 0)
  3. Open Tasks -  Open_Tasks__c  Number(18, 0)
One of the fields that needs adding to your Opportunity

Go to your Page Layout of the Opportunity object and move the fields to wherever you would like them on the page.

I recommend making them read only as the script will auto-fill them for you.


The updated Opportunity layout

Then, create a new Apex Trigger in Developer Console

Select New > Apex Trigger

I called mine CountTasks and you need to associate it to the Opportunity object

The Developer Console screen
If you have never used the Developer Console, check out this great guide from Trailhead.

The Code

Then, just copy and paste this code into your new Trigger.

trigger CountTasks on Opportunity (before update) {
    
    // Put all Opportunities into a Set
    Set allOppIDs = new Set();
    for (Opportunity newOpp : Trigger.new) {
        allOppIDs.add(newOpp.ID);
        System.debug('ID added: ' + newOpp.ID);
    }
    
    //Query All Tasks to find Tasks with matching IDs
    List allTasks = [SELECT Id, Status, WhatID from Task
                           WHERE WhatID IN :allOppIDs];
    System.debug('allTasks is ' + allTasks.size());
    // Create a Map that lets you search for Tasks by their ID - faster than SOQL each time
    
    List TaskArray = new List(); //All Tasks
    List IDToTaskMapOpen = new List(); //usedforOpenTasks
    List IDToTaskMapClosed = new List(); //usedforClosedTasks
    
    Map elCountAll = new Map(); //Count of All Tasks
    Map elCountOpen = new Map(); //Count of Open Tasks
    Map elCountClosed = new Map(); //Count of Closed Tasks
    
    
    
    for (Task u : allTasks) {
        if (u.Status != 'Completed' ) {
            IDToTaskMapOpen.add(u.WhatID);
            System.debug('Added Open Task');
            
        }
        if (u.Status == 'Completed') {
            System.debug('Added Completed Task');
            IDToTaskMapClosed.add(u.WhatID);
            
        }
        TaskArray.add(u.WhatID);
    }  
    
    System.debug(allTasks.size());
    System.debug(IDToTaskMapOpen.size());
    System.debug(IDToTaskMapClosed.size());
    
    
    
    // Get the matching tasks from the Map - and count Status
    //Start with our Opportunity
    for (Opportunity newOpp : Trigger.new) {
        
        //Count all Tasks
        for(String key : TaskArray)
        {
            if(!elCountAll.containsKey(key)){
                elCountAll.put(key,0);
            }
            Integer currentInt=elCountAll.get(key)+1;
            elCountAll.put(key,currentInt);
        }
        
        
        //Count all Open Tasks
        for(String key1 : IDToTaskMapOpen)
        {
            if(!elCountOpen.containsKey(key1)){
                elCountOpen.put(key1,0);
            }
            Integer currentInt1=elCountOpen.get(key1)+1;
            elCountOpen.put(key1,currentInt1);
        }
        
        //Count all Closed Tasks
        for(String key2 : IDToTaskMapClosed)
        {
            if(!elCountClosed.containsKey(key2)){
                elCountClosed.put(key2,0);
            }
            Integer currentInt2=elCountClosed.get(key2)+1;
            elCountClosed.put(key2,currentInt2);
        }
        
        
        //Finally update the record
        //All Activities
        newOpp.AllTasks__c = elCountAll.get(newOpp.ID);
        
        //Open & Not Started Tasks
        newOpp.Open_Tasks__c = elCountOpen.get(newOpp.ID);
        
        //Closed Tasks
        newOpp.Closed_Tasks__c = elCountClosed.get(newOpp.ID);
        
    }
    
    
}

When do the totals get updated?

This Trigger has been created with the 'before update' keywords. That means that it updates the count of Tasks whenever the Opportunity is updated.

So, just make a change to the Opportunity to update the figures.

Links


Special thanks to the following awesome posts:

David Liu - Data Collections - Lists and Maps - http://www.sfdc99.com/2013/09/28/data-collections-lists-sets-and-maps/


Icon from Iconfinder, artist Vecteezy.

Comments

Post a Comment