Master-Detail
- 5 minutes to read
ExpressQuantumGrid is capable of displaying data from several datasets. You can present data either from independent datasets in one control or datasets that are linked by a master-detail relationship. One-to-many relationships or master-detail relationships can be represented in one of two ways. The first method is to use two grid controls. A detail grid control displays only the records corresponding to the current record in a master table. In the second method only one grid control is used to present master-detail relationships. This method is specific to the ExpressQuantumGrid and employs grid levels to create a hierarchical data structure.
You can see, for instance, the MasterDetailTableDemo for a complete example of implementing a master-detail relationship. This topic describes the basic principles involved in setting up master-detail relationships based on the database used in this demo.
Data in this example is loaded using the default loading mode (when grid mode is not applied). Refer to the Grid Mode: Master-Detail topic for details on how to create a master-detail relationship between tables in grid mode.
ExpressQuantumGrid allows you to populate Views with data from non data-aware sources using provider and unbound modes. Refer to the Provider Mode: Master-Detail and Unbound Mode: Master-Detail sections to see how to set up master-detail relationships in these modes.
Database structure
Let us consider two tables that are shipped with the ExpressQuantumGrid demos: FILMS and FILMSPERSONSSTAFF. The FILMS table contains information on the films in the catalog and contains fields such as CAPTION, YEAR, PHOTO, TAGLINE, ID and others. ID is the table key field name.
The FILMSPERSONSSTAFF table describes people involved in film production. It also has an ID field uniquely identifying records in the table. Other fields are PERSONID, PERSONLINEID, DESCRIPTION and FILMID. FILMID denotes a value of the ID field from the FILMS table. It associates each record in the FILMSPERSONSSTAFF table with a specific film from the FILMS table. Thus a master-detail relationship can be established between these tables in which FILMS is a master table and FILMSPERSONSSTAFF is a detail table.
Bind Tables to Data
The following tutorial step contains step-by-step instructions on how to bind the Data Grid control to data: Table View Tutorial: Step 1 - Bind to a Data Source.
We have created a table (dataset) and datasource objects for the FILMS table: tblFilms and dsFilms. The dataset and data source for the FILMSPERSONSSTAFF table are tblFilmsPersonsStaff and dsFilmsPersonsStaff, respectively.
Creating the data structure
To display a master-detail relationship, you need to have at least two grid levels with associated DB Views (one for a master table and the other for its detail table). Views must be connected to data sources identifying master and detail tables respectively. Refer to the Working With Levels topic to learn more about creating a data structure within a grid control. The following grid levels (TcxGridLevel) and Views (TcxGridDBTableView) were created to represent the FILMS and FILMSPERSONSSTAFF tables:
The lvFilms level is linked to the tvFilms View connected to the dsFilms data source.
The lvFilmsPersonsStaff level is a child level of lvFilms. It is associated with the tvFilmsPersonsStaff View connected to the dsFilmsPersonsStaff data source.
The following image shows the Structure Navigator with the levels and Views created:
Setting up a master-detail relationship
To link the tables’ data, you need to set the DetailKeyFieldNames and MasterKeyFieldNames properties of the detail View’s data controller.
Set MasterKeyFieldNames of the detail View’s data controller to ID. It identifies the field from a master table (FILMS).
Set DetailKeyFieldNames to FILMID. FILMID specifies the field in the current detail table (FILMSPERSONSSTAFF). It corresponds to the field(s) set via MasterKeyFieldNames.
Set KeyFieldNames to ID. The KeyFieldNames property specifies one or more key field names to identify each detail record uniquely. This is not required for setting up the master-detail relationship, but it is necessary for some operations to work, such as navigating, editing, deleting detail records, etc. So, if you want only to display detail records, you can omit assigning the KeyFieldNames property.
At design time, select the tvFilmsPersonsStaff View so that the Object Inspector displays its properties and then expand the DataController property. The following image shows the Object Inspector with MasterKeyFieldNames, DetailKeyFieldNames and KeyFieldNames set to values as described above:
The following code performs the same operations:
tvFilmsPersonsStaff.DataController.KeyFieldNames := 'ID';
tvFilmsPersonsStaff.DataController.MasterKeyFieldNames := 'ID';
tvFilmsPersonsStaff.DataController.DetailKeyFieldNames := 'FILMID';
Sorting the detail dataset
The last step of setting up a master-detail relationship is to sort the detail dataset (for parameterized queries this is not necessary). Providing that the detail dataset is sorted, the data controller will correctly retrieve all the necessary records from it. The detail dataset must be sorted against the fields specified by the DetailKeyFieldNames property.
The grid below demonstrates two tables linked by the ID & FILMID fields:
