Working with SOQL in Salesforce

Salesforce Object Query Language (SOQL) is used to search your Salesforce data for specific records. SOQL is similar to the SELECT statement in the widely used Structured Query Language (SQL) but is designed specifically for Salesforce data.

The basic syntax of a SOQL query is as follows: SELECT (list of fields) FROM (an object) WHERE (filter statements/sorting).

For a list of standard fields for major Salesforce objects, see: Salesforce Fields Reference (opens new window).

Inputting SOQL WHERE Conditions (Syntax):

The WHERE clause follows field expression syntax. A fieldExpression is defined as follows: <fieldName> <comparisonOperator> <value>.

Comparison operators: Comparison operators include the following: =, !=, <, <=, >, >=, LIKE, IN, NOT IN, INCLUDES, and EXCLUDES. Here is a simple example following fieldExpression syntax:

For detailed information on how to use each comparison operator, see: Comparison Operators (opens new window).

Logical operators: Multiple field expressions can be joined using logical operators. These include: AND, OR, and NOT. The basic syntax is as follows:

• fieldExpressionX AND fieldExpressionY
• fieldExpressionX OR fieldExpressionY
• NOT fieldExpressionX.

Here is an example showing two fieldExpressions joined by a logical operator:

For more information on logical operators, see: Logical Operators (opens new window).

Date formats and date literals:

To filter on date fields in a query, you must use Date only format. The syntax for this is: YYYY-MM-DD. To filter on dateTime fields in a query, you must use the format including: date, time, and time zone offset. There are three possible syntax formats for this:

• YYYY-MM-DDThh:mm:ss+hh:mm
• YYYY-MM-DDThh:mm:ss-hh:mm
• YYYY-MM-DDThh:mm:ssZ.

In order to query a date or dateTime field, you may need to turn on formula mode if you are not using it already. This is needed to convert your timestamp to the ISO8601 format expected in SOQL. Also note that you do not need to use single quotes around date or dateTime values. For date fields, add ‘.to_date’ to the end of your date formula to convert your date or timestamp to the correct format.

For dateTime fields, the third syntax format is the simplest to use. After entering the formula to get your desired timestamp (eg: now, 2.weeks.ago.beginning_of_day, etc.), add ‘.strftime("%Y-%m-%dT%H:%M:%S%z")’ to the end of it.

For more information on date formats and date literals, see: Date Formats and Date Literals (opens new window)

Triggers/actions that use SOQL

Querying with a WHERE clause

To quickly setup a SOQL query with the input configured with the fields available in the Workato UI, use one of the triggers or actions below:

Triggers

• Scheduled record search using SOQL query WHERE clause

Actions

• Search records in bulk using SOQL query (API 1.0)
• Search records using SOQL query WHERE clause

When using the trigger and actions that simply require a WHERE clause, the recipe will automatically handle the SELECT FROM portion of your query. It will SELECT all fields FROM the object you choose from the Fields to retrieve pick list.

If the LIMIT and OFFSET fields are available, you may also specify those clauses. Note that these are not supported in the bulk actions.

Querying with a full SOQL query

For users who prefer to use a full SOQL query or if copying and pasting after running query tests on Salesforce or Workbench, use one of the triggers or actions below:

Triggers

• Scheduled record search using SOQL query

Actions

• Search records in bulk using SOQL query (API 2.0)
• Search for records using SOQL query

In the SOQL query field, enter the SOQL query like the example below:

TIP

Unlike the usual SELECT * operation that is typically used in SQL queries, SOQL queries do not support that pattern. Users must specify the fields to be retrieved in the SELECT clause. Also note that the maximum length of a SOQL query is 16,000 characters.

To generate the output schema, click on the 'Use SOQL' button. Users should use the same SOQL in the SOQL query field. The query will run and return sample rows, generating the output automatically.

In subsequent recipe steps, the output from the SOQL query can now be accessed in the data tree.

Other clauses

Use the LIMIT clause to limit the number of results returned in the search. The OFFSET clause is also available if users need to skip a number of rows before returning results. The maximum value of both clauses is 2,000. Requesting an offset greater than 2,000 results in a 'NUMBER_OUTSIDE_VALID_RANGE' error.

Additional documentation

For additional help, see Salesforce documentation:

• SOQL (opens new window)
• WHERE Clause Syntax (opens new window)