+ Start a Discussion
Ankit SehgalAnkit Sehgal 

Roll up Summary Trigger

I need to calculate the sum of tax field of all the quote line items which have taxable check box true and display it on the Quote field.The tax field on the line item is a formula field so i am unable to create a roll up summary.I wrote a trigger but it is failing in delete case.Can somebody guide me where i am goinge wrong with it 
trigger CalculateTotalTax on QuoteLineItem (after insert,after update,after delete) 
{
    list<QuoteLineItem> l;
    if(Trigger.isInsert || Trigger.isUpdate)
    {
    	l =[SELECT Id,QuoteId,Tax__c,Taxable__c FROM QuoteLineItem WHERE ID IN: Trigger.new];
    }
    if(Trigger.isDelete)
    {
        l =[SELECT Id,QuoteId,Tax__c,Taxable__c FROM QuoteLineItem WHERE ID IN: Trigger.old];
    }
    Decimal ttx=0.00;
    String qid;
    for(QuoteLineItem q : l)
    {
        qid=q.QuoteId;
    }
    for(QuoteLineItem f : l)
    {
        if(f.Taxable__c == true)
        {
                ttx+=f.Tax__c;
        }
    }
    Quote q=[SELECT Id,TotalTax__c FROM Quote WHERE Id =: qid];
    q.TotalTax__c=ttx;
    System.debug(ttx);
    update q;
}
Best Answer chosen by Ankit Sehgal
Deepak Kumar 138Deepak Kumar 138
I have corrected your to code to avoid the exception - 
 
trigger CalculateTotalTax on QuoteLineItem (after insert,after update,after delete) 
{
    list<QuoteLineItem> l;
    if(Trigger.isInsert || Trigger.isUpdate)
    {
    	l = Trigger.new;
    }
    if(Trigger.isDelete)
    {
        l = Trigger.old;
    }
    Decimal ttx=0.00;
    String qid;
    for(QuoteLineItem q : l)
    {
        qid=q.QuoteId;
    }
    for(QuoteLineItem f : l)
    {
        if(f.Taxable__c == true)
        {
                ttx+=f.Tax__c;
        }
    }
    Quote q=[SELECT Id,TotalTax__c FROM Quote WHERE Id =: qid];
    q.TotalTax__c=ttx;
    System.debug(ttx);
    update q;
}

But, If you looking for better code which handles bulk scenarois and is sclable, use the code below - 

Use this utility 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) {
            try{
                update parentsToUpdate;
            }catch(Exception e){
                ApexPages.addmessage(new ApexPages.message(ApexPages.severity.ERROR,e.getDMLMessage(0)));
                System.debug('Exception'+e.getDMLMessage(0));
            }
        }
         
    }
 
}

And update your trigger to as below - 
 
trigger CalculateTotalTax on QuoteLineItem (after delete, after insert, after update, after undelete){
	if(trigger.isInsert || trigger.isUpdate || trigger.isUnDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('SUM', 'Tax__c', 
            'TotalTax__c')
        };
         
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.new, 
        'QuoteLineItem', 'QuoteId', 'Quote', 'And Taxable__c = true');
	}	
	
	if(trigger.isDelete){
         
        list<RollUpSummaryUtility.fieldDefinition> fieldDefinitions = 
        new list<RollUpSummaryUtility.fieldDefinition> {
            new RollUpSummaryUtility.fieldDefinition('SUM', 'Tax__c', 
            'TotalTax__c')
        };
         
        RollUpSummaryUtility.rollUpTrigger(fieldDefinitions, trigger.old, 
        'QuoteLineItem', 'QuoteId', 'Quote', 'And Taxable__c = true');
	}	
}

Note - Mark this as best answer if it solves your problem.