In January, I wrote Quick Guide to Salesforce Inspector and SOQL, to give Admins and those interested in data a better understanding of how they could review their data without having to pull reports and manually search.
To those of you that found this useful and interesting I thought I’d write a “Part 2”. What I covered was the fundamentals of a database query, I used an example as below:
SELECT ID, Name, AccountNumber, BillingStreet, BillingCity, BillingCountry, Type
FROM Account
WHERE BillingCountry IN (‘USA’,’US’)
ORDER BY Name
I then covered off what each of these bold words meant and how to use them. What I didn’t cover was some very useful other features of SOQL.
I have highlighted importantly that the FROM clause is used to identify the object, but what about multiple objects, how could you query an object whilst considering another object? You must first think about what way the relationship works.
Is your focus the Child and you’d like some reference to the Parent or are you querying the Parent with certain Children?
For those that are not data crazy like myself and are unaware of the terminology of Parent and Child, this refers to the Objects relationships, I think of the Parent as the object that can have many related records of a different object, but generally the Child will only have one Parent, in Salesforce this could be Account and Contact, where Account is the Parent and Contact is the Child.
Anyway, I digress, this can turn into quite an explanation if I’m left to ramble. In most cases when dealing with ideas like this, examples are the key so let’s have a look at a couple of problems and see if you get the jist!
Query: I would like all the Opportunities that relate to an Account with the Record Type of Customer.
Steps
- What are you trying to return? Opportunities
- How is the object you wish to return related to the other object? Child Note: You can tell it’s a child as you can only relate an Opportunity to one Account not multiple, but you can relate multiple Opportunities to that Account, this is what is called a One to Many or vice versa Many to One relationship. You do get many to many relationships, but that conversation will be saved for another day.
Now know the relationship, we can begin building our query. In Step 1 we highlighted our FROM requirement, and from my previous tip we know that we will need to use SELECT to determine which fields we return.
Currently our query would look like this:
SELECT {FIELDS TBD}
FROM Opportunity
We have specified in our text query that we would like all opportunities, but no specifics about what we would like to see, this is intentional. I’d like to show you here how you could return the number of Opportunities as well as their information.
Firstly, we need to these Opportunities, specifically on the Account Record Type. This is done by highlighting the field that Opportunity uses to relate to the Account. Many of you will know instantly that this is the AccountId field, but what if you have custom objects and don’t necessarily know the data structure, how do you find out?
Navigate to the trusty Object Manager in Setup, find your object that you are querying in our instance this is Opportunity. Now look for a field with a data type of Lookup ({Parent Object}). 
I highlight the Field Name, rather than the label, because we are using SOQL and SOQL doesn’t recognise labels only Field Name/ API names.
This is a simplified use case in some instances you may have more than one lookup to the same object, but in this instance, we only have the one. If you do have more than one lookup you will have to identify why to understand which Lookup is going to return the information you require.
To add complexity to this query, we are looking specifically for the Record Type of the Account, those of you who are data savvy will realise that this points to an additional object. All objects with Record Types will have a RecordTypeId field, which is a Lookup to the Record Type object.
So, the simple case we could use would look like this:
SELECT Id
FROM Opportunity
WHERE Account.RecordTypeId = ‘0128d0000001C0QAAU’
I have used Id as the field to return out of habit rather than useful information, you can use any fields you wish here.
More importantly you can see I have an 18-digit ID to represent the RecordType and also I have used “Account.” Before the RecordTypeId field. You may ask,
“Why Steve, the field was AccountId not just Account!”.
I would agree with this statement, but the simple explanation is that the Account Lookup from AccountId is a Salesforce Standard. So, they have simplified the text you need to use. This is true for any standard lookups, if you had a Custom Lookup to Account through the field Account__c, you would use the field name with one minor change.
SELECT Id
FROM Opportunity
WHERE Account__r.RecordTypeId = ‘0128d0000001C0QAAU’
Note the field was Account__c, but now I’ve used a “__r”, this is Salesforce way to identify that you are using a custom relationship. Not too important to understand but important to know how to use these fields in your queries.
If you did want just a count of records returned, don’t forget my function from the last tip called COUNT().
SELECT COUNT(Id)
FROM Opportunity
WHERE Account.RecordTypeId = ‘0128d0000001C0QAAU’
I bring this up because it is a quick easy way of getting a number of records and when used efficiently can identify issues in your data quickly.
Anyway, back to this query, I don’t like this query, I don’t like using ID’s, I think they look messy and I feel like I’m betraying my inner developer, to those of you who know; it feels like I’m hardcoding an id into my query, where I like to make my queries flexible between Sandboxes and Production.
Then, I will go one step further with my query, and the same as we did with Account, I will use the RecordType lookup to get the Name field and use my English language to make this query more user friendly.
SELECT Id
FROM Opportunity
WHERE Account.RecordType.Name = ‘Customer’
See how I have used the same syntax to navigate my query up to the Record Type object. If you weren’t to know the Record Types names, you can find these within the Object Manager.
Here you see I’m using the Record Type Label, which in the back end of Salesforce is called “Name”.
Well, there you have it, you now can see how to navigate from a Child to a Parent or Parent’s. There was a lot covered there, I tried to simplify it, but I think there is a lot to understand and once you start implementing it, it will become second nature.
I hope this does great things for you as it has for me in my day-to-day.
May the Salesforce Be with You.
