cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zdames
Advocate I
Advocate I

How to extract a row from a collection within a collection.

Hi everyone, I'm working on a POC that extracts data from an API,

 

now whenever I get my data, it results into a collection within a collection (APICollect is the collection, 'd' is a column that contains the 'results' record). As shown:

Capture.PNG

I believe you can get the row of a collection inside a collection, but in this case, it's 3 layers of collections. I was hoping to extract these set of data only. Tried searching through discussions but wasn't able to find answers and it's making me go crazy! Man LOL 

 

Appreciate everyone's help! 🙂

6 REPLIES 6
timl
Super User
Super User

Hi @zdames

APICollect is a collection that can contain multiple records and I assume that your API call only  returns a single row. Therefore, something on the lines of the following should return your child collection:

First(APICollect).d
Mike8
Memorable Member
Memorable Member

Hello zdames,

I agree with timl.
First(APICollect).d will return the data.
In order to select a specific record you can use the First , Last and LookUp functions.
Examples:

First(First(APICollect).d)
Last(First(APICollect).d)

LookUp(First(APICollect).d,ID=2)                 (If the there is an ID column)

 

Edit: I can see that there is also an extra nested table called results,
You can try the following to return the first record of the results table:
LookUp(LookUp(LookUp(APICollect,true,d),true,results),true)


You can change ,true, to a condition like ID=2, to retrieve the record that you need.
LookUp(LookUp(LookUp(APICollect,true,d),true,results),ID=2) or an other example
LookUp(LookUp(LookUp(APICollect,GUID="21321323fdgdfg21321321321",d),true,results),ID=2)


Thanks for the tip @timl! But unfortunately this only goes one step lower in my nested collection and wasn't still able to dig deeper. I need to atleast get to the 'results' table after 'd'

Hi @zdames

Sorry for not getting back to you sooner. @Mike8s suggestion looks as though it should work. 

LookUp(LookUp(LookUp(APICollect,true,d),true,results),true)

Did you ever resolve this problem?

Realise this is an old thread but as it hasn't been marked as resolved I thought I'd add a comment!

 

@Mike8's suggestion worked for me on a similar issue I was having (so thanks!). Basically I have a SharePoint list as the source and one of the columns is a multi-select people picker (i.e. a table within a table). I was trying to extract a single column list of the email addresses (from all people in each row).

 

The following looks to be working so far:

 

 

ClearCollect
(
    collection,
    LookUp
    (
        list,
        true,
        peopleField.Email
    )
)

 

 

UPDATE:

Spoke too soon. LookUp only returns the first record, so I had to update to the following:

 

Clear(collection);

ForAll
(
    list,
    Collect
    (
        collection,
        ThisRecord.peopleField.Email
    )
);

@cattre that did the trick! Thank you.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,141)