Setting Master Detail dengan Dev Express Quantum Grid

Ternyata barang canggih konfigurasinya juga tidak trivial. Tulisan ini digunakan untuk siapa saja yang belum tahu atau lupa melakukan konfigurasi master detail dengan Quantum Grid dari Dev Express.

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). Grid mode is a special loading mode provided where there are a large number of records. The Grid Mode: Master-Detail section shows how to create a master-detail relationship between tables in grid mode.

EQGrid 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 EQGrid 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.

Creating connections to tables
The Connecting to Data topic outlines the steps required to connect a grid control to a database. It involves:

creating TDataSet and TDataSource objects for all data tables in the project

linking TDataSet to a database table

linking TDataSource to the TDataSet

Other steps relate to adjusting the grid’s properties and will be discussed below.

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. Please 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:

Also, you have to link views to corresponding TDataSource objects and add columns within these views to display the data in the tables. See Connecting to Data and Create And Delete Columns for more details.

 

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 dataset record uniquely. This is not required for the actual master-detail relationship, but it is necessary for some operations to work, such as editing detail view data, deleting records, etc.

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:

//Delphi

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.

To enable sorting for query datasets, you should use the “ORDER BY” SQL command. For TTable and TCustomADODataSet components, the sorting of dataset records is implemented via the IndexFieldNames property. In this example, we need to set the IndexFieldNames property of the dsFilmsPersonsStaff dataset to FILMID (the same value as for the tvFilmsPersonsStaff.DataController.DetailKeyFieldNames property).

//Delphi

dsFilmsPersonsStaff.IndexFieldNames := ‘FILMID‘;

The grid below demonstrates two tables linked by the ID & FILMID fields: