[Power Automate] Repeat Group Data Flow Configuration

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

This flow is critical for migrating complex repeat group data structures from a SQL database into CommCare. It ensures that each piece of data is correctly formatted, transferred, and linked within the CommCare system, maintaining data integrity and accuracy.

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 Rows from SQL Database (Visits): Retrieves visit-related data from the SQL database.

Filter Array: Filters data based on parent_case_id to ensure only relevant entries are processed.

Individual Case XML Blocks for Parent Case: Constructs XML blocks for each individual case, including details like visit_count, Visit_Date, Health_Score, Weight, and Vaccinated status.

Repeat Group Block: Compiles current repeat group data into a structured XML format.

Paste the Raw XML copied previously for the visits registration form submission in the input of the Compose action. Select only the <medication> 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

<medication>

Root element for medication details

<medicine_name>

Name of the medication

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

<dosage>

Dosage of the medication

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

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. Medication_Name

  • Used for:

  • <medicine_name>: The name of the medication.

  • Example Placeholder: @{body('Parse_JSON_for_Generating_List')?['Medication_Name']}

  1. Dosage

  • Used for:

  • <dosage>: The dosage of the medication.

  • Example Placeholder: @{body('Parse_JSON_for_Generating_List')?['Dosage']}

 

Current Case Block: Compiles current case data into a structured XML format.

Paste the Raw XML copied previously for the visits registration form submission in the input of the Compose action. Select only the form content and <subcase> 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

<visit_counter>

Counter for the number of visits

@{items('Apply_to_each')?['visit_count']}

<visit_date>

Date of the visit

@{items('Apply_to_each')?['Visit_Date']}

<health_score>

Health score of the individual

@{items('Apply_to_each')?['Health_Score']}

<weight>

Weight of the individual

@{items('Apply_to_each')?['Weight']}

<vaccinated>

Vaccination status of the individual

@{items('Apply_to_each')?['Vaccinated']}

<subcase_0>

Container for subcase details

<n0:case>

xmlns:n0

Namespace for CommCare case transaction

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

case_id

Unique identifier for the visit case

@{items('Apply_to_each')?['case_id']}

date_modified

Date and time when the case was last modified

2024-07-17T16:00:21.547+05:30

user_id

User ID responsible for the case

3d33230ce23d4a7f85db80031e805c06

<n0:create>

Section defining the creation of a new visit case

<n0:case_name>

Name of the visit case

@{items('Apply_to_each')?['visit_count']}

<n0:owner_id>

ID of the case owner

3d33230ce23d4a7f85db80031e805c06

<n0:case_type>

Type of the case

visit

<n0:update>

Section including updates to the visit case

<n0:health_score>

Updated health score

@{items('Apply_to_each')?['Health_Score']}

<n0:visit_date>

Updated date of the visit

@{items('Apply_to_each')?['Visit_Date']}

<n0:weight>

Updated weight

@{items('Apply_to_each')?['Weight']}

<n0:index>

Section defining the index relationship

<n0:parent>

case_type

Type of the parent case

household_member

Parent case ID

@{items('Apply_to_each')?['parent_case_id']}

 

Parent Case Block: Updates parent case information with relevant visit data.

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

<n1:case>

xmlns:n1

Namespace for CommCare case transaction

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

case_id

Unique identifier for the parent case

@{items('Apply_to_each')?['parent_case_id']}

date_modified

Date and time when the case was last modified

2024-07-17T16:00:21.547+05:30

user_id

User ID responsible for the case

3d33230ce23d4a7f85db80031e805c06

<n1:update>

Section including updates to the parent case

<n1:visit_counter>

Updated counter for the number of visits

@{items('Apply_to_each')?['visit_count']}

 

XML Header and Footer Blocks: Constructs the header and footer for the XML submission, including metadata such as device ID, timestamps, username, user ID, instance ID, and application version.

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.