+ Start a Discussion
Shukla YogeshShukla Yogesh 

Updating field on parent using sum of child object values. I have to do this using Trigger.

trigger InvoiceSum on Invoice_Items__c (after insert, after update)
    {
     Set<Id> InvIds = new Set<Id>();   
     for(Invoice_Items__c ii :trigger.new)
     { InvIds.add(ii.InvoiceFK__c); }

        List<Invoice__c> InvRecs = [Select id, Ammount__c from Invoice__c where id in : InvIds ];
       
       for(Invoice__c inv : InvRecs)
        {
          //inv.Ammount__c= [Select sum(Invoice_items__r.Amount__c) from Invoice_Items__c ];
           Decimal total=0;
        //   for (Invoice_items__c iit: inc.Invoice_items__c)
          inv.Ammount__c =inv.Invoice_items__r.Amount__c;
            update inv;
        }
     
    }
Invoice__c is parent object and Invoice_item__c is child, I have to sum the all line items amount and display on Invoice__c record. Please help.
Best Answer chosen by Shukla Yogesh
Mahesh DMahesh D
Hi Shukla,

What is the relationship between 2 objects.

If it is a Master detail then you can use the RollUp Summary to calculate it.

If it is a Lookup then use the below code:

 
trigger InvoiceSum on Invoice_Items__c (after insert, after delete, after undelete, after update) {
    set<Id> invIdSet = new set<Id>();
    
    if(trigger.isinsert || trigger.isUpdate || trigger.Isundelete){
        for(Invoice_Items__c invItem: Trigger.new){
			if(Trigger.isInsert || Trigger.isUndelete || (invItem.Amount__c != Trigger.oldMap.get(invItem.Id).Amount__c || invItem.InvoiceFK__c != Trigger.oldMap.get(invItem.Id).InvoiceFK__c))
				invIdSet.add(invItem.InvoiceFK__c);            
        }
    }    
    if(trigger.isUpdate || trigger.isDelete) {
        for(Invoice_Items__c invItem: Trigger.old){
            if(Trigger.isDelete || (invItem.Amount__c != Trigger.newMap.get(invItem.Id).Amount__c || invItem.InvoiceFK__c != Trigger.newMap.get(invItem.Id).InvoiceFK__c))
				invIdSet.add(invItem.InvoiceFK__c);
        }
    }       
    List<Invoice__c> invList = [select id, Ammount__c, (Select Id, Amount__c from Invoice_Items__r) from Invoice__c Where ID IN: invIdSet];    
    for(Invoice__c inv : invList){
        
        inv.Ammount__c = 0;
        for(Invoice_Items__c invItem : inv.Invoice_Items__r) {
			inv.Ammount__c += invItem.Amount__c;
        }
    }
    update invList;
}

Please do let me know if it helps you.

Regards,
Mahesh

All Answers

venkat-Dvenkat-D
If you have Master-Detail relationship between Invoice and Invoice Item. Use Rollup summary field type to get SUM value.
VamsiVamsi
Hi Yogesh,

try the below 
trigger InvoiceSum on Invoice_Items__c (after insert, after update)
{
list<Invoice__c > invt = new list<Invoice__c >(); // adding invoices with updated amount 
list<ID> invid = new list<ID>(); // colletcting the invoice line item ids 
Set<Id> InvIds = new Set<Id>();
for(Invoice_Items__c ii :trigger.new)
{
InvIds.add(ii.InvoiceFK__c);
invid.add(ii.ID);
}
List<Invoice__c> InvRecs = [Select id, Ammount__c from Invoice__c where id in : InvIds ];
 
for(invoice __c inv : invrecs)
{

for (AggregateResult a : [select sum(Amount__c) amt from Invoice_Items__c where ID IN : invid])
{

inv.Ammount__c  = (decimal) a.get('amt');
invt.add(inv);
}

}

if (invt.size()>0)
{
update invt;
}
}

Please make sure with the object API names and let me know if you have any queries.

If it works, then please make sure to mark this as best answer ...

 
Mahesh DMahesh D
Hi Shukla,

What is the relationship between 2 objects.

If it is a Master detail then you can use the RollUp Summary to calculate it.

