+ Start a Discussion
shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com 

Calculate summary field on Look up (Master Record), using trigger , Please Help!!!

Hi All

    I have 2 custom objects A__C , B__c . Object B looks upto Object A.

 Now B has a field say "Revenue__c". A__c has a field "Total Revenue". I wanna write a trigger on B to update Total Revenue__c on A , upon addition,deletion or update of B record. Please help me with the outline of that triggger.

 

 

Thanks

Best Answer chosen by Admin (Salesforce Developers) 
JayNicJayNic

What?! Soql queries in for loops? Guys, this is APEX development 101!

 

Do not put SOQL queries in for loops!

 


This is the proper batchable way to do this... It will work for you:

trigger objB_aIaUaDaUD on cObjB__c (after insert, after update, after delete, after undelete) {



    map<id,double> revenueAdjustments = new map<id,double>(); //A map of Object A ids, to revenue adjustments
    
    if(trigger.isInsert || trigger.isUndelete) { //for inserts and un deletes we do simpel addition of the revenue supplied to the parent revenue
        for(cObjb__c b : trigger.new) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c);
            
            revenueAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + b.Revenue__c);
        }
    }
    
    if(trigger.isDelete) { //for deletes, we will subtract the revenue amount on the record from the parent...
        for(cObjB__c b : trigger.old) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c)*-1;
            
            revenueAdjustments.put(b.CustomObjectA__c, (currentAdjustmentValue + b.Revenue__c)*-1 ); //Make sure to flip it back to a negative value
        }
    }
    
    if(trigger.isUpdate) { //For updates, we will only adjust base don the difference to our revenue
        for(cObjB__c b : trigger.new) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c);
            
            double adjustmentValue = b.Revenue__c - trigger.oldMap.get(b.id).Revenue__c; //Only figure out the difference
            
            revenueAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + adjustmentValue );
        }
    }
    
    if(!revenueAdjustments.isEmpty()) {
        cObjA__c[] objectAs = [SELECT id, TotalRevenue__c FROM cObjA__c WHERE id IN :revenueAdjustments.keyset()];
        
        for(cObjA__c a : objectAs) {
            a.TotalRevenue__c = a.totalRevenue__c + revenueAdjustments.get(a.id);
        }
        
        update objectAs;
    
    }
    

}

 

Trust me. The other solutions will break under pressure. Mine will not.

Simply copy past this thing, I just did it in my own org.

Replace my fields an objects with yours and you are done.

All Answers

prakash_sfdcprakash_sfdc
If you need to calculate SUM or AVERAGE, you can create a roll up summary field on Object A__c

For trigger, use the following code:

trigger updateTotalRevenue on B(after update, after delete){

for(B__c ob:Trigger.new)
{
List<A__c> temp = [SELECT Total_Revenue__c FROM A__c WHERE Id=:ob.lookupFieldName LIMIT 1];
//Do all the calculations

temp[0].Total_Revenue__c = 10;

update temp[0];

}

}
souvik9086souvik9086

Hi,

 

try this

 

trigger revenueUpdation on B__c(after insert,after update, after delete){

if(Trigger.isDelete()){

for(B__c bObj : Trigger.old){
A__c aObj = new A__c;

aObj = [SELECT Total_Revenue__c FROM A__c WHERE Id =: bObj.lookupFieldName ];

 

aObj.Total_Revenue__c += bObj.Revenue__c;

upsert aObj;
}

}

else{
for(B__c bObj : Trigger.new){
A__c aObj = new A__c;

aObj = [SELECT Total_Revenue__c FROM A__c WHERE Id =: bObj.lookupFieldName ];

 

aObj.Total_Revenue__c += bObj.Revenue__c;

upsert aObj;
}
}
}

 

If this post solves your problem kindly mark it as solution. if this post is helpful please throw Kudos.

Thanks

 

Bhawani SharmaBhawani Sharma
You need to write aggregate query like
Select A__c a,SUM(Revenue__c) total from B__c where A__c =: b.A__c group By A__c

Now create a new instance on A__c using alias a and update total revenue
JayNicJayNic

What?! Soql queries in for loops? Guys, this is APEX development 101!

 

Do not put SOQL queries in for loops!

 


This is the proper batchable way to do this... It will work for you:

