[Power Automate] Child Cases Flow Configuration

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

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

  • member_id

  1. Relation_to_Head: Used for:

  • n0:relation_to_head

  • relation_to_head

  1. parent_case_id: Used for:

  • n0:parent element in the n0:index section

  1. case_id: Used for:

  • n0:case attribute case_id

  • Outer XML_Data_Mapping: Maps data for each parent case block, transforming it into XML format. This includes the parent case update, XML header and XML footer.

The rest of the flow uses logic and conditions to iterate and increment node values, parse JSON data and create structured XML body for submission to CommCare.

Parent Case Update Block

Element

Attribute

Description

Value/Example

Element

Attribute

Description

Value/Example

<n3:case>

Represents a case element in CommCare

xmlns:n3

Namespace for CommCare case transaction

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

case_id

Unique identifier for the parent case

@{body('Parse_JSON')?['parent_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

These values are dynamically filled into the XML structure using the placeholders @{body('Parse_JSON')?['parent_case_id']}where parent_case_id corresponds to the column from the SQL database.

  1. parent_case_id

  • Used for:

  • n3:case attribute case_id

XML Footer Block

Element

Attribute

Description

Value/Example

Element

Attribute

Description

Value/Example

<n4:meta>

xmlns:n4

Namespace for form metadata

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

<n4:deviceID>

ID of the device used for form submission

Formplayer

<n4:timeStart>

Start time of form entry

2024-07-24T15:07:44.140+05:30

<n4:timeEnd>

End time of form entry

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

<n4:username>

Username of the person submitting the form

migration1

<n4:userID>

User ID associated with the form submission

3d33230ce23d4a7f85db80031e805c06

<n4:instanceID>

Unique identifier for the form instance

@{guid()}

<n5:appVersion>

xmlns:n5

Namespace for application version

<http://commcarehq.org/xforms>

Application version used for the form submission

Formplayer Version: 2.53

<n4:drift>

Time drift indicator

0

XML Header Block

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/613315C3-833E-4D9D-ABDC-9370C14001B1>

uiVersion

User interface version

1

version

Version of the form

63

name

Name of the form

Household Member Registration

  • Final XML for Submission: Combines the XML header, case data, and footer into a final XML string for submission.

This action concatenates the value of each of the above XML blocks to create the final XML body for submission to CommCare.

  • Form Submission to CommCare: Submits the final XML data to CommCare via an HTTP POST request, including Authorization headers.

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. Ensure that the xmlstring is reset after the submission.