+ Start a Discussion
Smike25Smike25 

Count cases on custom object

Hey all!

Looking for some assistance with a trigger that would be used to count the total number of cases that are listed on a custom object we've built.  Trucks_Under_Warranty__c is the custom object we have built. 

Under Cases we have the field "Truck Under Warranty__c" which is a lookup(Trucks Under Warranty) Indexed and its controlling field is Account Name.  Gets a little confusing for the "Truck/Trucks" in the field names.  
Case field TUW

The field used to hold the count is named Case Count (Case_Count__c).  I could use some direction on the trigger due to it being on this custom object.  We're wanting a way to designate if a truck is problematic or not based upon how many cases are generated under it.

I had been trying to look at a snippit of code someone posted for counting cases on an contact to understand it and translate it to our ORG
trigger tskUpdatetrigger on Case (after insert, after update,after delete) {
    List<Case> lstCase = [select id, ContactId from Case where id in: trigger.newmap.keyset()];
    set<Id> sAccId = new set<Id>();

    for(Case cs: lstCase){
        if(cs.ContactId != null){
            sAccId.add(cs.ContactId);
        }
    }
    if(sAccId != null && sAccId.size() > 0){
        List<Contact> lstContact = [select id, Cases__c, (select id from Cases) from Contact where id in: sAccId];
        if(lstContact.size() > 0){
            for(Contact acc: lstContact){
                acc.Cases__c = acc.Cases.size();
            }
                      update lstContact;
            }
    }
}

But i'm not sure what to change "ContactId" to and it work with our custom object.  I've also tried to read through sfdcfox's example ( https://developer.salesforce.com/forums/?id=906F00000008zIxIAI ) but it is still a little out of my apex knowledge base.  

TLDR: Would love some help with writing a trigger to count the number of cases on a custom object
Best Answer chosen by Smike25
AmulAmul
Hi Kevin,

you can see my LinkeIn Profile.
https://in.linkedin.com/in/amulbaranwal

Please let me know if i can help you.

Create this class



public class RollUpSummaryUtility {
     
    //the following class will be used to house the field names
    //and desired operations
    public class fieldDefinition {
        public String operation {get;set;}
        public String childField {get;set;}
        public String parentField {get;set;}
         
        public fieldDefinition (String o, String c, String p) {
            operation = o;
            childField = c;
            parentField = p;
        }
    }
     
    public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
    list<sObject> records, String childObject, String childParentLookupField, 
    String parentObject, String queryFilter) {
         
        //Limit the size of list by using Sets which do not contain duplicate
        //elements prevents hitting governor limits
        set<Id> parentIds = new set<Id>();
         
        for(sObject s : records) {
            parentIds.add((Id)s.get(childParentLookupField));
        }
         
        //populate query text strings to be used in child aggregrator and 
        //parent value assignment
        String fieldsToAggregate = '';
        String parentFields = '';
         
        for(fieldDefinition d : fieldDefinitions) {
            fieldsToAggregate += d.operation + '(' + d.childField + ') ' + 
            ', ';
            parentFields += d.parentField + ', ';
        }
         
        //Using dynamic SOQL with aggergate results to populate parentValueMap
        String aggregateQuery = 'Select ' + fieldsToAggregate + 
        childParentLookupField + ' from ' + childObject + ' where  ' + 
        childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
        ' group by ' + childParentLookupField;
         
        //Map will contain one parent record Id per one aggregate object
        map<Id, AggregateResult> parentValueMap = 
        new map <Id, AggregateResult>();
         
        for(AggregateResult q : Database.query(aggregateQuery)){
            parentValueMap.put((Id)q.get(childParentLookupField), q);
        }
         
        //list of parent object records to update
        list<sObject> parentsToUpdate = new list<sObject>();
         
        String parentQuery = 'select ' + parentFields + ' Id ' +
         ' from ' + parentObject + ' where Id IN :parentIds';
         
        //for each affected parent object, retrieve aggregate results and 
        //for each field definition add aggregate value to parent field
        for(sObject s : Database.query(parentQuery)) {
             
            Integer row = 0; //row counter reset for every parent record
            for(fieldDefinition d : fieldDefinitions) {
                String field = 'expr' + row.format();
                AggregateResult r = parentValueMap.get(s.Id);
                //r will be null if no records exist 
                //(e.g. last record deleted)
                if(r != null) { 
                    Decimal value = ((Decimal)r.get(field) == null ) ? 0 : 
                        (Decimal)r.get(field);
                    s.put(d.parentField, value);
                } else {
                    s.put(d.parentField, 0);
                }
                row += 1; //plus 1 for every field definition after first
            }
            parentsToUpdate.add(s);
        }
         
        //if parent records exist, perform update of all parent records 
        //with a single DML statement
        if(parentsToUpdate.Size() > 0) {
            update parentsToUpdate;
        }
         
    }
 
}





