Getting started with SOQL

Klipfolio Dashboard's Salesforce data connector leverages SOQL (Salesforce Object Query Language) to retrieve your data in the form of a JSON file so you can create Salesforce dashboards. SOQL is similar to SQL (Structured Query Language), but has some limitations such as its inability to perform database modifications and to perform arbitrary joins in your query (we'll get to this in a future article).

Beyond the technical jargon, the real difference between SOQL and SQL is the use-case. If you have a SQL database, then it is maintained by your IT team. When you need to make a query, chances are you will give IT the specs and they will provide you with the resultant data. With Salesforce, however, you may be the primary user and your IT may not be involved with your account. That means when it comes to crafting the query with SOQL, you may be on your own.

If you aren't technically inclined (like me), don't worry. SOQL is a well-designed language that uses syntax that is easy to understand and that relates to your Salesforce account. I am assuming your starting point is knowledge of your Salesforce account and familiarity with the Salesforce UI. By the end of this article, (hopefully) you will be comfortable enough to start using SOQL to create a query to retrieve data from your Salesforce account.

Understanding how SOQL works

I want to start by helping you understand where the data in your query is coming from. I will cover this in more depth below, but for now I only want to mention the two required clauses: SELECT and FROM. The syntax for these two clauses is taken directly from your Salesforce account and is the foundation of any query you write.

To see what I mean, log in to Salesforce.com, click the Customer Support option at the top of the page, and select Setup from the menu. Under the App Setup category in the navigation bar on the left side of the page, and click the Customize node. This will expose a list of objects associated with your account, such as Leads, Accounts, and Opportunities. I'll reiterate this below, but objects are typically specified in the FROM clause (FROM Lead / FROM Account).

When you select an object like Opportunities, you will have the option to work with different settings associated with that object. Within the context of using SOQL to plug Salesforce data into Klipfolio Dashboard, I am only interested in the Fields option. Click on that option to bring up a table that lists all of the fields associated with your object. Again, I'll reiterate this below, but fields are typically specified in the SELECT clause (SELECT Name / SELECT Email).

The best way to think about a Salesforce query is to frame it in the form of a question. For instance, I want to know the name of each of my opportunities. I can see that Opportunities is an object, and that Name is a valid field for this object. Therefore, my query will look like this:

SELECT Name
FROM Opportunity

The problem with a query like this is that it retrieves a JSON file that may have thousands of entries. This illustrates exactly why SOQL is so important for retrieving data sets, as it allows you to specify and limit the information you retrieve. Instead of retrieving all the names for all of your opportunities, you can use additional, optional clauses to place parameters on the query. Instead, you may write a query that says give me all the names for opportunities created in the last 30 days. That query will look like this:

SELECT Name
FROM Opportunity
WHERE CreatedDate = Last_N_Days:30

The difference between these two queries illustrates how useful SOQL can be. The first query shows the power of SOQL to retrieve huge swaths of data from Salesforce, while the second query shows how to set parameters for that data and retrieve very specific data. Now that you understand how your data is pulled from your Salesforce account, let's take a look at SOQL's Syntax.

Introduction to SOQL SELECT Syntax

Note: This section is a summary/simplification of the SOQL SELECT Syntax documentation and other resources listed at the bottom of the article.

As noted above, the best way to formulate a query is to start with a question you want answered. That question can be as simple as "I want the names of all my opportunities" (see above), or can be as complex as "I want the name and dollar value for each of my opportunities created in the last 90 days and I want the results ordered by their last name." Getting the answer out of Salesforce is what SOQL's syntax is all about. Each part of your question is reflected by the different clauses you can use in SOQL.

Unlike SQL, which has multiple statements (UPDATE/DELETE), SOQL only uses the SELECT statement. The SELECT statement is used to retrieve results from databases (in SQL) and results from your Salesforce account (in SOQL). In the context of Klipfolio Dashboard, you will be crafting and executing statements using the data connector (see "How do I connect to Salesforce.com"). This will retrieve a JSON file that provides the "answer" to your query.

There are many different ways to get the answer you want using SOQL. I want to take a closer look at the SOQL SELECT Syntax to provide you with the framework to get started. To make this section as concise and practical as possible, I am only going to look at 5 clauses: SELECT, FROM, WHERE, GROUP BY, and ORDER BY.

SELECT (fieldList)

This clause lets you specify one or more fields to retrieve from a specific object. The clause can include multiple fields, so long as they are separated by commas. This is a required clause in each query you write.

Standard and Custom Fields. You can create Custom Fields for any object in your Salesforce account and query it using SOQL. Custom Fields are displayed in the Fields sections below the Standard fields table and are distinguish in the query by the suffix __c (underscore underscore c).

Example:

SELECT Name, Amount, Account

FROM (objectType)

This clause lets you specify the object you want to query and is a required clause in each query you write.

Example:

FROM Opportunity

WHERE (conditionExpression)

This clause lets you determine what values are shown for a specified object, and select the type of filter. One of the most common ways to use the WHERE clause is to filter based on dates (check out the date documentation here).

Example:

WHERE CreatedDate = today

Note: The WHERE clause can use a variety of case insensitive operators, like x = y, or x > y. To learn about operators, check out this documentation.

Salesforce Docs: Condition Expression Syntax (WHERE Clause)

GROUP BY

This clause is used to summarize fields and can be used with aggregate functions. Just like Klipfolio Dashboard's GROUP and GROUPBY function, this clause is useful for simplifying data and only showing distinct values in your data source.

Example:

SELECT Name, Amount
FROM Opportunity
GROUP BY Name

Salesforce Docs: GROUP BY Clause

ORDER BY

This clause lets you specify the ordering method for your query results, and can include multiple fields.

Example:

SELECT Name, Amount
FROM Opportunity
ORDER BY Amount

Salesforce Docs: ORDER BY Clause

Sample Queries

I am going to end this article by looking at a few sample queries you can use in Klipfolio Dashboard. It is important to note that you can only pull data that exists in your Salesforce account, so if you don't have a Leads object with data associated with it, then queries with "FROM Lead" won't work. The purpose of this article was to get you started with SOQL and comfortable enough with it to do craft some queries on your own.

Note: I've included some custom fields in the queries. You can replace these values with relevant fields from your Salesforce account including your own custom fields.

1. Today's Leads

This query will return a rolling list of today's leads, along with information about the name, company, email, and product they are interested in. Initial Product is a custom field I've made up to see which of our products the lead was first interested in. Just remember to change this field to align with your Salesforce account.

SELECT name, company, email, Initial_Product__c
FROM Lead
WHERE CreatedDate = today

2. Leads Last N Days

This query will return a rolling list of leads captured over the last n days, where n is a custom value, and is ordered based on their names (by default in alphabetical order with blank values first).

SELECT name, company, email, Initial_Product__c
FROM lead
WHERE CreatedDate = Last_N_Days:30
ORDER BY Name

3. Bookings Past 90 Days

This query will return a rolling list of all bookings for the last 90 days, including data like the name, product (custom field), amount, closedate, and type.

SELECT Name, Product__c, Amount, CloseDate, Type
FROM opportunity
WHERE stageName = 'Closed Won' AND CloseDate = LAST_90_DAYS

Resources

These resources proved invaluable in my research for this article, so make sure to check them out if you need clarification or more in-depth coverage of SOQL:

• SOQL SELECT Syntax documentation
• Date Formats and Date Literals documentation
• Connect Salesforce to Klipfolio Dashboard - Our support article
• Salesforce Documentation for Relationship Queries