Power Automate: Loop Through the Dataverse Child Records and Analyse input/output

Let’s see how we can loop through the child records returned from the Dataverse, and analyse the input / outputs at each step.

Following is the example scenario we will cover.

On deactivation of Account record we want to loop through the related opportunities.

Following are the steps we will cover:

Create Cloud Flow on Account Deactivate

Create a automated cloud flow.

From outside solution.

or from inside solution.

Select Microsoft Dataverse connector and choose trigger

“When a row is added, modified or deleted”

Because this flow will trigger on deactivate, which is update of account.

Update Trigger properties

Set the flow properties as following:

First of all rename the step to Account or anything else meaningful, to easily identify this step.

Then we need to fill step properties

  • Change type: Modified
    • This defines the on change type on which this flow will trigger.
    • Other options are as following
  • Table name: Accounts
    • This option defines on which table we want flow to run.
    • Any table can be selected
  • Scope: Organization
    • This defines the scope of flow, for which users this flow will trigger.
    • Select organization if this flow should trigger for all users.
  • Select columns: statecode
    • Specify comma separated list of columns.
    • Flow will trigger If any of them are modified.
  • Filter rows: statecode eq 1
    • We are specifying flow to run when statecode is 1, which is inactive status for account.
    • In this you specify OData style filter to determine eligible rows.
  • Run as: Modifying user
    • Specify under which user context flow will run.

So far, it should look like following.

Retrieve child opportunities

Next add the Dataverse action to list rows as following.

Rename the step to List opportunities, to identify the step.

Update the List Opportunities step properties as following:

  • Table name: opportunities
    • Specifies which table records we want to retrieve, opportunity in this case.
  • Select columns: name
    • Specify the columns we want to retrieve, it’s good idea to retrieve only the required columns. In this case we are retrieving name column only.
  • Filter rows: _parentaccountid_value eq [Account from Dynamics content]
    • Specify OData style filter to filter rows.
    • Here we have specified to retrieve only the opportunity rows with parent account id matching to triggering account record id.

We can specify more properties as needed, but for this example leaving as it is.

Analyse value, body and body/value – item from list rows step

List rows step returns following Dynamics content.

  • value: value returns the array of records from the specified table in json format.
  • body: body will return the same array of records along with some other properties in body.
  • body/value – item: this contains a single instance of the array item which is single opportunity record in this case.
    • notice when we add this, automatically Apply to each step will be added.

Let’s analyse each of them.

Add three compose steps for each of them

Notice, when we add body/value – item, automatically Apply to each step will be added, and compose step will be nested inside with current item dynamic content.

Add one more step inside apply to each to extract the opportunity id

Set the expression as following to get the opportunityid from opportunity.

This should appear like this, so far.

Update each opportunity record

Next, for example we want to update current opportunity record, current item in the loop. We can do as following.

Add a Dataverse – Update a row step inside Apply to each.

and set the Table name and Row ID properties as following.

Output is from opportunity id compose item in Dynamics content window.

Here we are specifying:

  • Table name: opportunity
    • We want to update the opportunity record.
  • Row ID: id of the opportunity record we want to update, current item id in this case.

We can update any other property as we want to update on this record and same will be updated in Dataverse.

It should appear like this by now,

Save this flow

Analyse flow input / output

To test the flow and analyse the input / output, deactivate an Account record having few opportunities.

Account step

on clicking Show raw inputs

We can see the parameters passed to Dataverse.

On clicking on Show raw outputs.

List of opportunities step

On clicking Show raw inputs

We can see the filter is applied while retrieving the list of opportunities.

On click to download, json. it we can analyse all the returned data from Dataverse.

