1

Attempting to access the variables MIlestone1_Project__c has a Lookup relationship to Account with relationship name: `Projects__r'.

List<Account> accList = [
    SELECT Id, Name, EHR_Status__c, PM_Status__c, Project_Imp_Status__c, Other_Status__c, (
        SELECTClient_Advisor_Email__c, Resource_Coordinator_Email__c
        from Projects__r
    ) 
    FROM Account
    WHERE Id IN :AcctIds
];

List<String> emailAdds = new List<String>();
for (Account al: accList) {
    emailAdds.add(al.Client_Advisor_Email__c);
    emailAdds.add(al.Resource_Coordinator_Email__c); 
}

I get the error message:

Variable does not exist: Client_Advisor_Email__c

Why do I get this error? How can I fix it?

3

You have to loop through your subquery list of records. Here is how you would access these fields and records:

for(Account a : accList)
{
    for(Project__c p : a.Projects__r)
    {
        System.debug(p.Client_Advisor_Email__c);
    }
}
3

Some background

When working with data that's related to an object, there's two ways it can be related:

  • Child-to-Parent (e.g. Opportunity.Amount in a query on OpportunityLineItem)
  • Parent-to-Child (e.g. (SELECT Id FROM Opportunities) in the SELECT clause in a query on Account)

An important concept to keep in mind here is that all relationship fields in Salesforce are 1:n (one-to-many). Given a single relationship field, for example, OpportunityId on OpportunityLineItem, that field can only hold a single value.
OpportunityLineItem, the child record, can only be related to a single Opportunity, the parent record.

If we switch our point of view to the Opportunity, a single parent (Opportunity) can have multiple children (OpportunityLineItems).

How related data is stored/represented in the query result

In the end, every record we query data for is presented to us as an SObject

If your query looks like this:

[SELECT Id, Account.Name, (SELECT Id, UnitPrice FROM OpportunityLineItems) 
FROM Opportunity 
LIMIT 1]

Then we're querying data from Account, Opportunity, and OpportunityLineItem. All of those are represented as SObject. This allows us to do things like Account myAccount = myOpp.Account;

What happens with the parent-to-child subquery results then?

Since a parent can have many children, we can't store all of the child records in a single SObject.

If Salesforce automatically chose one child to put in an SObject for us, which one would it be? How would we switch to another child record?

I hope that this is starting to make your code-senses tingle. We want a single variable to hold multiple pieces of data. What tool(s) do we have to accomodate that?

Salesforce's solution to this is to return child records as a List<SObject>. This allows us to do things like List<OpportunityLineItem> myOLIs = myOpp.OpportunityLineItems;

Now, to your issue

Your code was assuming that the child records were a single SObject (glommed onto the Account object) instead of being an honest-to-goodness List<SObject> of its own.

Some common patterns to work with records from a subquery include:

Looping over the subquery results (the safest method)

for(Account a : accList){
         // a.Projects__r is a List, and we're feeding it into a loop
         //   just like we would with any other List
         for(Project__c p : a.Projects__r){
             System.debug(p.Client_Advisor_Email__c);
         }
     }

Accessing a single record from the subquery

// It's just like accessing an index of any other List because _it is just a List_
for(Account a : accList){
    System.debug(a.Projects__r[0].Client_Advisor_Email__c);
}

Salesforce prefers we use the nested loop approach because, after a certain number of child records, there's an internal call to queryMore() which a loop can handle correctly (and basically nothing else can handle it properly). Even calling a.Projects__r.size() runs the risk of needing a queryMore().

  • 2
    Bryan Anderson's answer is correct (and much shorter). My goal here was to give a deeper understanding about why his solution works, and why OP's initial approach didn't, which wouldn't have worked very well as a comment. – Derek F Nov 22 at 15:58
  • Great explanation @DerekF – Bryan Anderson Nov 22 at 16:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.