Common:Filter Expressions in APIs
Filter Expression Syntax
A filter expression defines search criteria in this format:
- {field_name} {comparison operator} {field_value}
- Optionally, complex expressions can be designed, using {logical operators} and parenthesis (...)
- where
-
- {field_name}
- The name of a field in an Object; for example in the Grocery object, a Fruit field might exist
- Learn more: Field Name
- {comparison operator}
- Used to compare a field to a value, creating an expression.
- Examples:
- Less than: <
- Equals: =
- {field_value}
- A number, datetime, or string; the value of a field; for example, in the Fruit field, a field value might be apples or oranges
- {logical operator}
- A logical operator, used to create complex filters
- Examples:
- apples OR oranges
- apples AND oranges
- parentheses (...)
- Used to group expressions; determines the order in which the components of the expression are evaluated
- Examples:
- (((apples AND oranges) OR (oranges AND grapes)) AND pears)
- (apples OR oranges OR grapes OR peaches) AND plums))
- Considerations
-
- Spaces are legal in filter expressions.
- Quotes are needed around dates and strings. They are not needed around numbers.
- Only single quotes are recognized around values, not double quotes.
Building Filter Expressions in APIs
- FIELD_NAME
- The name of the Field or Computed Field to use in creating a filter
- OPERATOR
- The standard alphabetic operators shown in the GUI can be used:
Operator Description equals Returns only records with the specified value not equal to Returns only records that do not include the specified value less than Returns only records that are less than the specified value less than equals Returns only records that are less than or equal to the specified value greater than Returns only records that are more than the specified value greater than equals Returns only records that are more than or equal to the specified value contains Returns only records that contain the specified value not contains Returns only records that do not contain the specified value starts with Returns only records that start with the specified value ends with Returns only records that end with the specified value
- These additional alphabetic operators are also available:
Operator Description less than equals Returns only records that are less than or equal to the specified value greater than equals Returns only records that are more than or equal to the specified value
- The symbolic operators can be used:
Operator Description = Equal to Returns only records with the specified value
!= Not Equal to Returns only records that do not include the specified value
< Less than Returns only records that are less than the specified value
> Greater than Returns only records that are more than the specified value
<= Less than or equal to Returns only records that are less than or equal to the specified value
>= Greater than or equal to Returns only records that are more than or equal to the specified value
- The logical operators can be used for more complex expressions:
Logical Operators Description ( Open Bracket Used to structure expressions into groups for evaluation; must always be paired with a Close Bracket
) Close Bracket Used to structure expressions into groups for evaluation; must always be paired with an Open Bracket
AND Logical AND (and) OR Logical OR (or)
- FIELD_VALUE
- The search value; a value can be either a string, a date, a number
- Follow these guidelines when specifying values:
- The value can be partial text or whole words
- Enclose strings and date/time values in 'single quotes'
- To specify date values, use the Date Format as specified in Company Information
- To search for a blank (empty) string, use two single quotes, with nothing between them ().
- To search for a blank (empty) field of other kinds, use a field value of BLANK
- To include multiple values for a field, separate each value using the pipe symbol (|)
- To include a single quote in the value, specify a backslash and a double quote (\").
- For example, to search for the phrase, That's right! you would specify 'That\"s right!'.
- LOGICAL OPERATOR
- Learn more: Using Logical Operators and Brackets
Searching Different Kinds of Fields
Date/Time search in Java APIs
- Search for records with a specific date:
- date_created = '01/01/2010'
- (The date format is specified by the user-settings of the person entering the expression.)
- Search for records created within a specified date range:
- date_created > 04/20/2002 AND date_created < '01/01/2010'
- Search for records with a specific date:
Date/Time search in REST APIs, using UTC Format
- Search for records created on a specified date (Jan 20, 2010):
- date_created = '2010-01-20'
- Search for records created at a specified time (2 pm):
- appointment_time = '14:00:00Z'
- Search for records created within a specified date/time range (11pm to 3am the next day):
- date_created > '2010-01-20T23:00:00Z' AND date_created < '2010-01-21T2:00:00Z'
Text search
- Search for records where the last_name field contains the string 'smith':
- last_name contains 'smith'
- Search for records where the state field does not contain the string 'CA':
- state != 'CA'
Numeric search
- Search for records where a field value matches a specific number:
- price = 20.00
- Search for records where a field value is less than a specific number:
- price < 20.00
Empty field search
- Search for records where a field is empty:
- price = BLANK
Using Logical Operators and Parentheses
Logical operators are used in Filter Expressions to combine subexpressions, to search using multiple conditions and complex criteria.
- Syntax
- {subexpression1} {Logical_operator} {subexpression2}...
Logical operators provide the ability to include or exclude groups of records in Expressions. These logical operators are available:
- AND: Logical AND
- OR: Logical OR
- Considerations
- Use parentheses--()--to group expressions logically and to join multiple expressions.
- Parentheses are used in pairs; each open parenthesis "(" requires a closing parenthesis ")"
- (({subexpression_1} AND {subexpression_2}) OR {subexpression_3}) AND ({subexpression_4})
- Precedence
Expressions are evaluated in this order:
- expressions in parentheses (...)
- AND expressions (Logical AND)
- OR expressions (Logical OR)
- If expression execution order is not dictated by parentheses, then AND expressions take precedence over OR expressions.
- Examples
-
- Find all accounts that have fewer than 50 employees located in area code 408 or accounts in the construction industry in California
- (Number of Employees <50 AND Area_Code = '408') OR (State = 'CA' AND Industry = 'Construction'
- Find all accounts where the state is either California, Nevada, or Washington
- (State = 'CA') OR (State = 'NV') OR (State = 'WA')
- Find all accounts where the state is not California, Nevada, or Washington:
- (State != 'CA') AND (State != 'NV') AND (State != 'WA')
- Find all accounts in the construction industry where the state is California or Nevada
- ((State = 'CA') OR (State = 'NV')) AND (Industry = 'Construction')
- Finds account in the State of California or accounts in construction in the state of Nevada
- State = 'CA' OR State = 'NV' AND Industry = 'Construction'
- Also finds account in the State of California or accounts in construction in the state of Nevada (the AND (AND) operator implies brackets)
- State = 'CA' OR (State = 'NV' AND Industry = 'Construction')
- Find accounts in California or Texas where the account name contains "Equipment"
- (Acct_Name contains 'Equipment') AND (state = 'CA' OR state ='TX')
- Find accounts in California where the account name contains "Equipment", or any record in Texas
- (Acct_Name contains 'Equipment') AND (state = 'CA')) OR (state ='TX')