You need to sign in to do that
Don't have an account?
Shukla 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.
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:
Please do let me know if it helps you.
Regards,
Mahesh
All Answers
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 ...
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:
Please do let me know if it helps you.
Regards,
Mahesh
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);
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;
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);
}
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.
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
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;
}
}
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
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
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
https://sfdctree.wordpress.com/2019/03/04/write-a-trigger-which-will-populate-sum-of-child-field-values-on-parent/