cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
seadude
Level 10

Complex Collection of a Collection

Hello!

Looking for some insights on this one... not sure how its done and tried a few things that didn't work.
Scenario: I have a table with the schema below. I want to Collect a new collection of ONLY the first two items under the "coordinates" subtable.

 

{
    "type": "FeatureCollection",
    "metadata": {
        "generated": 1555194002000,
        "url": "https://earthquake.usgs.gov/fdsnws/event/1/query?format=geojson&starttime=2014-01-01&endtime=2014-01-02&latitude=47&longitude=-122&maxradiuskm=300&orderby=magnitude-asc",
        "title": "USGS Earthquakes",
        "status": 200,
        "api": "1.8.1",
        "count": 9
    },
    "features": [
        {
            "type": "Feature",
            "properties": {
                "mag": -0.33,
                "place": "27km NNW of Packwood, Washington",
                "time": 1388615267370,
                "updated": 1469215317030,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664496",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664496&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 2,
                "net": "uw",
                "code": "60664496",
                "ids": ",uw60664496,",
                "sources": ",uw,",
                "types": ",general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 9,
                "dmin": 0.01894,
                "rms": 0.08,
                "gap": 71,
                "magType": "md",
                "type": "earthquake",
                "title": "M -0.3 - 27km NNW of Packwood, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -121.7506667,
                    46.8501667,
                    1.436
                ]
            },
            "id": "uw60664496"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.19,
                "place": "22km S of Sequim, Washington",
                "time": 1388595112000,
                "updated": 1469215316190,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664386",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664386&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 1,
                "net": "uw",
                "code": "60664386",
                "ids": ",uw60664386,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 3,
                "dmin": 0.1492,
                "rms": 0.22,
                "gap": 306,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.2 - 22km S of Sequim, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -123.108,
                    47.881,
                    4.572
                ]
            },
            "id": "uw60664386"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.22,
                "place": "21km NNW of Amboy, Washington",
                "time": 1388536764680,
                "updated": 1469215315430,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664276",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664276&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 1,
                "net": "uw",
                "code": "60664276",
                "ids": ",uw60664276,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 9,
                "dmin": 0.08565,
                "rms": 0.1,
                "gap": 182,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.2 - 21km NNW of Amboy, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -122.5188333,
                    46.0926667,
                    13.97
                ]
            },
            "id": "uw60664276"
        },
        {
            "type": "Feature",
            "properties": {
                "mag": 0.43,
                "place": "19km E of Kelso, Washington",
                "time": 1388577684900,
                "updated": 1469215315950,
                "tz": -480,
                "url": "https://earthquake.usgs.gov/earthquakes/eventpage/uw60664346",
                "detail": "https://earthquake.usgs.gov/fdsnws/event/1/query?eventid=uw60664346&format=geojson",
                "felt": null,
                "cdi": null,
                "mmi": null,
                "alert": null,
                "status": "reviewed",
                "tsunami": 0,
                "sig": 3,
                "net": "uw",
                "code": "60664346",
                "ids": ",uw60664346,",
                "sources": ",uw,",
                "types": ",cap,general-link,geoserve,nearby-cities,origin,phase-data,",
                "nst": 13,
                "dmin": 0.06314,
                "rms": 0.15,
                "gap": 143,
                "magType": "md",
                "type": "earthquake",
                "title": "M 0.4 - 19km E of Kelso, Washington"
            },
            "geometry": {
                "type": "Point",
                "coordinates": [
                    -122.6526667,
                    46.1491667,
                    15.17
                ]
            },
            "id": "uw60664346"
        }
}

JUST THE FIRST TWO ITEMS in the "Coordinates" subtable. I want a Collection that looks like:

 

{id: 1, long: -122.6526667, lat: 46.1491667}
{id: 2, long: -122.5188333, lat: 46.0926667}
{id: 3, long: -123.108, lat: 47.881}

Tried so far:

ClearCollect(
    colSecondCollection,
    First(colFirstCollection.features).features.geometry).geometry
); 
// Results in a table of nested tables. I need the coordinates alone
// Also tried:

ForAll(
    colFirstCollection,
    ClearCollect(
        colSecondCollection,
        First(First(colFirstCollection.features).features.geometry).geometry.coordinates).coordinates
    )
);

