Filter Language Components

Identifiers

Column identifiers are column names wrapped in square brackets. E.g. [Assignee], [Banana], [Business Name] You can use a string from your formula as a column name by using the formula function DB_ENCODEIDENTIFIER.

Literals

Name E.g. Notes
Null NULL The NULL constant. This value indicates ‘no value’.
Boolean Literal TRUE, FALSE A logical or boolean value. Only two possible values: TRUE or FALSE.
DateTime Literal #2013-12-25T19:30:05#
Number Literal 3.5
String Literal ‘Hello there’, ‘Stuart”s house’ Double up single quotes to include a ‘ in your string

Operators

Logical Operators

Operator Example Result Description
<<any>> = <<any>> 1 = 1 Boolean
<<any>> <> <<any>> 1 <> 1 Boolean
<<any>> > <<any>> 8 > 10 Boolean
<<any>> >= <<any>> 4 >= 1 Boolean
<<any>> < <<any>> 8 < 2 Boolean
<<any>> <= <<any>> 1 <= 4 Boolean
NOT <<boolean>> NOT TRUE Boolean Invert the operand. If the operand is TRUE, FALSE. If the operand is FALSE, TRUE.
<<any>> IS NULL Boolean Is the operand a value NULL.
<<any>> IS NOT NULL Boolean Is the operand a value other than NULL.
<<boolean>> AND <<boolean>> Boolean TRUE if both the left and right operands are TRUE.
<<boolean>> OR <<boolean>> Boolean TRUE if either of the left and right operands are TRUE.
<<any>> IN (<<any1, anyN>>) Boolean You can include any number of items in the list. All items will be converted to the type of the left value.

String Operators

Operator Example Result Description
<<string>> STARTS WITH <<string>> Boolean
<<string>> ENDS WITH <<string>> Boolean
<<string>> CONTAINS <<string>> Boolean
<<string>> CONTAINS INSENSITIVE <<string>> Boolean
<<string>> IS EMPTY
Boolean Is the operand a value NULL or the empty string (”).
<<string>> IS NOT EMPTY Boolean Is the operand a value other than NULL or the empty string (”).
<<string>> & <<string>> String Add two strings together to form one.

Mathematical Operators

Operator Example Result Description
<<number>> + <<number>>  1 + 1 Number
<<number>> <<number>>  10 – 5 Number
<<number>> / <<number>>  45 / 100 Number
<<number>> * <<number>>  4 * 4 Number

Role Operations

Operator Example Result Description
<<string>> IN ROLE <<string>> [Assignee] IN ROLE ‘Administrators’ Boolean Determine if a user is a member of a group
<<string>> SUPERVISOR OF <<string>> CURRENT_USER SUPERVISOR OF [Assignee] Boolean Determine if a user is the supervisor of another user, or if he indirectly supervises the user further up the tree
<<string>> DIRECT SUPERVISOR OF<<string>> CURRENT_USER SUPERVISOR OF [Assignee] Boolean Determine if a user is the direct supervisor of another user
GET_USER_SUPERVISOR(<<string>>) GET_USER_SUPERVISOR([Assignee]) String Get the value of a given users’ supervisor field
GET_USER_DEPARTMENT(<<string>>) GET_USER_DEPARTMENT([Assignee]) String Get the value of a given users’ department field
GET_USER_POSITION(<<string>>) GET_USER_POSITION([Assignee]) String Get the value of a given users’ position field
DEPT_CONTAINS(<<string>> container, <<string>> dept) DEPT_CONTAINS(‘Frankston City Council’, ‘Operations Department’) Boolean Is the department on the right a descendent of the department on the left

DateTime Operations

Operator Example Result Description
TO_SECOND(<<datetime>>) DateTime Deletes information from the date at any lower resolution than seconds.I.e. removes microseconds
TO_DAY(<<datetime>>) DateTime Deletes information from the date at any lower resolution than days.I.e. removes hours, minutes, seconds
TO_MONTH(<<datetime>>) DateTime Deletes information from the date at any lower resolution than months.I.e. removes days, hours, minutes
TO_YEAR(<<datetime>>) DateTime Deletes information from the date at any lower resolution than years.I.e. removes month, day, hours, minutes
TO_STR_SECOND(<<datetime>>)
TO_STR_DAY(<<datetime>>)
TO_STR_MONTH(<<datetime>>)
TO_STR_YEAR(<<datetime>>)

Magic Values

Name Result Description
CURRENT_DATE DateTime The current date and time.
CURRENT_USER String Current authorisation role (Replaced by acting or by form credentials).
CURRENT_USER_IDENTITY String The current logged in user. (Or the acting user)
CURRENT_ACTUAL_USER String The current logged in user.

Aggregates

Aggregates can only be used in the context of a DB_REPORT or within a dashboard control (Table, PieChart, BarChart).

Name Result Description
COUNT(<<any>>) Number Gives the number of rows found. A row will be ignored if the value within the brackets is null. You will almost always want to use RecordID as the argument
MIN(<<any>>) Same as argument Gives the lowest value found.
MAX(<<any>>) Same as argument Gives the highest value found.
SUM(<<number>>) Number Gives the sum (a+b+c) of the values found
AVERAGE(<<number>>) Number Gives the mean average of the values found with equal weighting for each row

Case Expressions (5.1)

This language element has been added to allow decisions to be made within a filter string. E.g.

CASE WHEN [Name]='John' THEN 'Red' ELSE 'Blue' END

This expression is structured:

CASE
    WHEN cond1 THEN result1
    WHEN condN THEN resultN -- any number of condition/result pairs
    ELSE otherwise
END

You may include any number of conditions. The result type is defined by the result type of result1. If resultN is of a different type, it will be converted to this type. Some more examples:

CASE WHEN [Total]=0 THEN 0 ELSE [Score]/[Total] END -- Avoid divide by zero errors
CASE
    WHEN [Risk]>7 THEN 'Red'
    WHEN [Risk]>4 THEN 'Orange'
    WHEN [Risk]>2 THEN 'Yellow'
    ELSE 'Green'
END

Stepping Expressions (5.3)

The Stepping Expression has been added to allow users/form designers to access data from a second or third form when doing reporting or filtering.

These expressions take this form:

STEP TO { PARENT | CHILD |  } [form] ON condition
STEP TO { PARENT | CHILD |  } [form]  ON condition -- any number of steps
GET aggregateExpression

The first part of the expression (stepping clause) can be one of three options:

  • STEP TO PARENT [form]
  • STEP TO CHILD [form]
  • STEP TO [form]

To make this a little less abstract, here is an example:

-- the filter is being run in the context of the sys.Users form
STEP TO [Contract] ON [To].[Originator]=[Username]
GET COUNT(1)

This will give you a total number of assigned Contracts for each user in the system. Note that the [To] reference ([To].[Assignee]) doesn’t have to appear on the left of the condition, you can use any number of [To] references anywhere in a stepping condition.

You can try this in a tabular report, using an Advanced Column.

 

 

Related Articles