Conditional count of Related List

Counting items in related list

Following on from my previous blog where I mentioned that some things in Salesforce.com 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 Salesforce.com 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: Trigger.new) {
        
        if(trigger.isDelete){

//We don't want to add up calls in a delete operation
            ids.addAll(trigger.oldMap.keySet());
            System.debug('Delete operation - removing IDs from Map');
        }
        if (trigger.isUpdate && (Trigger.newMap.get(acc.id).Status == 'Closed' && Trigger.oldMap.get(acc.id).Status != 'Closed')) {
            //Only count calls if the case changed to close
            String oldStatus = Trigger.oldMap.get(acc.id).Status;
            String newStatus = Trigger.newMap.get(acc.id).Status;
            System.debug(oldStatus + ' and new status ' + newStatus); 
            
            System.debug('Only cases that have just been closed should be counted');    
            ids.addAll(trigger.newMap.keySet());
            
            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: Trigger.new) {
        
        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