// Error:  "this action is not allowed within a ForAll"

Anyone have some idears?

Thank yoU!

 

11 REPLIES 11
Highlighted
Super User
Super User

Re: Complex Collection of a Collection

@seadude 

You can get part of the way there with this formula:

ClearCollect(finalResults, 
   ForAll(Ungroup(colFirstCollection.features, "features"), 
     {id:Value.id, long:First(Value.geometry.coordinates.Value).Value, lat:Last(FirstN(Value.geometry.coordinates.Value,2)).Value}
  )
)

This will return the record table like you wanted - with one exception...the sequential ID numbering.  In the above formula, you will get the id that is in the original JSON payload not a sequence.

Trying to do sequential numbering in PowerApps is oddly challenging.

 

I hope this puts you on the right track.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Dual Super User
Dual Super User

Re: Complex Collection of a Collection

@seadude 

Clear(colSecondCollection);ForAll(colFirstCollection.features,Collect(colSecondCollection,features.Value));Clear(colThirdCollection);ForAll(colSecondCollection,Collect(colThirdCollection,Value.geometry))

 

This will give you the main collection colThirdCollection which will contain all the coordinates

image.png

 

Now, if you only need the first 2 coordinates then use 

First(colThirdCollection).coordinates.Value - First coordinates

Last(FirstN(colThirdCollection,2)).coordinates.Value - Second coordinates

 

And to fetch individual values for the coordinates

Example : coordinates: [
-121.7506667,
46.8501667,
1.436
]

 

First(First(colThirdCollection).coordinates.Value).Value    =  -121.7506667

Last(FirstN(First(colThirdCollection).coordinates.Value,2)).Value   =  46.8501667

Last(FirstN(First(colThirdCollection).coordinates.Value,3)).Value = 1.436

 

Hope this helps

seadude
Level 10

Re: Complex Collection of a Collection

Hi @RandyHayes and @RezaDorrani ,

Thank you both for the thoughtful replies. This was the easiest for me to implement:

 

ForAll(
    Ungroup(colEarthquakeResults.features, "features"),
        Collect(colEarthquakeLatLongs,
            {long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
)

It results in:

image.png

@RandyHayes, you were right! It is not easy to implement an incremental ID column in this case! Here's what I tried so far:

ClearCollect(colEarthquakeLatLongs,
    AddColumns(
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        ),
        "id",
        If(
            1 = 1,
CountRows(colEarthquakeLatLongs) + 1 ) ) );
ClearCollect(colEarthquakeLatLongs,
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {id: Text(CountRows(colEarthquakeLatLongs)) + 1, long: First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
    )
ForAll(
    Ungroup(colEarthquakeResults.features, "features"),
        Collect(colEarthquakeLatLongs,
            {id: Value(CountRows(Ungroup(colEarthquakeResults.features, "features"))) + 1, long: First(geometry.coordinates.Value).Value, lat: Last(FirstN(geometry.coordinates.Value,2)).Value}
        )
)
ClearCollect(colEarthquakeLatLongs,
    AddColumns(
        ForAll(
            Ungroup(colEarthquakeResults.features, "features"),
            {long:First(geometry.coordinates.Value).Value, lat:Last(FirstN(geometry.coordinates.Value,2)).Value}
        ),
        "id",
        If(
            !IsEmpty(First(First(First(colEarthquakeResults.features).features.geometry).geometry.coordinates)),
            Text(Value(varCounter) + 1)
        )
    )
)

 

I bet @mr-dang knows how to do this! 🙂

 

Super User
Super User

Re: Complex Collection of a Collection

@seadude 

Well, here's the loaded question...do you have any idea on how many records you anticipate?

If you have knowledge that there is a preset amount or at least an amount of rows that will never exceed a certain number, then you can alter the formula like this:

ClearCollect(colEarthquakeLatLongs,
             ForAll([1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],
                    If(Value<=CountRows(Ungroup(colEarthquakeResults, "features")),
                        {id:Value, 
                         long:First(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates).Value, 
                         lat:Last(FirstN(Last(FirstN(Ungroup(colEarthquakeResults, "features"), Value)).Value.geometry.coordinates,2)).Value
                        } 
                      )
                   )
            )

Of course, in this case, that gives you only a max of 30 sequential ID's...but you could change that if needed.

 

See if that fits better for you.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
seadude
Level 10

Re: Complex Collection of a Collection

Hi @RandyHayes ,

There is no way of knowing exactly how many results will be returned.

  • In this blog post, I found a fun way to create a large Numbers Table.
  • Without a way to programatically create a Value Table based on CountRows or the like, we'll always run into this issue. Bummer!

Ended up going with this MONSTER!:

 

Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);

Clear(colNumbersTable);

Clear(colRecordToCount);

ClearCollect(colNumTemp, varNumTable);

ForAll(colNumTemp,
    ForAll(colNumTemp,
        ForAll(colNumTemp,
            Collect(colRecordToCount,
                {
                    record: 1
                }
            );
            Collect(colNumbersTable,
                {
                    number: CountRows(colRecordToCount)
                }
            )
        )

    )
);

ClearCollect(
    colEarthquakeResults,
    earthquakeAPI.GETearthquakes(
        {
            format: "geojson",
            starttime: Today() - 1,
            endtime: Today(),
            latitude: Location.Latitude,
            longitude: Location.Longitude,
            maxradiuskm: 1000,
            orderby: "magnitude-asc"
        }
    )
);

ClearCollect(colEarthquakeLatLongs,
    ForAll(colNumbersTable,
        If(number <= CountRows(Ungroup(colEarthquakeResults.features, "features")),
            {
                id: number, 
                long: First(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates
).Value, lat: Last(
FirstN(
Last(
FirstN(
Ungroup(colEarthquakeResults.features, "features"),
number
)
).geometry.coordinates,2
)
).Value } ) ) )

...all to get an ID column in this Collection!:

image.png

@RandyHayesand @RezaDorrani , thanks for the Sunday help! Have a great week!

 

Super User
Super User

Re: Complex Collection of a Collection

@seadude 

I just hope we don't have that many earthquakes!!  Smiley LOL

Glad things are working now.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
mr-dang
Level 10

Re: Complex Collection of a Collection

Thanks for the help here @RezaDorrani @RandyHayes.

 

One thing I'll add. When I want to understand the structure a little bit more and figure out when I need to use First() Last() etc., I use a JSON viewer:

 

code beautify.png

 

This one is Code Beautify which is one of the prettier viewers. The collapsing helps to understand things.

Microsoft Employee
@8bitclassroom
seadude
Level 10

Re: Complex Collection of a Collection

@RandyHayes, hep meh! I'm missing the pattern here... Looking to do a similar thing, but this time I have a different schema. Instead of earthquakes, I want to take the results of a Fuzzy Search and plot pins on a map.

Heres the schema:

 

{
    "summary": {
        "query": "pub",
        "queryType": "NON_NEAR",
        "queryTime": 39,
        "numResults": 10,
        "offset": 0,
        "totalResults": 30,
        "fuzzyLevel": 1,
        "geoBias": {
            "lat": 47.56328695,
            "lon": -122.37870965
        }
    },
    "results": [
        {
            "type": "POI",
            "id": "US/POI/p0/8718662",
            "score": 2.575,
            "dist": 128.26041102796466,
            "info": "search:ta:840539001007346-US",
            "poi": {
                "name": "West Seattle Brewing Company",
                "phone": "+(1)-(206)-7086627",
                "categorySet": [
                    {
                        "id": 9376007
                    }
                ],
                "categories": [
                    "café/pub",
                    "microbrewery/beer garden"
                ],
                "classifications": [
                    {
                        "code": "CAFE_PUB",
                        "names": [
                            {
                                "nameLocale": "en-US",
                                "name": "microbrewery/beer garden"
                            },
                            {
                                "nameLocale": "en-US",
                                "name": "café/pub"
                            }
                        ]
                    }
                ]
            },
            "address": {
                "streetNumber": "4415",
                "streetName": "Fauntleroy Way SW",
                "municipalitySubdivision": "Seattle, Genesee, Fairmount Park",
                "municipality": "Seattle",
                "countrySecondarySubdivision": "King",
                "countryTertiarySubdivision": "Seattle",
                "countrySubdivision": "WA",
                "postalCode": "98126",
                "extendedPostalCode": "981262631",
                "countryCode": "US",
                "country": "United States",
                "countryCodeISO3": "USA",
                "freeformAddress": "4415 Fauntleroy Way SW, Seattle, WA 98126",
                "localName": "Seattle",
                "countrySubdivisionName": "Washington"
            },
            "position": {
                "lat": 47.56428,
                "lon": -122.37784
            },
            "viewport": {
                "topLeftPoint": {
                    "lat": 47.56518,
                    "lon": -122.37917
                },
                "btmRightPoint": {
                    "lat": 47.56338,
                    "lon": -122.37651
                }
            },
            "entryPoints": [
                {
                    "type": "main",
                    "position": {
                        "lat": 47.56412,
                        "lon": -122.37767
                    }
                }
            ]
        }
    ]
}

 

Problem: I can only get the first set of Lat / Longs from the Fuzzy Search results

Tried so far:

/*====================================================================
Create a numbers table for ForAll looping purposes
====================================================================*/

Set(varNumTable, [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]);

Clear(colNumbersTable);

Clear(colRecordToCount);

ClearCollect(colNumTemp, varNumTable);

ForAll(colNumTemp,
    ForAll(colNumTemp,
        ForAll(colNumTemp,
            Collect(colRecordToCount,
                {
                    record: 1
                }
            );
            Collect(colNumbersTable,
                {
                    number: CountRows(colRecordToCount)
                }
            )
        )

    )
);

/*====================================================================
Collect all nearby "pubs" within a 2mile (~3200 meter) radius
====================================================================*/

ClearCollect(
    colNearbySpots,
    azureMapTimezone.GETSearchFuzzy(1.0,1,3,
        {
            query: "pub",
            lon: Location.Longitude,
            lat: Location.Latitude,
            radius: 3200
        }
    )
);

/*====================================================================
Using the numbers table, create a Collection of all nearby spot Lat/Longs returned from 
the previous API call.

Thank god for the PowerApps forums!: https://powerusers.microsoft.com/t5/General-Discussion/Complex-Collection-of-a-Collection/m-p/267273#M78095
====================================================================*/
ClearCollect(colNearbySpotLatLongs,
    ForAll(colNumbersTable,
        If(number <= CountRows(Ungroup(colNearbySpots.results, "results")),
            {
                id:number, 
                long: First(First(colNearbySpots.results).results.position).position.lon,
                lat: First(First(colNearbySpots.results).results.position).position.lat
            } 
        )
    )
);

/*====================================================================
Using the table of nearby spot Lat/Longs and the numbers table, call the Azure Maps API 
to get a map image of these spots with pins, at each zoom level!
====================================================================*/
Clear(colNearbySpotMaps);

ForAll(
    Filter(colNumbersTable, number >= 10 && number <= 14),
    AddColumns(
        Collect(colNearbySpotMaps,
        azureMapTimezone.GetMapImageWithPins(
            1.0,
            600,
            600,
            {
                zoom: number,
                center: Concatenate(Text(Location.Longitude), ",", Text(Location.Latitude)),
                pins: Concatenate("default||", Concat(colNearbySpotLatLongs, lat & " " & lat, "|")),
                path: Concatenate("ra3200", "||", Text(Location.Longitude), " ", Text(Location.Latitude))
            }
        )
    ) ,
    "id",
    number
    )
);

ClearCollect(colNearbySpotMapsWithIDs,
    ForAll(colNumbersTable,
        If(number <= CountRows(colNearbySpots),
            {
                id:number,
                mapImage: colNearbySpotMaps
            }
        )
    )
)

I also tried First(Ungroup(colNearbySpots.results, "results").position).position.lon (/lat).

I end up with a lat/long list that only shows the first Result copied a bunch:

Screenshot from 2019-06-01 08-47-59.png
...and colNearbySpotMapsWithID is nested instead of a flat table:

image.png
image.png
I know this is a long post 🙂 . Hopefully the patterns that emerge for unfolding JSON will help others too.

Thank you!

Super User
Super User

Re: Complex Collection of a Collection

@seadude 

So, I breezed through some of the post because I had a question from the start.  I see only one record with a position record in it...is the assumption that you have multiple records in the results array?

I'll start there - I saw some issues with the remaining formulas, but we can cover that after I clarify the above as I don't want to start down a road that is on the road trail.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (930)