OFFICIAL

Searching and filtering

Owned by Former user (Deleted)
Last updated: Jan 06, 2021 by Scott Davey
6 min read

Introduction

Crisisworks has a powerful search feature which can be used for advanced word/text search as well as a powerful 'query language' that supports sophisticated searching and filtering of its data, beyond what is supported by the standard field filters

At the top of each data grid filter form, there is a Search box (see right, for example) that accepts queries and returns matching results.

Below are some examples of queries using generic field names. Specific record types will likely have different field names, but the general format of the queries applies to all.

Documentation about the specific fields for each record type is available via the Search Dictionary link below each Search box.

Keyword and phrase matching

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"

Search for records with the word "clean" in the title:

title:clean

Search for the phrase "clean up" in the title field:

Search for an empty title field:

ID searches

Search for a record by its record ID or property ID using text search:

Record ID with the field name: 

Property ID with the field name:

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.

To search for content that contains one of the terms "red" or "apple":

Search for phrase "red apple" in the title field AND the phrase "quick fox" in the body field:

Search for word "red" and NOT "apple" in the title field.

A minus sign can be used instead of NOT:

Using parentheses

Parentheses can be used to group together search terms in a logical way. Search for either the phrase "red apple" in the title field AND the phrase "quick fox" in the body field, OR the word "fox" in the title field:

Searching by specific fields

Each field within Crisisworks is searchable, and fields behave based on their field type.

To learn about the list of fields available, click the “Search Dictionary” link under the search box to see the full list of fields along with examples of their usage.

Numeric fields

Fields containing numeric data, such as currency or progress fields, can be queried for exact or relative values.

Search for items with progress of 10:

Search for items with progress of 10 or more:

Search for items with cost of less than $1000:

Dates

Similarly, date fields can be searched for relative or exact values.

Search for items that were created since the start of 2019:

The keyword "now" can be used as a shorthand for the current date and time.

Search for items that were updated within the past week:

Or, using days as a date unit:

Search for items that are overdue:

Search for items that are due in the next week:

Date shortcuts:

  • M is for months

  • m is for minutes

  • y is for years

  • w is for weeks

  • d is for days

  • now is for the current date and time

Using shorthand searches

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

Status

Status is a numeric field with support for special values to make searching friendlier.

Search for all items with an active status based on numeric comparison (depending on item type this could, for example include all 'Requested', 'In Progress' and 'Assigned' items):

Or use the special value "active" to search for all items with an active status:

Search for all items with an inactive status (depending on item type, this could be 'Resolved' or 'Completed' items):

Search for a specific status keyword:

Or collection of statuses:

Assignment

You can search individual assignment fields by looking up their field in the Search Dictionary, or you can use the shorthand “assignment” term to search across all assignment fields.

Searching for items assigned to me (across any assignment field).

Searching for assignment to another user (by name)

Searching for items assigned to any user

Searching for items not assigned

Priority

Priority is another numeric field with support for friendlier searching.

Search for items matching multiple priorities:

Search for items with a priority of 2 or higher (greater than or equal to 2):

Tags

Search for records tagged 'visited' and 'blue':

Search for records tagged 'visited' and NOT 'blue':

Or using the minus sign:

Or the '#' character can be used as a shorthand:

The first form needs to be used when searching for tags containing spaces, and in this case, the tag name should be enclosed in quotes:

“is” searches

A special shorthand “is” helps with some specialised queries:

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:

To search for a field NOT containing a value:

For example to find records not containing a value in buildingType.value:

Searching by count of related records

 

From the Private asset or recovery task, to find where there are no linked cases:

From the Private asset or recovery task, to find where the linked cases are NOT active ie NOT closed or closed (duplicate):

Searching by count of attachments

You can search for items that have or do not have attachments by searching for the CountActive or CountTotal suffixes of the attachment field name (use the Search Dictionary link under the search field to discover the available fields).

Example: This searches for one or more attachments to the media field.

Using wildcards

You can use one or more wildcard characters in your search. For example, you could search for "red c*" to find "red cross" or "red car".

Wildcards can either replace a single character in your search, or multiple characters.

Single character

To replace a single character in your search, use a question mark (?) as a wildcard, For example, to search for 'butter', 'bitter', 'better', or 'batter'.

Multiple characters

To replace multiple characters in your search, use an asterisk (*) as a wildcard. For example, to search for 'clean' or 'cleaning':

Multiple wildcards

Use multiple wildcards in your search. The following query will match 'chick', 'coconut', or 'chickpea':

You can also combine wildcard characters in one search. For example, the search term below will return 'chick' but not 'chickpea':

Searching by geospatial regions

Geospatial operators

The CQL language supports two geospatial operations:

  • intersects:<area> — returns records that touch the polygon defined by <area>

  • within:<area> — returns records that are wholly within the polygon defined by <area>

You can combine any of the search options with AND, OR , NOT etc with geospatial region(s).

Searching items within a predefine search area

Search areas can be created and managed in the Library, as “Geospatial Features” with the search feature enabled. See Reference Library#AddingGeospatialFeatures for more details.

Searching items within another item

Additionally, any register item’s geometry can be used by simply specifying its numeric ID as the <area>.

This example shows searching one search area by its search reference (in this case, burnt-area-2020), and another by its direct ID.

Searching items using WKT

Additionally, geospatial search areas can be defined using the geospatial WKT standard, wrapped in ” quotes:

WKT stands for “Well Known Text”, and most GIS systems allow polygons to be copied as WKT.

The Cookbook

A cookbook of CQL recipes is available containing many advanced searches that may suit your needs.

https://datalink.atlassian.net/wiki/spaces/KB/pages/1301708921