{
    "statusCode": 200,
    "headers": {
        "Vary": "Accept-Encoding",
        "x-ms-service-request-id": "e20b3c32-6708-414a-8e2a-533514cdf8e4,78409faa-75a8-4dbe-a535-b289b3327575",
        "Cache-Control": "no-cache",
        "Set-Cookie": "ARRAffinity=a50f3777a483fd4e96aadf1d0e2923f38abbc01c3df5d6cb731bbcf61f629039; domain=org1f7aa4d6.crm11.dynamics.com; path=/; secure; HttpOnly,ReqClientId=68e2c649-02b0-4fcc-89e8-d274f8d5c7bc; expires=Wed, 12-Aug-2071 20:39:52 GMT; path=/; secure; HttpOnly,ARRAffinity=a50f3777a483fd4e96aadf1d0e2923f38abbc01c3df5d6cb731bbcf61f629039; domain=org1f7aa4d6.crm11.dynamics.com; path=/; secure; HttpOnly",
        "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
        "REQ_ID": "78409faa-75a8-4dbe-a535-b289b3327575",
        "AuthActivityId": "1c30924d-64e5-45eb-b690-a28fbdf484af",
        "x-ms-dop-hint": "4",
        "x-ms-ratelimit-time-remaining-xrm-requests": "1,198.57",
        "x-ms-ratelimit-burst-remaining-xrm-requests": "7995",
        "OData-Version": "4.0",
        "Preference-Applied": "odata.include-annotations=\"*\"",
        "X-Source": "1113514625493361791435986622061331061211582156719719922214047249116202212155240124105180,1113514625493361791435986622061331061211582156719719922214047249116202212155240124105180",
        "Public": "OPTIONS,GET,HEAD,POST",
        "Timing-Allow-Origin": "*",
        "Date": "Thu, 12 Aug 2021 20:39:53 GMT",
        "Allow": "OPTIONS,GET,HEAD,POST",
        "Content-Type": "application/json; odata.metadata=full",
        "Expires": "-1",
        "Content-Length": "1702"
    },
    "body": {
        "@odata.context": "https://org1f7aa4d6.crm11.dynamics.com/api/data/v9.1/$metadata#opportunities(name)",
        "#Microsoft.Dynamics.CRM.DeleteMultiple": {
            "title": "DeleteMultiple",
            "target": "https://org1f7aa4d6.crm11.dynamics.com/api/data/v9.1/opportunities/Microsoft.Dynamics.CRM.crmbaseentity/Microsoft.Dynamics.CRM.DeleteMultiple"
        },
        "@Microsoft.Dynamics.CRM.totalrecordcount": -1,
        "@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
        "value": [
            {
                "@odata.type": "#Microsoft.Dynamics.CRM.opportunity",
                "@odata.id": "https://org1f7aa4d6.crm11.dynamics.com/api/data/v9.1/opportunities(b052fc98-e8f0-ea11-a815-000d3a1b14a2)",
                "@odata.etag": "W/\"1774838\"",
                "@odata.editLink": "opportunities(b052fc98-e8f0-ea11-a815-000d3a1b14a2)",
                "name": "18 Airpot Coffee Makers for Northwind Traders",
                "opportunityid@odata.type": "#Guid",
                "opportunityid": "b052fc98-e8f0-ea11-a815-000d3a1b14a2"
            },
            {
                "@odata.type": "#Microsoft.Dynamics.CRM.opportunity",
                "@odata.id": "https://org1f7aa4d6.crm11.dynamics.com/api/data/v9.1/opportunities(2ec06197-31ec-ea11-a817-000d3a1b14a2)",
                "@odata.etag": "W/\"1773955\"",
                "@odata.editLink": "opportunities(2ec06197-31ec-ea11-a817-000d3a1b14a2)",
                "name": "2 Café Corto for Northwind Traders",
                "opportunityid@odata.type": "#Guid",
                "opportunityid": "2ec06197-31ec-ea11-a817-000d3a1b14a2"
            },
            {
                "@odata.type": "#Microsoft.Dynamics.CRM.opportunity",
                "@odata.id": "https://org1f7aa4d6.crm11.dynamics.com/api/data/v9.1/opportunities(3cbbd39d-d3f0-ea11-a815-000d3a33f3c3)",
                "@odata.etag": "W/\"1774047\"",
                "@odata.editLink": "opportunities(3cbbd39d-d3f0-ea11-a815-000d3a33f3c3)",
                "name": "5 Café BG-1 Pro Grinders for Northwind Traders",
                "opportunityid@odata.type": "#Guid",
                "opportunityid": "3cbbd39d-d3f0-ea11-a815-000d3a33f3c3"
            }
        ]
    }
}

Compose step – Value

Show raw inputs

Value Dynamic content contains all the returned rows.

Show raw outputs

Compose Step – Body

Show raw inputs

Body Dynamic content contains all the returned rows along with some other body properties.

Show raw outputs

Apply to each step

Show raw inputs

We can see a single opportunity record inside apply to each.

Apply to each – opportunityid

Apply to each – Update a row

Show raw inputs

We can see what parameters were passed as update request to Dataverse.

Show raw outputs

Conclusion

In this post we learned how we can loop through the list of child records. And on each step we analysed what are input/output to each step visualize what is going in and out.

Leave a comment