There is a powerful yet underused tool in the Salesforce ecosystem. That tool is SOQL. It is an asset that has been typically wielded by Developers, usually within Apex. This tool gets records and allows us to then manipulate them easily.
We are querying Salesforce Objects, sounds easy so far, right? SOQL (pronounced closely to Sock Well or So Quell) stands for Salesforce Object Query Language. SOQL is similar to SQL (pronounced sequel), Structured Query Language, and is used ubiquitously across almost every RMDBS on the market.
Salesforce isn’t the only company that adapted SQL in its product; quite famously, we have JQL, Jira Query Language.
While Developers use SOQL, it is typically reserved for DML operations, simply used as a vessel to carry out other tasks.
What if Admins could leverage SOQL to carry out other tasks? What if I told you that there’s a way to level up your day-to-day with just a little investment in learning a “”language.”” What does that even look like?
Efficiency
Who doesn’t want to be more efficient in their day-to-day duties? A few lines of SOQL can do wonders. My engineering habits haven’t faded as I have transitioned into leadership roles, so I always have a terminal window open.
Thanks to the SFDX or Salesforce CLI, I can perform many functions directly from my terminal without having to worry about firing up a browser window and logging in.
I don’t have to create a report to ask a question about my data. I am sure most of the seasoned Admins out there have their canned reports written and can adapt them in seconds to see what is happening in their organizations.
But what if you just want to see if a user has logged in recently? Maybe you want to check to see if a particular integration is running. Do you need to check on it periodically throughout the day? SOQL is your friend!
You can use SOQL in various places: Terminal, VS Code, Workbench, Developer Console, and even third-party tools such as Jetstream App.
So which users have logged on in the past day? I could run a logins report, or I can use the following line:
SELECT Username
FROM User
WHERE LastLoginDate = TODAYWhat if you wanted to know the number of users logged in today? With one quick change, I can grab that answer:
SELECT COUNT(Username)
FROM User
WHERE LastLoginDate = TODAYOne last example, from the command line, we can grab a list of all users who logged in today and export that directly to a CSV:
sfdx force:data:soql:query -q "SELECT Username FROM User WHERE LastLoginDate = TODAY" -u "test@salesforce.com" -r csv > user_logins.csvThese are simple examples, but it highlights the power of SOQL to quickly gather data without sledging through the report writer.
Automation
We can leverage the command line and SOQL to automate uploads. Or we can use it to purge records or mass update records. Who doesn’t want to make their lives easier?
We all have those jobs that we have to do repeatedly, and SOQL enables us to shave time off them. Wrapping SOQL in a quick shell script can help us ingest data, transform it, and then insert it directly back into our instance. All without the need to use our friend XLOOKUP.
While a bit advanced we can write a couple lines of Apex with SOQL to automate so many things.
Career Development
Since SOQL is aligned with SQL, it opens up avenues for career advancement. Once you start thinking in a query format, you can apply it to SQL and other databases throughout your company.
If your company is relatively tech-forward, you probably have a data democratization approach, where employees have access to other datasets. Access to these datasets allows you to stretch your SQL muscle. Next, you can start combining Salesforce data with other company data. This opens up avenues to build robust dashboards for your stakeholders.
You can find yourself on different career paths, such as a data analyst. After a while, you may find out that you like working with SQL and data. Pulling data from other sources can also help you validate source data coming into Salesforce.
Understand Your Schema
When we traverse our schema, we learn more about it. We have to know what our schema is and which object to start from by writing queries.
Choose an object too high in a schema, and we’re not going to get the fields we want. Start too low, and our query may be inefficient.
It also requires us to grasp which fields are in which object. Without the benefit of the drag and drop report writer, we rely more on our brains, committing things to a logical path.
The last and most crucial piece forces us to pay attention to API names. It makes us become better partners to our developer counterparts. I have often heard an Admin say: we’ll just change the field label and leave the API name. Or, don’t worry about the API name.
When we write SOQL, the API name is critical for us. We use it to determine which fields to grab. When we have to type horribly named API names, we start to understand the importance of having data parity.
Improve Your Flows
We know that the Get Records operation in Flows is valuable, especially when we need to perform a DML operation. But, we often don’t ask what that operation is doing underneath the hood. How is my process performing? Could it be optimized?
At its core, you can compare the Get Records operation to running a SOQL query in the background to get the records you want.
( In actuality, this is abstracted to other languages and through Salesforce’s own ORM, but for simplification, we’ll use SOQL as the concept is similar)
How would we get those same records through SOQL? What would that look like?
SELECT Id, StageName, Amount
FROM Opportunity
WHERE IsWon = True
ORDER BY Name ASC
LIMIT 1The above query grabs the fields we want to return – to use later in our Flow – filters by the IsWon field, and then sorts by Name in an ascending manner. Notice the keyword LIMIT, this makes our query return only one record. This query isn’t too complex since we’re only traversing one object, but more complex queries will need to be written for performance.
We are just scratching the surface of what SOQL can do for you. If we use SOQL in conjunction with learning a shell language or maybe wrapping a bit of Apex around it, we can accomplish many wonders.
You don’t need to invest much time learning SOQL to reap results. You can be up in the running in less than an hour, especially if you have a solid grasp of your data model.
Stay tuned for an introduction to SOQL and how you can get started!