/
Power Automate for Data Migration

Power Automate for Data Migration

For advanced users: Learn how to use Power Automate to migrate data from other platforms into CommCare with example scripts, detailed steps, and troubleshooting tips.

Parent Cases

This flow facilitates the migration of Parent Cases from the SQL database to CommCare. The flow is integrated within Power Automate and utilizes multiple components to ensure the accurate transfer and transformation of data.

Flow Components

Trigger: The flow is manually triggered, allowing users to start the process on demand. This trigger is set up as a button in Power Automate, providing flexibility in scheduling migrations.

The following actions are part of the flow:

Get Rows from SQL Database:

  • Operation: This action retrieves rows from the SQL database, specifically targeting the [dbo].[households] table. It extracts all necessary data fields required for migration.

  • Connection: The connection is made using a predefined SQL Server connector.

XML Creation Loop:

This loop iterates over each row obtained from the SQL database. For each row, it performs the following actions:

  • Individual Row Transformation:

  • Update XML Action: Converts the data into an XML format suitable for CommCare. The XML structure includes essential elements like household_id, location, and case_id. The transformation ensures that each row is correctly formatted and ready for submission.

Paste the Raw XML copied previously for the household registration form submission in the input of the Compose action.

Here's a table format for the XML structure, breaking down each element and its details:

Element

Attribute

Description

Value/Example

Element

Attribute

Description

Value/Example

<data>

xmlns:jrm

Namespace for CommCare XForms

<http://dev.commcarehq.org/jr/xforms>

xmlns

Namespace for the form designer

<http://openrosa.org/formdesigner/4E761EC2-20EA-47EB-904E-557041DCAD1C>

uiVersion

User interface version

1

version

Form version

66

name

Name of the form

Household Registration

<household_id>

Unique identifier for the household

@{items('Individual_Row_Transformation')?['Household_ID']}

<location>

Location information for the household

@{items('Individual_Row_Transformation')?['Location']}

<n0:case>

xmlns:n0

Namespace for CommCare case transaction

<http://commcarehq.org/case/transaction/v2>

case_id

Unique identifier for the case

@{toLower(items('Individual_Row_Transformation')?['case_id'])}

date_modified

Date and time when the case was last modified

2024-07-17T16:29:41.762+05:30

user_id

User ID responsible for the case creation or update

3d33230ce23d4a7f85db80031e805c06

<n0:create>

Section defining the creation of a new case

<n0:case_name>

Name of the case

@{items('Individual_Row_Transformation')?['Household_ID']}

<n0:owner_id>

ID of the case owner

3d33230ce23d4a7f85db80031e805c06

<n0:case_type>

Type of the case

household

<n0:update>

Section including updates to the case

<n0:household_id>

Unique identifier for the household (updated)

@{items('Individual_Row_Transformation')?['Household_ID']}

<n0:location>

Location information (updated)

@{items('Individual_Row_Transformation')?['Location']}

<n1:meta>

xmlns:n1

Namespace for form metadata

<http://openrosa.org/jr/xforms>

<n1:deviceID>

Device ID used for form submission

Formplayer

<n1:timeStart>

Start time of form entry

2024-07-17T16:29:29.800+05:30

<n1:timeEnd>

End time of form entry

2024-07-17T16:29:41.762+05:30

<n1:username>

Username of the person submitting the form

migration1

<n1:userID>

User ID associated with the form submission

3d33230ce23d4a7f85db80031e805c06

<n1:instanceID>

Unique identifier for the form instance

@{guid()}

<n2:appVersion>

xmlns:n2

Namespace for application version

<http://commcarehq.org/xforms>

Formplayer version used

2.53

<n1:drift>

Time drift indicator

0

This table outlines each XML element, its attributes, descriptions, and example values. This structured format helps in understanding the composition and purpose of each part of the XML.

These values are dynamically filled into the XML structure using the placeholders @{items('Individual_Row_Transformation')?['FieldName']} where FieldName corresponds to the columns from the SQL database.

  1. Household_ID

  • Used for:

  • household_id

  • n0:case_name

  • n0:household_id

  1. Location

  • Used for:

  • location

  • n0:location

  1. case_id

  • Used for:

  • n0:case attribute case_id

 

  • Form Submission to CommCare:

