Power Community

Power Community

Updating Array Property Values by Looking Up Another Array in Power Automate Cloud flow

Hi everyone,

In this article i will be sharing easy approach of updating an array by looking up another array values. Let’s see how ūüôā

Flow Design Logic Overview

Let me first give overview of the logic which i will be using here to update the arrays. So for example, let’s take two arrays¬†array1¬†&¬†array2. We need to update array1 using data from array2. So here array2 will be converted to XML format and then use xpath to compute whether the value is present for iteration of array1 in array2 (which is in xml format) and then updating array1 accordingly.

To understand the logic properly, An example is provided below:

Example 

Here an Instant Cloud flow will be created for demonstration purposes

Nived_Nambiar_0-1697301281829.png

Also we will be using two arrays Array1 and Array2  as shown below.

Array1

[
  {
    "EmpID": 1289,
    "EmpName": "John",
    "Location": ""
  },
  {
    "EmpID": 1360,
    "EmpName": "Jack",
    "Location": ""
  },
  {
    "EmpID": 1571,
    "EmpName": "James",
    "Location": ""
  }
]

Array2

[
  {
    "EmpID": "1289",
    "Location": "New York",
    "DOJ": "20/09/1997"
  },
  {
    "EmpID": 1361,
    "Location": "Canada",
    "DOJ": "12/03/2020"
  },
  {
    "EmpID": 1572,
    "Location": "London",
    "DOJ": "08/01/2000"
  }
]

Goal here is to update Array1 Location property value by lookup of Location property values from Array2 using EmpID as primary field.

So let’s see how to get it step by step !

Step 1: Use Initialize Variable actions to initialize two Array type variables РArray1 and Array2 as shown below:

Nived_Nambiar_1-1697301778105.png

Nived_Nambiar_2-1697301860062.png

Step 2: Use Compose action to create a JSON structure as below for Array2 variable. [ This would be used later for XML format conversion]

Nived_Nambiar_3-1697301972323.png

{
  "Root": {
    "data": @{variables('Array2')}
  }
}

Step 3: Use Select action to update the Location property of each element of the Array1 by lookup of Array2.

Nived_Nambiar_4-1697302204229.png

Since we are not updating EmpID and EmpName property of Array1, so we can keep the same value of them as in Array1 like below:

Nived_Nambiar_5-1697302354499.png

Nived_Nambiar_6-1697302418027.png

Let’s see how the Location property has been updated, which is the most important point to be discussed.

The below expression has been used to update the Location Property.

Nived_Nambiar_0-1697302571804.png

So let me denote the expression used for location update by [Expression1] for easy reference.

if(equals(length(xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))),0),'',xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))?[0])

Looking at this expression may seems to be complex at first sight, But let me make it easy by step by step :).

So overall the [Expression1] is an If function which checks for some condition, if it is true or false assign some values which would be output.

So let’s go through an important expression , which would be necessary to understand¬†[Expression1].

Let’s denote below expression as¬†[Expression2]¬†

xpath(xml(outputs('Compose')),concat('//data[EmpID=',item()?['EmpID'],']/Location/text()'))

[Expression2] is a xpath function which helps to compute the Location values from Array2 based on EmpID attribute and returns the result in array of Location values.

This expression uses Array2 in XML format¬†xml(outputs(‘Compose’))¬†by converting the json structure as in the Compose Action (Step 2). It uses XPath¬†concat(‘//data[EmpID=’,item()?[‘EmpID’],’]/Location/text()’)¬†¬†which helps to compute Location node values from Array2 (in XML format) when its EmpID node text value equals the EmpID property value of Array1.

As said above, [Expression2] returns Location values in array format. So based on this we can go to [Expression1] to understand it better.

As said earlier ,¬†[Expression1]¬†is an If function. It checks whether the output of¬†[Expression2]¬†has elements in array or not, if there are no elements in array, it will assign Location property with value¬†. Else it will assign¬†xpath(xml(outputs(‘Compose’)),concat(‘//data[EmpID=’,item()?[‘EmpID’],’]/Location/text()’))?[0]¬†which is first element of output array of¬†[Expression2].

So overall flow looks like below:

Nived_Nambiar_0-1697306129984.png

So let’s run the flow and see its output of select action:

Nived_Nambiar_1-1697306236967.png

As you can see for the element with EmpID property 1289, Location value is updated with New York and since rest of employees data were not present in Array2 it was not updated. Hence the flow worked as expected.

This is basic example on how you can utilize XML while comparing/updating two arrays. You can extend same thing according to your usecases ūüôā

Hope this give you a clear idea on updating 2 arrays.

————————————————————————————————————————————–

Let me know your thoughts/doubts after going through this blog.

Thanks & Regards,

Nived N

This post was originally published on this site

- Advertisement -spot_img

LEAVE A REPLY

Please enter your comment!
Please enter your name here

- Advertisement - Advertisement

Latest News

Power Apps Licensing for Guest Users

Inviting Guests to Access your Power App which has Premium ConnectorsA pre-requisite of access to a Power App is...

More Articles Like This

- Advertisement -spot_img