If it is a Lookup then use the below code:

 
trigger InvoiceSum on Invoice_Items__c (after insert, after delete, after undelete, after update) {
    set<Id> invIdSet = new set<Id>();
    
    if(trigger.isinsert || trigger.isUpdate || trigger.Isundelete){
        for(Invoice_Items__c invItem: Trigger.new){
			if(Trigger.isInsert || Trigger.isUndelete || (invItem.Amount__c != Trigger.oldMap.get(invItem.Id).Amount__c || invItem.InvoiceFK__c != Trigger.oldMap.get(invItem.Id).InvoiceFK__c))
				invIdSet.add(invItem.InvoiceFK__c);            
        }
    }    
    if(trigger.isUpdate || trigger.isDelete) {
        for(Invoice_Items__c invItem: Trigger.old){
            if(Trigger.isDelete || (invItem.Amount__c != Trigger.newMap.get(invItem.Id).Amount__c || invItem.InvoiceFK__c != Trigger.newMap.get(invItem.Id).InvoiceFK__c))
				invIdSet.add(invItem.InvoiceFK__c);
        }
    }       
    List<Invoice__c> invList = [select id, Ammount__c, (Select Id, Amount__c from Invoice_Items__r) from Invoice__c Where ID IN: invIdSet];    
    for(Invoice__c inv : invList){
        
        inv.Ammount__c = 0;
        for(Invoice_Items__c invItem : inv.Invoice_Items__r) {
			inv.Ammount__c += invItem.Amount__c;
        }
    }
    update invList;
}

Please do let me know if it helps you.

Regards,
Mahesh
This was selected as the best answer
VamsiVamsi
Hi,

A small change on the below lines 

inv.Ammount__c  = (decimal) a.get('amt');
invt.add(inv);


add "+" to the above code. use the below lines.

inv.Ammount__c + = (decimal) a.get('amt');
invt.add(inv);
 
Shukla YogeshShukla Yogesh

Thanks Vamshi for your quick response!!

Mahesh, I have a doubt in your code:

You have stored Ids in same set for both type operations update, undelete, insert & update, delete. I think you did this for maintaining the amount at parent for the cases when amount is decreased at child or a child item is deleted. But you have written just single db operation which sums in all cases.

Do we need to code like this for the cases when line item is deleted or is updated with a lesser amount?

