CQL Search Cookbook
Overview
This page has a series of useful CQL searches for conducting recovery operations in Crisisworks. Consult the https://datalink.atlassian.net/wiki/spaces/KB/pages/468746247 page to learn about general search usage.
- 1 Overview
- 2 Cases
- 2.1 Active cases with no damaged assets and no active tasks
- 2.2 Active cases with active recovery tasks
- 2.3 Cases with open private assets
- 2.4 Closed cases with open dwellings
- 2.5 IIA cases without SIA inspections
- 2.6 Cases with tasks assigned to a Contractor
- 2.7 Reconciling cases with windemere tag against assignments to Windemere.
- 2.8 Records Tagged with arborist that don't have a recovery task for “arborist”
- 2.9 Find the use of the words tree or arborist that don't have a recovery task for “arborist”
- 2.10 Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence"
- 2.11 Find cases with any word starting with "Fence" that don’t have an affected private asset of "Fence"
- 3 Private Assets
- 4 Persons
- 5 EHA
Cases
Active cases with no damaged assets and no active tasks
The objective is to find active cases that has no damage and no active tasks, to assess whether the status of the case should transition to “Closed”.
Case CQL:
is:active
tasksCountActive:0
privateAssetsCountActive:0Active cases with active recovery tasks
The objective is to find active cases with active recovery tasks.
Case CQL:
is:active
tasksCountActive:>0Cases with open private assets
The objective is to find cases to re-open that have been closed but still have unrecovered private assets.
Case CQL:
is:active
privateAssetsCountActive:>0Advanced: you could alternatively search statuses using status.id:-2 (“closed”) rather than the more general is:inactive in order to exclude “closed duplicates” cases.
Closed cases with open dwellings
The objective is to find closed cases with open private assets that are dwellings.
Because dwellings are used for EMV reporting, these cases should remain open until recovered, even if no further assistance is required by council. It is recommended these are set to the “Resolved” status instead of “Closed”.
Case CQL:
Advanced: you could alternatively search statuses using status.id:-2 (“closed”) rather than the more general is:inactive in order to exclude “closed duplicates” cases.
IIA cases without SIA inspections
The objective is to find cases that came from the FRV import process that have been incorrectly closed without having an SIA follow-up. It assumes the council is using one or more tags containing sia to track cases having SIA data or requirements to collect data.
Case CQL:
Advanced: you could alternatively search statuses using status.id:-2 (“closed”) rather than the more general is:inactive in order to exclude “closed duplicates” cases.
Cases with tasks assigned to a Contractor
The objective is to find cases where a Needs & Tasks record was created and assigned to a contractor, in this example, ”Windemere”.
Case CQL:
Reconciling cases with windemere tag against assignments to Windemere.
Some councils use tags at the case level to identify records they want to assign before assignment actually occurs. They then assign the contractor via Needs & Tasks.
To extend the above recipe, this reconciles cases against the windemere tag as follows: the objective is to find records tagged with windemere that have NOT been formally referred to Windemere using a Needs & Tasks record.
Case CQL:
Records Tagged with arborist that don't have a recovery task for “arborist”
We want to find all active records that have a tag starting with "arborist" that don't have a task with a label starting with "arborist" so we can bulk create a new task, note once done remove these tags so you are only working with the needs/tasks for the recovery cases
Find the use of the words tree or arborist that don't have a recovery task for “arborist”
look for words in the case anywhere where "tree" or "arborist" appear where there is no task created yet, so you can bulk add a task, you may want to review each record to be sure
Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence"
Find cases with any word starting with "Fence" that don’t have an affected private asset of "Fence"
Private Assets
Active private assets without a case
Private assets should not generally be active without a case. These are often referred to as “orphaned records” and should be closed or merged with other records.
This occurs when a user clicks the red cross against the private asset from with a case, and is a normal function of Crisisworks.
This CQL recipe is included in the Data Cleaning counters
Private assets without a building type
This CQL finds open records without a buildingType attribute, which is typically a mistake.
Private Asset CQL:
Private assets with a PDF but without a grocon-cleanup-completed tag on the case
Some councils engage contractors to conduct cleanups and provide post-cleanup documentation, which is then uploaded into Crisisworks private assets. Some councils have adopted a case tagging strategy to manage their cases post-cleanup (e.g. applying the grocon-cleanup-completed tag to the case after uploading the PDF into the private asset). This cross-check discovers cases that have a cleanup letter but do not have the tag.
Option 1: look at the telltale sign of a missing buildingType, so may not always be accurate.
Private Asset CQL:
Option 2: Another way to discover private assets having the Grocon letters is via a PDF being attached to the photos field.
Private Asset CQL:
Damaged private assets connected to closed cases
These private assets are related to only closed cases.
Private Asset CQL:
Persons
Active persons that are unrelated to any case
These persons have been deleted from a case with the user pressing the red cross but remain active in the system.
Person CQL:
These records can be safely closed, as the persons are unlinked from any case.
Active persons that are unrelated to any active case
These persons are active but are linked only to cases that have inactive statuses (e.g. closed, closed-duplicate).
Person CQL:
Persons that have more than one recovery case
This is an informational query. It’s completely valid for a single person to have multiple cases.
Person CQL:
Persons that have more than one active recovery case
This is an informational query. It’s completely valid for a single person to have multiple cases. This differs from the previous because it is looking only at active cases.
Person CQL:
EHA
EHAs in ‘inspection required’ even though the inspection was conducted
Some inspections were conducted but the status incorrectly set to ‘inspection required’.
EHA CQL:
EHAs in ‘inspection required’ and no inspection was conducted
Sometimes inspection records are created but no inspection carried out. This is usually due to the fact the inspection record was a duplicate and another EHA exists for that case.