trigger objB_aIaUaDaUD on cObjB__c (after insert, after update, after delete, after undelete) {



    map<id,double> revenueAdjustments = new map<id,double>(); //A map of Object A ids, to revenue adjustments
    
    if(trigger.isInsert || trigger.isUndelete) { //for inserts and un deletes we do simpel addition of the revenue supplied to the parent revenue
        for(cObjb__c b : trigger.new) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c);
            
            revenueAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + b.Revenue__c);
        }
    }
    
    if(trigger.isDelete) { //for deletes, we will subtract the revenue amount on the record from the parent...
        for(cObjB__c b : trigger.old) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c)*-1;
            
            revenueAdjustments.put(b.CustomObjectA__c, (currentAdjustmentValue + b.Revenue__c)*-1 ); //Make sure to flip it back to a negative value
        }
    }
    
    if(trigger.isUpdate) { //For updates, we will only adjust base don the difference to our revenue
        for(cObjB__c b : trigger.new) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c);
            
            double adjustmentValue = b.Revenue__c - trigger.oldMap.get(b.id).Revenue__c; //Only figure out the difference
            
            revenueAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + adjustmentValue );
        }
    }
    
    if(!revenueAdjustments.isEmpty()) {
        cObjA__c[] objectAs = [SELECT id, TotalRevenue__c FROM cObjA__c WHERE id IN :revenueAdjustments.keyset()];
        
        for(cObjA__c a : objectAs) {
            a.TotalRevenue__c = a.totalRevenue__c + revenueAdjustments.get(a.id);
        }
        
        update objectAs;
    
    }
    

}

 

Trust me. The other solutions will break under pressure. Mine will not.

Simply copy past this thing, I just did it in my own org.

Replace my fields an objects with yours and you are done.

This was selected as the best answer
shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com

Thanks A ton JayNic. You saved me a hell lot of time!!!!

shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com
One more thing I wanna ask is how can I get this code to work for 3 different types of roll ups.
i.e there are three fields on b object debt, revenue, expenses. And I wanna map them to 3 different firleds of A object i.e total debt, total revenue and total expenses.. please help
shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com

Hi Jay Nic,

      There is also something wrong with line

 

 a.TotalRevenue__c = a.totalRevenue__c + revenueAdjustments.get(a.id);

This is throwing a null pointer exception in my case. Please help!!!1
JayNicJayNic

most likely you have empty values in your TotalRevenue__c field. You should enforce that a number is entered even if it is zero

 

 

shrey.tyagi88@tcs.comshrey.tyagi88@tcs.com

Got it Jay, Your code worked like a charm!!!!

JayNicJayNic

glad to hear it!

 

If you need to augment this to roll up additional fields, then you can still use the same principles...

 

near the original map, create another map eg:

   map<id,double> revenueAdjustments = new map<id,double>(); //A map of Object A ids, to revenue adjustments
    map<id,double> otherAdjustments = new map<id,double>(); //A map of Object A ids, to some other field

 And for each if statement, simply add the additional other field using the same form eg:

 

    if(trigger.isInsert || trigger.isUndelete) { //for inserts and un deletes we do simpel addition of the revenue supplied to the parent revenue
        for(cObjb__c b : trigger.new) {
            if(revenueAdjustments.get(b.CustomObjectA__c) == null) {
                revenueAdjustments.put(b.CustomObjectA__c, 0);
            }
            double currentAdjustmentValue = revenueAdjustments.get(b.CustomObjectA__c);
            
            revenueAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + b.Revenue__c);
            
            if(otherAdjustments.get(b.CustomObjectA__c) == null) {
                otherAdjustments.put(b.CustomObjectA__c, 0);
            }
            currentAdjustmentValue = otherAdjustments.get(b.CustomObjectA__c);
            
            otherAdjustments.put(b.CustomObjectA__c, currentAdjustmentValue + b.OtherRevenueField__c);

        }
    }


.... do this for the other if blocks

 

Then in the final if statement at the bottom, you just need to make sure that it takes in to consideration the new maps, eg:

 

    if(!revenueAdjustments.isEmpty() || !otherAdjustments.isEmpty()) {
        cObjA__c[] objectAs = [SELECT id, TotalRevenue__c FROM cObjA__c WHERE id IN :revenueAdjustments.keyset() OR id IN :otherAdjustments.keySet()];
        
        for(cObjA__c a : objectAs) {
            //Need a little more logic in here since we may or may not be using all the maps
            if(revenueAdjustments.get(a.id) != null) {
                a.TotalRevenue__c = a.totalRevenue__c + revenueAdjustments.get(a.id);
            }
            if(otherAdjustments.get(a.id) != null) {
                a.TotalOtherField__c = a.TotalOtherField__c + otherAdjustments.get(a.id);
            }
        }
        
        update objectAs;
    
    }

 

And you're still only using one soql query!