This action submits the XML data to CommCare via an HTTP POST request. The request includes an Authorization key and a predefined URI for the CommCare receiver endpoint. This ensures the data is securely transferred and integrated into the CommCare system.

Child Cases

This flow automates the process of migrating child case data from an SQL database to CommCare. It handles the extraction, transformation, and submission of data in the correct XML format, ensuring that all child cases are accurately represented and linked to their parent cases.

Flow Components

Trigger: The flow is manually triggered, allowing users to start the process on demand.

The following actions are part of the flow:

Initialization and Variable Handling: Initializes and manages variables like prefixCounter, prefixCounter_parent, and combinedxmlstring for counting and concatenating XML data blocks.

Get Data from SQL and Retrieve Unique Parent Case IDs:

  • Get Rows from SQL Database: Retrieves rows from the [dbo].[household_members] table in the SQL database.

  • Select_Parent_Case_IDs: Extracts the parent_case_id from the retrieved SQL rows.

  • List_Unique_Parent_Case_IDs: Generates a unique list of parent case IDs.

Individual XML Generation for Submission: Iterates over each unique parent case ID and performs the following actions:

  • Filter_array: Filters rows based on matching parent_case_id.

  • Individual Case XML Blocks for Child Case: Processes each individual child case related to the parent case.

    • Add Individual Fields from SQL Rows: Adds specific fields like case_id, Name, Age, Gender, etc., to the XML.

    • Append to string variable: Appends the formatted XML string for each case.

Paste the Raw XML copied previously for the household_members registration form submission in the input of the Compose action. Select only the <household_member_details> block.

Here's a table format for the XML structure, breaking down each element and its details:

Element

Attribute

Description

Value/Example

Element

Attribute

Description

Value/Example

<household_member_details>

Root element for household member details

<n0:case>

xmlns:n0

Namespace for CommCare case transaction

<http://commcarehq.org/case/transaction/v2>

case_id

Unique identifier for the household member case

@{outputs('Lower_Case_(case_id)')}

date_modified

Date and time when the case was last modified

2024-07-24T15:09:29.318+05:30

user_id

User ID responsible for the case creation or update

3d33230ce23d4a7f85db80031e805c06

<n0:create>

Section defining the creation of a new household member case

<n0:case_name>

Name of the household member case

@{body('Parse_JSON_for_Generating_List')?['Name']}

<n0:owner_id>

ID of the case owner

3d33230ce23d4a7f85db80031e805c06

<n0:case_type>

Type of the case

household_member

<n0:update>

Section including updates to the household member case

<n0:age>

Age of the household member

@{body('Parse_JSON_for_Generating_List')?['Age']}

<n0:gender>

Gender of the household member

@{body('Parse_JSON_for_Generating_List')?['Gender']}

<n0:member_id>

Unique identifier for the household member

@{body('Parse_JSON_for_Generating_List')?['Member_ID']}

<n0:relation_to_head>

Relation of the household member to the head of the household

@{body('Parse_JSON_for_Generating_List')?['Relation_to_Head']}

<n0:index>

Section defining the index relationship

<n0:parent>

case_type

Type of the parent case

household

Parent case ID

@{outputs('Lower_Case_(parent_case_id)')}

<member_id>

Repeated identifier for the household member

@{body('Parse_JSON_for_Generating_List')?['Member_ID']}

<name>

Name of the household member

@{body('Parse_JSON_for_Generating_List')?['Name']}

<age>

Age of the household member

@{body('Parse_JSON_for_Generating_List')?['Age']}

<gender>

Gender of the household member

@{body('Parse_JSON_for_Generating_List')?['Gender']}

<relation_to_head>

Relation of the household member to the head of the household

@{body('Parse_JSON_for_Generating_List')?['Relation_to_Head']}

This table outlines each XML element, its attributes, descriptions, and example values. This structured format helps in understanding the composition and purpose of each part of the XML.

These values are dynamically filled into the XML structure using the placeholders @{items('Individual_Row_Transformation')?['FieldName']} where FieldName corresponds to the columns from the SQL database.

  1. Name: Used for:

  • n0:case_name

  • name

  1. Age: Used for:

  • n0:age

  • age

  1. Gender: Used for:

  • n0:gender

  • gender

  1. Member_ID: Used for:

  • n0:member_id