Don't have an account?

Browse by Topic

+ Start a Discussion

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.

April 22, 2016
·
Like
0
·
Follow
5

Best Answer chosen by Shukla Yogesh

Mahesh 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

April 22, 2016
·
Like
2
·
Dislike
0

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

April 22, 2016
·
Like
0
·
Dislike
1

Vamsi
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 ...

April 22, 2016
·
Like
1
·
Dislike
0

This was selected as the best answer

Vamsi
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);

April 22, 2016
·
Like
0
·
Dislike
0

Shukla 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;

April 25, 2016
·
Like
0
·
Dislike
0

Shukla 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);
}

April 25, 2016
·
Like
0
·
Dislike
0

Vamsi
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.

April 25, 2016
·
Like
0
·
Dislike
0

Shukla 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;} }

April 25, 2016
·
Like
0
·
Dislike
0

Mahesh 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

April 25, 2016
·
Like
1
·
Dislike
0

Vamsi
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;
   }
}

April 25, 2016
·
Like
0
·
Dislike
0

Nagaraju 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

September 25, 2017
·
Like
0
·
Dislike
0

Shukla Yogesh
This is the field having reference of relationship so it will hold an recordid.

September 25, 2017
·
Like
0
·
Dislike
0

Nagaraju 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

September 26, 2017
·
Like
0
·
Dislike
0

Shukla 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.

September 26, 2017
·
Like
0
·
Dislike
0

Nagaraju 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

September 26, 2017
·
Like
0
·
Dislike
0

Umesh Rathi
Please refer below link:
https://sfdctree.wordpress.com/2019/03/04/write-a-trigger-which-will-populate-sum-of-child-field-values-on-parent/

July 14, 2020
·
Like
0
·
Dislike
0

You need to sign in to do that.

Need an account? Sign Up

Have an account? Sign In

Dismiss

Browse by Topic

Welcome to Support!

Show

sorted by

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

All Answers

You need to sign in to do that.