after this Write Trigger on Trucks_Under_Warranty__c ...see below example

trigger ContractTrigger on Contract (after delete, after insert, after update, after undelete) {
    if(Trigger.isUnDelete || Trigger.isDelete)
        ContractTriggerController.updateAccountCountLeases(Trigger.old);
    else
        ContractTriggerController.updateAccountCountLeases(Trigger.new); 
        
     // New code for Rollup Summary Field <Start>
     if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('COUNT', 'Id','no_of_Active_Lease__c')
           
        };
         
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new, 
        'Contract', 'AccountId', 'Account', 'and Is_Active__c=true');
         
    }
     
    if(trigger.isDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('COUNT', 'Id','no_of_Active_Lease__c')
        };
         
         RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old, 
        'Contract', 'AccountId', 'Account', 'and Is_Active__c=true');
    }
    
     // New code for Rollup Summary Field <End>
        
}



let me know what else i can help you

All Answers

AmulAmul
Hi Kevin,

you can see my LinkeIn Profile.
https://in.linkedin.com/in/amulbaranwal

Please let me know if i can help you.

Create this class



public class RollUpSummaryUtility {
     
    //the following class will be used to house the field names
    //and desired operations
    public class fieldDefinition {
        public String operation {get;set;}
        public String childField {get;set;}
        public String parentField {get;set;}
         
        public fieldDefinition (String o, String c, String p) {
            operation = o;
            childField = c;
            parentField = p;
        }
    }
     
    public static void rollUpTrigger(list<fieldDefinition> fieldDefinitions,
    list<sObject> records, String childObject, String childParentLookupField, 
    String parentObject, String queryFilter) {
         
        //Limit the size of list by using Sets which do not contain duplicate
        //elements prevents hitting governor limits
        set<Id> parentIds = new set<Id>();
         
        for(sObject s : records) {
            parentIds.add((Id)s.get(childParentLookupField));
        }
         
        //populate query text strings to be used in child aggregrator and 
        //parent value assignment
        String fieldsToAggregate = '';
        String parentFields = '';
         
        for(fieldDefinition d : fieldDefinitions) {
            fieldsToAggregate += d.operation + '(' + d.childField + ') ' + 
            ', ';
            parentFields += d.parentField + ', ';
        }
         
        //Using dynamic SOQL with aggergate results to populate parentValueMap
        String aggregateQuery = 'Select ' + fieldsToAggregate + 
        childParentLookupField + ' from ' + childObject + ' where  ' + 
        childParentLookupField + ' IN :parentIds ' + queryFilter + ' ' +
        ' group by ' + childParentLookupField;
         
        //Map will contain one parent record Id per one aggregate object
        map<Id, AggregateResult> parentValueMap = 
        new map <Id, AggregateResult>();
         
        for(AggregateResult q : Database.query(aggregateQuery)){
            parentValueMap.put((Id)q.get(childParentLookupField), q);
        }
         
        //list of parent object records to update
        list<sObject> parentsToUpdate = new list<sObject>();
         
        String parentQuery = 'select ' + parentFields + ' Id ' +
         ' from ' + parentObject + ' where Id IN :parentIds';
         
        //for each affected parent object, retrieve aggregate results and 
        //for each field definition add aggregate value to parent field
        for(sObject s : Database.query(parentQuery)) {
             
            Integer row = 0; //row counter reset for every parent record
            for(fieldDefinition d : fieldDefinitions) {
                String field = 'expr' + row.format();
                AggregateResult r = parentValueMap.get(s.Id);
                //r will be null if no records exist 
                //(e.g. last record deleted)
                if(r != null) { 
                    Decimal value = ((Decimal)r.get(field) == null ) ? 0 : 
                        (Decimal)r.get(field);
                    s.put(d.parentField, value);
                } else {
                    s.put(d.parentField, 0);
                }
                row += 1; //plus 1 for every field definition after first
            }
            parentsToUpdate.add(s);
        }
         
        //if parent records exist, perform update of all parent records 
        //with a single DML statement
        if(parentsToUpdate.Size() > 0) {
            update parentsToUpdate;
        }
         
    }
 
}





