Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
sfdc_newbie17sfdc_newbie17 

Trigger to calculate sum of value on child object

Hi all

I am trying to calcualte the sum of values on a custom object "invoice__c" to Opportunity which is a lookup relationship. As it's not MD I can't use rollup summary. The field on invoice is value and needs to be aggregrated the the "total invoice value" on the opportunty object.

Can anyone help, everything I've tried so far hasn't worked.

Thanks
Best Answer chosen by sfdc_newbie17
Parteek Goyal 3Parteek Goyal 3
Hi,

Please try below code.
  
trigger updateOpportunityDis on invoice__c (after insert, after update, after delete, after undelete) {
    Set<ID> setID = new Set<ID>();
    List<Opportunity> lstAcc = new List<Opportunity>();
    
    if(trigger.isinsert || trigger.isundelete){
        for(invoice__c c : trigger.new){
            setID.add(c.Opportunityid);
        }
    }
    else if(trigger.isDelete){
        for(invoice__c c : trigger.old){
            setID.add(c.Opportunityid);
        }
    }
    
    else if(trigger.isUpdate){
         for(invoice__c c : trigger.new){
            if(c.Opportunityid != null){
                if(trigger.oldmap.get(c.id).Opportunityid != c.Opportunityid){
                    setID.add(c.Opportunityid);     
                }
            } 
            setID.add(trigger.oldmap.get(c.id).Opportunityid);
         }
    }
    if(setid.size() > 0){
        lstAcc = [Select id,total_invoice_value__c,(Select id,value__c from invoice__cs) from Opportunity where id IN : setID];
    }
    for(Opportunity acc : lstAcc){
		integer val = 0;
        for(invoice__c con : acc.invoice__cs){
            
            val += con.value__c;
            
        }
        acc.total_invoice_value__c = val;
    }
    update lstAcc;
}

Let me know if you face any issue.

Thanks,
Parteek

All Answers

Waqar Hussain SFWaqar Hussain SF
Please refer the below article.

https://salesforce.stackexchange.com/questions/33650/how-to-write-a-rollup-summary-trigger-for-a-lookup-relationship
Parteek Goyal 3Parteek Goyal 3
Hi,

Please try below code.
  
trigger updateOpportunityDis on invoice__c (after insert, after update, after delete, after undelete) {
    Set<ID> setID = new Set<ID>();
    List<Opportunity> lstAcc = new List<Opportunity>();
    
    if(trigger.isinsert || trigger.isundelete){
        for(invoice__c c : trigger.new){
            setID.add(c.Opportunityid);
        }
    }
    else if(trigger.isDelete){
        for(invoice__c c : trigger.old){
            setID.add(c.Opportunityid);
        }
    }
    
    else if(trigger.isUpdate){
         for(invoice__c c : trigger.new){
            if(c.Opportunityid != null){
                if(trigger.oldmap.get(c.id).Opportunityid != c.Opportunityid){
                    setID.add(c.Opportunityid);     
                }
            } 
            setID.add(trigger.oldmap.get(c.id).Opportunityid);
         }
    }
    if(setid.size() > 0){
        lstAcc = [Select id,total_invoice_value__c,(Select id,value__c from invoice__cs) from Opportunity where id IN : setID];
    }
    for(Opportunity acc : lstAcc){
		integer val = 0;
        for(invoice__c con : acc.invoice__cs){
            
            val += con.value__c;
            
        }
        acc.total_invoice_value__c = val;
    }
    update lstAcc;
}

Let me know if you face any issue.

Thanks,
Parteek
This was selected as the best answer
sfdc_newbie17sfdc_newbie17
Hi Parteek,

Thanks for this but getting the following error Variable does not exist: Opportunityid at line 7 column 25, also is the invoice__cs supposed to be invoice__c?
Parteek Goyal 3Parteek Goyal 3
Hi,

Please use the opportunity lookup field API which you have created in invoice object instead of Opportunityid . Secondly please use child relationship name of opportunity lookup field(Please add  __r after that) instead of invoice__cs.

Thanks,
Parteek
sfdc_newbie17sfdc_newbie17
I have changed opportunityid to opportunity__c.id which seems to have got past the error, but this is what I get now. Code attached

Error: Compile Error:
(Select id,invoice_value__c from invoice_item__r) from Opportunity
^
ERROR at Row:1:Column:68
Didn't understand relationship 'invoice_item__r' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 27 column 18
  
trigger updateOpportunityDis on invoice_item__c (after insert, after update, after delete, after undelete) {
    Set<ID> setID = new Set<ID>();
    List<Opportunity> lstAcc = new List<Opportunity>();
    
    if(trigger.isinsert || trigger.isundelete){
        for(invoice_item__c c : trigger.new){
            setID.add(c.opportunity__r.id);
        }
    }
    else if(trigger.isDelete){
        for(invoice_item__c c : trigger.old){
            setID.add(c.opportunity__r.id);
        }
    }
    
    else if(trigger.isUpdate){
         for(invoice_item__c c : trigger.new){
            if(c.opportunity__r.id != null){
                if(trigger.oldmap.get(c.id).opportunity__r.id != c.opportunity__r.id){
                    setID.add(c.opportunity__r.id);     
                }
            } 
            setID.add(trigger.oldmap.get(c.id).opportunity__r.id);
         }
    }
    if(setid.size() > 0){
        lstAcc = [Select id,total_invoice_value__c, (Select id,invoice_value__c from invoice_item__r) from Opportunity where id IN : setID];
    }
    for(Opportunity acc : lstAcc){
        integer val = 0;
        for(invoice_item__c con : acc.invoice_item__c){
            
            val += con.invoice_value__c;
            
        }
        acc.total_invoice_value__c = val;
    }
    update lstAcc;
}

 
Parteek Goyal 3Parteek Goyal 3
Hi,

Can you please share the screen shot of opportunity lookup field on invoice item object?

Thanks,
Parteek
sfdc_newbie17sfdc_newbie17
User-added image

I have also changed opportunity__r.id to opportunity__c
Parteek Goyal 3Parteek Goyal 3
Click on opportunity field and share the screen shot.

Thanks,
Parteek
sfdc_newbie17sfdc_newbie17
Hi Parteek,

Here you go. thanks again

User-added image
Parteek Goyal 3Parteek Goyal 3
Please use invoices__r instead of invoice_item__r

Thanks,
Parteek
sfdc_newbie17sfdc_newbie17
That's great Parteek, it works!!

Only issue I'm facing is the 50k limit, is there anyway around this? I have approx 700k invoice records
Glyn Anderson 3Glyn Anderson 3
I know you got your answer already, but a better solution is to use a SOQL aggregate query to do the work for you.  This would be in a "before update" trigger:

<pre>
for ( AggregateResult result :
    [   SELECT  SUM(Value__c) total_value, Opportunity__c
        FROM    Invoice__c
        WHERE   Id IN :Trigger.new]
        GROUP BY Opportunity__c
    ]
    )
{
    Trigger.newMap( (Id) result.get( "Opportunity__c" ) ).Total_Value__c = (Decimal) result.get( "total_value" );
}
</pre>
Glyn Anderson 3Glyn Anderson 3
As far as updating 700k records, you will have to write a batch process.
Parteek Goyal 3Parteek Goyal 3
Hi,

Please create a report of opportunity with invoice item and extract the opportunity id, invoice item id,value, total value fields. After that use pivot table in ms-excel and update the data from dataloader for now. after that trigger will automatically update the correct data on every edit of invoice item.

Let me know if you face any issue.

Thanks,
Parteek