cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
djjonesy
Level: Powered On

Parse Data from API call Sharepoint list

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Parse Data from API call Sharepoint list

@djjonesy 

 

2019-08-13_15-21-39.png 

 

 

Regards,

Reza Dorrani

 

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

View solution in original post

Super User
Super User

Re: Parse Data from API call Sharepoint list

@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

 

 

 

View solution in original post

16 REPLIES 16
Super User
Super User

Re: Parse Data from API call Sharepoint list

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
Level: Powered On

Re: Parse Data from API call Sharepoint list

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? 

Super User
Super User

Re: Parse Data from API call Sharepoint list

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
Level: Powered On

Re: Parse Data from API call Sharepoint list

@RezaDorrani that sounds great but where exactly do I enter this? Smiley Very Happy 

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

 

FLOW Question.JPG

Super User
Super User

Re: Parse Data from API call Sharepoint list

@djjonesy 

can you share screenshots of prior flow actions 

 

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

djjonesy
Level: Powered On

Re: Parse Data from API call Sharepoint list

@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 Smiley Sad 

 

Thanks 

 

Total Flow Steps.JPGHTTP Call and JSON Schema.JPGMX Condition and SharePoint Update Item.JPGJSON response.JPG

Super User
Super User

Re: Parse Data from API call Sharepoint list

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
Level: Powered On

Re: Parse Data from API call Sharepoint list

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

 

 

Super User
Super User

Re: Parse Data from API call Sharepoint list

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
Level: Powered On

Re: Parse Data from API call Sharepoint list

@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

Super User
Super User

Re: Parse Data from API call Sharepoint list

@djjonesy 

 

2019-08-13_15-21-39.png 

 

 

Regards,

Reza Dorrani

 

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

View solution in original post

djjonesy
Level: Powered On

Re: Parse Data from API call Sharepoint list

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? 

Super User
Super User

Re: Parse Data from API call Sharepoint list

@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

 

 

 

View solution in original post

djjonesy
Level: Powered On

Re: Parse Data from API call Sharepoint list

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

Super User
Super User

Re: Parse Data from API call Sharepoint list

@djjonesy 

 

Yes so finally it worked !!! Smiley Happy

 

djjonesy
Level: Powered On

Re: Parse Data from API call Sharepoint list

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! Smiley Happy Thanks again. 

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020.

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 359 members 4,553 guests
Please welcome our newest community members: