# Copy of CQL examples ## Overview {% content-ref url="/pages/Hqx2rS3kEW8IpmSxPLI7" %} [Searching and Filtering](/concepts-and-fundamentals/on-a-computer/searching-filtering-and-querying-registers.md) {% endcontent-ref %} CQL (Crisisworks Query Language) can be used to perform advanced searches. They allow you to use structured queries to search for content. The following search queries can be pasted into the Contains box on the relevant datagrid after you select the filter icon.

Example of CQL search to find all the 'Ricks' and all the 'O'Sheas'

{% hint style="warning" %} You MUST be in the correct register for these searches to work {% endhint %} ## Registers ### Contacts {% content-ref url="/pages/LA4Q1JYzBEpq4X9RdhIY" %} [Broken mention](broken://pages/LA4Q1JYzBEpq4X9RdhIY) {% endcontent-ref %} The following common CQL searches can be performed on the Contacts Register #### **First and Last Name searches** Find contacts with the first name of rick or Rick `firstName:rick` Find contacts with last name matching O’Shea or o’shea `lastname:O'Shea` Find contacts with first name Rick **and** last name O’Shea (as well as lowercase versions) `firstName:Rick lastName:O'Shea` Find contacts with first name of Rick and last name of O’Shea or any Crisisworks user. `(firstName:Rick lastName:O'Shea) OR (isUser:true)` Find contacts that have a first name starting with Ric and contacts with last that sounds like Smith. For example this could return a contact called Rick Smith or Ricky Smyth `firstName:Ric* lastName:~Smith` Perform a full-text search on all fields with the term Rick `"Rick"` #### **Media searches** Find contacts where an avatar file has been uploaded `avatar:any` Find contacts where the avatar file field is empty `avatar:none` Find contacts where a signature file has been uploaded `signature:any` Find contacts where the signature file field is empty `signature:none` ### Requests {% content-ref url="/pages/6qkLMNrCU6gx9Gj5hsi7" %} [Broken mention](broken://pages/6qkLMNrCU6gx9Gj5hsi7) {% endcontent-ref %} The following common CQL searches can be performed on the Request register. #### **Due Date searches** Find items with due date of 1st March 2023 `due:2023-05-01` Find items with an exact due date and time `due: 2023-05-01 01:01:01` Find items that were due yesterday. `due:-1ddue:now-1d` Find items due between the date range provided. For this example it will find all items due between 1st May and 3rd May 2024 `due:>2024-05-01 due:<2024-05-03` Find items due between between date range and time provided. For this example it will find all items due on 1st May 2024 between 10am and 11am. `due:>"2024-05-01 10:00:00" due:<"2024-05-01 11:00:00` Find items greater than 7 days ago and less than 6 days ago. `due:>-7d due:<-6d` Find items where no due date is set `due:none` Find items where the due date is set `due:any` #### **Requestor searches** Find items where the requestor has a firstName of Rick `requestor:(firstName:Rick)` Find items where requestor has the firstName of Rick and the lastName of Jones (items must have both of these values set to return a match) `requestor:(firstName:Rick lastName Jones)` Find items where the requestor has either the firstName of Rick or the lastName of Jones. For example Rick Smith will be a match and Bob Jones will also be a match. `requestor:(firstName:Rick OR lastName:Jones)` Find items where no requestor has been set `requestor:none` Find items that have a requestor set `requestor:any` #### **Status searches** Find all items in the status of Requested. `status:requested` Find items in both the Assigned and In Progress statuses. You can use either the status key or label. `status:[requested,in_progress]status:["Requested","In Progress"]` #### **Region searches** Find items where the region is not set (ie empty) `regionId:none` Find items where the region is either not set or set to the region “default”. `regionId:[none,default` #### **Assignment searches** Find all items assigned to the MEMO Deputy position. `assigned:"MEMO Deputy"` #### **Cost Recovery Method searches** Find items where “Cost Recovery Method” is set to None `payer:noauth` Find items where “Cost Recovery Method” is set to “MEMO - authorised council to pay” `payer:council` Find items where “Cost Recovery Method” is set to “MERC - authorised as an incident cost” `payer:incident` Find items where “Cost Recovery Method” is set to “Requestor - authorised and has agreed to pay” `payer:requestor` Find items where “Cost Recovery Method” is set to “Other” `payer:other` #### **Media searches** Find items that have no Attachments uploaded `media:none` Find items that have Attachments uploaded to the item `media:any` ### Information {% content-ref url="/pages/nQaLaful7q945ISCYKvS" %} [Broken mention](broken://pages/nQaLaful7q945ISCYKvS) {% endcontent-ref %} #### **Promote on Dashboard Searches** Find all items that have the “Promote on dashboard” checkbox ticked. `promoteOnDashboard:truepromoteOnDashboard:1promoteOnDashboard:yes` Find all items where the “Promote on dashboard” checkbox is unticked. `promoteOnDashboard:0promoteOnDashboard:falsepromoteOnDashboard:no` #### **Date searches** Find items where the “Expires at” date is before the current date ie Overdue `validUntil:2025-01-01` Find items where the Schedule A Review checkbox is ticked `remind:Yes` Find items where the Next Review date is after 1st Jan 2025 `remindAt:>2025-01-01` Find items where the Next Review date is after 1st Jan 2025 `remindAt:none` Find items where the Next Review date is set `remindAt:any` #### **Media searches** Find items that have no Attachments uploaded `media:none` Find items that have Attachments uploaded to the item `media:any` ### **Recovery Cases** {% content-ref url="/pages/Sfj9Pht3MY5OyzcTaJAs" %} [Broken mention](broken://pages/Sfj9Pht3MY5OyzcTaJAs) {% endcontent-ref %} #### **Affected Person(s) Searches** Find cases where the Affected Person is unknown `isApplicantKnown:unknown` Find cases where the Affected Person is known `isApplicantKnown:known` Find cases where the Primary Contact has consented to share full details `sharingLevel:full` Find cases where the Primary Contact have not consented to share their details `sharingLevel:none` Find cases where the Primary Contact has not been asked about consent to share their details. `sharingLevel:notYetAsked` Find cases where the Primary Contact or group are of Aboriginal or Torres Strait Islander origin. `ATSIOrigin:yes` Below are other ATSI Origin searches where the values are No, Prefer Not to Say or Not Yet Asked. `ATSIOrigin:noATSIOrigin:preferNotToSaypreferNotToSay:notYetAsked` Find cases where the Primary Contact’s Contact Type is set to Individual. `applicant:(contactType:individual)` Find cases where the Primary Contact’s Contact Type is set to Organisation. `applicant:(contactType:organisation)` Find cases where applicants have the First Name set to Kelly. Note this will be an exact search for the word “Kelly”. If a middle name is set in this field it will not return a result. `applicant:(givenNames:Kelly)` Alternatively you can use a wildcard search as per the example below which will find results such as Kelly, Kelly-Anne or Kelly Rose. `applicant:(givenNames:Kelly*)` Find cases where applicants have the Family Name set to Smith `applicant:(familyName:Smith)` Find cases where the applicant has the Family Name set to Smith and the Given Names that start with the word Kelly `applicant:(givenNames:Kelly* AND familyName:Smith)` Find cases where the applicant has the Family Name set to either Smith or Brown `applicant:(familyName:Smith OR familyName:Brown)` Find cases where the applicant has the email address “`matt.brown@gmail.com`" `applicant:(email:matt.brown@gmail.com)` If the entire email address is not known, a wildcard search can be useful. This will find cases where the applicant’s email address has the word brown anywhere in the email address. `applicant:(email:*brown*)` **Property Use Searches** Find cases where the Property Use has the Residential checkbox ticked `propertyUseResidential:true` Find cases that have either the Residential - Primary or the Residential - Secondary checkboxes `propertyUseResidentialPrimary:true OR propertyUseResidentialSecondary:true` Find cases that do not have the Residential - Primary checkbox ticked. `propertyUseResidentialPrimary:false` Below is a list of all Property Use search terms that can be used to find one of more property uses: `propertyUseResidential propertyUseResidentialPrimary propertyUseResidentialSecondary propertyUseResidentialUnclassified propertyUseCommercial propertyUseAccommodation propertyUseFarm propertyUseCommunity propertyUseGovernment propertyUseVacant propertyUseInfrastructure propertyUseOther` **Private Asset searches** Find cases that have one or more Private Assets added `privateAssets:any` Find cases that have no Private Assets added `privateAssets:none` Find cases that have a Private Asset added with the category set to Dwelling `privateAssets:(buildingCategory:dwelling)` Find cases that have a Private Asset added with the category set to either Dwelling or Equipment `privateAssets:(buildingCategory:dwelling OR buildingCategory:equipment)` Find cases that have a Private Asset added with the Asset Type set to Dairy `privateAssets:(buildingType:dairy)` Find cases that have a Private Asset added with the Damage Level set to Totally Destroyed `privateAssets:(buildingDamageLabel:Totally Destroyed)` Find cases that have a Private Asset with the Category of Dwelling and has the Damage Level set to either “Totally Destroyed” or “Damaged- Habitable / Functioning”. Note - this query is using the label of the Damage Level field so the label must be entered exactly as it is displayed. `privateAssets:(buildingCategory:dwelling AND buildingDamageLabel:Damaged - Habitable / Functioning OR buildingDamageLabel:Totally Destroyed)` Alternatively you can use a wildcard to search for the word Damaged within the Damage Level label: `privateAssets:(buildingCategory:dwelling AND buildingDamageLabel:Damaged* OR buildingDamageLabel:Totally*)` This will return the same result as the previous search without the need for entering the entire label. This will return all cases that have Private Assets with the Category of Dwelling and has the Damage Level starting with the either the words “Damaged” or “Totally” Alternatively, you can use the numerical keys of the Damage Level field (which is called buildingDamage in the back end). `privateAssets:(buildingCategory:dwelling) AND privateAssets:(buildingType:dwelling/caravan)` #### Reference Lookup Values | Category | Label | Value | | ------------------------------ | --------------------------------- | ------------------------------ | | Building Category | Dwelling | dwelling | | Building Category | Facility | facility | | Building Category | Structure | structure | | Building Category | Equipment | equipment | | Building Category | Vehicle | vehicle | | Building Category | Other | other | | Building Type | Bikes | vehicle/bike | | Building Type | Boats | vehicle/boat | | Building Type | Business Economic Impact | other/business-economic-impact | | Building Type | Cars | vehicle/car | | Building Type | Caravans | dwellling/caravan | | Building Type | Community Centre(s) | facility/community | | Building Type | Dairy | equipment/dairy | | Building Type | Other Dwelling | dwelling/other | | Building Type | Other Equipment | equipment/other | | Building Type | Other Facility | facility/other | | Building Type | Fencing | structure/fencing | | Building Type | Flats/Units | dwelling/flat | | Building Type | Government | facility/gov | | Building Type | Holiday House | dwelling/holiday-house | | Building Type | Holiday Rental | dwelling/holiday-rental | | Building Type | A Primary Place of Residence | dwelling/house | | Building Type | Irrigation Pumps | equipment/irrigation | | Building Type | Machinery | equipment/machinery | | Building Type | Other | other/other | | Building Type | Shed/s | structure/shed | | Building Type | Shops/Factories | facility/shop | | Building Type | Other Structure | structure/other | | Building Type | Tank | structure/tank | | Building Type | Trailers | vehicle/trailers | | Building Type | Other Vehicle | vehicle/other | | Building Damage (Damage Level) | Damaged - Habitable / Functioning | 1 | | Building Damage (Damage Level) | Inaccessible / Non-Functioning | 2 | | Building Damage (Damage Level) | Partially Destroyed | 3 | | Building Damage (Damage Level) | Totally Destroyed | 4 | | Building Damage (Damage Level) | Unknown - Requires Assessment | 5 | | Building Damage (Damage Level) | Not Damaged | 6 | This will allow you to perform the following search using these numerical keys This will return the same result as the previous search where the Private Asset Category is set to Dwelling and has the Damage Level set to either “Damaged- Habitable / Functioning” or “Totally Destroyed”.\ \ `privateAssets:(buildingCategory:dwelling AND buildingDamage:1 OR buildingDamage:4)` Will find private assets with the category of Dwelling where the Is this a Primary Place of Residence checkbox is ticked `privateAssets:(buildingCategory:dwelling AND primaryPlaceOfResidence:true)` ### **Needs and Tasks** {% content-ref url="/pages/cbvxkWADYBnySUMuIgVf" %} [Broken mention](broken://pages/cbvxkWADYBnySUMuIgVf) {% endcontent-ref %} Find cases that have a Needs & Task record with the category set to Accommodation. `tasks:(category:accommodation)` Find cases that have a Needs & Task record with the category set to either Accommodation or Counselling `tasks:(category:accommodation OR category:counselling)` You can also use the label of the category when searching. `tasks:(categoryLabel:Accommodation Temporary)` You can use wildcard searches when using the label if you’re unsure of the entire label `tasks:(categoryLabel:Livestock*)` This will return cases that have a Needs & Task record with the category that starts with Livestock. This will return results where the category is set to Livestock Burial, Livestock Feed and Livestock Transport. ### General Case Queries #### Active cases with no damaged assets and no active tasks `is:active privateAssets:none tasks:none` Active cases with active recovery tasks `is:active tasks:any` Date searches Find cases where the Referral Date is after 1st Jan 2025 `referralDate:>2025-01-01` Find cases where the Referral Date is not set `referralDate:none` Find cases where the Referral Date is set `referralDate:any` **Media searches** Find cases where the field “General Photo(s) of property” is empty `photos:none` “General Photo(s) of property” field `photos:any` Find cases where the media field “Case Documents” is empty `caseDocuments:none` Find cases that have photos uploaded into the “Case Documents” field `caseDocuments:any` #### 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: `NOT is:active tags:iia-frv NOT tags:*sia*` #### Cases with tasks assigned The objective is to find active cases where a Needs & Tasks record was created and assigned the MRM Deputy position `is:active tasks:(assigned:"MRM Deputy")` #### 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: `is:active tags:windermere NOT tasks:(assigned:"Windemere")` #### 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 `is:active tags:arborist* NOT tasks:(categoryLabel:Arborist*)` #### Find the use of the words `tree` that doesn’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 is:active not tasks:(categoryLabel:Arborist*)` #### Find tagged cases starting with "Fence" that don’t have an affected private asset of "Fence" `tags:fence* NOT privateAssets:(buildingTypeLabel:Fencing)` ### Private Assets {% content-ref url="/pages/eIC3EKq3W5hWtAu59Baw" %} [Broken mention](broken://pages/eIC3EKq3W5hWtAu59Baw) {% endcontent-ref %} #### Private assets without a building type This CQL finds open records without a buildingType attribute, which is typically a mistake. Private Asset CQL: `is:active buildingType:none` #### Private assets with no related Case This CQL finds any private assets with an 'active' status that have been removed from a Case. `is:active not relatedcase:(status:any)` #### Private assets with an attachment uploaded without the tag `import` This CQL finds open records with a PDF uploaded that do not have the tag `import` `is:active photos:any NOT tags:import` Private assets with category of dwelling and building Type of Caravan `buildingCategory:dwelling AND buildingType:dwelling/caravan` ### Environmental Health Assessments {% content-ref url="/pages/3f5gt65zzLPgcI3VkrJq" %} [Broken mention](broken://pages/3f5gt65zzLPgcI3VkrJq) {% endcontent-ref %} #### EHAs in ‘inspection required’ even though the inspection was conducted Some inspections were conducted but the status incorrectly set to ‘inspection required’. We can achieve this by checking if one of the inspection fields has been set. EHA CQL: `electricityOnsite:any status:inspection-required` #### EHAs in ‘inspection required’ and no inspection was conducted `electricityOnsite:none status:inspection-required` ### Infrastructure Assessments {% content-ref url="/pages/S4Vi1g255T0pGZOB48B9" %} [Broken mention](broken://pages/S4Vi1g255T0pGZOB48B9) {% endcontent-ref %} Find Infrastructure Assessments with the type of either Council Facility or Council Property with the Damage Level of either “Damaged- Habitable / Functioning” or “Unknown - Requires Assessment” `is:active AND (infrastructureType:property OR infrastructureType:facility) AND (damageLevel:1 OR damageLevel:5)` Below are the Damage Level numerical keys: `Damaged - Habitable / Functioning = 1 Inaccessible / Non-Functioning = 2 Partially Destroyed = 3 Totally Destroyed = 4 Unknown - Requires Assessment = 5 Not Damaged = 6` This will allow you to perform searches on the damageLevel field using these numerical keys --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.cw.crisisworks.com/concepts-and-fundamentals/on-a-computer/searching-filtering-and-querying-registers/cql-examples-1.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.