function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Air FoxxAir Foxx 

Selecting two most recent child records from master

Still a newbie here.  I'm trying to determine the best way to find the average unit cost of the two most recent invoices associated with each of multiple projects. The project names (which includes duplicates) are part of the master object and the relevant invoice fields (quantity and amount) are part of the child object.  So far, this is what I have:

List<MasterObj__c> master = [SELECT id, (SELECT Invoice_Number__c, Inv_Amount__c, Inv_Quantity__c FROM ChildObj__r WHERE ORDER BY Invoice_Number__c DESC LIMIT 2) FROM MasterObj__c]; 

I need to find a way to assign the sum of invoice amounts and quantities for each project to a variables to calculate the average.

Any help would be appreciated.  
Thanks!
surasura
check whether below script helps you , field api names may vary according to your scenario 
  
Map<Id,double> MastertoChildSum = new Map<Id,double>();
Map<Id,Integer> MastertoChildqunatity = new Map<Id,double>();
Map<Id,double> MastertoChildAvg= new Map<Id,double>();
List<MasterObj__c> master = [SELECT id, (SELECT Invoice_Number__c, Inv_Amount__c, Inv_Quantity__c FROM ChildObj__c WHERE ORDER BY Invoice_Number__c DESC LIMIT 2) FROM MasterObj__c]; 

for(MasterObj__c m :master)
{
   List<ChildObj__c> childrecords = m.ChildObj__r;
   double sum=0;
   Integer quantity = 0;
   double avg = 0;
   for(ChildObj__c childrecord :childrecords)
   {
       sum += childrecord.Inv_Amount__c;
	   quantity += childrecord.Inv_Quantity__c; 
   }
   MastertoChildSum.put(m.Id,sum);
   MastertoChildqunatity.put(m.id,quantity);
   avg = sum/quantity;
   MastertoChildAvg.put(m.Id,avg);
}

Average , Sum , ara maintianed in Maps against master record Id  you can use them  for your purose
Air FoxxAir Foxx
Thanks for the input. If I don't change the ChildObj on line 4 to ChildObj__r  I get an error " Didn't understand relationship 'ChildObj__c' 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."  So I assume that's the way to go.

Also, to assign the sum, quantity, and average to fields for reporting, I assume I would have to use the get (key) - e.g., double field = MastertoChildSum.get(m.id)?