JAVA API:Filter Expressions in JAVA 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 the ProductInventory object might have a field named QuantityOnHand.
- 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
Components of a filter expression are: <FIELD_NAME> <OPERATOR> <VALUE> <LOGICAL_OPERATOR>
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
- In addition, 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
FIELD_VALUE
- The search value - a string, a date, a number.
- Examples:
- String: 'Paper 123'
- Date: '06/06/2007'
- Numeric Value: 9383
- Empty Field: '' or BLANK
- 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 search for multiple field values, separate each value using vertical pipe (|)
- For example: company_name='Acme|Ajax'
- (The criteria is satisfied when the company name is either Acme or Ajax)
- To search for a field value that happens to contain a vertical pipe (|), you need to "escape" the pipe so it is recognized as a literal character in the field. To do that, you put a backslash in front of it: \|. For example, company_name contains '\|'
- That works in REST APIs and in the GUI.
- In Java strings, the backslash is already an escape character. (It combines with the one that follows it to create a single character.)
- So in Java code, you need two backslashes: \\|
- (The first two (\\) combine to become a single backslash (\), so the filter expression handler sees "\|", as desired.)
- Finally, note that "equals" does not work for this particular search. The required operator is "contains".
- To include a single quote in the value that you are searching for, specify an additional quote for every single quote ('').
LOGICAL_OPERATOR
- Logical operators can be used to build more complex expressions.
- The logical operators are:
- AND
- OR
- Considerations
-
- Two subexpressions joined by a logical operator form a logical expression.
- Logical expressions resolve to a Boolean value: 1/0 or TRUE/FALSE.
- Use parentheses--()--to group expressions logically and to join multiple expressions.
- Parentheses are used in pairs; each open parenthesis "(" requires a closing parenthesis ")".
- For example:
- ((<expression1> AND <expression2>) OR (<expression3> AND <expression_4>))
- Learn more: Using Logical Operators and Parentheses
Searching Different Kinds of Fields
Date/Time search
- 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:
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 Java Variables
To use a Java variable in a search expression, add it to a string containing the expression, and surround it with single quotes to make the expression legal. Here, the record_id field is searched for the value contained in contactID:
- "record_id equals '" + contactID + "'"
Here is a more complete example that gets the value from function parameter and uses it to invoke the Java search API:
String contactID = requestParams.get("contact_id"); Result result = Functions.searchRecords( "CONTACT", "record_id,name", "record_id equals '" + contactID + "'");
Note: For more complex queries, the Java SearchContext Class can be used.