Microsoft Flow: Using data returned from N:N relationship for further action

In the previous blog, we saw how to retrieve N:N records i.e. we retrieved the related “Rate” records of a particular “Revenue Schedule Line”. In this blog, we’ll see how to process the data returned for further action.

From our previous example, we wanted to loop through each related “Rate” record of “Revenue Schedule Line” returned and update “End Date” field if it doesn’t have any value. Below are the steps we followed:

Below “Retrieve related Rates”, click on Add Action –> Search for “apply” –> Select “Apply to each action” from the result as shown:

Select an output from previous step on Apply to each action depends on what result we are getting in the previous step. We are getting the below result in JSON format from the “Retrieve related Rates” step:

{
“@odata.context”: “https:// [urlPart] .azure-apim.net/apim/commondataservice/8d17407c-7925-4ae4-a48b-47fc21a62aac/$metadata#datasets(‘ [orgname] .crm [version] ‘)/tables(‘dxc_revenuescheduleline_dxc_rateset’)/items”,
“value“: [
{
“@odata.id”: “https:// [orgname] .crm [version] .dynamics.com/api/data/v9.0/dxc_revenuescheduleline_dxc_rateset(5af644ba-a8c7-e911-a868-000d3a37207b)”,
“@odata.etag”: “”,
“ItemInternalId”: “5af644ba-a8c7-e911-a868-000d3a37207b”,
“dxc_revenuescheduleline_dxc_rateid“: “5af644ba-a8c7-e911-a868-000d3a37207b”,
“dxc_rateid“: “54f644ba-a8c7-e911-a868-000d3a37207b”,
“dxc_revenueschedulelineid“: “a75964c3-10c2-e911-a851-000d3a3627b3”,
“versionnumber”: 5942672
}
]
}

Since we are getting “value” in the output, we can use that to loop through the result set. Hence, the expression for Select an output from previous step becomes body(‘Retrieve_related_Rates’)?[‘value’].
NOTE: Please mark the null check operator (?) in the expression which we should not forget to put. Otherwise while executing Flow we will get null reference error.

As we know, in the intermediate entity of N:N relationship the data stored is GUIDs of both the related records, in our case being dxc_rateid and dxc_revenueschedulelineid, returned in the result above in JSON, we can use dxc_rateid to retrieve corresponding Rate record and check if End Date has any value or not.
Hence, the expression becomes items(‘Apply_to_each’)?[‘dxc_rateid’] for the item identifier.
NOTE: The steps “Get Record”and “Condition 2” are optional. We required these 2 steps as we wanted to check the value of End Date field before updating it.

Add Update a record action and use the expression items(‘Apply_to_each’)?[‘dxc_rateid’] as Record identifier. Put the value or expression for End Date field. In our case, we used addDays(triggerBody()?[‘dxc_enddate’],1). Once done, Save the flow.