You can perform a join of two collections (tables) by using a Collect with AddColumn calls to add the columns from a related table. Here's an example:
Create a button, add the following to its OnSelect property to create the first collection:
ClearCollect(orders,
{ id: 1, date: Date(2016, 2, 9), customer: "John Doe" },
{ id: 2, date: Date(2016, 2, 15), customer: "Jane Roe" },
{ id: 3, date: Date(2016, 2, 22), customer: "Jean Poe" })
Next, create the second "table" by adding a new button with the OnSelect property set to the following expression:
ClearCollect(orderItems,
{ id: 1, orderId: 1, name: "Bread", quantity: 1 },
{ id: 2, orderId: 1, name: "Milk", quantity: 1 },
{ id: 3, orderId: 2, name: "Cheese", quantity: 1.25 },
{ id: 4, orderId: 2, name: "Ham", quantity: 1.5 },
{ id: 5, orderId: 2, name: "Bread", quantity: 1 },
{ id: 6, orderId: 3, name: "Soda", quantity: 1 },
{ id: 7, orderId: 3, name: "Salad", quantity: 1 })
At this point you'll have two collections (tables), in a 1:N relationship. To denormalize this relationship into a separate collection, you can collect all items from the 'N' side of the relationship, and add more columns from the '1' side, like with the expression below:
Collect(merged, AddColumns(orderItems,
"orderCustomer", LookUp(orders, id = orderId).customer,
"orderDate", LookUp(orders, id = orderId).date))
Now, before denormalizing the table, you can also consider splitting the visualization of the data into multiple parts. For this example, in the main screen you'd display all the orders (in a gallery), and if the user selects it, you'd navigate to a separate screen, where you can filter the orderItems collection (e.g., by the Gallery1.Selected.id) to only show the rows (items) from the selected order.
Carlos Figueira
