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.

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:

Active cases with active recovery tasks

The objective is to find active cases with active recovery tasks.

Case CQL:

Cases 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:

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.

Closed cases with open dwellings

The objective is to find closed cases with open private assets that are dwellings.

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.

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:

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.