Filter criteria

When defining a view, you can add detailed filter criteria to narrow the result set. This filtering mechanism is the same that Platform uses for object-specific searches and for reports to determine which records are shown in the output.

The filter mechanism allows you to add up to five filters by selecting a field, an operator, and a value. You can additionally filter by a date interval.

For example, in the filter criteria shown below, the filter specifies records (in this case, library book titles) where the title's Status field equals Available.

The operators available depend on the selected field type. The operators are: equals, not equal to, less than, greater than, less or equal, greater or equal, starts with, contains, does not contain, is one of, is not one of, is empty, and is not empty.

The Filter Conditions parameter allows you to control how each of the filters is used to determine the output of the view:

• All (AND): Selecting this condition means that all filter conditions must be met in order for a record to display in this view.
• Any (OR): Selecting this condition means that at least one of the filter conditions must be met in order for a record to display in this view, but it doesn't matter which one.
• Expression: Expressions allow you to specify how filters should be evaluated together to determine whether or not a record should be shown in the view. To define an expression, use the numerical value of the filters (1 through 5) along with AND, OR, and NOT keywords and parentheses "(" ")" to group subexpressions together. For example:
  • (1 OR 2) AND 3 - Only show records that match filter 3 and at least one of 1 or 2
  • (1 AND 2) OR NOT 3 - Only show records that either match both 1 and 2, or do not match 3.
  • (1 AND 2) OR (3 AND 4) - Only show records that match both 1 and 2, or both 3 and 4.
  • (1 AND (2 OR 3)) OR NOT 4 - Only show records that do not match 4 or those that match 1 and either 2 or 3.
• Formula: The Formula filter condition allows you to specify a custom filter using the syntax of a SQL WHERE clause. The length of the filter formula must not exceed 500 characters. This option is not available for OpenEdge services. For more information about filtering by formula, see Filtering by formula.
• Search criteria: The Search criteria condition allows you to filter using OpenEdge ABL syntax. This option is only available for OpenEdge services. For more information about filtering by search criteria, see Filtering OpenEdge Service objects by search criteria.

Some fields have special tokens that can be used in filters. Do not use quotation marks around tokens or keywords.

• For text fields, you can create "or" filters by using the is one of operator and separating multiple values with commas. For example, Sales, Marketing looks for either Sales or Marketing.
• For user lookup fields, use CURRENT_USER to represent the current user.
• For date fields, you can use special tokens derived from the current date:

  Token	Description	Example
  TODAY	12:00 AM of current date	08/18/2015
  WEEK	First day (Sunday) of current week	08/09/2015
  MONTH	First day of current month	08/01/2015
  QUARTER	First day of current quarter	07/01/2015
  YEAR	First day of current year	01/01/2015

  Note: The value of the TODAY token is 12PM+1ms of the current day in the time zone of the current user for date/time fields; for date fields, its value is either the beginning (for the greater than operation) or the end (for the less than operation) of the current day.

  You can add or subtract integer numbers from tokens to specify a date not in the current time period. For example:

  • To filter by the first day of next week, use the condition WEEK+1
  • To filter by the current week (from Sunday to Saturday), use the conditions date>=WEEK AND date<WEEK+1
  • To filter by the previous month, use the conditions date>=MONTH-1 AND date<MONTH
  • To filter by the previous and current year, use the conditions date>=YEAR-1 AND date<YEAR+1