Working with SOQL in Salesforce
Salesforce Object Query Language (SOQL) is used to search your Salesforce data for specific information. SOQL syntax consists of a required SELECT statement which may be followed by a number of optional clauses (such as TYPEOF, WHERE, WITH, GROUP BY, etc.).
In a Workato recipe, the scheduled object query trigger will run SOQL queries with the following basic syntax: SELECT (list of fields) FROM (an object) WHERE (filter statements/sorting).
The recipe will automatically handle the SELECT FROM portion of your query. It will SELECT all fields FROM the object you choose from the pick list. For optional clauses, the trigger currently only supports WHERE conditions.
For a list of standard fields for major Salesforce objects, see: Salesforce Fields Reference.
Inputting SOQL WHERE Conditions (Syntax):
The WHERE clause follows field expression syntax. A fieldExpression is defined as follows: \
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.
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.
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:
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
For additional help, see Salesforce documentation