Basic Syntax and Concepts

This overview provides a concise summary of the key search techniques.

This section contains some of the types of searches you can perform on standard fields.

Keyword and phrase matching

You can perform keyword queries on any field by entering the field as a prefix with a colon. The Contains field provides a simple way of performing searches across all fields.

Search for the word "clean" in all text fields:

clean

Use double quotes to search for records that contain phrases with spaces. For example, to search for the phrase "clean up" in all text fields:

"clean up"

Searching within specific fields

You can reference specific fields by prefixing them with a colon. Consult the technical specification for a full list of field names.

Search for records where the title must equal the word clean

title:clean

Wildcard searches are available using the * symbol. This will search the title field where the first word is “water”. This would return results such as “water”, “waterfall”, “water-pump” or phrases such as “water is needed”.

title:water*

Search for records where the word water is anywhere within the title. This would return results with titles such as “I need water”, “Water is required”, “Property water is needed”

title:*water*

Search for an empty title field:

title:none

Search for whether the title field has a value

title:any

The ~ symbol performs SOUNDS LIKE searches. For example, in the contacts register a search such as the one below would return records that sound like “Smith” such as Smyth, Smith, Smath.

lastName:~Smith

AND, OR and NOT searches

By default, when can combine multiple search terms they are combined with an “AND” logic — that is, the results will only contain records that match all search terms.

If you want to change this behaviour, you can explicitly specify the way the search should logical combine the terms by using the words AND, OR and NOT.

This will return records that have both the words water and clean in the title field:

title:*water* AND title:*clean*

This will return records that have either the word water or clean in the title field:

title:*water* OR title:*clean*

Return items that have the word water in the title but not the word clean in the title:

title:*water* NOT title:*clean*

“is” Searches

Shorthand search syntax has been developed to help simplify some common search queries.

Returns all active items:

is:active

Returns active and non-active items:

is:any

You can also use the NOT syntax to find all inactive items:

NOT is:active

Using parentheses

Parentheses can be used to group together search terms in a logical way.

For contacts this search below will find records with the first name of Rick and last name of O’Shea OR any user:

(firstName:Rick AND lastName:O'Shea) OR (isUser:true)

Testing if a field contains a value

You can test whether a field is set or unset using the following syntax.

To search for a field containing a value:

fieldname:any

to search for a field NOT containing a value

fieldname:none

You can also test whether a sub-record has been added for a register. For example in Recovery Case you can return items with no Private Assets:

privateAssets:none

You can also search for cases that have one or more private assets added.

privateAssets:any

Operators and Wildcards

The search also supports operators and wildcards. ​

Some useful examples for beginners are:

Operator	Usage	Examples
?	Substitutes a single character​	b?tter​ returns 'butter', 'bitter', 'batter' etc​
*	Substitutes multiple characters​	clean*​ returns 'clean', 'cleans', 'cleaning' etc​
~	Sounds like​	~smith​ returns 'smith', 'smyth' etc​
none	finds fields without data	title:none
any	finds fields with any data	title:any

Textual Data and Complex Searches

For searches involving names or textual data, consider the following:

1. Use quotes for multi-word values.

2. Use "Sounds Like" searches by placing the tilde (~) character before the search term

3. Use asterisks (*) for wildcard searches (e.g., searching for names starting with a specific letter).

4. Implement boolean logic for complex searches using brackets ( and ), as well as AND, OR and NOT keywords.

Examples using fields:

• firstName:"Cameron Smith" — will find a field containing a phrase

• firstName:~April — will find April and Apryl using a sounds-like search

• firstName:Sc* — finds all records beginning with Sc, such as Scott, Scot, Scod

• firstName:Scott or firstName:April — finds records with a first name of either Scott or April

• (firstName:Ashley lastName:G*) OR (firstName:Apryl lastName:D*) — looks for records Ashley G* and Apryl D*.

Soundex, phrase and boolean searches can also be performed on full text searches.

Multi-register queries

You can perform a multi-register query on registers with sub-items or related items, however to do so you must do so via a field that contains a key for the other register. Consult the CQL technical documentation for further information on which registers and fields are available.

The query a different register, nest the query in brackets following the colon prefix of the key field.

Examples using multi-register queries:

• relatedcase:(status:resolved) — will only find related cases with a status of resolved

• relatedcontacts:(id:123 status:inactive) — will find related contacts with an ID of 123 and a status of Inactive