[Power Automate] Parent Cases Flow Configuration

Owned by Rishabh Rath
Last updated: Aug 27, 2024
3 min read

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.

Â