How to view data in related tables

This topic explains how to view data in related tables with the help of Master-Detail Browser.

Master-Detail Browser is a user-friendly utility for retrieving and displaying data from interconnected tables. It functions based on relationships, where values in master rows define the display of associated detail rows. The tool also allows for sorting and filtering data.

Scenario: We need to determine the quantity of bicycles within the product category available in stock in each store. To view this information, we’ll use the data from three tables - production.categories, production.products, and production.stock in the BikeStores database.

1. On the ribbon, select Database > Master-Detail Browser.

2. From Database Explorer, drag the required tables to the Design view.

3. The first table you drag automatically becomes a parent table. To assign another table as the parent, select the table and click Set Master on the toolbar of the master-detail document. Alternatively, right-click the required table and select Set Master.

In our example, the production.categories table is the parent table.

Set the table as Master

When you drag the tables to the Design area, a relationship is automatically created if at least one table has a foreign key reference to the other table. In cases when the tables have not initially been linked, you can manually build the relationships between them. To do this, drag a column from one table to a column of another table you want to link.

4. By default, all columns are selected. Clear the corresponding checkboxes next to the columns you want to exclude from the result.

5. Optional: To limit the number of retrieved rows, use the Fetch Limit field at the bottom of the document.

6. Optional: To filter the retrieved rows by a specific criterion, specify the condition in the Where Condition field.

Note

When saving a master-detail document, the values you specified in the Fetch Limit and Where Condition fields are also kept.

Limit the number of retrieved rows and specify the filtering condition

7. On the toolbar of the document, click Browse Data or press F5.

8. The result will be displayed on the Data view with the parent table at the top and the detailed tables - below.

To view the available bikes by category, select the required category - for example, Electric Bikes. The lower table will display all accessible bikes. To find out how many of these bikes are in stock for each store, click the plus sign for the required bicycle and view the available amount in the production.stocks table.

For example, the availability of Sun Bicycles ElectroLite - 2021 for the Electric Bikes category would be: