Skip to main content


Showing posts from February, 2019

SOQL Gotchas

There are a few things that trip me up when writing SOQL. I hope that sharing these gotchas will save you from making the same mistakes. Maybe writing this post will even help me remember this stuff too 🤔
Single QuotesNOT LIKEFiltering a DateTime with a Date Single Quotes It seems silly, but single quotes always trip me up. For example, I get an unexpected token error when querying for an Account named: Brett's Awesome Company

This error happens because SOQL only sees: WHERE Name = 'Brett'.
The s Awesome Company' part is hanging off the end, making it an unexpected token.

We need to escape the single quote with a backslash (\) so SOQL can see WHERE Name = 'Brett\'s Awesome Company'.

SOQL requires you to escape single quotes and a few other characters. A complete list with some examples can be found in the docs.
NOT LIKE The LIKE comparison operator in SOQL will let you filter with a wildcard by using either the % or _ character. This is a powerful techniqu…

SOQL as a Summary Report

When learning SOQL, I found it helpful to relate things back to something I was familiar with. I'll be using Summary Reports to help explain how to use GROUP BY and GROUP BY ROLLUP in SOQL.
First, a Salesforce Report Let's start with a Salesforce Report of Accounts grouped by Billing State/Province:

To get this, I created a Report with the Accounts standard report type. Then I created the grouping, added some fields and ran the report. Writing SOQL I'll be executing my SOQL via the SFDX Command Line Interface (CLI). If you aren't familiar with this, check out my other post - SOQL from the Command Line
Feel free to use your preferred method to run your SOQL. If you don't have a preferred method, I talk about my favorites in the last section of another post - Why should I use SOQL? GROUP BY Here's our example Salesforce Report done via SOQL:

The key difference is that the Salesforce Report gives us data about each record and SOQL just gives us the totals for each…

Why should I use SOQL?

You need data, fast!
Whether it's exporting a csv or answering a question, SOQL can get you the result much faster than creating a Salesforce report.

My top 3 reasons to use SOQL are:
Get data fastGet answers fastFlexible access Get Data Fast How often do you find yourself making a throwaway report to check what is happening in the system? For example, you updated your Lead routing and want to see if it's been assigning Leads as you expect. Well you could make a report, run it and review the results. Or you could run a SOQL query.
The benefits of running a SOQL query in this case is that you can retrieve different data sets much quicker. In a Salesforce report, you are stuck with the Report Type you selected when creating the report. With SOQL, you can traverse object relationships in a much more natural way. Get Answers Fast Have you ever been in a meeting and someone asks a data question that no one has the answer to? Well SOQL comes in handy here because you can run a quick …

SOQL from the Command Line

SOQL and the Command Line go together better than peanut butter and jelly. Here's why.
Once upon a time, I got tired of running Salesforce Reports to get miscellaneous data. I found Workbench and started using their interface to run SOQL queries. This was great for a while, but I knew there was a better way.

Pro-tip with Workbench: If you set a browser bookmark after your query runs, you can return to that bookmark and it will re-run your query!

Enter the SFDX Command Line Interface (CLI). Get ready to take your productivity into hyperdrive! 🚀🚀🚀
sfdx force:data:soql:query If there's one command you learn, this is the one. All you have to do is pass a query string into the -q parameter and you're in business. Check it out:
-r | --resultformat This is my 2nd favorite thing. How often do you need to export a csv from Salesforce? And how annoying is it to make throwaway Salesforce reports each time? Yeah, it's the worst.
-r to the rescue! Just add "-r csv" to …