+ Start a Discussion
hometeamhometeam 

Using Parent/Child Queries in APEX

I'm trying to figure out how to set a variable based on a child field.  My SOQL is part of a for loop and I need to reference the CloseDate and Committed_Premium__c fields but I don't know how.  I've searched and read all morning and can't figure this out.  Please help.

 

 

for (Account A : [Select a.Id, a.Name, a.OwnerId, a.Total_Premium__c, (Select o.CloseDate, o.Committed_Premium__c From Opportunities o where o.CloseDate > 2010-03-01) From Account a where a.type in ('Prospective Agency', 'Existing Agency')]) {

John De SantiagoJohn De Santiago

Opportunities is a child object and so will return a list of opportunities. 

 

You will need to do something like this.

 

for (Account A : [your query]) {

for (Opportunity opp : A.Opportunities) {

date closeDate = opp.CloseDate;

  }

}

 

You might set a limit on the sub-query if you are only expecting to get one opportunity back. Otherwise you will need to work out some rules for which opportunity data you want to use. 

 

Hopefully this is what you were looking for. 

TrueCloudTrueCloud

Your for loop logic is ok but you may retrieve more than one opportunity. If I understand correctly you need to reference the CloseDate and Committed_Premium__c field from Opportunity record if the Account selected is on opportunity.

 

a) Where are you using this for loop? (Trigger, VF Page, Web Service???)

b) What if you retrieve more than one opportunity record for a given Account, would you set the close date and the Committed field for the last record retrieved?

 

I think once we are able to figure out the business case we can then work on the code part to resolve your issue.

 

 

hometeamhometeam

What I have is a client that has one opportunity per year per account.  They would like for me to create that new opportunity record on July 1st for the following year.  The new opportunity will be based on the current year's opportunity values (Committed_Premium__c)..  So in "theory", I should only pull one opportunity record for each account but if I do reteive more, then I'd want to use the more current record. 

 

Here's a copy of the code with errors:  I'm a newbie so feel free to offer any advice...

 

public with sharing class clsCreateOppty {

 public void RunCreateOppty(){
  List<Opportunity> OpptyToCreate = new List<Opportunity>();
  
  // Set Close date to April 1 of next year
  Date dCloseDate = date.newinstance(date.today().year()+1,04,01);

  // Get the string value of the Close date year
  String strCloseDate = string.valueOf(dCloseDate);
  String[] StringDate = strCloseDate.split('-');
  String strCloseYear = StringDate[0];
  system.debug(strCloseYear);
  
  for (Account A : [Select a.Id, a.Name, a.OwnerId, a.Total_Premium__c, (Select o.CloseDate, o.Committed_Premium__c
       From Opportunities o where o.CloseDate > 2010-03-01)
       From Account a where a.type in ('Prospective Agency', 'Existing Agency')]) {
    

    // if no existing Opporunity record then set Committed Premium to 0
    double CP = a.o.Committed_Premium__c;
    if (CP = NULL) {
     CP = 0;
    }
     
    Opportunity O = New Opportunity(Account=a.Id, CloseDate=dCloseDate, Name=a.Name + ' - ' + strCloseYear,
          OwnerId='00560000001VRy9', StageName='Open', Type='Existing Business',
          Committed_Premium__c=CP, Last_Year_s_Commitment__c=CP,
          Total_Premium__c=a.Total_Premium__c);
    OpptyToCreate.add(O);
  }
  insert OpptyToCreate;
 }
}