Saturday, 14 March 2015

Conditional count of Related List

Counting items in related list

Following on from my previous blog where I mentioned that some things in look complicated at first but actually have a really easy solution this requirement sounded easy but required a trigger in the end to solve it.

The problem

My customer wanted to count have many Activity Records with a given criteria were linked to a Case record up to the point where the case was closed.

This count would be populated on a number field on the Case Object called: Number_of_Calls__c

This field would show how many interactions took place from Case Open to Case Close.

Unfortuantely, buried within the activity history on the case there were other non-relevant activities so a way to count specific tasks was required.

At this point, if Activity History was a Master Detail relationship under the Case then I could have added a Roll Up summary, but as it's a Lookup there's currently no easy way to do this out of the box.

Your 2 options are to either install a 3rd party app like Rollup Helper or Code a custom trigger.

For complete flexibility I went down the trigger route - here's the code.


trigger CountCalls on Case (before update) {
    Set<Id> ids = new Set<Id>();
    for (Case acc: {

//We don't want to add up calls in a delete operation
            System.debug('Delete operation - removing IDs from Map');
        if (trigger.isUpdate && (Trigger.newMap.get( == 'Closed' && Trigger.oldMap.get( != 'Closed')) {
            //Only count calls if the case changed to close
            String oldStatus = Trigger.oldMap.get(;
            String newStatus = Trigger.newMap.get(;
            System.debug(oldStatus + ' and new status ' + newStatus); 
            System.debug('Only cases that have just been closed should be counted');    
            Integer casesToUpdate = ids.size();        
            System.debug('Number of cases to update: ' + casesToUpdate);
    } //end of For loop
    List<Task> taskCount  = [SELECT ID from Task where (CallType = 'Inbound' OR CallType = 'Outbound') 
       AND Status = 'Completed' 
       AND WhatID In : ids];
    //Amend to specify only Inbound/Outbound etc
    System.debug('TaskCount is ' + taskCount);
    //Query how many tasks have that Case linked to them
    for (Case acc: {
        if (taskCount.size() > 0) {
            System.debug('Found tasks associated to this');
            acc.Number_of_Calls__c = taskCount.size();
        } else {
            System.debug('No tasks associated to this');
            acc.Number_of_Calls__c = 0;
} // end of trigger