SOQL Secrets

Happy Saturday!

This week a client asked me:

How many of our Cases are related to an Action Plan?

An action plan is a Financial Service Cloud object, and natively there isn't a simple report to get answers to a question like this.

What to do?

Enter SOQL.

SOQL stands for Salesforce Object Query Language.

SOQL lets us "ask" our Salesforce data questions.

The answers are often insightful.

To answer my client's question I wrote the following query:

SELECT Count(Id) total, Status FROM Case WHERE Id IN (SELECT TargetId FROM ActionPlan) GROUP BY Status ORDER BY Count(Id) DESC

And then, with one button click, Salesforce gave me a list of Cases related to Action Plans, grouped by their status, in order of their total count.

Perfect, just what my client needed.

And thats the key takeway this week.

SOQL skills let you answer questions you might not be able to otherwise.

It's a skill that sets you apart.

Here's a quick refresher on the basics:

First: Download the Salesforce Inspector Reloaded extension.

It's immediately useful because it helps you build SOQL queries.

• Chrome
• Firefox
• Edge

Next: The basic syntax for a query is:

SELECT fields FROM object

For example:

SELECT Id, Name FROM Account

This query "selects" the Id and the Name fields from all accounts in the system, and shows them in a list.

Running this query in the Inspector extension will return results like this:

You will often add a filter to a query by using a WHERE clause, in order to narrow down your results.

For example:

SELECT Id, Name FROM Account WHERE Name = 'Genepoint'

Notice this is the same query as before, we just added a filter WHERE Name = 'Genepoint' to make our query very specific.

SOQL is "under the hood" all over the place in Salesforce.

• Reports
• List Views
• Salesforce Flow Get Elements
• Apex code

They all use SOQL to query for Salesforce data.

Here are some other good things to know when you're writing queries:

You can use AND and OR statements.

SELECT Id, Name FROM Account WHERE Name = 'Genepoint' OR Name = 'Edge Communications'

You can Count totals and group records by fields.

SELECT Count(Id), StageName FROM Opportunity GROUP BY StageName

You can give a field or object name an "alias" that shows up in the result header when using aggregrate expressions.

SELECT Count(Id) Total, StageName Stage FROM Opportunity GROUP BY StageName 

You can use functions like FORMAT(date) to make results more readable.

SELECT Id, Name, StageName, FORMAT(CreatedDate) FROM Opportunity

Notice how the CreatedDate column is easy to read. Try it without formatting to see the difference.

You can use aggregrate functions like SUM, AVG, MIN, MAX to aggregate information or perform rollups.

SELECT SUM(Amount) Total FROM Opportunity WHERE Account.Name LIKE 'United%'

You can use both an aggregate function and the format function to make results more readable.

SELECT FORMAT(SUM(Amount)) Total FROM Opportunity WHERE Account.Name LIKE 'United%'

You can use dynamic dates to specify date ranges:

SELECT Id, Name, StageName, FORMAT(CreatedDate) FROM Opportunity WHERE CloseDate = LAST_90_Days

• YESTERDAY
• TODAY
• TOMORROW
• LAST_WEEK
• THIS_WEEK
• LAST_90_DAYS
• NEXT_90_DAYS
• LAST_N_DAYS:n
• NEXT_N_DAYS:n

You can use object relationships to include more fields, or filter records in your queries.

SELECT Contact.FirstName, Contact.LastName FROM Contact

SELECT Id, Name FROM Opportunity WHERE Account.Name = 'Genepoint'

SELECT Id, CaseNumber, Account.Id, Account.Name FROM Case ORDER BY Account.Name

You can use the IN operator to join parent and child records.

For example, find Tasks where the Contact for the Task is located in Burlington.

SELECT Id, Subject, Owner.Name FROM Task WHERE WhoId IN (SELECT Id FROM Contact WHERE MailingCity = 'Burlington') 

And the list goes on, but we'll stop here. 

I suspect many of you are already familiar with SOQL.

Even still, I'd highly encourage you to skim through the SOQL Reference Guide once in a while to brush up your skills.

Like we said before, it's a skill that will set you apart.

Hope this helps!

Best,

Nick