if(trigger.isUpdate || trigger.isDelete) {  //also adding condition to check amoount updated is less than previous value
     for(Invoice_Items__c invItem : inv.Invoice_Items__r) {
        inv.Ammount__c -= invItem.Amount__c;
-------------------------
---------------------------
if(trigger.isinsert || trigger.isUpdate || trigger.Isundelete){ //also adding condition to check amoount updated is greater than previous value
     for(Invoice_Items__c invItem : inv.Invoice_Items__r) {
        inv.Ammount__c += invItem.Amount__c;

 

Shukla YogeshShukla Yogesh

Vamshi, I tried but your code works for new line item insertion but if update existing line with new amount it just adds new amount to total on parent. It should iterate all lines for any change in line items.

I think there is some problem with this code even if I initialize with 0 but it diesn't iterate on all line items :

for(invoice __c inv : invrecs)
{
inv.Ammount__c=0;
for (AggregateResult a : [select sum(Amount__c) amt from Invoice_Items__c where ID IN : invid])
{

inv.Ammount__c  = (decimal) a.get('amt');
invt.add(inv);
}

VamsiVamsi
Can you try replacing the below

inv.Ammount__c  = (decimal) a.get('amt');
with 
inv.Ammount__c + = (decimal) a.get('amt');

and please let me know if it works with update too or not.
Shukla YogeshShukla Yogesh
I have already updated that but it was not working:
trigger InvoiceSum on Invoice_Items__c (after insert, after update)
	{
     List<Invoice__c> invt = new List<Invoice__c>();   
     Set<Id> InvLineIds = new Set<Id>();   
     Set<Id> InvIds = new Set<Id>();   
        
     for(Invoice_Items__c ii :trigger.new)
     { InvLineIds.add(ii.Id);
       InvIds.add(ii.InvoiceFK__c);        
     }
  List<Invoice__c> InvRecs = [Select id, Ammount__c from Invoice__c where id in : InvIds ];
     for(Invoice__c inv : InvRecs)
        {
            inv.Ammount__c=0;
            for( AggregateResult a : [Select sum(Amount__c) amt from Invoice_Items__c  where Id in :InvLineIds])  
          {            
              inv.Ammount__c+= (decimal) a.get('amt');
              invt.add(inv);
          }
        }
        
        if(invt.size()>0){update invt;}
	}

 
Mahesh DMahesh D
Hi Shukla,

To calculate Rollup Summary using the Trigger, you need to follow below:

Need to consider all DML operations like insert, update, delete and undelete of child records.

Insert of Child record:
     --> Need to write an after insert trigger to update the Parent record with calculated sum value.
Update of Child record:
     --> Make sure that the Parent information got changed.
     --> Need to write an after update trigger and get both old and new Parent information and perform the re-calculation of sum.
Delete of Child record:
     --> Need to write an after delete trigger to update the Parent record with re-calculated sum value.
Undelete of Child record:
     --> Need to write an after undelete trigger to update the Parent record with re-calculated sum value.

Please do let me know if it helps you.

Regards,
Mahesh
VamsiVamsi
Hi,

Pleas try with the below code and let me know ...!!!. 


trigger InvoiceSum on Invoice_Items__c (after insert, after update)
    {
     List<Invoice__c> invt = new List<Invoice__c>();   
     Set<Id> InvLineIds = new Set<Id>();   
     Set<Id> InvIds = new Set<Id>();   
        
     for(Invoice_Items__c ii :trigger.new)
     { InvLineIds.add(ii.Id);
       InvIds.add(ii.InvoiceFK__c);        
     }
  
List<Invoice__c> InvRecs = [Select id, Ammount__c, (select id ,Amount__c from Invoice_Items__c  where ID IN : trigger.new) from Invoice__c where id in : InvIds ];

if(trigger.isinsert)
{     
for(Invoice__c inv : InvRecs)
  {
            
            for( AggregateResult a : [Select sum(Amount__c) amt from Invoice_Items__c  where Id in :InvLineIds])  
          {            
              inv.Ammount__c+= (decimal) a.get('amt');
              invt.add(inv);
          }
        
        
  }
}

if(trigger.isupdate)
{

for (Invoice__c in : InvRecs )
{    
    for (Invoice_Items__c inv : in.Invoice_Items__c )
{
    if(trigger.oldmap.get(inv.id).Amount__c !=trigger.newmap.get(inv.id).Amount__c && trigger.newmap.get(inv.id).Amount__c < trigger.oldmap.get(inv.id).Amount__c)
    
{
    in.Ammount__c -= trigger.oldmap.get(inv.id).Amount__c - trigger.newmap.get(inv.id).Amount__c ;
    invt.add(in);
}
else 
if(trigger.oldmap.get(inv.id).Amount__c !=trigger.newmap.get(inv.id).Amount__c && trigger.newmap.get(inv.id).Amount__c > trigger.oldmap.get(inv.id).Amount__c)
{

in.Ammount__c += trigger.newmap.get(inv.id).Amount__c ;
    invt.add(in);

}
}
}

}
if(invt.size()>0)
    {
    update invt;
    }
}
Nagaraju MogiliNagaraju Mogili
Hi Mahesh/Shukla,

Thanks for your code, what is the InvoiceFK__c in the above code, is this a field or Object..?

could you please let me know, we want to do the same program for our requirement.

Regards,
Nagaraju Mogili
Shukla YogeshShukla Yogesh
This is the field having reference of relationship so it will hold an recordid.
Nagaraju MogiliNagaraju Mogili
Hi Shukla/Mahesh,

I want the samething for the 3 Generations like child,parent and Grand parent with trigger..

if you have any idea about this, please share the code with me, it would be very helpful to me.

In this task --- the values which were inserted in the child records should be updated in the parent records and Grand parent records, as well as the values of child and parent records should be populated in the GrandParent Records.

Regards,
Nagaraju Mogili
Shukla YogeshShukla Yogesh
If you post what exactly you are looking for I can help. Please post the use case and what you have done and where are you stuck.
Nagaraju MogiliNagaraju Mogili
I have done for the Two objects as discussed above , Invoice and Invoice Line Items..

Here is My code.>>>>>>>>>>>

trigger Task1 on Invoice_Line_Item__c (after insert, after delete, after update, before update,before delete) {
    set<Id> invIdset = new set <id>();
    
    if(trigger.isinsert || trigger.isupdate){
        for(Invoice_Line_Item__c invitem : trigger.new){
            if(trigger.isinsert || trigger.isupdate || trigger.isundelete || (invitem.Amount__c != trigger.oldmap.get(invitem.Id).Amount__c
                || invitem.invoice__c != trigger.oldmap.get(invitem.Id).invoice__c)){
                invIdset.add(invitem.Invoice__c);
            }
        }
    }
    if(trigger.isupdate || trigger.isdelete){
        for(Invoice_Line_Item__c invitem : trigger.old){
            if(trigger.isDelete || (invitem.Amount__c != trigger.newMap.get(invitem.Id).Amount__c || invitem.Invoice__c != trigger.newMap.get(invitem.Id).invoice__c)){
                
            }
        }

      /*  if(trigger.isdelete){
             invoice__c inv;
            for(Invoice_Line_Item__c invitem : trigger.new){
                if(trigger.isDelete || (invitem.Amount__c != inv.Total_Bill__c)){
                    inv.Total_Bill__c =0;
                }else{
                    inv.Total_Bill__c = invitem.Amount__c;
                }
            }
        }*/
        
      list<invoice__c> invList = [select id,Total_Bill__c,(select id,Amount__c from Invoice_Line_Items__r) from Invoice__c where id in : invIdset]; 
    for(invoice__c inv : invList){
        
        inv.Total_Bill__c = 0;
        for(Invoice_Line_Item__c invitem :inv.Invoice_Line_Items__r){
            inv.Total_Bill__c +=invitem.Amount__c;
        }    
        
      }  
            upsert invList;

    }
}


I want to update these Invoice Line Item values in the PO(Grand Parent / third object) object.

Regards,
Nagaraju Mogili