An Admin’s Guide to SOQL + Examples

There are multiple ways to retrieve Salesforce information, varying in complexity and general use. While searching for records one by one, building a simple Salesforce report, or even navigating through Setup to check a permission set’s field access, are all tasks that Salesforce Admins are familiar with. SOQL (Salesforce Object Query Language) is here to make everyone’s life several times easier.

Let’s dive deep into a few uses of SOQL and how you can ultimately leverage it to optimize daily activities as a Salesforce Admin or Consultant.

What Is SOQL?

First of all, what exactly is SOQL? The term stands for Salesforce Object Query Language, and it is a way in which you can quickly retrieve information from Salesforce. In this context, information refers to Salesforce data, such as records and their field values.

While Salesforce Developers will mainly use queries within their Apex classes, SOQL can help Salesforce Admins with day-to-day tasks in a similar way to reports, but without the need to select the right report type, double-check if the needed fields are there, or create a custom report type. SOQL queries do not replace Salesforce reports by any means, especially when it comes to what users need to see. It is definitely up to you to choose when a sharable report makes more sense than running a query to get to the data you need.

You can choose where to work with SOQL, as while it’s the same functionality being used, there are several tools to choose from depending on the experience you prefer. Let’s go through the most popular ones you might have already heard about:

• Firstly, the native Developer Console Query Editor can be a quick option. You simply have to click the Setup wheel, and it will open in a new window, which you can close when done. As you execute your queries, the last ten of them will be visible in the panel on the right side, while any errors will be displayed below the query.

• Another well-known option is to use Workbench. While this is not an official Salesforce tool, it is already a step up in the experience department. It offers the option to simply select the object you’d like to query from a drop-down, continue by selecting one or multiple fields, then sorting and filtering – all without having to write anything. Additionally, you don’t have to worry about how to export the results, as there are a few viewing options available, including Bulk CSV, which allows you to download a file with all your data.

• Perhaps the option that quite a few Salesforce professionals prefer nowadays is the Salesforce Inspector Reloaded Chrome extension. Evolved from the OG Salesforce Inspector, which you most likely already know and love, Inspector Reloaded offers new functionality and is actively maintained. Through the Data Export option, which opens up the query editor, this extension makes it easy for anyone to build and execute SOQL, with readily available templates, automatic suggestions of field names and objects, and the option to quickly delete records if that’s what you’re looking for – just to name a few.

Create Your First SOQL Query

Before getting started, what you need to know is that a query is composed of two or three elements:

• The SELECT statement,
• The FROM keyword,
• The WHERE clause (optional). Once again, similar to a Salesforce report, you would ideally use the WHERE clause to add filters, so you don’t retrieve all the records unless you deliberately want to.

A simple SOQL query would look something like this:

SELECT Id, Custom_Field__c
FROM Account
WHERE [add filtering criteria of your choice]

If we use the same example with the Account fields and filter on BillingCountry, the results will be in the format of a table, with the fields you choose to add after the SELECT statement as the columns.

Queries are by no means limited to standard objects, so feel free to experiment with custom objects as well! Make sure to choose the tool that makes the most sense for you when using SOQL. In this article, I will be executing the queries in the Salesforce Inspector Reloaded Chrome extension.

If you also choose to continue with Inspector Reloaded, you will surely notice that if you access the Data Export button to reach the query editor from any Salesforce record, the editor will already contain a basic query filtered by the record Id from the page you were on. As you continue working with SOQL, this feature will surely come in handy and save you a bit of time in certain scenarios.

Note: WHERE is just one of the available optional clauses you can leverage. If you’d like to find out more about the others, how they can be used, and what limits to consider, check out the article linked below.

Use Cases

Now that we’ve covered the basics, let’s dive into a few common use cases where SOQL queries are sure to become your best friend.

Use SOQL to Avoid Wait Time on Reports

Chances are that one of the reasons you, as a Salesforce professional, will resort to using SOQL is the speed at which you can get to the data you’re looking for. While Salesforce reports are great and have their specific use cases, sometimes waiting several minutes for the results to load may be less than ideal.

Field History Tracking is a well-known functionality in Salesforce that allows you to flag up to 20 individual fields per object (with a few considerations). This feature allows you to track who changed these fields, when they were changed, and what were the values. While the changes are available in a related list on records, chances are you will run a report rather than manually check records. The thing is that these reports may have a lot of data and are notoriously slow to load.

