This page has a series of useful CQL searches for conducting recovery operations in Crisisworks. Consult the Searching and filtering page to learn about general search usage.
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:0 |
The objective is to find active cases with active recovery tasks.
Case CQL:
is:active tasksCountActive:>0 |
The objective is to find cases to re-open that have been closed but still have unrecovered private assets.
Case CQL:
is:active privateAssetsCountActive:>0 |
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.
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”.
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:
is:inactive privateAssets:( status.id:>0 buildingCategory.value:dwelling ) |
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.
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:
is:inactive tags:iia-frv NOT tags:*sia* |
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.
The objective is to find cases where a Needs & Tasks record was created and assigned to a contractor, in this example, ”Windemere”.
Case CQL:
is:active tasks.assignment.name:windemere |
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:
is:active tags:windermere NOT tasks.assignment.name:windemere |
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
is:active tags:arborist* NOT tasks.category.label:arborist* |
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
(tree or arborist) is:active not tasks.category.label:arborist* |
tags:fence* NOT privateAssets.buildingType.label:Fencing |
fence* NOT privateAssets.buildingType.label:Fencing |
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.
is:active recoveryCaseCountTotal:0 |
This CQL recipe is included in the Data Cleaning counters
This CQL recipe is included in the Data Cleaning counters
This CQL finds open records without a buildingType attribute, which is typically a mistake.
Private Asset CQL:
is:active hasnt:buildingType.label |
grocon-cleanup-completed
tag on the caseSome 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:
is:active hasnt:buildingType.label NOT recoveryCase.tags:grocon-cleanup-completed |
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:
photos.name:*.pdf NOT recoveryCase.tags:grocon-cleanup-completed |
These private assets are related to only closed cases.
Private Asset CQL:
status:damaged recoveryCaseCountActive:0 |
These persons have been deleted from a case with the user pressing the red cross but remain active in the system.
Person CQL:
is:active recoveryCasesAppliedForCountTotal:>0 recoveryCasesRelatedToCountTotal:>0 |
These records can be safely closed, as the persons are unlinked from any case.
These records can be safely closed, as the persons are unlinked from any case.
These persons are active but are linked only to cases that have inactive statuses (e.g. closed, closed-duplicate).
Person CQL:
is:active recoveryCasesAppliedForCountActive:>0 recoveryCasesRelatedToCountActive:>0 |
This is an informational query. It’s completely valid for a single person to have multiple cases.
Person CQL:
is:active recoveryCasesAppliedForCountTotal:>1 |
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:
is:active recoveryCasesAppliedForCountActive:>1 |
Some inspections were conducted but the status incorrectly set to ‘inspection required’.
EHA CQL:
has:electricityOnsite status:inspection-required |
Tip: The CQL identifies these records by using the has
operator against electricityOnSite
, which tests for a value in a common field. This CQL does not mean the property has electricity; it means the field has a value.
Tip: The CQL identifies these records by using the has
operator against electricityOnSite
, which tests for a value in a common field. This CQL does not mean the property has electricity; it means the field has a value.
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.
hasnt:electricityOnsite status:inspection-required |