Hi Guys,
I am back on the MS Flow learning and I am working on some fairly heavy flows.
One item I am getting stuck with is when I call an API that has several results all nested with the same tag.
The details:
I am calling an API from an external cloud controller which is returning a response.
I am then parsing the JSON response and can see the data.
I want to add all devices with a model of "MX" in the list to a SharePoint list initally showing each devices Serial Number.
I am struggling to understand how to do this. I feel an Array or Select is needed but need some help.
The parsed JSON format is below:
[
{
"lat": 0.0,
"lng": -0.0,
"address": "BLAH",
"serial": "D123-D123-D123",
"mac": "aa:aa:aa:aa:aa",
"wan1Ip": "123.123.123.123",
"wan2Ip": null,
"lanIp": "123.123.123.123",
"tags": " #MX250 ",
"networkId": "123",
"name": "BLAH",
"model": "MX250"
},
{
"lat": 0.0,
"lng": -0.0,
"address": "BLAH",
"serial": "D124-D124-D124",
"mac": "aa:aa:aa:aa:aa",
"wan1Ip": "123.123.123.123",
"wan2Ip": null,
"lanIp": "123.123.123.123",
"tags": " #MX250 ",
"networkId": "123",
"name": "BLAH",
"model": "MX250"
},
]
I can get the filter working to pull just model data with "MX" in the title, however it only filters in the data for the last serial and not both.
So this means my Sharepoint list has 1 serial number repeated twice.
How do I take the filtered data and enter the different serial numbers into the "Serial number 1" and "Serial number 2" columns in a SharePoint list?
Thanks
Solved! Go to Solution.
@Anonymous
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@Anonymous
The update item action will be right after the compose action (no need for any loops)
in update item axction for your 2 columns
use body('Filter_array')?[0]?['serial']
and body('Filter_array')?[1]?['serial']
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Anonymous
I am assuming your JSON response is always an array with 2 objects
then in this case you can use the following expressions
dynamiccontentresponsebody?[0]?['serial']
and
dynamiccontentresponsebody?[1]?['serial']
the dynamiccontentresponsebody is the dynamic content in flow for the output of yor JSON request
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Thank you @RezaDorrani
Sadly most times the response array has 30+ objects.
However as I am using a condition to filter on "MX" would the formula still work? Or would I need to do something else prior to this?
Hi @Anonymous
You surely can
but once you query on MX will you get only 2 items in array JSON response
if not then you can also run a loop on dynamiccontentresponsebody which is the JSON object you showcased
and then within loop
use expression
item()?['serial']
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@RezaDorrani that sounds great but where exactly do I enter this? 😄
When I enter the expression its say invalid? I suspect this is to do with me rather than the command!
I want to get the data into the field that says "MX Serial Pri" and "MX Serial Sec".
Thanks
@Anonymous
can you share screenshots of prior flow actions
also can you share screenshots of flow in run mode especially the json response
@RezaDorrani sure but obviously a lot of blanks to hide sensitive info.
When I set the expression of
item()?['serial']
I still get the same serial twice so I am clearly missing a step above 😞
Thanks
Hi @Anonymous
Ok so firstly, you are calling the api in a for loop
then doing a parse json which returns the array object
Next you are also looping the array object and if model contains MX you are updating the SharePoint item
But problem is the same SharePoint item will keep getting updated and it will probably end up with that item being updated with the last matching case in the array
question is what do want to do?
you said you have 2 serial number cols which you want to populate
but your array returns multuple objects
how will we know which 2 items in the array contains the serial numbers u need?
In each network there are only ever 2 MX devices.
Each network has its own unique ID so my thinking was that using the condition of model contains "MX" it would filter out the 2 Serial numbers and then loop between networks.
I can get the serial from the first device if I add the dynmaic field of "serial" into the sharepoint populate field. This works for each network. What I am unable to do is get the second serial into the second Sharepoint column as it also has the same "tag" in the JSON of "Serial".
Hi @Anonymous
So instead of runnin that second for loop on body of Parse JSON
try using the Filter data operation and filter your results to only show ones which have MX
then you will get a new array which only contains MX based results (which would be array contianing 2 records)
Next you an directly update item in sharepoint using
filterarrayoutput?[0]?['serial']
filterarrayoutput?[1]?['serial']
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@RezaDorrani thank you. Would you mind sharing a screenshot of how this should look? I looked at filter early and was unable to work pout the logic behind it - still learning.
Thank You
@Anonymous
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Ah! I was missing compose! Sorry.
OK so I have added as you have labelled.
In the SharePoint column do I now enter item()?['serial'] exactly like that to complete the process?
@Anonymous
The update item action will be right after the compose action (no need for any loops)
in update item axction for your 2 columns
use body('Filter_array')?[0]?['serial']
and body('Filter_array')?[1]?['serial']
Regards,
Reza Dorrani
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Superb!! Thank you @RezaDorrani - I have been messing with this for 2 hours and its now working.
@Anonymous
Yes so finally it worked !!! 🙂
Yes it sure is!
I am sure this wont be the last time I need help with some logic.
But I have certainly gained some insight into looping outside of where I thought to loop! 🙂 Thanks again.
Check out new user group experience and if you are a leader please create your group
See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.
User | Count |
---|---|
50 | |
41 | |
39 | |
36 | |
34 |
User | Count |
---|---|
68 | |
65 | |
65 | |
54 | |
49 |