Salesforce coding lessons for the 99%Finally, Apex tutorials for point-and-click admins! Written by a self-taught Google engineer.
Salesforce coding lessons for the 99%
Preface: this post is part of the SOQL: A Beginner’s Guide series.
Whenever you’re traversing related objects in Salesforce, you’re always either going up or down.
Upwards traversal is used whenever there’s a lookup or master-detail field from the base object. This is sometimes referred to as going from the child to the parent.
Here are a few examples of upwards traversal:
SELECT Id, Account.Name, Account.Industry, Account.Website
FROM Contact
WHERE Account.NumberOfEmployees >= 200
The above example might look familiar to you – if you built a workflow on Contacts, you’d be able to reference the contact’s account fields by using the same dot notation.
SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact
You can traverse multiple levels upwards!
SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact
Here we’re traversing a custom lookup field Best_Friend__c on the contact object. Notice how the “__c” changes to a “__r” when traversing that field!
Next post: How to write a cross-object SOQL query (“downwards”)
This returns [object Object] for Account Name. Any indication why traversing upwards from opportunitylineitem to account does not return results?
SELECT
OpportunityId,
Opportunity.LastModifiedDate,
Opportunity.AccountId,
Opportunity.Account.Name,
Service_Line_Name__c,
Volume__c,
totalPrice, CM__c,
Estimated_CM__c
FROM OpportunityLineItem
WHERE Opportunity.IsClosed = false
Hi, David:
Can SOQL handle upward then downward query? I have a custom object, BP, looking up to Account, then, Account has a master-detail relationship with another custom object called Location. How do I query the BP object to the Location record? Is it possible?
Yes it’s possible! Keep reading the guide you’ll get there!
Help in Write a basic Trigger on Opportunity ?
i want write a trigger on Opportunity,When a Opportunity is ‘close won’ after that trigger update the related account’s Custome Field Sum_of_CloseWon__C ,with the amount of closed won Opportunity ‘Amount’ Field ..
Thanks in advance. ;)
Hi piyush,
Why you want a trigger to calculate sum of closed won opportunities related to that account if it is possible via Roll-up Summary field on account.
I tried this scenario and it worked through Sum Aggregate of roll-up summary.
Let me know if I am missing something.
Thanks
Prassu
Hi David,
I tried to execute the following SOQL stmt in your sfdc99 Org.
Select Id, Best_Friend__r.Likes_Ice_Cream From contact
But it is giving this error.
Select Id, Best_Friend__r.Likes_Ice_Cream__c
^
ERROR at Row:1:Column:12
Didn’t understand relationship ‘Best_Friend__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.
Okay. I understood that the Field API names are different in your Org. when I used the appropriate field API name it works,
Also I observed that all your custom field’s Field API names are starting with ‘Regalaray1__’. Is there any specific reason to be used so.
Someone logged into my org and registered it into that domain unfortunately…. I need to track that person down =)
Hey David,
I came across a very weird behavior where in i was trying to use a field from the parent’s parent and it was not returning me any value. In fact it was giving parent’s parent id as null.This was weird because i was able to get the parent id.
I was writing an apex code in before insert operation.
for(MCPM_Interaction__c objInteraction: lstInteractions){
if(objInteraction.MCPM_Prospect__r.MCPM_PremiseID__r.MCPM_Lightning_premise__c && objInteraction.MCPM_Lightning_premise__c==false){
objInteraction.MCPM_Lightning_premise__c= true;
}
}
This not working bewildered me :(
I’m completely stuck trying to change a specific field on a contact account, I’m not sure where to start, maybe someone here can help?
I created a process which creates a Contact record whenever someone creates a User. The Contact record mirrors the User record. However, I need to set the account name to a specific string, but I’m not sure how to do that, as whenever I trying to set AccountName to anything, it doesn’t recognize it as a field, or says I can’t go from a Contact to a User.
I’ve been programming in Apex for about a week now, so any help or direction would be much appreciated!
Love the site by the way, it’s been a huge help!
Hi David,
I am just wondering if I could write a SOQL query for this:
find out list of leads that have open task assigned to specific user and where the Lead CreatedDate equals to the Task CreatedDate?
Thanks for your help, S
How many joins write in SOQL queries like ( Child to Parent) for multiple Objects and Thair Relation ships
SELECT Id, Account.Name, Account.Industry, Account.Website
FROM Contact
WHERE Account.NumberOfEmployees >= 200
Can we write the same code in the following way:
SELECT name, industry, website, (select ID from contacts) from Account Where (NumberOfEmployees >= 200)
Hi David,
Thank you so much for your explanation to this. It really confused me everytime I look at a SOQL query to see __r at the end of custom objects. Now it is clear. Thank you for your time describing the subject in an easy way.
Hello David,
I understand the dot notation and the sub query while traversing upwards or downwards and I also understand using the Child relationship name while querying for child records from parent records. However can you tell the significance of _r and _c? When do I use each of these?
Thank you, thank you, thank you! I had spent two hours sifting through Salesforce’s documentation trying to find this – and found it on your site within 2 minutes. I needed to cross and Opportunity name with and Opportunity Line item and was able to do so easily thanks to your notes! I am so excited – this opens up a whole new world of SOQL searching!
Yay!!!! =)
Hi,
Great tutorial on Apex.
I could not fully grasp how to determine what one can traverse. If I try to use a formula, it shows me which fields that I can traverse from another object that is “joined” to the current object as a master detail.
How to get that info in apex? Does Workbench have a way to get the proper sequence?
To be more specific, if the object employee is master detail where the detail object has a field with hours worked for the month. How to get this field? SELECT employee.detail.hoursworkedforthemonth from EMPLOYEE?
Am I making sense?
The only way I’ve found to simply find the exact traversal code is to use the explorer in the Eclipse Force.com IDE. But that’s a huge pain to set up!
Luckily it always follows the exact same pattern so once you get the hang of it, you’ll be cruising forever =)
Know that there’s two directions for traversal. If you’re getting the parent (only one possible parent), you use dot notation. If you’re getting the child (many potential children), you use a nested query. It’s one or the other so if your first guess doesn’t work try the other =)
Hi David,
I have question regarding process definition,Process Instance and Prcess Instance step for approval process.
My objective was to write and workflow/trigger to fire an email notification when the approval time between the steps in approval process takes more than some specific time. So, i was trying to fetch the timestamp from each step such that if the time stamp is greater than of required period then an email will be fired according step related user/queue.
To fetch the time stamp i wrote an SOQL query between the processdefinition,processinstance,processinstance step. which is
SELECT ID,(SELECT Id, (SELECT ElapsedTimeInMinutes, FROM ProcessInstanceStep) FROM ProcessInstance ) FROM ProcessDefinition WHERE Id = ‘some value’
Here for every record creation in an approval process there will be a new process instance id created keeping process definition kind of master id as reference . And for every process instance id, there will be associated step id created for approavl steps for a record in approval.
Now the question is that i an unable to understand the realtion between these 3 tables to frame my query as my query fails when it reads the subquery select.
Please let me know how can i over come this problem.
Thanks for your help
Raj
David,
I kind of got one of the related queries with repected to process instance and its steps which is
SELECT Id, (SELECT Id, StepStatus FROM Steps)
FROM ProcessInstance
However i still need to find the timestamp or field like ElapsedTimeInMinutes etc which is available in processinstancestep but not in steps.
SELECT Id, ElapsedTimeInMinutes, (SELECT Id, StepStatus FROM Steps)
FROM ProcessInstance
=D
ElapsedTimeInMinutes is required for the step as i am looking for time taken for each approval step eg from step1 approavl to step2 or from step3 to step4 etc. while on the other ElapsedTimeInMinutes in ProcessInstance will give you the overall time or time taken from start of the approavl till the end or to current step in approval only. will see if i can find more data into this.
You can only have one subquery for each SOQL statement!
In this case you should break it down into two separate statements (you’ll probably need to use a Map)
Thanks a lot David,
Will proceed ahead in suggested direction :)
Why do we get an output on SELECT Account.Owner.profile.createdby.name FROM Contact but not on SELECT Account.Owner FROM Contact
There is no “Owner” field on Accounts!
There is an OwnerId field and there is an Owner.Something =)
Hi David,
I’m having a hard time accessing the Parent_Case__r.Owner.Name in a soql query. Any ideas why I can get the Parent_Case__r.CaseNumber but the Owner.Name just returns [object Object] values for all rows.
Yes – that owner of a Case can be a queue or User – so it’s confusing for the code to choose which one! There is a special syntax in SOQL to get the User owner name, but don’t remember it off the top of my head!
Hi David,
When i am trying this query SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact. I am getting this error “Parent relationship queries are disabled in Workbench: Account.Owner”
Please help
I believe that due to the amount of processing required, the SOQL relationship queries are disabled on the Workbench. If you try it in the Data Loader it should work without issue.
Please navigate to workbench settings and enable : Allows SOQL Parent Relationship Queries
Thank you anonymous hero!
Hi David & Anonymous Hero –
What is the downside of enabling this (if any)? Just wondering because of the big red error message that pops up when it is not enabled & we try & run a parent relationship query. Specifically,
“Due to issues rendering query results, parent relationship queries are disabled by default. If you understand these limitations, parent relationship queries can be enabled under Settings. Alternatively, parent relationship queries can be run with REST Explorer under the Utilities menu without issue.”
What exactly are the”issues” & “limitations” that we need to understand?
None really as enabling it is only temporary – next time you open up workbench it’ll go back to default!
I turn it on all the time (and have been doing so for years), no repercussions really unless you’re doing some outlandish stuff in workbench (in which case you can just turn it off)
Thank you!!!! (forgot turn on “notify of new comments” or I would have thanked you sooner!!)
SELECT Account.Owner.Profile.CreatedBy.Name FROM Contact = can this query be explained in detail means wat its displaiyng
hi david .
SELECT name FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true
with this i get name of the contact whoes best frnd likes ice cream
how can i get the name of the best frnd ? who likes ice cream
if i write
SELECT Best_Friend__c FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true
it returns me the id of best frnd , but what if i want the name of that best frnd
SELECT Best_Friend__r.Name FROM Contact WHERE Best_Friend__r.Likes_Ice_Cream__c = true
Thnkyou(:
Hey David,
Do I have this write:
In dot notation, when referencing a field on a custom object, the object will be appended with __r and the field with __c?
Thanks!
__c for all custom fields/objects and __r when traversing custom relationships!
So if you have a custom field “Hi”: Hi__c
If you have a custom object “Yo”: Yo__c
If you have a custom lookup field “Best Friend” on contacts: contact.Best_Friend__r.Hi__c
“SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact”
hi,
Best_Friend__r.Likes_Ice_Cream__c
it vl return the name of the best frnd who likes ice cream
is it so ??
Only if you have a filter:
“Best_Friend__r.Likes_Ice_Cream__c = TRUE”
Otherwise it’ll simply return that value whether it’s true or not!
David
Unclear about this one –
“SELECT Id, Best_Friend__r.Likes_Ice_Cream__c FROM Contact”
SO Best.Friend__c is a custom field on Contact. What about Likes_Ice_Cream__c ? Is this a custom field on a different object? If so which object?
think i ve got it. Best_Friend__c looks up to Contact itself. Best.Friend__r.Likes_Ice_Cream__c will return the value of Likes_Ice_Cream__c in the contact record for the value in Best_Friend__c
Exactly!! Great job!!
Hey David,
Can u please explain diff between ‘__c’ and ‘__r’??
Thanks
Srinivas
When using dot notation, you user __r =) Otherwise you use __c !
https://www.sfdc99.com/2013/10/05/dot-notation-navigating-relationships-and-using-methods/
Thank you for this series! SOQL has these neat tricks that work a bit differently than the other SQL flavors.
Hi David – Thanks for ll you do!
So, your second example of the dot notation is a little unclear to me. “Account.Owner.Profile.CreatedBy.Name” Not sure if you are showing an example of the same different query as example 1, or is this a new query on a single field (the acct creator name)?
Sorry., The first example queried website, industry, acct name. Does the second one introduces different fields or a single query line for Acct Creator? Am I just being too literal?
Sorry it is unclear!!
The examples are all independent. The first shows a simple, one-level traversal while the second shows that you can traverse many levels at a time! Different fields are always separated by a comma, so in the second example there is only one field being queried (Account.Owner.Profile.CreatedBy.Name)
Hope this helps!
David