cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
WillPage
Level 8

Building an array from API lookups?

I have a requirement to build an HTML table with data from a RESTful API.

 

The first API call returns an object containing records. One of the columns is a resource ID, which is an integer.

 

That resource ID can be used to perform another API call to get the details of the resource.

 

So I want to create a table with the columns of Title, Description and resourceName, but I only know resourceName once I've grabbed the resource ID from the first HTTP GET then looked it up with a second one.

 

I already know how to get the resource names for each resourceID in the original object by putting the object through a for-each and doing an HTTP GET for each resourceID, but I don't know how to then add that as a column to the table generated by the original request.

 

Furthermore, the same resource ID might appear multiple times in the original object, but the apply-to-each method does a look up for each record in the object regardless whether it has been looked up already by a previous run through the for-each.

 

Consequently the Flow takes a long time to run, makes hundreds of API calls and generates a lot of network traffic - and that's before I even solved my problem.

 

What it feels like I need to do, is take the original object, then use it to generate a collection of distinct resourceIDs, then do the look up on those and cross-reference the two by resourceID.

 

Is that something I can do in Flow?

1 ACCEPTED SOLUTION

Accepted Solutions
WillPage
Level 8

Re: Building an array from API lookups?

Ok here goes:

 

1. HTTP GET to grab the array of whatever it is. In this case my HTTP GET result is an array of tickets from AutoTask. Each ticket has a few attributes that are IDs, and those IDs can be looked up with another HTTP GET.

 

So first is HTTP GET, then Parse JSON to get an array with the right schema.

 

Now we initialise an array variable. I've called it distinct_accountID. The reason I called it that is becuse it's going to be an array with distinct accountIDs from the tickets.

 

2. Now we loop through the original array of tickets (Parse JSON from earlier). The logic is here:

image.png

In this Apply to each, it checks if the current record's accountID already exists in the array variable. If it does (yes) then nothing happens, but if no, then it appends that value to the array variable. The result is distinct accountIDs in an array variable.

 

3. Next, I can use that array of distinct accountIDs to lookup the accounts in the API and build an array of accounts objects:

image.png

Append to accounts is an append to array variable action that's appending the result of Parse JSON 3 to an array variable called accounts.

 

Now I have two arrays, the body of Parse JSON from the original GET, and variables('accounts') which contains the accounts.

 

Now we have to match them up into a larger array:

 

4. Another Apply to each of the original Parse JSON action (the tickets). Within that, we filter the accounts array by the accountID of the current item. The result is an array but due to the way this Flow works it'll only ever have one record within it. Lastly, we use the Union() function to match the two.

 

The reason for the Select action is not only to eliminate unwanted attributes from the accounts array, but also because the union() action will overwrite any attribute in the left hand record that has the same name as one in the right hand record. In this case both the ticket and the account has an attribute called "id" and I want to filter out the id of the account to it doesn't destroy the ticket's id. If I wanted both I'd rename one with this Select action.

 

In Filter accounts array the id with the flow button icon is item()?['id']. The icon is a misleading bug, it's nothing to do with the trigger.

image.png

Now I have a single array called results with the ticket details and the account details.

 

There's a lot of looping involved, and in this example there's only accountID, but in production I actually have accountID and resourceID to lookup with two loops round the tickets and a more complex union() expression, including an If() because resourceID can sometimes be null and Union() doesn't like null.

 

Also the initial HTTP GET has cursor pagination that Flow doesn't support natively so there's some looping there to build an array.

 

It's slow, so you wouldn't want this with a PowerApps trigger and make the user wait (and why would you when PowerApps has way better tools for working with arrays). In my case it emails the result as an HTML table and adds them to an Excel spreadsheet too so time is not critical.

3 REPLIES 3
Highlighted
WillPage
Level 8

Re: Building an array from API lookups?

I've solved this with an epic series of apply to each loops and array variables. I'll post the solution for others who may be looking for the same thing.
Community Support Team
Community Support Team

Re: Building an array from API lookups?

Hi @WillPage,

 

Thank you for your contribution to the community!

If you have solved your issue, you can consider post your solution and then Accept your post as the solution to help the other members find it more quickly.

 

Best Regards,

Community Support Team _ Lin Tu

WillPage
Level 8

Re: Building an array from API lookups?

Ok here goes:

 

1. HTTP GET to grab the array of whatever it is. In this case my HTTP GET result is an array of tickets from AutoTask. Each ticket has a few attributes that are IDs, and those IDs can be looked up with another HTTP GET.

 

So first is HTTP GET, then Parse JSON to get an array with the right schema.

 

Now we initialise an array variable. I've called it distinct_accountID. The reason I called it that is becuse it's going to be an array with distinct accountIDs from the tickets.

 

2. Now we loop through the original array of tickets (Parse JSON from earlier). The logic is here:

image.png

In this Apply to each, it checks if the current record's accountID already exists in the array variable. If it does (yes) then nothing happens, but if no, then it appends that value to the array variable. The result is distinct accountIDs in an array variable.

 

3. Next, I can use that array of distinct accountIDs to lookup the accounts in the API and build an array of accounts objects:

image.png

Append to accounts is an append to array variable action that's appending the result of Parse JSON 3 to an array variable called accounts.

 

Now I have two arrays, the body of Parse JSON from the original GET, and variables('accounts') which contains the accounts.

 

Now we have to match them up into a larger array:

 

4. Another Apply to each of the original Parse JSON action (the tickets). Within that, we filter the accounts array by the accountID of the current item. The result is an array but due to the way this Flow works it'll only ever have one record within it. Lastly, we use the Union() function to match the two.

 

The reason for the Select action is not only to eliminate unwanted attributes from the accounts array, but also because the union() action will overwrite any attribute in the left hand record that has the same name as one in the right hand record. In this case both the ticket and the account has an attribute called "id" and I want to filter out the id of the account to it doesn't destroy the ticket's id. If I wanted both I'd rename one with this Select action.

 

In Filter accounts array the id with the flow button icon is item()?['id']. The icon is a misleading bug, it's nothing to do with the trigger.

image.png

Now I have a single array called results with the ticket details and the account details.

 

There's a lot of looping involved, and in this example there's only accountID, but in production I actually have accountID and resourceID to lookup with two loops round the tickets and a more complex union() expression, including an If() because resourceID can sometimes be null and Union() doesn't like null.

 

Also the initial HTTP GET has cursor pagination that Flow doesn't support natively so there's some looping there to build an array.

 

It's slow, so you wouldn't want this with a PowerApps trigger and make the user wait (and why would you when PowerApps has way better tools for working with arrays). In my case it emails the result as an HTML table and adds them to an Excel spreadsheet too so time is not critical.

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: 191 members 5,080 guests
Please welcome our newest community members: