DET Tips and Tricks

Export repeat groups

• Set the "Data Source" to be the path of your repeat group, e.g., form.form.question_group.repeat_group[*]

• Reference the questions directly i.e. instead of "form.question_goup.repeat_group.question1" you should use "question1"

• Reference root properties of the form with: $

  • e.g. $.form.@name, $.id

• Reference the id of the repeat group with: id

Export a case or form in JSON representation 

• Set the "Data Source" to "form" or "case", depending on what is supposed to be extracted. 

• Set the "Source Field" to "[*]"

Export one row for each case that was altered by a form

• Use "form.form..case" as the data source.

• Reference case properties directly, e.g., @case_id

• Reference root properties of the form with '$' e.g. $.domain, $.form.@name

Determine if a case was created, update, or closed by a form

• Use the technique above to export one row for each case in the form

• Reference the 'create', 'update', and 'close' fields in the case and map them via the 'bool' mapping

This will produce a 'True' or 'False' value in the output.

Determine if a form was submitted from a mobile phone or via some other means (API, bulk upload, Cloudcare, etc.)

• Reference the 'is_phone_submission' property of the form

Convert output values based on a mapping

You may want to convert an output value to something else by doing a 'lookup' in some instances. For example:

To do this, we need to add a 'Mappings' sheet to our configuration file, which contains the lookups for the fields we want to convert.

Here is our configuration sheet:

Open

And here we've added a new sheet called "Mappings", which contains 3 columns:

• Mapping Name - this must match the name of a "Field" in the main configuration sheet

• Source - this is the value that you want to convert from

• Destination - this is the value that you want to convert to

Here is the Mapping sheet from our example:

Open

Exporting the form_link

form_link currently isn't available to be exported as a source field. In order to get the form_link, you can write the following expression in the "Map Via" Column: template(https://www.commcarehq.org/a/{}/reports/form_data/{}/, $.domain, $.id) (see screenshot below)

Export the Parent Case's ID

When exporting cases, if you need the parent's case_id or case_type, use the following source field:

Export the Host Case's ID

When exporting cases, if you need the host's case_id or case_type, use the following source field: