Parse Data from API call Sharepoint list

[djjonesy]

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

[RezaDorrani]

Hi @djjonesy 

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

[djjonesy]

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? 

[RezaDorrani]

Hi @djjonesy 

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

[djjonesy]

@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

[RezaDorrani]

@djjonesy 

can you share screenshots of prior flow actions 

also can you share screenshots of flow in run mode especially the json response

[djjonesy]

@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 

[RezaDorrani]

Hi @djjonesy 

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?

[djjonesy]

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".

[RezaDorrani]

Hi @djjonesy 

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

[djjonesy]

@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

[RezaDorrani · Accepted solution]

@djjonesy 

 

Regards,

Reza Dorrani

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[djjonesy]

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? 

[RezaDorrani · Accepted solution]

@djjonesy 

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

[djjonesy]

Superb!! Thank you @RezaDorrani  - I have been messing with this for 2 hours and its now working.

[RezaDorrani]

@djjonesy 

Yes so finally it worked !!! 🙂

[djjonesy]

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.