+ Start a Discussion
pierrefrazny.ax358pierrefrazny.ax358 

Rollup with Apex

I have been working on this trigger for a day now so I wondering if anybody could give me ideas. I have 2 objects: Account and Gig__c. I need to calculate the sum of Total_Estimated_Hours__c (on Gig__c) and copy it to of_Hours__c (on Account). The trigger works but I woulk to understand how I can move the second SELECT out of the loop. Here is the portion of the code in question:

 

 

trigger UpdateTotalHours1 on Gig__c (after delete, after insert, after update) { set<ID> acctIDs = new Set<ID>(); if(Trigger.isInsert) { for(Gig__c g : System.Trigger.new){ acctIDs.add(g.Customer__c); } Account[] accts = new List<Account>(); //get the list of acccount accts = [select Id, of_Hours__c from Account where ID in :acctIDs]; Account[] updatedAccounts = new List<Account>(); for (Account a : accts ){ Double SumOfHours = 0; for (Gig__c gig : [SELECT Id, Total_Estimated_Hours__c from Gig__c where Customer__c=:a.Id]) { SumOfHours += gig.Total_Estimated_Hours__c; } a.of_Hours__c = SumOfHours; updatedAccounts.add(a); } update updatedAccounts; }}

 

 

 

 

 

Thanks.

 

Pierre 

 

 


 

Message Edited by pierrefrazny on 04-16-2009 03:18 PM
Best Answer chosen by Admin (Salesforce Developers) 
BritishBoyinDCBritishBoyinDC

Looks good from here. Excellent!

 

Be sure to test every scenario though - e.g. if you delete the final Gig record for an account - does the trigger handle there being no gig records in the gis list...you might need to check if certain lists have at least one records before you do any processing...

All Answers

werewolfwerewolf

Make your list of Accounts into a List<Id>, let's call it accountIds.  Then you can make your query like:

 

[SELECT Id, Total_Estimated_Hours__c,Customer__c from Gig__c where Customer__c IN accountIds]

 

 

pierrefrazny.ax358pierrefrazny.ax358

Thanks for the answer. I am trying to understand your suggestion but I don't. Could you give me a little more details?

 

Thanks

Pierre 

BritishBoyinDCBritishBoyinDC

You're on the right lines, but put the SOQL results for the accounts into a map and then you can reference the related accounts  during the loop from the map using a get reference based on the (one) original query

pierrefrazny.ax358pierrefrazny.ax358

Thanks for the reply. I think I am making progress but I am still missing something. Here is my updated code:

 

trigger UpdateTotalHours1 on Gig__c (after delete, after insert, after update) { set<ID> acctIDs = new Set<ID>(); if(Trigger.isInsert) { for(Gig__c g : System.Trigger.new){ acctIDs.add(g.Customer__c); } Account[] accts = new List<Account>(); accts = [select Id, of_Hours__c, (Select Id, Total_Estimated_Hours__c from Gigs__r) from Account where ID in :acctIDs]; Map<Id, Gig__c[]> acctgig = new Map<Id, Gig__c[]>(); for (Account eachAcct: accts) { acctgig.put(eachAcct.Id, eachAcct.Gigs__r); } Account[] updatedAccounts = new List<Account>(); for (Account a : accts ){ Double SumOfHours = 0; for (Gig__c gig : ) { SumOfHours += gig.Total_Estimated_Hours__c; } a.of_Hours__c = SumOfHours; updatedAccounts.add(a); } update updatedAccounts; }}

What I don't understand is how to loop through the map 'acctgig' to total each 'Total_Estimated_Hours' ?

Thanks

Pierre 

 

 

pierrefrazny.ax358pierrefrazny.ax358

Ok. I came up with this. Does this look correct to you?

 

 

trigger UpdateTotalHours1 on Gig__c (after delete, after insert, after update) { set<ID> acctIDs = new Set<ID>(); if(Trigger.isInsert) { for(Gig__c g : System.Trigger.new){ acctIDs.add(g.Customer__c); } Account[] accts = new List<Account>(); accts = [select Id, of_Hours__c, (Select Id, Total_Estimated_Hours__c from Gigs__r) from Account where ID in :acctIDs]; Map<Id, Gig__c[]> acctgig = new Map<Id, Gig__c[]>(); for (Account eachAcct: accts) { acctgig.put(eachAcct.Id, eachAcct.Gigs__r); } Account[] updatedAccounts = new List<Account>(); Gig__c[] gigs = new List<Gig__c>(); for (Account a : accts ){ gigs = acctgig.get(a.Id); Double SumOfHours = 0; for (Gig__c g : gigs){ SumOfHours += g.Total_Estimated_Hours__c; } a.of_Hours__c = SumOfHours; updatedAccounts.add(a); } update updatedAccounts; }}

 Thanks

 

 

BritishBoyinDCBritishBoyinDC

Looks good from here. Excellent!

 

Be sure to test every scenario though - e.g. if you delete the final Gig record for an account - does the trigger handle there being no gig records in the gis list...you might need to check if certain lists have at least one records before you do any processing...

This was selected as the best answer