{"type":"doc","content":[{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"excerpt","parameters":{"macroParams":{},"macroMetadata":{"macroId":{"value":"518fac6a8b65040f6387c6f7362c830cbfe76a61cf907d9fe4cc81db68cb6ecd"},"schemaVersion":{"value":"1"},"title":"Excerpt"}},"localId":"310cb335-e158-4120-8a6c-0f3146df3005"},"content":[{"type":"paragraph","content":[{"text":"The CommCare Data Export Tool (DET) is a command-line interpreter tool that you can use to export data from CommCare to a database. DET is an ideal solution for large datasets which can be incrementally refreshed and connected to BI tools like Power BI for visualization needs. ","type":"text"}]}]},{"type":"embedCard","attrs":{"layout":"center","originalHeight":480.0,"width":100.0,"url":"https://www.loom.com/share/c0483859d68a4eb184059142489292fd","originalWidth":853.34}},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"multiexcerpt-include-macro","parameters":{"macroParams":{"macro_uuid":{"value":"77a79df7-a5d1-4f8f-828d-6c8d8a40e9b2"},"name":{"value":"Software Plan - Pro"},"templateData":{"value":"eJyLjgUAARUAuQ=="},"page":{"value":"CommCare Help Site Design Guidance"},"addpanel":{"value":"false"}},"macroMetadata":{"macroId":{"value":"f345059a-caac-41b4-a970-0568905b5033"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://raw.githubusercontent.com/fuegokit/appfire-design-systems-brand-svg/main/single-color-brand-icons/af-logo-multi-excerpt-24.svg"}}],"title":"MultiExcerpt Include"}},"localId":"441a6727-0a0a-4dc5-a225-3c9d8c1fe2fd"}}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"multiexcerpt-include-macro","parameters":{"macroParams":{"macro_uuid":{"value":"846f10d6-3459-462c-8ce9-b12bb0b90265"},"name":{"value":"Technical Documentation"},"templateData":{"value":"eJyLjgUAARUAuQ=="},"page":{"value":"CommCare Help Site Design Guidance"},"addpanel":{"value":"false"}},"macroMetadata":{"macroId":{"value":"2ae60e0e-6dd5-48d8-9c62-9b1eb9e80784"},"schemaVersion":{"value":"1"},"placeholder":[{"type":"icon","data":{"url":"https://raw.githubusercontent.com/fuegokit/appfire-design-systems-brand-svg/main/single-color-brand-icons/af-logo-multi-excerpt-24.svg"}}],"title":"MultiExcerpt Include"}},"localId":"af7c85da-3def-4486-aaaa-f4811f54631c"}}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"minLevel":{"value":"1"},"maxLevel":{"value":"2"},"outline":{"value":"false"},"style":{"value":"none"},"type":{"value":"list"},"printable":{"value":"false"}},"macroMetadata":{"macroId":{"value":"df1f67cf-d28f-41c1-8c7b-b06f0449e845"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"e14a5cad-b4fe-4e5b-8e6c-140e9779f1b0"}},{"type":"heading","attrs":{"level":1},"content":[{"text":"About the DET","type":"text"}]},{"type":"paragraph","content":[{"text":"The Data Export Tool does not change your data in CommCare. It simply exports the data into a raw format that you can use for data insights and analytics using the ","type":"text"},{"text":"CommCare API","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/CommCare+HQ+APIs"}}]},{"text":" to export the data. The configuration for what data is exported and how it is formatted are provided to the tool via an Excel configuration file. The export can be in the format of a file (Excel, CSV, or JSON) or to a specified database. You can configure the DET to pull data on a schedule to automatically keep your data up to date or run it as a one-time data export. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Using the DET requires knowledge of both Python and SQL, and for this reason, is an advanced feature. ","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"Important note about PHI: ","type":"text","marks":[{"type":"strong"}]},{"text":"Please note that the commcare-export tool offers no security features for dealing with Protected health information (PHI) directly. If you intend to export PHI data from CommCare, please ensure the appropriate measures are in place to protect your data.","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"DET Example Use Case","type":"text"}]},{"type":"paragraph","content":[{"text":"Haik is a data analyst for a child immunization NGO specializing in tracking polio vaccinations in developing countries using CommCare. The increase of vaccination supplies and the training of competent healthcare workers to administer the vaccinations have significantly improved the child immunization program. More children are getting the vaccinations that they need. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The program's success is leading to a considerable increase in the data collected. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Haik needs to export data regularly to analyze and visualize it for the weekly reports. Manually exporting the data from CommCare HQ is not sustainable. Haik needs a way to export data in bulk in an automated manner. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The CommCare Data Export Tool will help Haik. He can use the DET to pull data from one or more domains and combine data into one large data file, or database, for analysis. Below is an example dashboard that Haik may make with DET. ","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":531,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":586,"alt":"Screenshot 2024-03-04 at 9.53.10 PM.png","id":"8a2ff5e5-642d-4c4a-9080-196f2a4efca1","collection":"contentId-2143955952","type":"file","height":305}},{"type":"caption","content":[{"text":"Example dashboard configured with CommCare DET","type":"text"}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Technical Requirements","type":"text"}]},{"type":"paragraph","content":[{"text":"Using the DET requires knowledge of both Python and SQL, and for this reason, is an advanced feature. You a can learn more below about the requirements for using both of these tools. ","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Python","type":"text"}]},{"type":"paragraph","content":[{"text":"More information about setting up Python for the DET. ","type":"text"}]},{"type":"embedCard","attrs":{"layout":"center","originalHeight":480.0,"width":100.0,"url":"https://www.loom.com/share/a36220b02e6248b38caca44642683cbf?sid=ae9b4a43-f58c-403c-b84f-1798ec508a07","originalWidth":768.0}},{"type":"paragraph","content":[{"text":"To start, the DET requires you to download ","type":"text"},{"text":"Python","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.python.org/"}}]},{"text":" to your computer. You will use a command-line interpreter tool to run the commcare-export script from the Python script library. You need Python installed to use the commcare-export script. The script library is installed with Python. Installing the script allows the Data Export Tool to export your data from CommCare HQ.Below is a video walking through this process.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"The Excel Query File","type":"text"}]},{"type":"paragraph","content":[{"text":"Read more about about setting up the Query file for the DET.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Overview","type":"text"}]},{"type":"paragraph","content":[{"text":"A Query file is an Excel file (.xlsx) saved to the Python Scripts folder. ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The Query file specifies which data objects should be requested from the API and which values should be stored when the API payload is received. All other values that are not specified are discarded and not stored in the results set.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"One query file can contain many tabs and can combine case and form data queries.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"It’s common to have a single query file with one tab for each case or form that you want to export.","type":"text"}]},{"type":"paragraph","content":[{"text":"A template query file is provided here for cases and forms. ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"demo_query_file_case.xlsx","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/download/attachments/2143955458/demo_query_file_case.xlsx?version=1&modificationDate=1617704131941&cacheVersion=1&api=v2"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"demo_query_file_form.xlsx","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/download/attachments/2143955458/demo_query_file_form.xlsx?version=1&modificationDate=1617792057260&cacheVersion=1&api=v2"}}]}]}]}]},{"type":"paragraph","content":[{"text":"You must provide the path to where the query file is saved in the commcare-export command.","type":"text"}]},{"type":"paragraph","content":[{"text":"E.g.,commcare-export --query /home/username/Documents/my-query-file.xlsx","type":"text"}]},{"type":"paragraph","content":[{"text":"Below is more information about this process.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Quick Start","type":"text"}]},{"type":"paragraph","content":[{"text":"The easiest way to get started with the Data Export Tool is to have CommCare generate a query file for your data models based on an export. See “Creating an Excel Query File in HQ” Below for details.","type":"text"}]},{"type":"paragraph","content":[{"text":"If you prefer to start from scratch, We have provided a template query file ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/2143955458"}}]},{"text":" which can be used to build your query files.","type":"text"}]},{"type":"paragraph","content":[{"text":"See below for more details on the terminology and fields:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]},{"text":": (Required) ","type":"text"},{"type":"hardBreak"},{"text":"This determines whether you are exporting ","type":"text"},{"text":"case","type":"text","marks":[{"type":"strong"}]},{"text":", ","type":"text"},{"text":"form","type":"text","marks":[{"type":"strong"}]},{"text":", or ","type":"text"},{"text":"messaging-event ","type":"text","marks":[{"type":"strong"}]},{"text":"data.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]},{"text":": (Optional) ","type":"text"},{"type":"hardBreak"},{"text":"When you include the “Filter Name” and “Filter Value” columns, you can limit the set of forms or cases returned when you run the DET. ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Forms","type":"text","marks":[{"type":"strong"}]},{"text":": By default, if your data source is “form”, every form submitted to a project space will be returned. To select a particular form type, set “Filter Name” to xmlns.exact and “Filter Value” to your form's ","type":"text"},{"text":"XML namespace","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Finding+a+Form%27s+XMLNS"}}]},{"text":". ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Cases","type":"text","marks":[{"type":"strong"}]},{"text":": If your data source is “case”, every case in a project will be returned. To download one particular case type, set “Filter Name” to type and “Filter Value” to the name of the case type you'd like to download. See additional case filtering fields here: ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/NDHKfw"}},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]},{"text":": (Optional) ","type":"text"},{"type":"hardBreak"},{"text":"Filter Values determine which forms or cases will be processed and stored. ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Forms","type":"text","marks":[{"type":"strong"}]},{"text":": When exporting forms, you can find the XML namespace for a form by going to your form > Advanced > View (Source XML) and look for the address in the header of your","type":"text"},{"text":" XForm.","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Finding+a+Form%27s+XMLNS"}}]},{"text":" ","type":"text"},{"type":"hardBreak"},{"text":"See additional form filtering options here: ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/VjHKfw"}},{"text":". Filter Names are paired with Filter Values to filter the results you request from CommCareHQ. Results are limited to the set of forms or cases whose Filter Name property matches the value listed in this field.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Cases","type":"text","marks":[{"type":"strong"}]},{"text":": When exporting cases, the Filter Value is the Case Type listed on your case's settings page. You can pull all cases and store them in the same table or filter one case type at a time into a specific table.","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]},{"text":": (Required) ","type":"text"},{"type":"hardBreak"},{"text":"Each row of this column specifies the name of one ","type":"text"},{"text":"output","type":"text","marks":[{"type":"em"}]},{"text":" field.","type":"text"},{"type":"hardBreak"},{"text":"Note that a field named “id” is required for use with a SQL database.","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"type":"hardBreak"},{"text":"Note that there is no maximum number of output fields.","type":"text","marks":[{"type":"strong"},{"type":"em"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]},{"text":": (Required)","type":"text"},{"type":"hardBreak"},{"text":"Each row of this column specifies where the data of the corresponding ","type":"text"},{"text":"Field","type":"text","marks":[{"type":"strong"}]},{"text":" should be extracted from. Note that form properties must be prefixed with “form”. and case properties must be prefixed with “properties”. ","type":"text"},{"type":"hardBreak"},{"text":"(See more details about the proper format for Source Fields below in the ","type":"text"},{"text":"How to Reference Fields in Forms and Cases","type":"text","marks":[{"type":"strong"}]},{"text":" section).","type":"text"},{"text":" ","type":"text","marks":[{"type":"em"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Map Via: ","type":"text","marks":[{"type":"strong"}]},{"text":"(Optional) ","type":"text"},{"type":"hardBreak"},{"text":"This is only relevant if you're exporting to SQL database. ","type":"text"},{"type":"hardBreak"},{"text":"This column allows you to convert strings to other types, e.g.:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Str2date converts to datetime column","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Str2num converts to integer column ","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Table Name: ","type":"text","marks":[{"type":"strong"}]},{"text":"(Optional) ","type":"text"},{"type":"hardBreak"},{"text":"By default, the table name is taken as the Excel Sheet name. This can be overridden by adding this column with a single value in the column for the table name.","type":"text"}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-cross_mark","panelIcon":":cross_mark:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Including referenced items in the results may significantly decrease the tool's performance due to the additional data that is being requested, which must be fetched separately from the main form or case data.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Advanced Usage","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Data Types","type":"text","marks":[{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When exporting to a SQL database you can also explicitly set data types by adding a column with the heading \"Data Type\". See ","type":"text"},{"text":"Data Types","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Exporting+Data#ExportingData-Datatypes"}}]},{"text":" in ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/cgrKfw"}},{"text":".","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Data Source ","type":"text","marks":[{"type":"strong"}]},{"type":"hardBreak"},{"text":"Advanced Usage: You may specify a deep JSONPath into the data to iterate over repeats. For example, if you have a repeat called \"child\" in the root of the form, the data source would become \"form.form.child\". Also, note that fields are relative to the source, not absolute. See the ","type":"text","marks":[{"type":"em"}]},{"text":"DET Tips","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" and ","type":"text"},{"text":"Tricks","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" page for more information.","type":"text","marks":[{"type":"em"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Alternate Source Field [N]:","type":"text","marks":[{"type":"strong"}]},{"text":" (Optional) ","type":"text"},{"type":"hardBreak"},{"text":"In instances where different source fields should be placed in the same output field (for example, when renaming fields in the app), alternate source fields can be specified. Each ","type":"text"},{"text":"Alternate Source Field. ","type":"text","marks":[{"type":"em"}]},{"type":"hardBreak"},{"text":"The column must be named ","type":"text"},{"text":"Alternate Source Field [N] ","type":"text","marks":[{"type":"strong"}]},{"text":"where \"","type":"text"},{"text":"[N]\" ","type":"text","marks":[{"type":"strong"}]},{"text":"is replaced by a number. For example:","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"7a7dc613-ac6a-4760-a30f-1dac4000932f"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[176.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[277.0]},"content":[{"type":"paragraph","content":[{"text":"Alternate Source Field 1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[277.0]},"content":[{"type":"paragraph","content":[{"text":"Alternate Source Field 2","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[176.0]},"content":[{"type":"paragraph","content":[{"text":"dob","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[277.0]},"content":[{"type":"paragraph","content":[{"text":"date_of_birth","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[277.0]},"content":[{"type":"paragraph","content":[{"text":"date_of_birth","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"The source fields are each considered in order, and the first one that appears in the data is used (even if its value is empty).","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Creating an Excel Query File in CommCare HQ","type":"text"}]},{"type":"paragraph","content":[{"text":"The easiest way to create a query file is to have it generated by CommCare.","type":"text"}]},{"type":"paragraph","content":[{"text":"Any Export from the Data → Export Form/Case Data tab in CommCare can be used as a basis for an Excel query file. To generate a query file for any export, check the box that says \"Generate a Data Export Tool config file\".","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":601,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":682,"type":"external","url":"https://user-images.githubusercontent.com/66555/119354953-f4a45180-bca4-11eb-89c5-26bc9e335f6c.png","height":238}}]},{"type":"paragraph","content":[{"text":"Then, on the exports list page, a button labeled \"DET Config\" with a download icon will appear with the option to download the configuration file. This file will be pre-configured with the right filters and properties to export the selected data.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":200,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":195,"id":"08ca555b-ecf9-4cfb-b2e9-58194adec527","collection":"contentId-2143955952","type":"file","height":117}}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Mappings and Tips","type":"text"}]},{"type":"paragraph","content":[{"text":"The following mappings are used to convert from the export to the query file:","type":"text"}]},{"type":"paragraph","content":[{"text":"The \"sheet name\" from the export will be used as the table name in the config file. It is recommended to edit this from the default \"Cases\" and \"Forms\" to something more meaningful like \"pregnant_women\" or \"household_registrations\".","type":"text"}]},{"type":"paragraph","content":[{"text":"The \"Display\" property will be used as the SQL column name.","type":"text"}]},{"type":"paragraph","content":[{"text":"For best practices and maximum SQL compatibility is recommended to remove all special characters from both sheet names and \"Display\" properties, and to make the fields lowercase.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Known Limitations","type":"text"}]},{"type":"paragraph","content":[{"text":"The following limitations apply to any query files generated in this manner:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Any \"special\" transformations - for example, pulling an owner name from an owner_id are not yet supported. These values will be pulled down as the raw underlying data (e.g. the ID).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The \"Parent Cases\" sheet on case exports is not supported.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Nested repeat data in forms is not fully-supported.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"How to reference Fields in Forms and Cases","type":"text"}]},{"type":"paragraph","content":[{"text":"One of the easiest ways to confirm form and case field names is by looking at the column name in a regular form/case export from CommCareHQ.","type":"text"}]},{"type":"paragraph","content":[{"text":"In general, the name of form and case fields matches those that you wrote when you created your application, with predictable modifications. When referencing properties in the Source Field column, ensure that you've typed them in exactly as they're defined in CommCare - case-sensitivity matters.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Top-Level Form Fields:","type":"text","marks":[{"type":"strong"}]},{"text":" A field named ","type":"text"},{"text":"FIELD","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" in a form is referenced by ","type":"text"},{"text":"form.","type":"text","marks":[{"type":"strong"}]},{"text":"FIELD. ","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":"For example, the field age is referenced by form.age in an Excel query file.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Top-Level Case Fields:","type":"text","marks":[{"type":"strong"}]},{"text":" A field name ","type":"text"},{"text":"FIELD","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":" in a case is referenced by ","type":"text"},{"text":"properties.","type":"text","marks":[{"type":"strong"}]},{"text":"FIELD. ","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":"For example, the field delivery_date is referenced by properties.delivery_date in an Excel query file.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Nested Fields: ","type":"text","marks":[{"type":"strong"}]},{"text":"A field nested more deeply within a form or case may be referenced by separating each level of nesting with a period. For example, in the ","type":"text"},{"text":"demo_query_file_form.xlsx","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"/wiki/download/attachments/2143956875/demo_query_file_form.xlsx?version=1&modificationDate=1617792188550&cacheVersion=1&api=v2"}}]},{"text":",","type":"text","marks":[{"type":"underline"}]},{"text":" you will see the nested field form.delivery_information.delivery_type, which will pull out the value of the delivery_type question inside the group delivery_information. This will always match the structure of your application.","type":"text"}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"(Power Users / Programmers Only)","type":"text","marks":[{"type":"em"}]},{"text":" To find out exactly what fields are available, you must view the raw output of the CommCareHQ ","type":"text"},{"text":"Case List API","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/pages/viewpage.action?pageId=2143957300"}}]},{"text":" and ","type":"text"},{"text":"Form List API","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/List+Forms"}}]},{"text":". You can then reference these fields using an arbitrary ","type":"text"},{"text":"JSONPath","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://github.com/kennknowles/python-jsonpath-rw"}}]},{"text":" into the structure.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Aggregation and Calculation Functions","type":"text"}]},{"type":"paragraph","content":[{"text":"The following are functions that you can use to change the value of a column. They can be added to the ","type":"text"},{"text":"Map Via","type":"text","marks":[{"type":"strong"}]},{"text":" column in the Excel query file.","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","width":760.0,"localId":"f3a680e0-bca1-440a-a6c3-36d9efe0ca85"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"text":"Function name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Example usage","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"Since version","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"count-selected"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-count-selected"}},"macroMetadata":{"macroId":{"value":"97f254f0-2ea6-4264-9a73-c131da089412"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"f9e3ca09-e948-4b2c-ab19-fcbc7a40f97f"}}]},{"type":"paragraph","content":[{"text":"count-selected","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Count the number of options selected for a multi-select value.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: a b c d","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: count-selected","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: 4","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"0.12.4","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"selected-at"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-selected-at"}},"macroMetadata":{"macroId":{"value":"c8008925-92e1-4f30-8379-2abae32bbddc"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"54ab9185-9e42-4d21-831d-4744153f5822"}}]},{"type":"paragraph","content":[{"text":"selected-at","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Get the option at the given position for a multi-select value. Note: the position index is zero-indexed.","type":"text"}]},{"type":"paragraph","content":[{"text":"This means that to choose the first item, you need to enter 0, the second item, 1, etc.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: red green blue","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: selected-at(2)","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: blue","type":"text"}]},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"0.12.4","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"selected"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-selected"}},"macroMetadata":{"macroId":{"value":"ac739f25-0a1d-49aa-bee3-9fb4a39e9db3"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"04490ddc-e7ad-49a1-a7c6-7c8b0a3e57d8"}}]},{"type":"paragraph","content":[{"text":"selected","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Checks to see if a value was selected from a multi-select value. Outputs 'True' or 'False'.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: rash fever","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: selected(fever)","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: True","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"0.12.4","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"attachment_url"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-attachment_url"}},"macroMetadata":{"macroId":{"value":"d0c3a4c0-da8d-4e25-975f-8dd2edb15809"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"8b4b11c9-6717-451e-b57f-2821c6be1979"}}]},{"type":"paragraph","content":[{"text":"attachment_url","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Converts a value into a URL that can be used to download attachments","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: photo.jpg","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: attachment_url","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: ","type":"text"},{"text":"https://www.commcarehq.org/a/my-domain/api/form/attachment/the-form-id/photo.jpg","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/a/my-domain/api/form/attachment/the-form-id/photo.jpg"}}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"0.13.2","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"substr"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-substr"}},"macroMetadata":{"macroId":{"value":"4e57b36f-96c9-4899-9d16-0885f02a36d7"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"5dcfc31d-7ef2-486e-9c9d-9a88a4d71154"}}]},{"type":"paragraph","content":[{"text":"substr","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Returns the substring indexed by [first arg, second arg), where indexes start from zero. Suitable for truncating long strings to fit a column's maximum length.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: abcdef","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: substr(2, 5)","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: cde","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.3.2","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"form_url"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-form_url"}},"macroMetadata":{"macroId":{"value":"51fc51bc-10f9-415c-b60f-23ff1cbaf59a"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"8330667e-5137-4cb4-b587-e6a1281153b5"}}]},{"type":"paragraph","content":[{"text":"form_url","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Returns the URL to the form page on CommCare HQ (only relevant for form exports)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: (anything)","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: form_url","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: ","type":"text"},{"text":"https://www.commcarehq.org/a/my-domain/reports/form_data/","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/a/my-domain/reports/form_data/"}}]},{"text":"

/","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.4.0","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"case_url"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-case_url"}},"macroMetadata":{"macroId":{"value":"4d3e25ba-1708-4cad-aec4-26bbb6cb3694"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"947a559a-dfcc-4ef4-9a3c-850775d1bdb1"}}]},{"type":"paragraph","content":[{"text":"case_url","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Returns the URL to the case page on CommCare HQ (only relevant for case exports)","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: (anything)","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: case_url","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: ","type":"text"},{"text":"https://www.commcarehq.org/a/my-domain/reports/case_data/","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/a/my-domain/reports/case_data/"}}]},{"text":"/","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.4.0","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"format_uuid"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-format_uuid"}},"macroMetadata":{"macroId":{"value":"073ae1eb-469d-4225-a7cf-f1d04ed145fe"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"cb8e5e69-eede-4a12-bdd7-37a2d44eea7c"}}]},{"type":"paragraph","content":[{"text":"format_uuid","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Given a UUID this function returns a formatted HEX UUID. If the input value is not a UUID it will return a blank value.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: 00a3e0194ce1458794c50971dee2de22","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: format_uuid","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: 00a3e019-4ce1-4587-94c5-0971dee2de22","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.4.0","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"sha1"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-sha1"}},"macroMetadata":{"macroId":{"value":"28bfdfe0-f544-4f55-9da4-a60eab3b2006"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"442fbb03-43f4-44a9-8612-de2cfc571dfd"}}]},{"type":"paragraph","content":[{"text":"sha1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Create an SHA1 hash of the column value.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Value: abc","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: sha1","type":"text"}]},{"type":"paragraph","content":[{"text":"Output: 03cfd743661f07975fa2f1220c5194cbaff48451","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.2.0","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[237.0]},"content":[{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.macro.core","extensionKey":"anchor","parameters":{"macroParams":{"":{"value":"unique"},"legacyAnchorId":{"value":"CommCareDataExportTool(DET)-unique"}},"macroMetadata":{"macroId":{"value":"bc05db7c-2f1a-4ca6-a810-59791fb41f95"},"schemaVersion":{"value":"1"},"title":"Anchor"}},"localId":"e8343133-a626-45f8-99ec-67ba59818f24"}}]},{"type":"paragraph","content":[{"text":"unique","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[230.0]},"content":[{"type":"paragraph","content":[{"text":"Output only unique values from a list","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Source expression: messages[*].phone_number","type":"text"}]},{"type":"paragraph","content":[{"text":"Value: ['+16355573', '+16355573', '+16355572']","type":"text"}]},{"type":"paragraph","content":[{"text":"Map Via: unique","type":"text"}]},{"type":"paragraph","content":[{"text":"Output '+16355573,+16355572'","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[62.0]},"content":[{"type":"paragraph","content":[{"text":"1.7.3","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Exporting User and Location Data","type":"text"}]},{"type":"paragraph","content":[{"text":"The --users and --locations options export data from a CommCare project that can be joined with form and case data. The --with-organization option does all of that and adds a field to Excel query specifications to be joined on.","type":"text"}]},{"type":"paragraph","content":[{"text":"Specifying the --users option or --with-organization option will export an additional table named 'commcare_users' containing the following columns:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"cddf2479-9250-4243-b306-c060606c32d1"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"Column","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph","content":[{"text":"Primary key","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"default_phone_number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"email","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"first_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"groups","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"last_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"phone_numbers","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"resource_uri","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"commcare_location_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph","content":[{"text":"Foreign key into the commcare_locations table","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"commcare_location_ids","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"commcare_primary_case_sharing_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"commcare_project","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[322.0]},"content":[{"type":"paragraph","content":[{"text":"username","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[76.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[362.0]},"content":[{"type":"paragraph"}]}]}]},{"type":"paragraph","content":[{"text":"The data in the 'commcare_users' table comes from the List Mobile Workers API ","type":"text"},{"text":"endpoint","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/List+Mobile+Workers"}}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"Specifying the --locations option or --with-organization options will export an additional table named 'commcare_locations' containing the following columns:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"dc211a9f-0b96-4107-88b9-0225eb6ec1fb"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"Column","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph","content":[{"text":"Note","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"created_at","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"domain","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"external_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"last_modified","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"latitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_data","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph","content":[{"text":"Primary key","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"longitude","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"parent","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph","content":[{"text":"resource_uri of parent location","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"resource_uri","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"site_code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_administrative","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_code","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_parent","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location level code","type":"text","marks":[{"type":"em"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph","content":[{"text":"Column name depends on the project's organization","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[312.0]},"content":[{"type":"paragraph","content":[{"text":"location level code","type":"text","marks":[{"type":"em"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[73.0]},"content":[{"type":"paragraph","content":[{"text":"Text","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[345.0]},"content":[{"type":"paragraph","content":[{"text":"Column name depends on the project's organization.","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"The data in the 'commcare_locations' table comes from the Location API endpoint along with some additional columns from the Location Type API endpoint. The last columns in the table exist if you have set up organization levels for your projects. One column is created for each organizational level. The column name is derived from the Location Type that you specified. The column value is the location_id of the containing location at that level of your organization. Consider the example organization from ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/NjPKfw"}},{"text":" . A piece of the 'commcare_locations' table could look like this:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"353ab92c-a832-47c5-a222-e1746f39661b"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"location_id","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"chw","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"supervisor","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"clinic","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"district","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"District","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"Clinic","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"Supervisor","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"4545b9","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"CHW","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"4545b9","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"468fc3cf-e997-4dda-8bd8-5798a531d836"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"location_id","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"location_type_name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"chw","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"supervisor","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"clinic","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"district","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"District","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"Clinic","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"Supervisor","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"NULL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[175.0]},"content":[{"type":"paragraph","content":[{"text":"4545b9","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"CHW","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"4545b9","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"a9ca40","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"c4cbef","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[111.0]},"content":[{"type":"paragraph","content":[{"text":"939fa8","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"In order to join form or case data to 'commcare_users' and 'commcare_locations', the exported forms and cases need to contain a field identifying which user submitted them. The --with-organization option automatically adds a field called 'commcare_userid' to each query in an Excel specification for this purpose. Using that field, you can use a SQL query with a join to report data about any level of your organization. For example, to count the number of forms submitted by all workers in each clinic:","type":"text"}]},{"type":"paragraph","content":[{"text":"Count forms by clinic","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","content":[{"text":"SELECT l.clinic,\n COUNT(*)\nFROM form_table t\nLEFT JOIN (commcare_users u\n LEFT JOIN commcare_locations l\n ON u.commcare_location_id = l.location_id)\nON t.commcare_userid = u.id\nGROUP BY l.clinic;","type":"text"}]},{"type":"paragraph","content":[{"text":"Note that the table names 'commcare_users' and 'commcare_locations' are treated as reserved names and the export tool will produce an error if given a query specification that writes to either of them.","type":"text"}]},{"type":"paragraph","content":[{"text":"The export tool will write all users to 'commcare_users' and all locations to 'commcare_locations', overwriting existing rows with current data and adding rows for new users and locations. If you want to remove obsolete users or locations from your tables, drop them, and the next export will leave only the current ones. If you modify your organization to add or delete levels, you will change the columns of the 'commcare_locations' table, and you will likely want to drop the table before exporting with the new organization.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Exporting Messaging Event Data","type":"text"}]},{"type":"paragraph","content":[{"text":"As of ","type":"text"},{"text":"version 1.7.3","type":"text","marks":[{"type":"strong"}]},{"text":", data from CommCare's SMS messaging events can be exported using the Data Export Tool. ","type":"text"}]},{"type":"paragraph","content":[{"text":"To export data, change the \"Data Source\" to \"messaging-event\".","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"c656b803-514a-4fa4-9cd4-898bd405d90f"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Column header","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"messaging-event","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Any of the filters (other than date and limit) can be applied","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"The values you would use with the API","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"The fields in the API response ","type":"text"}]},{"type":"paragraph","content":[{"text":"e.g. content_type, ","type":"text"},{"text":"recipient.name","type":"text","marks":[{"type":"link","attrs":{"href":"http://recipient.name/"}}]},{"text":", messages[*].phone_number, messages[*].email_address","type":"text"}]},{"type":"paragraph","content":[{"text":"messages[*].date","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"The user-defined name of the field in the output (e.g. Excel / table column name)","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Example Query File","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1406,"id":"40049638-c522-4025-993f-137ff330b117","collection":"contentId-2143955952","type":"file","height":340}}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"API Fields","type":"text"}]},{"type":"paragraph","content":[{"text":"Please ","type":"text"},{"text":"see here","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Messaging+Events#MessagingEvents-apifields"}}]},{"text":" for the full list of messaging event API response fields.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Exporting One Row Per Message","type":"text"}]},{"type":"paragraph","content":[{"text":"When \"Data Source\" is set to \"messaging-event\" the export will contain one row per messaging event. Since each messaging event may have more than one message, this may result in multiple messages per row, which can be difficult to analyse.","type":"text"}]},{"type":"paragraph","content":[{"text":"To export one row per message, change \"Data Source\" to \"messaging-event.messages.[*]\". This will result in each message being exported to its own row.","type":"text"}]},{"type":"paragraph","content":[{"text":"When \"Data Source\" is set in this way, you can access data from the parent messaging event by prefixing the fields with '$.'","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"54aeef3b-7325-4801-bd06-9ee2044f36c3"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"messaging-event.messages.[*]","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"content_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"sms","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.content_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"content_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.status","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"event_status","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"status","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"message_status","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"event_date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"message_date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"phone_number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"phone_number","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"email_address","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"email_address","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"content","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"message_content","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.recipient.recipient_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"recipient_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.recipient.type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"recipient_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.recipient.name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"recipient_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]}]}]},{"type":"paragraph","content":[{"text":"Note:","type":"text","marks":[{"type":"strong"}]},{"text":" if there are no messages then the event will not be exported at all. To work around this, you can use the “--export-root-if-no-subdocument” command line option which will result in a single row being exported for the event if there are no messages.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Additional Information","type":"text"}]},{"type":"paragraph","content":[{"text":"For information on the message event itself and its data fields, please see API at ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/6jHKfw"}},{"text":".","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Exporting Data","type":"text"}]},{"type":"paragraph","content":[{"text":"You have the choice to export to a file(Excel, CSV, JSON, etc.) or a SQL database. Exporting to Excel files and Markdown is primarily used for testing. Exporting to SQL is more commonly used. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"You can specify batch sizes by adding a --batch-size=number at the end of your command.","type":"text"}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"“Checkpoints” enable incremental exports. A checkpoint will get set after each batch of data that is fetched from the API. The next export will start at the last checkpoint. Checkpoints are only created when exporting to a SQL database. ","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to an Excel file","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to an Excel file allows you to get a full results-set without setting up a local database. When you export to Excel, there are no checkpoints. It’s all or nothing. If the query fails, the Excel file is not created.","type":"text"}]},{"type":"paragraph","content":[{"text":"You can run a data export and save it to an Excel file with the DET. Excel files are great for one-time data exports or if you want to use them to analyze and visualize the data. Excel is also a great option if you are not familiar with working with databases.","type":"text"}]},{"type":"paragraph","content":[{"text":"The downside to Excel files is that they can’t handle large datasets. You can also only do once-off export to an Excel file; there are no “checkpoints” created for incremental updates when the DET is run. ","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Note: You can specify batch sizes by adding a --batch-size=number at the end of your command.","type":"text"}]},{"type":"paragraph","content":[{"text":"When exporting to Excel, the DET will fetch data from the API in batches, and then when the final batch is processed, the output will be written to the Excel file.","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to a Database","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to a database supports incremental updates. When you export to a database, you can have millions of rows of data, so the DET handles it in batches. ","type":"text"}]},{"type":"paragraph","content":[{"text":"After each successful batch, the tool writes a checkpoint to a database table that the DET maintains.","type":"text"}]},{"type":"paragraph","content":[{"text":"By default, each time the commcare-export command runs, it will only import results that were modified after the most recently imported form or case. This means that, for very large datasets, you do not need to export a full set of results every time. ","type":"text"}]},{"type":"paragraph","content":[{"text":"You can configure the DET to pull data on a schedule to automatically keep your data up to date or run it as a one-time data dump.","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to SQL allows you to clean and manipulate the data before you integrate it with data visualization tools like Power BI or Tableau.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"If you are looking to export into a SQL database, you must have this database set up, and you should know how to provide a URL for it. The DET will create the necessary tables and columns in the SQL database based on the properties contained in the query file.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"By choosing \"--output-format sql\" and \"--output DATABASE_URL\" in your command, you can save the output to a SQL database. ","type":"text"}]},{"type":"paragraph","content":[{"text":"The term DATABASE_URL is a standard URL format for databases in these commands. If, for example, you have a PostgreSQL database called “registrations” that is hosted on ","type":"text"},{"text":"db.example.com","type":"text","marks":[{"type":"link","attrs":{"href":"http://db.example.com/"}}]},{"text":" that you would like to save into instead, the URL might look like ","type":"text"},{"text":"postgresql://db.example.com/registrations","type":"text","marks":[{"type":"link","attrs":{"href":"#"}}]},{"text":" (you may need to include username and password in the URL).","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Checkpoints","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The first time you run a DET query to a database, it will get all the records that exist and then set the checkpoint at the end.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The next time you run the query, it will just start from the last checkpoint and get all the cases modified after that time or forms submitted after that time.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"This checkpoint data is stored in a table called “commcare-export-runs” in the database.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you change the query file, the DET will know that it’s a new query file because the MD5 hash will not match. This triggers a full refresh of all data in the domain. See ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/OTDKfw"}},{"text":" for more information.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A checkpoint will get set after each batch of data that is fetched from the API. Using the --batch-size=number command-line argument will change how much data is fetched in each batch and, therefore, the frequency of the checkpoints.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Technical information for exporting to SQL","type":"text"}]},{"type":"paragraph","content":[{"text":"Note that a field named id is required for use with a SQL database.","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to SQL requires installing additional python libraries. These are listed in the table below.","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Supported Databases","type":"text","marks":[{"type":"em"}]}]},{"type":"table","attrs":{"layout":"full-width","width":760.0,"localId":"87cc2bd2-6864-44d4-ab80-115987172300"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"Database name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"URL format","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"Maximum field name length","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Dependencies","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"MSSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"mssql+pyodbc://:@/?driver=ODBC+Driver+18+for+SQL+Server","type":"text"}]},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'pyodbc' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install pyodbc","type":"text"}]},{"type":"paragraph","content":[{"text":"Also requires the SQL Server drivers to be installed. ","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"Azure MSSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"mssql+pyodbc:///?odbc_connect= (see: ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/x/lErKfw"}},{"text":" )","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Same as MSSQL above","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"MySQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"mysql+pymysql://:@/?charset=utf8mb4","type":"text"}]},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"64","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'pymysql' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install pymysql","type":"text"}]},{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"Oracle","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"oracle+cx_oracle://:@/","type":"text"}]},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"128","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'cx_oracle' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install cx_oracle","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[127.0]},"content":[{"type":"paragraph","content":[{"text":"PostgreSQL","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[353.0]},"content":[{"type":"paragraph","content":[{"text":"postgresql://:@/","type":"text"}]},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[78.0]},"content":[{"type":"paragraph","content":[{"text":"63","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[172.0]},"content":[{"type":"paragraph","content":[{"text":"Requires the 'psycopg2' library:","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install psycopg2","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Data types","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Data Type Detection","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"By default, the data export tool will choose the data type of the SQL column based on the incoming data. Strings, integers, and boolean values are supported.","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-info","panelIcon":":info:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"Columns are created dynamically when data is received, and the types of columns may be updated dynamically as new data comes in to make it compatible. This means that until the export tool sees a non-null value for the column, it will not create the column in SQL.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"You can also use the command-line option \"","type":"text"},{"text":"--strict-types","type":"text","marks":[{"type":"em"}]},{"text":"\" which will prevent SQL column types from being changed. This may result in errors if incompatible data is received from CommCare HQ.","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Explicit Data Types","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"You can also explicitly set data types by adding a column with the heading \"Data Type\". Allowed values for this column are:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"86d33eaf-1ef2-4491-bee3-d4bd70efb469"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"text","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"boolean","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"date","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"datetime","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"integer","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"json (Postgres Databases only)","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"If you specify a data type column, then the column will be created when the table is first created instead of dynamically based on data. Note that if you use explicit data types, you are responsible for ensuring the data going into the database is valid for that column. Specifying these conversions may be necessary for some data types - especially when converting string/text values to other types.","type":"text"}]},{"type":"heading","attrs":{"level":6},"content":[{"text":"Data Type Conversion","type":"text","marks":[{"type":"em"}]}]},{"type":"paragraph","content":[{"text":"To convert data of one type to another type before it goes into the database, you can provide the following functions in the Map Via column:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"dd3632c4-52c9-432b-b147-3fa9c29d54d9"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Function name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"background":"#f0f0f0","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2bool","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string value to a boolean. The following values result in a True output:","type":"text"}]},{"type":"paragraph","content":[{"text":"'true', 't', '1' (case doesn't matter)","type":"text"}]},{"type":"paragraph","content":[{"text":"If the value is already a boolean, it will be output without change. All other values result in a False output.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"bool2int","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"This supports converting boolean values and booleans represented as strings to the integers 1 or 0.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2num","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string representation of a number to a number.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"str2date","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Convert a string representation of a date or timestamp to a date or timestamp.","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export to Markdown","type":"text"}]},{"type":"paragraph","content":[{"text":"Exporting to “Markdown” gives you the results in the command-line. Markdown is primarily used for testing. ","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Generating Database Connection Strings","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Azure MSSQL","type":"text"}]},{"type":"paragraph","content":[{"text":"Microsoft Azure connection strings take the following form:","type":"text"}]},{"type":"paragraph","content":[{"text":"mssql+pyodbc:///?odbc_connect=","type":"text","marks":[{"type":"subsup","attrs":{"type":"sup"}}]}]},{"type":"paragraph","content":[{"text":"The value is difficult to generate by hand as it requires escaping many characters. To generate a connection string for Azure databases, it is recommended to use the following Python code:","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Generating a connection string","type":"text","marks":[{"type":"strong"}]}]},{"type":"codeBlock","attrs":{"language":"python"},"content":[{"text":"from urllib.parse import quote_plus\n\n# change these values to match your Azure database\nserver = 'tcp:your-azure-server.database.windows.net'\ndatabase = 'your-database-nametest'\nusername = 'yourusername'\npassword = '***' \n\n# these should not usually need to change\ndriver = '{ODBC Driver 18 for SQL Server}'\nport = 1433\n\nodbc_str = f'DRIVER={driver};SERVER={server};PORT={port};DATABASE={database};UID={username};PWD={password}'\nsqlalchemy_str = 'mssql+pyodbc:///?odbc_connect=' + quote_plus(odbc_str)\n\nprint(sqlalchemy_str) # use this value in the data export tool\n\n# optionally test your connection\nfrom sqlalchemy import create_engine\nengine = create_engine(sqlalchemy_str)\nquery = \"select * from \"\nprint(engine.execute(query).fetchall())","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Authentication when using DET","type":"text"}]},{"type":"paragraph","content":[{"text":"Two sets of credentials are required:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CommCareHQ credentials: a ","type":"text"},{"text":"Username ","type":"text","marks":[{"type":"strong"}]},{"text":"and an ","type":"text"},{"text":"API Key.","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Database credentials (if outputting to a database). The database user must have access to create new tables, create new columns in those tables, and read and edit data.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CommCare HQ Authentication Requires an API Key","type":"text"}]},{"type":"paragraph","content":[{"text":"API Key authentication method is required. Each user must create their own API key and store it securely. ","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"API Keys can be scoped (restricted) to a domain or a set of domains","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"API Keys can also be set to an Allow-List IP Address or list of IP Addresses. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"IP Ranges are not permitted. Each individual IP address will need to be named. If you leave this blank, API requests from all IP addresses in the world will be allowed.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create an API key: ","type":"text"},{"text":"https://www.commcarehq.org/account/api_keys/","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://www.commcarehq.org/account/api_keys/"}}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"API keys belong to individual web users. Each user who wants to run a Data Export Tool query will need their own API key.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Enterprise Domains","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When using domains that are linked to a main account, whose permissions inherit from the main account, the user doesn’t need to accept the invitation on all the domains. ","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Security Recommendations","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When using domains that are linked to a main account, whose permissions inherit from the main account, the user doesn’t need to accept the invitation on all the domains. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Store API Key name and password in a password manager.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Do not share your API key with anyone.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Do not email it to yourself or store it in any document that is not password-protected.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Rotate keys: Create new API Keys each month and name the API Key by the month.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":4},"content":[{"text":"Referencing API Key From Command Line","type":"text"}]},{"type":"paragraph","content":[{"text":"To use API Key authentication in Data Export Tool from the command line, the following two parameters are required:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"--auth-mode: \"","type":"text","marks":[{"type":"strong"}]},{"text":"apikey\"","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"--username:","type":"text","marks":[{"type":"strong"}]},{"text":" set to the web user's email (e.g. ","type":"text"},{"text":"username@email.com","type":"text","marks":[{"type":"link","attrs":{"href":"mailto:username@email.com"}}]},{"text":")","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"--password:","type":"text","marks":[{"type":"strong"}]},{"text":" set to the API Key generated above","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"An example call might look like:","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39\\Scripts>commcare-export --output-format xlsx --output YOUR_OUTPUT_FILE_NAME.xlsx --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx --username username@email.com --auth-mode apikey --password ***********YOUR_API_KEY**************","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Installing and Upgrading DET","type":"text"}]},{"type":"paragraph","content":[{"text":"The DET can be run by either:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Downloading the DET executable file (recommended for general users)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Installing the package through python (for the more tech-savvy)","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"The section below explores both options","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Download the DET executable","type":"text"}]},{"type":"paragraph","content":[{"text":"The latest DET executable file can be downloaded from the ","type":"text"},{"text":"github releases page","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"https://github.com/dimagi/commcare-export/releases"}}]},{"text":" by looking under the “Assets” section of the latest release. Both Windows and Linux machines are supported, but you have to download the correct executable for the appropriate operating system.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Windows executable","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The Windows executable file is called ","type":"text"},{"text":"commcare-export.exe","type":"text","marks":[{"type":"code"}]},{"text":". You can download the file to any place on your system and run it from there in the same way that you would run the ","type":"text"},{"text":"commcare-export","type":"text","marks":[{"type":"code"}]},{"text":" command, with one exception being that you have to run the file as you would run any executable file on Windows, for example","type":"text"}]},{"type":"codeBlock","content":[{"text":".\\commcare-export ","type":"text"}]},{"type":"paragraph","content":[{"text":"That’s it! No installation needed!","type":"text"}]},{"type":"paragraph","content":[{"text":"The only thing to keep in mind is that you have to specify the path to the query file correctly if you don’t have the DET executable in the same location as the query file. ","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Linux binary","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"The Linux binary file is simply called ","type":"text"},{"text":"commcare-export","type":"text","marks":[{"type":"code"}]},{"text":". After having downloaded the DET binary file you have to make sure the file has the necessary executable permission. You can check this by running","type":"text"}]},{"type":"codeBlock","content":[{"text":"test -x builds.txt && echo true || echo false ","type":"text"}]},{"type":"paragraph","content":[{"text":"If the file is not executable the terminal will show “false”, in which case you can simply give the file the necessary executable permission by running","type":"text"}]},{"type":"codeBlock","content":[{"text":"chmod +x ","type":"text"}]},{"type":"paragraph","content":[{"text":"Now you can simply run the DET as you would any other binary file","type":"text"}]},{"type":"codeBlock","content":[{"text":"./commcare_export ","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Installing DET through the package","type":"text"}]},{"type":"paragraph","content":[{"text":"This is for more tech-savvy users who have a fair level of comfort with using the command line and working with python packages. ","type":"text"}]},{"type":"paragraph","content":[{"text":"When using the DET, these are the basic steps to follow:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.python.org/downloads/"}}]},{"text":" and install Python.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Install a Python package manager (we recommend pip).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Install the ComCare Data Export tool.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create an Excel Query File.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Write a command-line query to run the export.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"(Optionally) Configure export to be re-run automatically.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use data.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install DET on Windows","type":"text"}]},{"type":"paragraph","content":[{"text":"The Data Export tool is installed by using the pip install commcare-export command.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install Python","type":"text"}]},{"type":"paragraph","content":[{"text":"Visit the Python for Windows page and download the latest version of Python.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install pip","type":"text"}]},{"type":"paragraph","content":[{"text":"Installation is straightforward if your local machine has a recent version of Python installed because the package installer, ","type":"text"},{"text":"pip","type":"text","marks":[{"type":"strong"},{"type":"em"}]},{"text":", will already be installed. ","type":"text"}]},{"type":"paragraph","content":[{"text":"You can check whether pip is installed by running the following in the command-line interpreter:","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39>python -m pip --version","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install commcare-export","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39\\scripts>pip install commcare-export","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Upgrade commcare export","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\python39\\scripts>pip install -U commcare-export","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install DET on MacOS","type":"text"}]},{"type":"paragraph","content":[{"text":"The Data Export tool is installed by using the pip install commcare-export command.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install Python","type":"text"}]},{"type":"paragraph","content":[{"text":"For MacOS make sure that your Python installation is upgraded to the latest version. The best way to install software on a Mac is via ","type":"text"},{"text":"Homebrew","type":"text","marks":[{"type":"underline"},{"type":"link","attrs":{"href":"http://brew.sh/"}}]},{"text":". You need to open a terminal and run two scripts to install Homebrew and the latest version of Python. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Open a terminal and run the following two commands. ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The first script installs Homebrew. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The second script installs Python and any prerequisites. ","type":"text"}]}]}]},{"type":"codeBlock","content":[{"text":"/usr/bin/ruby -e \"$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)\"\nbrew install python","type":"text"}]},{"type":"paragraph"},{"type":"paragraph","content":[{"text":"For Homebrew, use the following:","type":"text"}]},{"type":"codeBlock","content":[{"text":"brew install .","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install commcare-export","type":"text"}]},{"type":"codeBlock","content":[{"text":"pip2 install commcare-export","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Upgrade commcare-export","type":"text"}]},{"type":"codeBlock","content":[{"text":"sudo pip2 install -U commcare-export","type":"text"}]},{"type":"paragraph"},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":" NOTE: If you have any problems setting up Python on MacOS, use this reference: ","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineCard","attrs":{"url":"http://docs.python-guide.org/en/latest/starting/install/osx/"}},{"text":" ","type":"text"}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install DET on Linux","type":"text"}]},{"type":"paragraph","content":[{"text":"The Data Export tool is installed by using the pip install commcare-export command.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install Python","type":"text"}]},{"type":"paragraph","content":[{"text":"If you are on a Linux-based operating system, you probably have an automatic package manager available. You probably already have Python installed, but if not, then you will need to invoke the package manager for your operating system. Here are some possible commands to run in the command-line interpreter:","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ apt-get install python","type":"text"}]},{"type":"paragraph","content":[{"text":"or","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ yum install python","type":"text"}]},{"type":"paragraph","content":[{"text":"or","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ emerge python","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Install pip","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ sudo easy_install pip","type":"text"}]},{"type":"paragraph","content":[{"text":"Install commcare-export","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ sudo pip install commcare-export","type":"text"}]},{"type":"paragraph","content":[{"text":"Upgrade commcare-export","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ sudo pip install -U commcare-export","type":"text"}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"NOTE: If you get an error \"ValueError: Invalid column index 0\" you should run this command to downgrade one of the python libraries:","type":"text"}]},{"type":"paragraph","content":[{"text":"$ pip install -U openpyxl==1.8.6","type":"text"}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Run a CommCare Export","type":"text"}]},{"type":"paragraph","content":[{"text":"Once you have all the components ready, you can run the commcare-export command. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Components needed to run the DET:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The DET installed on your computer ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A project space with an application that contains data.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A query file for the DET to tell it what data to export.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The necessary credentials for CommCare HQ (Username and API key)","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If exporting to a database, the necessary credentials for the database you choose to use.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Optional commcare-export flags","type":"text"}]},{"type":"paragraph","content":[{"text":"For a full list of flags, you can run commcare-export --help.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"32f06618-0811-4f9a-b36a-d5179de9918b"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"argument","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"possible values","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--since","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Provide the DATE value in YYYY-MM-DD format. This will export all data after this date.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--until","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Provide the DATE value in YYYY-MM-DD format. This will export all data before this date.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--verbose","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Includes a more detailed output to the user.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--verbose 2> logs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"This flag exports log files to a text file.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--verbose 2>> logs.txt","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Append logs to an already existing log file.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--missing-value","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Override the default value (null) that is used whenever data is missing from the form/case.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--api-version","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Specify the version of the CommCare API to use for exports. Defaults to v0.5.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--start-over","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Ignore any checkpoints that may exists and re-export ALL data.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--strict-types","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"This won’t allow changing column types once they are created when saving to a SQL database.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--version","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Output the version of commcare-export that you're using (useful for debugging).","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[209.0]},"content":[{"type":"paragraph","content":[{"text":"--auth-mode","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[521.0]},"content":[{"type":"paragraph","content":[{"text":"Use \"digest\" auth, or \"apikey\" auth (for two factor enabled domains).","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"Tips","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"OUTPUT-FORMAT refers to the format in which you want to export the data. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"YOUR_PROJECT refers to the name of your project space, not the entire domain URL. For instance, the URL would be ","type":"text"},{"text":"https://www.commcarehq.org/a/YOUR_PROJECT","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/a/YOUR_PROJECT"}}]},{"text":". For the tool, type in YOUR_PROJECT. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If you are using a domain that isn't stored on ","type":"text"},{"text":"www.commcarehq.org","type":"text","marks":[{"type":"link","attrs":{"href":"http://www.commcarehq.org"}}]},{"text":", then you will need to use the \"--commcare-hq YOUR_URL\" flag in the command prompt.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"YOUR-QUERY_FILE_NAME.xlsx refers to your query file that is saved in the scripts folder of your Python installation.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"YOUR_OUTPUT_FILE_NAME.xlsx refers to the file that will contain the exported data. This file will also be saved in the scripts folder of your Python installation.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Export using Windows","type":"text"}]},{"type":"paragraph","content":[{"text":"Run the following commands in your command line interpreter to export data from CommCare HQ:","type":"text","marks":[{"type":"em"}]}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Markdown","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39\\Scripts>commcare-export --output-format markdown --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Excel","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39\\Scripts>commcare-export --output-format xlsx --output YOUR_OUTPUT_FILE_NAME.xlsx --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx --username username@email.com --auth-mode apikey --password ***********YOUR_API_KEY**************","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Database","type":"text"}]},{"type":"codeBlock","content":[{"text":"C:\\Python39\\Scripts>commcare-export --output-format sql --output DATABASE_URL --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Export using MacOS","type":"text"}]},{"type":"paragraph","content":[{"text":"Run the following commands in your command line interpreter to export data from CommCare HQ:","type":"text","marks":[{"type":"em"}]}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Markdown","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format markdown --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Excel","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format xlsx --output YOUR_OUTPUT_FILE_NAME.xlsx --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx --username username@email.com --auth-mode apikey --password ***********YOUR_API_KEY**************","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Database","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format sql --output DATABASE_URL --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Export using Linux","type":"text"}]},{"type":"paragraph","content":[{"text":"Run the following commands in your command line interpreter to export data from CommCare HQ:","type":"text","marks":[{"type":"em"}]}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Markdown","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format markdown --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Excel","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format xlsx --output YOUR_OUTPUT_FILE_NAME.xlsx --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx --username username@email.com --auth-mode apikey --password ***********YOUR_API_KEY**************","type":"text"}]},{"type":"heading","attrs":{"level":5},"content":[{"text":"Database","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --output-format sql --output DATABASE_URL --project YOUR_PROJECT --query YOUR_QUERY_FILE_NAME.xlsx","type":"text"}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":1},"content":[{"text":"Exporting Data from Multiple CommCare Project Spaces Using the Data Export Tool (DET)","type":"text"}]},{"type":"paragraph","content":[{"text":"This guide explains how to export data from multiple CommCare project spaces using the ","type":"text"},{"text":"Data Export Tool (DET)","type":"text","marks":[{"type":"strong"}]},{"text":" and the ","type":"text"},{"text":"commcare-export-multi","type":"text","marks":[{"type":"code"}]},{"text":" script. This is helpful if you manage or analyze data across different projects and need a consolidated export workflow.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"When and Why to Use Multi-Project DET Exports","type":"text"}]},{"type":"paragraph","content":[{"text":"You should consider exporting from multiple project spaces when:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You manage ","type":"text"},{"text":"similar programs","type":"text","marks":[{"type":"strong"}]},{"text":" or ","type":"text"},{"text":"regional implementations","type":"text","marks":[{"type":"strong"}]},{"text":" that are configured in separate project spaces (e.g., one per country or state). Oftentimes these apps are launched using our ","type":"text"},{"type":"inlineCard","attrs":{"url":"https://dimagi.atlassian.net/wiki/spaces/commcarepublic/pages/2143955137"}},{"text":" feature.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You want to ","type":"text"},{"text":"standardize analytics and reporting","type":"text","marks":[{"type":"strong"}]},{"text":" across projects using the same export definitions.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You want to ","type":"text"},{"text":"automate exports","type":"text","marks":[{"type":"strong"}]},{"text":" at scale, without maintaining separate scripts or cron jobs for each project space.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"You are part of a ","type":"text"},{"text":"central data or M&E team","type":"text","marks":[{"type":"strong"}]},{"text":" that monitors performance or aggregates data across programs.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This feature is especially valuable for organizations that follow a multi-project model but use consistent form/case structures across their projects.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Prerequisites","type":"text"}]},{"type":"paragraph","content":[{"text":"This guide assumes you:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Are already familiar with how to use the ","type":"text"},{"text":"CommCare Data Export Tool (DET)","type":"text","marks":[{"type":"strong"}]},{"text":" to pull data from a single project space.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Have a ","type":"text"},{"text":"single API key","type":"text","marks":[{"type":"strong"}]},{"text":" that provides access to all the project spaces you want to export from.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Have installed and configured DET on your system.","type":"text"}]}]}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 1: Install the ","type":"text"},{"text":"commcare-export-multi","type":"text","marks":[{"type":"code"}]},{"text":" Script","type":"text"}]},{"type":"paragraph","content":[{"text":"Download the ","type":"text"},{"text":"commcare-export-multi","type":"text","marks":[{"type":"code"}]},{"text":" script from the ","type":"text"},{"text":"CommCare Export GitHub repository","type":"text","marks":[{"type":"link","attrs":{"href":"https://github.com/dimagi/commcare-export/blob/dmr/commcare-export-multi-script/commcare-export-multi"}}]},{"text":". This is a lightweight shell script that simply loops over the list of project spaces and runs ","type":"text"},{"text":"commcare-export","type":"text","marks":[{"type":"code"}]},{"text":" for each one.","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1265,"alt":"image-20250321-201553.png","id":"7acefd1e-2afa-482c-8a6c-1d6ff3af8df1","collection":"contentId-2143955952","type":"file","height":535}}]},{"type":"paragraph","content":[{"text":"You can save the script to your current working directory or place it in your system path.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 2: Create a DET Query Config File","type":"text"}]},{"type":"paragraph","content":[{"text":"If you don’t already have a DET config file:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Go to one of your project spaces in CommCare.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a data export (form or case).","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Select the option to ","type":"text"},{"text":"Generate DET Config","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Download the generated ","type":"text"},{"text":".xlsx","type":"text","marks":[{"type":"code"}]},{"text":" config file.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This configuration file will be reused across all your project spaces — assuming the structure (forms, cases, fields) is consistent.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 3: Test Your DET Command with a Single Project","type":"text"}]},{"type":"paragraph","content":[{"text":"Run a standard ","type":"text"},{"text":"commcare-export","type":"text","marks":[{"type":"code"}]},{"text":" command to test that your config works with one project:","type":"text"}]},{"type":"codeBlock","content":[{"text":"commcare-export --project project1 --query client-DET.xlsx ...","type":"text"}]},{"type":"paragraph","content":[{"text":"Make sure this command completes successfully and gives you the expected output.","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 4: Use ","type":"text"},{"text":"commcare-export-multi","type":"text","marks":[{"type":"code"}]},{"text":" with One Project","type":"text"}]},{"type":"paragraph","content":[{"text":"Now adapt your command to use the ","type":"text"},{"text":"commcare-export-multi","type":"text","marks":[{"type":"code"}]},{"text":" script:","type":"text"}]},{"type":"codeBlock","content":[{"text":"PROJECTS=\"project1\" ./commcare-export-multi --query client-DET.xlsx ...","type":"text"}]},{"type":"paragraph","content":[{"text":"Verify that this second command does exactly the same thing as the first one. (If it doesn’t work, make sure that you downloaded commcare-export-multi into the current working directory; alternatively, you can replace `./` with the full path to commcare-export-multi.) ","type":"text"}]},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 5: Add Multiple Project Spaces","type":"text"}]},{"type":"paragraph","content":[{"text":"Once you've confirmed it works with one project, add the others:","type":"text"}]},{"type":"codeBlock","content":[{"text":"PROJECTS=\"project1 project2 project3 project4\" ./commcare-export-multi --query client-DET.xlsx ...","type":"text"}]},{"type":"paragraph"},{"type":"rule"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Tips & Troubleshooting","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Make sure your ","type":"text"},{"text":".xlsx","type":"text","marks":[{"type":"code"}]},{"text":" config file is compatible with all project spaces.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The script uses the ","type":"text"},{"text":"PROJECTS","type":"text","marks":[{"type":"code"}]},{"text":" environment variable. You can also pass this variable inline or define it in your shell profile if using the same set regularly.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If a specific project fails, check:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"API key access for that project.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Whether the form or case structure has diverged from the others.","type":"text"}]}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"DET Tips and Tricks","type":"text"}]},{"type":"paragraph","content":[{"text":"Below are some tips and tricks for your DET!","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export repeat groups","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the \"Data Source\" to be the path of your repeat group, i.e. the element that is repeated.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When using a repeat count, form.form.question_group.repeat_group[*]","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"When using model iteration, form.form.question_group.item[*]","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference the questions directly i.e. instead of \"form.question_goup.repeat_group.question1\" you should use \"question1\"","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference root properties of the form with: $","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"e.g. $.form.@name, $.id","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference the id of the repeat group with: id","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"c92f9e0f-8505-4485-bf56-1a1df405714f"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[246.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[98.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[94.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[246.0]},"content":[{"type":"paragraph","content":[{"text":"form.form.path.to.repeat[*]","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[98.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[94.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[246.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[98.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[94.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"form_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"$.form.@name","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[246.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[98.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[94.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"repeat_question1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"repeat_question1","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[246.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[98.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[94.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"repeat_question2","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"repeat_question2","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export a case or form in JSON representation","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the \"Data Source\" to \"form\" or \"case\", depending on what is supposed to be extracted. ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Set the \"Source Field\" to \"[*]\"","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"a16c37b7-18c6-4093-82cc-32f48e10b06c"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[187.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[149.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[187.0]},"content":[{"type":"paragraph","content":[{"text":"form","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[149.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[187.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph","content":[{"text":"json_doc","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[149.0]},"content":[{"type":"paragraph","content":[{"text":"[*]","type":"text"}]}]}]}]},{"type":"panel","attrs":{"panelIconId":"atlassian-warning","panelIcon":":warning:","panelColor":"#EAE6FF","panelType":"custom"},"content":[{"type":"paragraph","content":[{"text":"In this query file, we are not applying any filters. Considering that the \"Data Source\" is generic, this would include all forms or cases regardless of their type.","type":"text"}]}]},{"type":"paragraph","content":[{"text":"Export one row for each case that was altered by a form","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use \"form.form..case\" as the data source","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference case properties directly, e.g., @case_id","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference root properties of the form with '$' e.g. $.domain, $.form.@name","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"c2328953-dce4-4c1c-83b7-d96e7d5a024d"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[122.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[117.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[112.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[178.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph","content":[{"text":"form.form..case","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[122.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[117.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[112.0]},"content":[{"type":"paragraph","content":[{"text":"case_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[178.0]},"content":[{"type":"paragraph","content":[{"text":"@case_id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[122.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[117.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[112.0]},"content":[{"type":"paragraph","content":[{"text":"form_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[178.0]},"content":[{"type":"paragraph","content":[{"text":"$.form.@name","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[122.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[117.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[112.0]},"content":[{"type":"paragraph","content":[{"text":"user_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[178.0]},"content":[{"type":"paragraph","content":[{"text":"$.metadata.userID","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[201.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[122.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[117.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[112.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[178.0]},"content":[{"type":"paragraph","content":[{"text":"$.id","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Determine if a case was created, update, or closed by a form","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Use the technique above to export one row for each case in the form","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference the 'create', 'update', and 'close' fields in the case and map them via the 'bool' mapping","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"This will produce a 'True' or 'False' value in the output.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"5bc8c4d7-fd70-44e0-9494-ec00b9804d76"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"Map Via","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph","content":[{"text":"form.form..case","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"$.id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"case_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"@case_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"created","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"create","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"bool","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"updated","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"update","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"bool","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[208.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[119.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[114.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[80.0]},"content":[{"type":"paragraph","content":[{"text":"closed","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[124.0]},"content":[{"type":"paragraph","content":[{"text":"close","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[85.0]},"content":[{"type":"paragraph","content":[{"text":"bool","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Determine if a form was submitted from a mobile phone or via some other means (API, bulk upload, Cloudcare, etc.)","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Reference the 'is_phone_submission' property of the form.","type":"text"}]}]}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"1b2e04c7-3a7e-41a3-8be2-2489efc5abf0"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[161.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[107.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[161.0]},"content":[{"type":"paragraph","content":[{"text":"form","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[107.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[161.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[107.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"form_name","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"form.@name","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[161.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[107.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[102.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"is_phone_submission","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[180.0]},"content":[{"type":"paragraph","content":[{"text":"is_phone_submission","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Convert output values based on a mapping","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"78234a45-cc90-4855-aa92-b42d255680b0"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"#deebff","rowspan":1,"colwidth":[730.0]},"content":[{"type":"paragraph","content":[{"text":"Available in version 0.12.5 and above.","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"You may want to convert an output value to something else by doing a 'lookup' in some instances. For example:","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","width":760.0,"localId":"4df87c29-6832-4992-a5b9-08b50c7e6653"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"Convert this output","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph","content":[{"text":"→ To this output","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph"},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"nested-table","text":"","parameters":{"cxhtml":"

Gender
m
f

","nestedContent":{"type":"doc","content":[{"type":"table","attrs":{"layout":"default","localId":"922e4831-5cd8-40c3-bc65-519b538911b7"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Gender","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"m","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"f","type":"text"}]}]}]}]}],"version":1},"adf":"{\"type\":\"doc\",\"content\":[{\"type\":\"table\",\"attrs\":{\"layout\":\"default\",\"localId\":\"922e4831-5cd8-40c3-bc65-519b538911b7\"},\"content\":[{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Gender\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"m\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"f\",\"type\":\"text\"}]}]}]}]}],\"version\":1}","reason":"A table in a table cell can't be created or edited in the new editor."}}},{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[365.0]},"content":[{"type":"paragraph"},{"type":"extension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"nested-table","text":"","parameters":{"cxhtml":"

Gender
Male
Female

","nestedContent":{"type":"doc","content":[{"type":"table","attrs":{"layout":"default","localId":"bad532ab-01d7-4680-af62-bad07cad16df"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Gender","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Male","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Female","type":"text"}]}]}]}]}],"version":1},"adf":"{\"type\":\"doc\",\"content\":[{\"type\":\"table\",\"attrs\":{\"layout\":\"default\",\"localId\":\"bad532ab-01d7-4680-af62-bad07cad16df\"},\"content\":[{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableHeader\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Gender\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Male\",\"type\":\"text\"}]}]}]},{\"type\":\"tableRow\",\"content\":[{\"type\":\"tableCell\",\"attrs\":{\"colspan\":1,\"rowspan\":1},\"content\":[{\"type\":\"paragraph\",\"content\":[{\"text\":\"Female\",\"type\":\"text\"}]}]}]}]}],\"version\":1}","reason":"A table in a table cell can't be created or edited in the new editor."}}},{"type":"paragraph"}]}]}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Here is our configuration sheet:","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":760,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":725,"id":"1bf1aca1-f4b8-4f3d-ae73-f8c6901f02c7","collection":"contentId-2143955952","type":"file","height":238}}]},{"type":"paragraph","content":[{"text":"And here we've added a new sheet called \"Mappings\", which contains 3 columns:","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Mapping Name - this must match the name of a \"Field\" in the main configuration sheet","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Source - this is the value that you want to convert from","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Destination - this is the value that you want to convert to","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":"Here is the Mapping sheet from our example:","type":"text"}]},{"type":"mediaSingle","attrs":{"layout":"center","width":698,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":630,"id":"c2d31fe0-a7ae-498a-821f-f619d9020316","collection":"contentId-2143955952","type":"file","height":242}}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Exporting the form_link","type":"text"}]},{"type":"paragraph","content":[{"text":"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(","type":"text"},{"text":"https://www.commcarehq.org/a/{}/reports/form_data/{}/","type":"text","marks":[{"type":"link","attrs":{"href":"#"}}]},{"text":", $.domain, $.id) (see screenshot below).","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","width":760.0,"localId":"8b32ee74-4aca-411b-a3fd-0b31c5cbb6ab"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[55.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[424.0]},"content":[{"type":"paragraph","content":[{"text":"Map Via","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph","content":[{"text":"form","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph","content":[{"text":"form_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[55.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[424.0]},"content":[{"type":"paragraph"}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[92.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[53.0]},"content":[{"type":"paragraph","content":[{"text":"form_link","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[55.0]},"content":[{"type":"paragraph","content":[{"text":"form_link","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[424.0]},"content":[{"type":"paragraph","content":[{"text":"template(","type":"text"},{"text":"https://www.commcarehq.org/a/{}/reports/form_data/{}/","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/a/%7B%7D/reports/form_data/%7B%7D/"}}]},{"text":", $.domain, $.id)","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export the Parent Case's ID","type":"text"}]},{"type":"paragraph","content":[{"text":"When exporting cases, if you need the parent's case_id or case_type, use the following source field:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"f2104dae-8b16-489d-a9af-441f37ad3f33"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[159.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[108.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[103.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[150.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[210.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[159.0]},"content":[{"type":"paragraph","content":[{"text":"case","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[108.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[103.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[150.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[210.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[159.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[108.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[103.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[150.0]},"content":[{"type":"paragraph","content":[{"text":"parent_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[210.0]},"content":[{"type":"paragraph","content":[{"text":"indices.parent.case_id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[159.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[108.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[103.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[150.0]},"content":[{"type":"paragraph","content":[{"text":"parent_case_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[210.0]},"content":[{"type":"paragraph","content":[{"text":"indices.parent.case_type","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Export the Host Case's ID","type":"text"}]},{"type":"paragraph","content":[{"text":"When exporting cases, if you need the host's case_id or case_type, use the following source field:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"0fa64540-4a24-4c95-8cd8-1c1abe3d1b93"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[157.0]},"content":[{"type":"paragraph","content":[{"text":"Data Source","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[115.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Name","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[110.0]},"content":[{"type":"paragraph","content":[{"text":"Filter Value","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"Field","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[202.0]},"content":[{"type":"paragraph","content":[{"text":"Source Field","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[157.0]},"content":[{"type":"paragraph","content":[{"text":"case","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[115.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[110.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[202.0]},"content":[{"type":"paragraph","content":[{"text":"id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[157.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[115.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[110.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"host_id","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[202.0]},"content":[{"type":"paragraph","content":[{"text":"indices.host.case_id","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[157.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[115.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[110.0]},"content":[{"type":"paragraph"}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[146.0]},"content":[{"type":"paragraph","content":[{"text":"host_case_type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[202.0]},"content":[{"type":"paragraph","content":[{"text":"indices.host.case_type","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"DET Advanced Features","type":"text"}]},{"type":"paragraph","content":[{"text":"Below are some advanced features. ","type":"text"},{"text":"Please note that these features only apply to SQL exports.","type":"text","marks":[{"type":"strong"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Show export history","type":"text"}]},{"type":"paragraph","content":[{"text":"This command can be used to view the history of a particular export:","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export-utils history --project X --query A.xlsx --output [SQL URL]\n\n\n| Checkpoint Time | Batch end date | Export Complete | Project | Query Filename | Query MD5 | Key | CommCare HQ |\n| -------------------------- | ------------------- | --------------- | ------- | ------------------- | -------------------------------- | --- | ----------- |\n| 2018-08-29T15:18:04.299947 | 2014-08-30T04:30:48 | True | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |\n| 2018-08-29T15:18:01.553475 | 2014-08-29T09:45:32 | False | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |\n| 2018-08-29T15:17:59.300238 | 2014-08-28T05:13:17 | False | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |\n| 2018-08-29T15:17:57.151183 | 2014-08-27T14:36:38 | False | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |\n| 2018-08-29T15:17:54.914646 | 2014-08-26T15:41:11 | False | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |\n| 2018-08-29T15:17:52.771408 | 2014-08-25T21:46:47 | False | X | A.xlsx | 9043f06f8cb4b26871ebd939609ca01e | | prod |","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"User defined export key for checkpointing","type":"text"}]},{"type":"paragraph","content":[{"text":"Normally the export tool uses the MD5 hash of the query file as the checkpoint key i.e. when starting up it will query the database using the MD5 hash to find the last checkpoint time.","type":"text"}]},{"type":"paragraph","content":[{"text":"This means that if the query file is changed the MD5 will also change the the tool will not be able to find the last checkpoint which will cause it to re-export all the data. In most cases this is what's desired since change to the query file require re-processing the data however in certain scenarios it may be more desirable to continue using the previous checkpoints.","type":"text"}]},{"type":"paragraph","content":[{"text":"For example, if a new column is added to a form export query file which represents a new field in the form, it is not necessary to re-process all the old data since that field doesn't exist anyway.","type":"text"}]},{"type":"paragraph","content":[{"text":"To accommodate these use cases the export tool can be made to ignore the MD5 hash of the query file and instead use a key supplied on the command line:","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export --project X --query A.xlsx --output [SQL URL] --checkpoint-key my-key ...","type":"text"}]},{"type":"paragraph","content":[{"text":"Note that when running with a user supplied key the export tool will never consider the MD5 hash and therefore any re-processing of older data will need to be handled manually.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Converting a non-keyed export to a keyed export","type":"text"}]},{"type":"paragraph","content":[{"text":"To convert an export that has been using the MD5 hash as the key into one that uses a custom key run the following command:","type":"text"}]},{"type":"codeBlock","content":[{"text":"$ commcare-export-utils set-key --project X --query A.xlsx --output [SQL URL] --checkpoint-key my-key","type":"text"}]},{"type":"paragraph","content":[{"text":"Note that this will use the MD5 hash of the query file to find the checkpoints that need to updated.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Saving data as JSON","type":"text"}]},{"type":"paragraph","content":[{"text":"This feature is only available on Postgres databases.","type":"text","marks":[{"type":"strong"}]}]},{"type":"paragraph","content":[{"text":"You can save data as a \"JSON\" column. This is particularly useful when attempting to export ","type":"text"},{"text":"all","type":"text","marks":[{"type":"em"}]},{"text":" form or case data regardless of which fields are present - for example, if you are trying to create a simple data lake for your CommCare data.","type":"text"}]},{"type":"paragraph","content":[{"text":"To export all form contents as JSON, create a column in a case export file with a \"Source Field\" of \"form\" and a ","type":"text"},{"text":"data type column","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Exporting+Data#ExportingData-Datatypes"}}]},{"text":" set to \"json\".","type":"text"}]},{"type":"paragraph","content":[{"text":"To export all case properties as JSON, create a column in a case export file with a \"Source Field\" of \"properties\" and a data type column set to \"json\".","type":"text"}]},{"type":"paragraph","content":[{"text":"This data can then be queried using Postgres JSON queries. For example:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"select form->'@name' as form_name, form->'meta'->'username' as username, form->'meta'->'timeStart' as time_start from form_lake limit 5;\n\n form_name | username | time_start \n-------------+-------------------+-------------------------------\n \"New Event\" | \"alice\" | \"2017-06-24T16:24:07.676000Z\"\n \"New Event\" | \"bob\" | \"2017-07-02T13:43:52.655000Z\"\n \"New Event\" | \"chelsea\" | \"2017-07-04T07:51:12.528000Z\"\n \"New Event\" | \"daniel\" | \"2017-07-09T06:10:48.591000Z\"\n \"New Event\" | \"edith\" | \"2020-04-02T05:58:11.712000Z\"\n\n","type":"text"}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Configuring DET to Run as a Scheduled Task on Windows","type":"text"}]},{"type":"paragraph","content":[{"text":"The purpose of this section is to automatically configure the download of data from the Data Export Tool (DET) on Windows.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Steps to Create a Schedule ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a batch file using the .bat file extension and save it in your scripts folder ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"A batch file is a script which contains a series of commands to be executed by the command line ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The file can be created using a text editor such as NotePad or Notepad++","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"In the batch file, paste the ","type":"text"},{"text":"query command","type":"text","marks":[{"type":"link","attrs":{"href":"https://dimagi-dev.atlassian.net/wiki/display/commcarepublic/Run+a+CommCare+Export"}}]},{"text":" that you would have used on the command line to run the export ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Ensure that you use the absolute path when specifying the directory where the query command is specified","type":"text"}]}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Open the ","type":"text"},{"text":"Task Scheduler","type":"text","marks":[{"type":"strong"}]},{"text":" and create a basic task","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Under ","type":"text"},{"text":"Trigger ","type":"text","marks":[{"type":"strong"}]},{"text":"specify the frequency that the task should run","type":"text"}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":633,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":1045,"id":"8ffde3fe-3137-4f76-bd61-dd3e1365e3a7","collection":"contentId-2143955952","type":"file","height":734}}]},{"type":"orderedList","attrs":{"order":5},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Then under ","type":"text"},{"text":"Action ","type":"text","marks":[{"type":"strong"}]},{"text":"select Start a program ","type":"text"}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":633,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":320,"alt":"a7e8318e-ffb6-4cb2-b0c7-8024f0f8e19f.png","id":"b9247e2a-0a1f-4f32-8335-b2ba43b4adba","collection":"contentId-2143955952","type":"file","height":222}}]},{"type":"orderedList","attrs":{"order":6},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Once ","type":"text"},{"text":"Start a program","type":"text","marks":[{"type":"strong"}]},{"text":" has been selected, select the batch file as a program","type":"text"}]}]}]},{"type":"mediaSingle","attrs":{"layout":"center","width":633,"widthType":"pixel"},"content":[{"type":"media","attrs":{"width":320,"alt":"cf12248d-e9ea-4540-8124-b27819da4410.png","id":"7665123d-1fcf-41ea-a8fd-088885d40948","collection":"contentId-2143955952","type":"file","height":223}}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Considerations ","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"An automated task has to be set-up for each form or case type","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"For projects with an increasing case or form load, schedule the tasks in such a way that they do not run concurrently as this may cause timeout errors related to the SQL database ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"More details on scheduling a task with the task scheduler can be found ","type":"text"},{"text":"here","type":"text","marks":[{"type":"link","attrs":{"href":"https://docs.microsoft.com/en-us/troubleshoot/windows-server/system-management-components/schedule-server-process#schedule-the-task"}}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Troubleshooting CommCare Data Export Tool Errors","type":"text"}]},{"type":"table","attrs":{"layout":"full-width","width":1800.0,"localId":"e3c72a12-3ddf-4a20-997f-c8a66e4c6be7"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"background":"var(--ds-background-accent-gray-subtlest, #F4F5F7)","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Error Message and Plausible Root Cause ","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"background":"var(--ds-background-accent-gray-subtlest, #F4F5F7)","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Example Screenshot","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"background":"var(--ds-background-accent-gray-subtlest, #F4F5F7)","rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Steps to fix the Error","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Error","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"paragraph","content":[{"text":"401 Client Error: Unauthorized for url","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"paragraph","content":[{"text":"Plausible root cause","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"},{"type":"hardBreak"},{"text":"A 401 error code indicates that the request has not been successful due to the absence of correct authentication credentials. This could involve an incorrect username, password, or insufficient permissions. ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"wide","width":100.0},"content":[{"type":"media","attrs":{"width":1280,"id":"50ef7540-8de0-4491-9df4-8ed513bba9dc","collection":"contentId-2143955952","type":"file","height":431}}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Check that the username and API that was entered into the query command is correct ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If both login credentials are correct but the error persists, then check that the API was set to have access to the project space specified in the command. This can be checked under ","type":"text"},{"text":"API keys","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.commcarehq.org/account/api_keys/"}}]},{"text":" in the project column","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If the API key is for the specified project space, then check that no leading space was added when the API key was initially copied ","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"If all checks from step 1 to 4 fail, then ensure that the project space is on the correct subscription plan. DET is currently available from the ","type":"text"},{"text":"PRO plan ","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.dimagi.com/commcare/pricing/"}}]},{"text":"and higher ","type":"text"}]}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Error","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"},{"type":"hardBreak"},{"text":"Arithmetic overflow error converting expression to data type int","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"paragraph","content":[{"text":"Plausible root cause","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"paragraph","content":[{"text":"This occurs when a column of INT has reached its maximum of 2,147,483,647 and CommCare is unable to convert this to BIG for the SQL database ","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"wide","width":100.0},"content":[{"type":"media","attrs":{"width":893,"id":"df4170c0-35a7-4511-a086-8fd33f7a079b","collection":"contentId-2143955952","type":"file","height":280}}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"One possibility is that there is a str2num mapping in the \"Map Via\" column. Removing the str2num Mapping in the config file resolves the issue and the error disappears.","type":"text"}]}]}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Error","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"},{"type":"hardBreak"},{"text":"AttributeError: 'Connection' object has no attribute 'connect'","type":"text"}]},{"type":"paragraph","content":[{"text":"Plausible root cause","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"paragraph","content":[{"text":"An incompatible version of sqlalchemy is usually the cause of the error. ","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"align-start","width":85.0},"content":[{"type":"media","attrs":{"width":674,"id":"0668f783-15ad-4a3d-98d6-a7aeea9423dc","collection":"contentId-2143955952","type":"file","height":754}}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"The error seems to be related to the latest version of sqlAlchemy version 2.0 as per this ","type":"text"},{"text":"thread here","type":"text","marks":[{"type":"link","attrs":{"href":"https://stackoverflow.com/questions/75315117/attributeerror-connection-object-has-no-attribute-connect-when-use-df-to-sq"}}]},{"text":".","type":"text"},{"type":"hardBreak"},{"text":"Changing to a lower version of Alchemy using the below command might resolve the issue.","type":"text"}]},{"type":"paragraph","content":[{"text":" ","type":"text"}]},{"type":"paragraph","content":[{"text":"pip install --upgrade 'sqlalchemy<2.0'","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"paragraph","content":[{"text":"Error","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"},{"type":"hardBreak"},{"text":"Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535","type":"text"}]},{"type":"paragraph","content":[{"text":"Plausible root cause","type":"text","marks":[{"type":"strong"}]},{"text":":","type":"text"}]},{"type":"paragraph","content":[{"text":"This is related to the size of the table being exported to SQL.","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"mediaSingle","attrs":{"layout":"wide","width":100.0},"content":[{"type":"media","attrs":{"width":1280,"id":"8ffde3fe-3137-4f76-bd61-dd3e1365e3a7","collection":"contentId-2143955952","type":"file","height":521}}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1},"content":[{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"To fix this, try to limit the number of columns that a row has to 200, this error can start to occur when the dataset is too large.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Alternatively split the columns so that each table in the split has a maximum of 200 rows. Define a ","type":"text"},{"text":"one to one","type":"text","marks":[{"type":"link","attrs":{"href":"https://www.tutorialsteacher.com/sqlserver/tables-relations#one-to-one-relation"}}]},{"text":" relationship for the rows that have been split.","type":"text"}]}]}]}]}]}]}],"version":1}