SOQL can speed this up with a simple query, but of course, from a performance perspective, it is recommended to filter these query results as well. In this example, I wanted to see how and if the Industry field was changed on the accounts. Additional fields, depending on the scenario at hand, can be added, such as CreatedById and CreatedDate to get the most out of your results.

Additional filters should always be considered if you’re expecting a large number of rows, and history tables are well known for having a lot of information since changes happen every day.

Date filters and date literals can be especially useful and easy to use in this scenario, especially if you know that a change was mistakenly made earlier today, for example. Also, notice the formatting difference in comparison to the first screenshot. Similar to formulas, organizing your query in an easy-to-read manner can make a huge difference.

Retrieve Data from Multiple Related Records

Relationship queries are a game changer when it comes to using SOQL to retrieve data, as they allow you to easily tap into fields or information that is on other records related to the object you’re querying on.

If your organization uses leads, chances are you have some sort of lead-to-account matching process in place based on different criteria. The easiest out of the relationship queries is the child-to-parent one, as you can use the relationship name, followed by a dot, and the name of the field from the related parent record.

In this situation, the Relationship field is also a custom one, so we will add the __r suffix before the dot to obtain the name of the account that the lead was matched to. Once again, don’t forget about the filters, and note that even in the WHERE clause, the child-to-parent relationship can be used to narrow down the results based on account information.

SELECT Id, Status, Account__r.Name
FROM Lead
WHERE Account__c != null

Remember that the suffix __r has to be appended after the relationship name, as not doing so will result in an error. Luckily, the error in this situation clearly points to the incorrect relationship reference, so after checking either the suffix or relationship name, you should be good to go.

It is also possible to query records the other way around – through the use of a parent-to-child relationship query. This option is quite different as subqueries are being used alongside the child relationship name you defined, if you’re using custom relationship fields, as we do in this scenario with the lead-to-account matching.

For this type of query, I prefer using Workbench instead of Inspector Reloaded, given the table-like view in which the query results are generated. This makes it much easier to understand, especially in the beginning. The goal is to retrieve leads matched to an account whose name contains the word “Pyramid”. The LIKE clause can easily be used for strings, and as you might have guessed, it is the equivalent of a “Contains” filter in a report. With the help of this query, what could have taken a few minutes and a custom report type became a matter of seconds.

SELECT Name,
    (SELECT FirstName, LastName, Title
     FROM Leads__r )    
FROM Account
WHERE Account.Name LIKE 'Pyramid%'

Similarly to the above example for leads, you can apply the same logic for both standard or custom objects across your organization, such as retrieving opportunities and quotes related to an account or perhaps contacts that have a certain role. Depending on your use case, one of the two types of relationship queries is sure to be of help.

Fields, Fields, and More Fields…

Another way to enhance how you get information with SOQL is the FIELDS() keyword. This is meant for situations when you don’t know the name of the fields to be selected for the records, offering a great way to explore the org without having to click through every single object in Setup.

The available options for this keyword are FIELDS (All), which retrieves all fields for the selected records, FIELDS (Custom), which can be used to include custom fields only (both have to also include a limit of 200 rows), and FIELDS (Standard) which includes all standard fields. Depending on which one you decide to use, you can also combine it with other fields declared individually, but make sure that there is no overlap.

In the example below, we retrieved the Account name and up to 200 of the custom fields from the Account object.

SELECT Name, FIELDS (Custom) 
FROM Account LIMIT 200

A quick way to get past the 200 limit that FIELDS (All) and FIELDS (Custom) impose, is to leverage the built-in Salesforce Inspector Reloaded functionality, which automatically pulls in the query editor for all standard or custom fields. For example, if you type the query below, and use the control + spacebar keyboard shortcut while your cursor is between SELECT and FROM, all fields will be added to your query automatically. Alternatively, you can also type __c and use the shortcut if you’re only looking for the custom fields. 

SELECT (your cursor here) FROM Account
SELECT __c (your cursor here) FROM Account

Know Your Permissions

It may come as no surprise that while SOQL is great for retrieving data from the objects your users work with, it can also be used to surface key information, which all Salesforce Admins should be aware of – especially when it comes to field permissions.

While Salesforce has made significant improvement when it comes to seeing the permissions included in a permission set or the field permissions of a given user through the View Summary buttons, one question remains: if a user has multiple permission sets assigned, how do you know exactly which one contains read or write access to each field?

By querying the FieldPermissions standard object, you can quickly find out exactly from which permission set the permissions originate. Even though Salesforce has delayed the ‘full’ transition to permission sets, if you have started migrating to a base profile and tailored permission sets model, chances are that this question will come up quite often. The below query is sure to save a significant amount of time.

Additionally, the results include both permission sets and permission set groups, in case you would like to cross-check against the assignments since some users may have the permission set directly assigned while others may have a group containing the permission set which had the edit permissions for the field.

SELECT  ParentId, PermissionsEdit, PermissionsRead, SobjectType, Field,  Parent.Name, parent.Type
FROM FieldPermissions
WHERE Field='Lead.Account__c' AND Parent.IsOwnedByProfile = false

Currency Conversion

If your org has multiple currencies enabled and you’re ever in need of converting any currency field to the running user’s currency, look no further than the convertCurrency() function! Even though it is a very specific use case, knowing about this function may come in handy for third-party integrations or even situations in which you need to quickly obtain the converted amounts to answer a question.

SELECT Id, convertCurrency (Amount)
FROM Opportunity
WHERE StageName = 'Negotiation/Review'

Note: Trying this query before enabling multiple currencies will result in an error, so make sure that you already have the functionality enabled alongside the different conversion rates.

Combined Functions and Clauses

Using SOQL to simply retrieve data is awesome and, by far, quicker than using reports through the UI, but the output doesn’t always have to be simply raw data, which you can further manipulate in Excel or other tools. Most importantly, you don’t have to lose functionality just because you opt for SOQL to access record information.

Some of the simplest optional clauses you will quickly get accustomed to are ORDER BY and LIMIT. In a scenario where you would like to get only a few records and order them based on a date field, these clauses come into play. In this example, we want to retrieve the last five leads created in the org that have not yet been converted, order them by their creation date in a descending manner, and limit the number of results to display only five records.

SELECT Id, Name, Status, CreatedDate 
FROM Lead
WHERE IsConverted = false ORDER BY CreatedDate desc LIMIT 5

While sorting in reports may or may not be used, more often than not, Salesforce reports contain at least one grouping to easily read the information. The GROUP BY optional clause can help you group your results in an organized manner by a field’s value, while the COUNT(fieldname) aggregate function returns the number of records that have a value in the field of your choice. Generally, the Id is used for the count as all Salesforce records will surely have a unique Id. The query below is to return the total number of leads which match the filters grouped by their status.

As you’ll notice in the results below, such queries can also help you quickly check that the data is correct, as ideally, there shouldn’t be any closed-converted leads where IsConverted is false, right?

SELECT Status,Count (Id) NumberOfLeads 
FROM Lead
WHERE IsConverted = false AND Owner.FirstName = 'Andreea'
GROUP BY Status

Alternatively, If you also need a grant total row, as well as multiple subtotals, SOQL has you covered! GROUP BY ROLLUP is another optional clause that you can use instead of GROUP BY to obtain even more information when reviewing the output of your query, especially where the grouping is done by multiple fields, and subtotals would be nice to have.

SELECT Status, ProductInterest__c, Count (Id) NumberOfLeads 
FROM Lead
WHERE IsConverted = false AND Owner.FirstName = 'Andreea'
GROUP BY ROLLUP(Status, ProductInterest__c )

Saving Queries for Later

While this is not a use case per se, whether you’ve been working with SOQL queries for a while or you’re just getting started, you might not want to rely on a notepad to store the ones you frequently use. While over time, you will know the syntax by heart, with new fields and objects constantly being created in your org, there is no need to remember them all when Salesforce Inspector Reloaded exists.

When using the Data Export feature from this Chrome extension, not only can you write your queries from scratch and get object and field suggestions as you type, but you can also just as easily save any query that you write so you don’t have to write it again next time.

For example, if you’re using Sales Territories in your org, accessing them can be quite cumbersome. By saving the query below, you can rapidly just replace the territory name with the one you’re looking for and click on the Id to open it in Salesforce. While this is one of the simpler examples, it’s definitely one to save quite a bit of time.

Summary

Finally, if you haven’t already started with SOQL, there’s no time like now! Our dedicated Introduction to the SOQL course includes everything you might need: instructor-led videos, multiple examples and exercises, as well as all the considerations and best practices you should be aware of.

With the ability to access Salesforce data lightning-fast, easily filter and group records across different objects and scenarios, this skill is sure to future-proof your toolkit.