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

[zdames]

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:

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!  

Appreciate everyone's help! 🙂

[timl]

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]

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)

[zdames]

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]

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?

[cattre]

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