Creating master-detail relationships (overview)

Topic group

A master-detail form or report displays information selected from one or more related tables in a relational database. It groups the detail rows from the detail tables in relation to an associated row from the master table.

In a relational database, a master table can be linked to one or more related (detail) tables by key fields. A detail table may in turn act as a master table, with other key fields linked to other detail tables. Each detail table contains a masterRowset property pointing to its master table. You can implement a master-detail relationship between tables by setting this property in the detail tables.

A typical example is a CUSTOMER table with a key field called Orders. You could link it to an ORDERS table by setting the ORDER table’s masterRowset property to the master CUSTOMER table. You could then generate a report on a selection of customers (from the master table CUSTOMER) that lists the rows of each customer’s orders (from the detail table ORDERS). The result groups each customer’s orders with each customer’s name.

By creating a master-detail relationship and adding SQL statements to the Rowset or Query object properties, you can create forms and reports that group detail rows from detail tables with a selection of rows from the master table. For example, a report could include a filter on the ORDERS rowset to display a customer’s orders only for the month of March. You can create complex filtered joins and perform virtually any programmatic operation on a database.

Enabling a linked-detail rowset's navigateMaster and navigateByMaster properties allows master-detail rowsets to be navigated as though they were part of a single, combined rowset (similar to the xDML SET SKIP command).

Note: Using these properties will modify the behavior of the first( ), next( ), last( ), atfirst( ) and atlast( ) methods. For more information, see navigateByMaster.

This section includes three different procedures to link master and detail tables:

1. Use an SQL JOIN statement to generate a rowset from two or more tables. This procedure is often the fastest and easiest. It is illustrated in the AIRCRAFT.REP report in the SAMPLES directory.

2. For local BDE-standard tables, use the Rowset object’s masterRowset and masterFields properties.

3. For client/server databases, use the Query object’s masterSource property. (You can also use this in local tables.)

In general, for any procedure, you begin with these steps:

• Drag the tables from the Navigator to the visual design surface of the designer you’re working in.
  This creates a Query object for each table.