Data - How to access nested collections/tables

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/Datetime-value-returned-from-custom-connector-not-able-to-be/td-p/163224