Wednesday 15 January 2020

How to avoid Apply to Each from appearing

As a Power Automate maker you are happily building your flows in Power Automate and then suddenly after selecting dynamic content as a value in one of your actions, the Apply To Each appears. You think you’ve done something wrong so you delete the action and try again. The Apply To Each still appears!

Well my friend, you’ve come to the right place because in this WTF episode I can help you understand why this occurs and when you can avoid the Apply to Each from appearing. The first half of this episode is from the perspective of a CDS action however is applicable to the equivalent SharePoint action as well. In case you aren't aware, my background is with Dynamics 365 and my experiences with the Apply to Each has arisen from developing solutions for business use cases in Power Automate with flow.

Why or How does it appear

I have experienced the Apply To Each appearing when I select dynamic content from a CDS List Records action or an HTTP action. This is due to the JSON response as it is in the form of an array.

The way I like to explain an array to an everyday person is to think of it as a structured collection of data. Think of a table in an Excel worksheet. A table is structured where there are columns and rows of data. Each row would be an index and in each row there will be property values associated as reflected by the columns.

When a property is referenced from an array in an action, flow in Power Automate recognizes it is associated to an array and therefore the Apply to Each appears automatically. Flow in Power Automate will have the Apply to Each cocoon your action as it thinks the action is for each array.

In this WTF episode I’ll refer to the two common causes, in my opinion. There can be different causes but these are the two I tend to come across.

CDS List Records action – Cause No.1

There are times when a CDS List Records action is used and only a single record is expected to be returned. When using a List Records action there are multiple records expected to be returned in the result so the structure of the JSON response accommodates this in the form of an array.

An example is from a previous WTF episode where a CDS List Records action (the action in my Power Automate with flow is the 1.5 Get Time Zone Name of User) is used against the Time Zone Definitions entity that contains the time zone information used by CDS/Dynamics 365. Each array in the response will represent the different time zones. One of the properties in each array is standardname which is a value that can be referenced in expressions that require a time zone value, such as the Convert Time Zone action.

In my action I used a filter query to only return the time zone definition record that is equal to the time zone code of the CDS/Dynamics 365 user. This was so that only one record is returned rather than all time zone definitions as I’m only after the standardname property to identify the time zone name of the CDS/Dynamics 365 user to use downstream.

When you try use dynamic content to reference the standardname property in a subsequent action, the Apply to Each will appear which is what was seen in the Convert Time Zone action.


When you review the output of a List Records action it will be in the form of an array. The clue is from the square brackets [] that you’ll see in the output.

Parse JSON action – Cause No.2

Sometimes the Parse JSON action is used after an HTTP action in order to reference properties downstream. I’ll refer to my Power Automate with flow from this WTF episode as an example.

In my Power Automate with flow the goal is to reference the windowsTimeZoneId property downstream. The JSON response of the HTTP action is structured in the form of an array, similar to a CDS List Records action.

If we use the Parse JSON technique, we have to jump through a number of Apply to Each records to get to the windowsTimeZoneId because this property is buried within arrays.

Assess and choose a technique

I’m going to separate this section into two explanations in reference to the previous examples provided.
  • The first part will be in regards to a simple property where it is not buried in arrays.
  • The second part will be in regards to a complex property where it is buried in arrays.

When a simple property is to be referenced

Option one

When a single record is expected to be returned in the CDS List Records action or even an HTTP action, an expression can be used either directly in a field within an action or can be used in a Compose action. I'll use the Flow in my previous WTF episode as an example where the standardname property is to be referenced from a CDS List Records action that retrieves the time zone details of a user.

The key here is to use the first function in the expression. It will look something like this,


To explain this expression I’ll describe it in the way that my friend John Liu did when he helped me understand for my learning.

1.Start with outlining the array that is to be referenced. This is selected through the dynamic content Flow builder using the value output from the CDS List Records action - 1.5 Get Time Zone Name of User from the action (from my previous WTF episode).

body('1.5_Get_Time_Zone_Name_of_User')?['value']

2. Since we are only expecting one record to be returned in the List Records action, the next part of the expression is to wrap the first function around the output. If I go back to the Excel example from the beginning of this blog post, we are only going to reference the first row.

first(body('1.5_Get_Time_Zone_Name_of_User')?['value'])

3.The final part in the expression is the property to be referenced from the first row/record in the array which is standardname. This will be at the end of the expression.

first(body('1.5_Get_Time_Zone_Name_of_User')?['value'])?['standardname']

Bonus tip

The ? makes each property in the expression optional. For example if standardname is missing, the expression will return null and will not cause the flow in Power Automate to crash.
Using an expression either directly in an action or referencing from a compose action is suitable when you are only referencing a single property/field or let’s say two properties downstream in the Flow.

Option Two

Another way of writing an expression is the following

outputs('1.5_Get_Time_Zone_Name_of_User')?['body']?['value'][0]?['standardname']

This will retrieve the first row in the array of the JSON response from the [0] and the standardname property.

When would these options not be suitable

If you are going to be referencing a properties that aren't as straight forward, refer to the next section for another method.

When a complex property is to be referenced

Below is a screenshot of the JSON response in the HTTP action that I referenced earlier in the “Parse JSON action – Cause No.2” section. This is where we want to reference the windowsTimeZoneId downstream in my flow with Power Automate and the property is buried in arrays. To help with your understanding of the issue refer to the following screenshot.


You can see the windowsTimeZoneId property is buried in arrays. I’ll work backwards to help you with your understanding. The windowsTimeZoneId is within the timeZone array, which lives within the timeZoneAtLocation array, which lives in the resources array, which lives in the resourceSets array. Therefore Power Automate thinks the proceeding action is for each of the arrays within the output of the HTTP action.

The expression to use will be the following as per the above explanation where the first row in each array is represented by [0]

Summary

When you have a property to reference downstream and it’s in a simple structure within the JSON response, such as a CDS List Records action, use an expression that will either use the first function in the expression or has a reference to the row in the array. Otherwise if you're referencing a property that is not as straight forward where it may be buried in an array Option Two is better. These are the most two common scenarios I come across as a Power Automate maker working with Dynamics 365/CDS.

You can also check out John Liu’s blog post that mentions the Apply to Each appearing phenomenon, refer to Problem 2 - the array - item object and auto apply-each wrapping.

0 comments:

Post a Comment