+ Start a Discussion
John Ross 9John Ross 9 

Accessing a field with dot notation lookup in SOQL

Hello.  I'm having a bit of trouble understanding why I'm getting an error in SOQL.  Here's the query: 

SELECT CustomerTeamMember__c, CustomerTeam__c, Contact__r.Email FROM Customer_Team__c WHERE CustomerTeam__c = '006i000000OOuPSAA1' AND UseInLoop__c = True

Without the Contact__r.Email, everything works.  I'm looping a custom object (related list) on Opportunities - this works fine.  The related list has a lookup to the standard object "Contact" and I want to pull the email field from there.  I don't want to run a query within a loop to avoid governor limits.   Can anyone help me figure out why this is not working?

The error is: Didn't understand relationship 'Contact__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.
Best Answer chosen by John Ross 9
AshlekhAshlekh
Hi

If Contact is parent of  Customer_Team__c object. Then you have to use the API name of field Contact which is in your on Customer_Team__c  object. Just replace the "__c" to" __r".

If API name of contact field in your Customer_Team__c object is "Contact__c" then you have to use "Customer__r.Email",

All Answers

Numaan A.MNumaan A.M
Hello,
Try using Contact__c__r.This will work.

Regards,
Numaan
John Ross 9John Ross 9
I tried that (and various forms of it) .  Not working.  I'm looking up from a custom object to a standard object.  I'm sure the relationship exists as I can see it in the custom object definitions...
AshlekhAshlekh
Hi

If Contact is parent of  Customer_Team__c object. Then you have to use the API name of field Contact which is in your on Customer_Team__c  object. Just replace the "__c" to" __r".

If API name of contact field in your Customer_Team__c object is "Contact__c" then you have to use "Customer__r.Email",

This was selected as the best answer
John Ross 9John Ross 9
Ashiekh - thank you.  That works and it makes sense.  
I was thinking that you could just reference the field on the object you wanted to look up.  That was a mistake.
You are correct that you have to reference the field by the API name (not necessarily related to the parent object name) and use dot notation to access the field on the connected object.  It returns the results I expected now.  
AshlekhAshlekh
Hi John Ross 9 thanks for appreciation.