Blog

Data - How to access nested collections/tables

February 6. 2019

If you're struggling to access records in nested collections/tables, here's some example syntax.

A common question that arises is how to access collections or data sources with nested records. Here's a typical example. Here we have a collection called OrderDetails with the field names CustomerName and OrderItems. In this example, there are 3 records in this collection: Tim, Tom, and John.

Here is the content of the OrderItems field for the parent record "Tim". The field names are Item, OrderID, and Price. There are 2 rows in this nested collection.

To retrieve the first row in OrderItems for the first row in OrderDetails (eg, to retrieve the item 'Dell Laptop'), we would use the syntax:

First(First(OrderDetails).OrderItems).Item

To retrieve the first row in OrderItems for the user 'Tom' in OrderDetails, we can use the Lookup function like so:

First(Lookup(OrderDetails, CustomerName="Tom").OrderItems).Item

We can display Tim's OrderItems in a Gallery control by setting the Items property of the Gallery to:

Lookup(OrderDetails, CustomerName="Tim").OrderItems

To calculate a summary of the total price of Tim's order items, we can add a label and set the Text property to the following:

Sum(Lookup(OrderDetails, CustomerName="Tim").OrderItems,
    Price
)

This hopefully provides a gist of the syntax for accessing child collections/data sources. For more examples, here are some forum posts you can refer to.

https://powerusers.microsoft.com/t5/General-Discussion/Accessing-Nested-collectio/m-p/200450#M64625 https://powerusers.microsoft.com/t5/General-Discussion/How-to-extract-a-row-from-a-collection-within-a-collection/td-p/185680

https://powerusers.microsoft.com/t5/General-Discussion/How-to-extract-a-row-from-a-collection-within-a-collection/td-p/185680

https://powerusers.microsoft.com/t5/General-Discussion/Datetime-value-returned-from-custom-connector-not-able-to-be/td-p/163224