after this Write Trigger on Trucks_Under_Warranty__c ...see below example

trigger ContractTrigger on Contract (after delete, after insert, after update, after undelete) {
    if(Trigger.isUnDelete || Trigger.isDelete)
        ContractTriggerController.updateAccountCountLeases(Trigger.old);
    else
        ContractTriggerController.updateAccountCountLeases(Trigger.new); 
        
     // New code for Rollup Summary Field <Start>
     if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('COUNT', 'Id','no_of_Active_Lease__c')
           
        };
         
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new, 
        'Contract', 'AccountId', 'Account', 'and Is_Active__c=true');
         
    }
     
    if(trigger.isDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('COUNT', 'Id','no_of_Active_Lease__c')
        };
         
         RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old, 
        'Contract', 'AccountId', 'Account', 'and Is_Active__c=true');
    }
    
     // New code for Rollup Summary Field <End>
        
}



let me know what else i can help you
This was selected as the best answer
Smike25Smike25
Thank you for the lengthy solution attempt!  Im kind of confused on the trigger example with "
trigger ContractTrigger on Contract".  Should that be changed to "Cases" for the solution im looking for?  When I look at saving what is written now I get "Error: Compile Error: Variable does not exist: ContractTriggerController at line 3 column 9".  I could completely be missing and not understanding though
AmulAmul
yes  , you have to write trigger on case..
i just gave an example...that's it.

I can fix thix quickly for you. let me know ..you can approach me directly on amulhai@gmail.com
Tammer SalemTammer Salem
I'm not 100% clear on the use case here - but put simply you want to count all the cases that have an account referenced, and update that account with a total number of cases. Presumably it's all cases (regardless of open or not or resolution). Why are you querying the Contact?

You should be able to do this by getting the list of Account IDs that are referenced from the case, then loop on those Account IDs, and do a SOQL
[SELECT COUNT() FROM Case where AccountId = the Account ID in the loop ];
Which will give you the number of cases related to that account.
Smike25Smike25
Hey Tammer, 

Im not trying to do anything at all with Contact.  In my ORG we have a custom object named "Truck Inventory" which is the container for all of our inventory.  Our warranty department has an object called "Trucks Under Warranty".  Whenever a piece of truck inventory is sold through an opportunity, it creates a Truck Under Warranty record.  Our warranty department then goes into those records and adds Cases to them based upon any warranty calls.  I'm trying to make a way to count the total number of cases per truck (not per account), so that way they can easily see problematic trucks.  Does that make sense?  I can post some screen caps of how things are set up if needed.
Smike25Smike25
Amul, 

Thanks for the help.  I'll go back and edit the example to see if I can get it correct and i'll post the results here.  Thanks for offering to modify it, but i'd like the practice at going back and editing and learning it more.
Ashley Cobb 24Ashley Cobb 24
Hi Amul,

I have almos this exact same class in my org, but seem to be hitting limits.  Do you know any way of modifying this to get around that?  The error that I am getting says its happening on line 83, column 1 which is the area in your code below:


  for(sObject s : Database.query(parentQuery)) {
             
            Integer row = 0; //row counter reset for every parent record
            for(fieldDefinition d : fieldDefinitions) {
                String field = 'expr' + row.format();
                AggregateResult r = parentValueMap.get(s.Id);
                //r will be null if no records exist 
                //(e.g. last record deleted)
                if(r != null) { 
                    Decimal value = ((Decimal)r.get(field) == null ) ? 0 : 
                        (Decimal)r.get(field);
                    s.put(d.parentField, value);
                } else {
                    s.put(d.parentField, 0);
                }
                row += 1; //plus 1 for every field definition after first
            }
            parentsToUpdate.add(s);
        }


Any help would be appreciated!!

Thanks,