VLOOKUP Scenario in Azure LogicApps
Introduction
I want to write to Scenario on How to do vlookup using Azure LogicApps. Maybe, I have not did in a right way but I Just want to share how to do it with Logic Apps.
Scenario
We need to 2 Data Sources to compare, so we have used Json like below
1st Source:
{
"Details":[
{"name":"John", "age":23},
{"name":"lizzy", "age":23},
{"name":"stacey", "age":23}
]
}
2nd Source:
{
"employees": [
{
"age": 23,
"email": "ram@gmail.com",
"name": "Ram"
},
{
"age": 28,
"email": "shyam23@gmail.com",
"name": "Shyam"
},
{
"age": 33,
"email": "john@gmail.com",
"name": "John"
},
{
"age": 41,
"email": "bob32@gmail.com",
"name": "Bob"
}
]
}
As you Look at the Above, we will Compare the Names in both the Data Sources and if it matches we replace the Age with the Second Data Source. If it doesn’t match, we will keep the same.
We have used HTTP Request to get the First Data Source and for the sake of simplicity I have used Compose Action to get the Second Data Source.
Creation of LogicApp:
- I will Use the Event Triggered Logic App Like below using the HTTP Request to get the 1st Data Source
- We use the Compose Shape to have my Second Data Source.
- We used the Parse Json Action so that qw can just use all the elements pointing in the Designer Itself.
- We have initialised the Array Variable
- Then we have used the Foreach Loop to Loop the Details of my First Source, Inside it have used the Foreach Loop to Loop the Employees from my Second Data Source
- Inside it we dropped the Condition which will Check the name from Details of my First Source and name from the Details of Second Source.
- If its True, then we have drooped an Action and kept a Json Like below, if you see we are Passing the Json from our Second Source so that it has been replaced.
- Then we used the Parse Json Action for our Array so that we can use it later
- Then I used the Nested Foreach Loop so that we can Poll the Details from our Original First Source and array variable Like below
- Then we dropped the Condition to check the Name from the Original Data Source is not present in my Array Variable. If it’s not, Then we would just add the “Append to Array Varaible” Action Like below.
we are just taking age and Name from the Details of our First Source so that I could just get the Common rows.
- Outside the Foreach Loops, we just dropped the Response Action Like below ( To construct the Request).
- The Final Logic App will look like below
Testing
We have used the Post Man to test my Logic App like below
If you see the Name John Age has got replaced by the Second Source and the Rest of the elements remain the same.
Conclusion:
Setback for this Approach is since we have used too many Loops, it might not be viable if you have Large Data. Might be we have to try Liquid Templates or Azure Functions ( to have our own logic) to do it in that way
If you just want to get the common rows, Then its better to use Filter array rather than Foreach Loop as it reduces the Number of Actions in your Logic App. Below Article can Help you.
[https://toonvanhoutte.wordpress.com/2017/06/02/working-with-collections-in-logic-apps/
](https://toonvanhoutte.wordpress.com/2017/06/02/working-with-collections-in-logic-apps/)
Please share your Valuable Feedback.