Underpinning the advanced, flexible search capabilities of the Case List Explorer is the Case Query Language (CQL). This page describes the syntax and capabilities of the language as well as its limitations. 

Syntax

• Available operators:  and, or , =, !=, <, <=, >, >=, (, )

• Parentheses: () can be used to group logic and make complex expressions

• Dates: dates can be filtered with format 'YYYY-MM-DD'.  This must include the apostrophes around the date.  The date()  function may be used to validate a date value: date('2021-08-20') 

Examples

name = 'john' and age > 10

region = 'london' and registration_date < '2019-12-01'

(sex = 'm' and weight < 23) or (sex = 'f' and weight < 20)

status = 'negative' and subcase-exists('host', @case_type = 'lab_result' and result = 'positive')

Supported Functions

The following functions are supported:

date

• Behavior: Will convert a string or a number value into an equivalent date.  Will throw an error if the format of the string is wrong or an invalid date is passed.  

• Return: Returns a date

• Arguments:  The value to be converted (either a string in the format YYYY-MM-DD or a number).  Numbers are interpreted as days since Jan 01 1970.

• Usage: date(value_to_convert)

today

• Return:  Returns the current date according in the timezone of the project space.

• Arguments: None

• Usage: today()

• Note: When comparing the output of this function to a value with a date and a time by using the =  operator, this function returns the current date at midnight. For example, last_modified=today() will look for an exact match between the date and time in last_modified and the current date exactly at midnight.

not

• Behavior: Invert a boolean search expression

• Arguments: The expression to invert

• Usage: not(is_active = 0 and stock_level < 15)

starts-with

• Behavior:  Match cases where a multi-select case property value begins with the given argument value.

• Arguments:  Two arguments, the multi-select case property and the value to check.

• Notes:

  • This filter is case sensitive

  • Using this filter may impact the performance of your query

• Usage:

  • starts-with(social_security_num, "123")

  • starts-with(timezone, "Africa/")

selected

• Behavior:  Match cases where a multi-select case property contains the given value. The behavior of this function matches that of the selected-any  function.

• Return: True if that particular value is present in the case property.  Otherwise False.  

• Arguments:  Two arguments, the multi-select case property and the value to check.

• Notes:

  • If the 'value to check' contains spaces, each word will be considered independently as in 'select-any'

• Usage:

  • selected(tests_performed, "testA")

  • selected(tests_performed, "testA testB")

    • This works the same as the 'selected-any' function. Best practice would be to use 'selected-any' in this instance to make the intention clear.

selected-any

• Behavior:  Match cases where a multi-select case property contains ANY of the values in the input provided

• Arguments:  Two arguments, the multi-select case property and the values to check represented as a space separated string.

• Usage: selected-any(tests_performed, "testA testB testC") 

Outcomes table for selected-any 

selected-all

• Behavior:  Match cases where a multi-select case property contains ALL of the values in the input provided

• Arguments:  Two arguments, the multi-select case property and the values to check represented as a space separated string.

• Usage: selected-all(tests_performed, "testA testB testC") 

Outcomes table for selected-all 

fuzzy-match

• Behavior:  Determines if a given value is a fuzzy match for a given case property.

• Return: True if that particular value matches the case property.  Otherwise False.  

• Arguments:  Two arguments: the case property and the value to check.  

• Usage: fuzzy-match(first_name, "Sara")

phonetic-match

• Behavior:  Match cases if a given value "sounds like" (using Soundex) the value of a given case property. (e.g. "Joolea" will match "Julia")

• Return: True if that particular value matches the case property. Otherwise False. 

• Arguments:  Two arguments: the case property and the value to check.  

• Usage: phonetic-match(first_name, "Julia")

Filtering on parent (ancestor) cases

Searches may be performed against ancestor cases (e.g. parent cases) using the "/" operator. 

For example, the below search will search for cases that have a parent case that matches the filter of age greater than 55:

parent/age > 55

And if you want to perform a search against cases higher in the case heirarchy (e.g. the "grandparent" case), you can add multiple parent/parent/ steps as shown below:

parent/parent/dod = ''

ancestor-exists

• Behavior:  Match cases that have an ancestor with the given relation that matches the ancestor filter expression.

• Arguments:  Two arguments, the ancestor relationship (usually one of parent or host) and the ancestor filter expression.

• Usage:

  • ancestor-exists(parent/parent, city = 'SF')

  • ancestor-exists(parent, food_included = 'yes' and ancestor-exists(parent, city!='' and selected(city, 'Boston')))

• Limitation

  • The arguments can't be a standalone function and must be a binary expression

    • This will not work: ancestor-exists(parent, selected(city, 'SF'))

    • This will work:  ancestor-exists(parent, city != '' and selected(city, 'SF'))

  • The ancestor filter expression may not include subcase-exists or subcase-count

Filtering on child cases (subcases)

Special functions are provided to support filtering based on the properties of subcases. These are:

subcase-exists

• Behavior:  Match cases that have a subcase with the given relation that matches the subcase filter expression.

• Arguments:  Two arguments, the subcase relationship (usually one of 'parent' or 'host') and the subcase filter expression.

• Usage: subcase-exists('parent', lab_type = 'blood' and result = 1) 

subcase-count

• Behavior:  Match cases where the number of subcases matches the given expression.

• Arguments:  Two arguments, the subcase relationship (usually one of 'parent' or 'host') and the subcase filter expression.

• Usage: subcase-count('parent', lab_type = 'blood' and result = 1) > 3

  • The count function must be used in conjunction with a comparison operator. All operators are supported ( >, <, >=, <=, =, !=)
    

Multi-select case property searches

As shown above, the selected , selected-any  and selected-all  functions can be used to filter cases based on multi-select case properties.

A multi-select case property is a case property whose value contains multiple 'terms'. Each 'term' in the case property value is typically separated by a space.

tests_completed = 'math english physics'

The following table illustrates how a case property value is split up into component terms. Note that some characters are removed and other are used as separators.

Limitations

• Comparison between case properties is not supported e.g. activity_completion_date < opened_on 

• Calculations within search criteria are not supported e.g. age = 7+3 , dob = today() - 7