cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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! 🙂

5 REPLIES 5
Super User III
Super User III

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

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
    )
);

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (4,529)