Skip to main content

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 🤔
  1. Single Quotes
  2. NOT LIKE
  3. Filtering 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 technique.

Trouble comes for me when I want to filter out records with a wildcard. The trick here is to apply a NOT to the wildcard filter. This tells the query to return all records where the condition is NOT true.

For example, this is how we would filter out all Contacts that have a First Name starting with B:


If we want to filter out B and C, we can wrap multiple wildcard filters in parentheses and put a NOT before the whole thing. This works like a charm:

Filtering a DateTime with a Date

Have you ever needed to query for records created on a certain date? You could do this:

But wow, talk about painful 😰

Instead, try passing your DateTime field into the DAY_ONLY() date function. This will return the date portion of the value, letting you do something like this:

Much better 😎

Continued Learning

The best way to learn is by getting your hands dirty! If you don't write enough SOQL as part of your day-to-day workflow, I recommend using questions on the Salesforce StackExchange as examples to practice with. Who knows, you might even answer some questions! 

When I get stuck on something, I tend to go to the SOQL docs directly. Here are a few pages I bookmark for quick reference:

Comments

Popular posts from this blog

Salesforce Lighting Migration: Plan for Success

In April 2018, I led the Salesforce Lightning Migration at Postmates. This is how I would do it over again if I could.

Define Goals The goals of a Lightning Migration are: Enable Lightning ExperienceConfigure Lightning ExperienceDocument all Salesforce processesCreate training and support materialsLightning Experience is adopted Easy enough right? Let's go though the plan to see how we accomplish each one. Enable Lightning Experience Just turn it on right? Well, kinda.
Although it may be controversial, I recommend turning it on and hiding the option from all users. This can be done by removing the Lightning Experience User permission from your custom profiles. 
And yes, turn it on in production
Why? You're going to turn it on anyway. Salesforce has declared this as the path forward, so you either turn it on now or wait until they flip the switch for you.
Plus, turning this on ahead of time will make deploying from a sandbox much easier. Trust me.
Goal #1 Enable Lightning Exp…

Modify Salesforce Data from the Command Line

When doing Salesforce data loads, how often are you putting the same value in an entire column? I find this happening when I need to:
Move all accounts from one user to anotherAssign a set of leads to a specific userSet a field to a specific value Let's use the example from my other post: Salesforce Data Loads from the Command Line - Move all accounts owned by one user to another user. Magic Let's break down each piece of this: soql This is an alias I set for sfdx force:data:soql:query --query. It saves me from having to type this out each time and let's me query from the command line by just typing soql and my query.

This is a popular StackExchange post that talks about how to set an alias.
cat This command outputs the contents of a file. It is common to use this command with other commands by piping the output into another command. In our example, we are piping the output into the cut command.
This post gives more examples of how to use the cat command. cut This is a comm…

Why Users Switch Back to Classic

When rolling out Lightning Experience, people are constantly switching back to Classic. Even after the initial rollout it's common to see people switching back and forth.

The most common reasons someone switches back to Classic are:
Classic loads fasterTheir workflow is slower in LightningThey just don't want to switch to Lightning Overcoming these objections will be key to a successful rollout. Let's dive into each one in more detail. Classic Loads Faster It does. 😢
And when you hear someone complain about this, take their side.  Agree with them.  Make that ironic joke, "haha, yeah Classic is faster than Lightning". ⚡⚡⚡
Then overcome this by highlighting how their overall workflow is faster in Lightning.
The page takes longer to load because each page is made up of many components. Once it has loaded, someone can complete their task on a single page. Their Workflow is Slower in Lightning It probably is. At least until they get used to it. 👨‍💻👩‍💻
Similarly, i…