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! 🙂

5 REPLIES 5
timl
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
Mike8
Solution Sage
Solution Sage

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'

timl
Super User III
Super User III

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (66,935)