Case List Explorer

Owned by Kai Cowger
Last updated: Jun 25, 2024 by Gillian Javetski
6 min read

The Case List Explorer is a powerful feature for CommCare HQ which enables users to inspect their case data by building custom-filtered views of their cases. The Case List Explorer allows you to find and validate test data, identify cases that fit a very specific criteria, and find data anomalies and outliers. 

https://www.youtube.com/watch?v=dStNRojjno0

Watch this short video to learn more about the Case List Explorer and how to use it!

Table of Contents

 

Case List Explorer Overview

Seeing Your Case List

To begin, go to your CommCareHQ project space, select the Reports tab at the top of page, and locate the "Inspect Data" header on the left-hand side.

This tool provides a list of all of the cases (clients) ever opened during this project.  A specific user's or group's cases can be displayed typing the name of one or more users or groups into the "Groups or Users" box in the filter. You can also type in "All Data" to view all cases regardless of case ownership.

  • Name: the name of the case (client).  By clicking on the link, more details of the patient can be seen.

  • User: this is the name of the mobile worker that registered this case. 

  • Created date: the date when the mobile worker registered this case.

  • Modified date: the date when the mobile worker last filled out a form regarding this case.

  • Status: displays if the case is currently "open" or "closed".  A case is "closed" if the mobile worker-case interaction has been completed (e.g. a pregnant women has given birth and no longer requires follow-up visits).

To search the case list, you can use the following format in the "Search" box:

  • use AND and OR to join search terms.  Example:  "ian OR seema"

  • use "is:" before open or closed, and "modified:" for the modified date and "opened" for the created date.  Put a star after the date if you do not have the exact timestamp.  Example: "ian AND is:open AND modified:2011-10-01*"

  • use TO in between date ranges.  Must be capitalized.  Example: "ian AND is:open AND opened:[2011-10-01 TO 2011-12-01]"

Purpose: This tool allows the data manager to get an in-depth view of the current cases (clients) that are part of this program.  This tool is helpful when the case manager wants to inspect a certain mobile worker's client list or even a certain client.

Leveraging the Case List Explorer

To access the Case List Explorer:

  1. Go to Reports > Inspect Data > Case List Explorer.

Search Expressions

The search box enables you to filter your cases based on the values of their case properties. The syntax for searching case properties can be found on https://dimagi.atlassian.net/wiki/x/UBTKfw .

Selecting Columns

Click the Edit Columns button to expand the columns section.



Label: This is the label which will be used in the Case List Explorer report as the header. By default the label will be pre-populated with the selected case property name.

Upon clicking on the "Property" text box, a dropdown of suggested case properties will appear. This will be filtered based on the selected case type filter.

The default label can be overridden by the user as shown below: 

The following columns are populated by default:

  • Case Type

  • Case Name

  • Last Modified Date

Additionally, you will find the "View Case" links are always in the first column. These are links that take you to the corresponding case in the row.

Special Properties and Columns

Certain meta-data properties are available for searching and including as columns. Click to expand below to see these:

Property

Description

Property

Description

@case_id

The CommCare internal ID for this case. ID is unique across all of CommCare.

case_name

The name of the case

@case_type

The case type of the case. Note, this is available as its own selectable filter as well.

closed_by_user_id

The CommCare internal ID of the user that closed this case

closed_by_username

The username of the user that closed this case.

date_opened

The date and time that the form that opened the case was completed on the mobile device (timeEnd). Format is: 'YYYY-MM-DD HH:MM:SS' (UTC)

external_id

The external_id of a case. Used for integrations with other systems.

last_modified

The date and time that the most recent form against this case was completed on the mobile device (timeEnd). Format is: 'YYYY-MM-DD HH:MM:SS' (UTC)

last_modified_by_username

The username of the user that most recently updated this case.

opened_by_user_id

The CommCare internal ID of the user that opened this case

opened_by_username

The username of the user that created this case.

@owner_id

The CommCare internal ID for the owner of the case. This could be a user, group, or location.

owner_name

The human-readable display name of the owner of the case. This could be a username, a group name or a location name.

server_last_modified_date

The date and time that the most recent form against this case was received by CommCareHQ (received_on). Format is: 'YYYY-MM-DD HH:MM:SS' (UTC)

@status

Whether the case is open or not. Values are open or closed.

Additional Filters

  • Case Owners: Filters the results by the owner of the case.

  • Case Type: Only return certain case type(s). 

  • Opened / Closed: Only return open or closed cases. 

Exporting to Excel

The Export to Excel option will send an email to the address defined in your account settings with a link to an Excel file with the data populated from the search. The link will only be active for 24 hours. 

Saving Searches

You are able to save reports made via the Case List Explorer by clicking on the "Save" button. 

This will save your reports into the "My Saved Reports" page as shown below. You can then access any of these reports at a later date.

Favorited Reports

You are able to quickly pull up previously saved reports using the "Favorites" dropdown. This will automatically generate the saved report you select, complete with the search criteria and the columns (including labels) auto-populated.

XPath Date/Timezone Conversion

Previously, project-specific properties were stored as standalone time or dates, meaning that no conversion to UTC was required while generating a query for this, after an update was deployed to address a longstanding issue on the Case List Explorer page, that which disallowed a user's input into the query search box from being translated back to UTC from the domains timezone, which had caused cases outside the specified date range to be returned, or cases inside the range to be hidden.

To rectify this, a new update has introduced a new feature that that allows exact matches of special case properties to now work. For example, "last_modified = '2023-06-03'" will return cases that were modified on project timezone's date "2023-06-03". Originally, exact matches of special case properties do not work because in Elastic Search, special case properties are stored as timestamps, meaning "'2023-06-03" won't be an exact match. 

Additionally, this change adds a check to apply the timezone conversion to only datetime special case properties, since they are stored in Elastic Search as UTC timestamps.
These expressions can be used in conjunction with the Case Query Language, as a Search Expression, as outlined above.