function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Need to SUM lookup up child records values in trigger

What is the Best approach if we want to Calculate SUM of ChildRelationship records (through LookUp relationship) NOT Mast-Detail Relationship.


My scenerio is this:


Contact: Name, Balance

Bill:         Name, Amount, Contact__c (Lookup Field)


So I want to Fire a trigger on Bill such that whenever a Bill is updated I can Update Contact.Balance value with SUM of Amount for ALL Bills Where Contact__c = current_saving_bill.Contact__c.




Also I'm not able to work this approach with this case: Say If I change Contact__c value in bill so I now Want to Update Contact.Balance value for current edited bill's OLD Contact__c and NEW Contact__c value. :( can anyone help me devise a solution for this ...





I'm working on the same thing right now, though I have run into a problem you may not have the same issue.

This is what I am doing.




Trigger on insert/update on Bill


loop through collecting ContactID for every Bill record passed in the trigger's dataset, ensuring that your list of contactIds is unique.  (Check for the IDs existance in your list before adding.)


Loop through the list with a for loop and a query that pulls the contact and all of it's bill children, where is in your contactid list


For loop through each contact, sub loop through that contact's bills  (contact.bill) and add the values that you want to roll up in the appropriate field in contact.   Don't forget to initialize the destination values in your contact to zero.


Finally, update the contacts with a single update call.



This approach will take care of bulk triggers, as well as query and dml limitations.  




The problem that I have run into is where the subquery (bills in your case) contains more then 200 records, you will get an Invalid Query Locator error.   (I believe it to potentially be a bug in Salesforce and it's being looked at now.) 

See my thread here for more info and a better code example.  There is a potential workaround but in my case it breaks down for other reasons, though it may work out for you.



Good luck.






Thanks I checked that link too .. I just did a short test with Updating a Bill whose contact already had 250 bills, and the Query something like this:

Select Id, (select Id, Amount__c From Bills__r) From Contact


And it didn't caused the error of QueryLocator . .so I guess it might have been fixed ...


But my question and concern now deepens more .. as I'm concerned with Multiple Masst Insert/Update of records ....


Running the query like this and then Loop through All records (Bills) in for loop .. don't you think I will run into Maximum script statements error ? as each line will be counted as script statement ....


Also I might be running into More of the Future issue .. which will be like I got 1000's of bills for each contact then I can surpass the Governor Limits quite easiliy ... also, Is there any mechanism like Database.QueryLocator() .. by which I can process Batches of records in trigger ? ...



This is an amazing resource and it deals with handling bulk triggers and rollups.






This link appears to be a basic rollup but it doesn't handle bulk triggers.

The APEX provided will request two queries per record in the Trigger's dataset, which will quickly run into query limits if bulk triggers are used.