Example Data Structure
To demonstrate, let's take the following two tables which provide a typical example of a parent-child data structure. A 'Customer' table stores customer details and a region. The 'Orders' table displays the orders that are related to each customer.
In the remainder of this post, we'll walk through how to display this data in a gallery control and how to show the sum of the order prices by customer.
Showing parent records in a gallery
To begin, we'll add a gallery control to display the customer details. To do this, we add a gallery control and set the Items property to the customer table, as shown in the screenshot below.
To display the related order records for each customer, we add a nested gallery inside the customer gallery. In this example, the name of the gallery is galOrders.
To do this, we set the Items property of the gallery to the following formula. This filters the gallery to return Orders where the CustomerID of the Customer record matches the CustomerID in the Orders table.
From the nested galOrders gallery, we can add a label and display the Price field. As the screenshot beneath highlights, we can format the price value with a currency symbol and to display 2 decimal places with the following syntax:
How to show a subtotal of gallery values
To display the sum of the order prices grouped by customer, we can add a label to the parent galCustomers gallery. We can then use the following syntax to calculate the sum.
With this formula, we call the Sum function against the datasource galOrders.AllItems. For each row in galCustomers, galOrders.AllItems returns the related order records since galOrders is filtered by CustomerID.
We can further adapt the formula to format the sum as a currency value like so:
The screenshot below shows the final result.