Master-Detail Relationship (M/D)
Go Up to Working with DataSets (FireDAC)
FireDAC has flexible support for master-detail relationships between datasets.
Contents
General
The master-detail relationship allows you to automatically filter a detail dataset based on a current master dataset record. For example, the master dataset has "Order" records, and the detail dataset has "Order Line" records. So the detail dataset shows only lines for the current order.
Special setup is not required for a master dataset.
FireDAC offers two base methods to set up a detail dataset in a master-detail relationship:
- Parameter-based. The master dataset field values are assigned to the detail TFDQuery or TFDStoredProc parameters, and then the detail dataset query is reexecuted.
- Range-based. The master dataset field values are used to apply a range to the detail dataset. The detail dataset might be any FireDAC dataset with current active index.
These methods can be combined. To choose between them, consider the following table:
| Feature | Parameter-based | Range-based |
|---|---|---|
| The detail query returns a limited number of records. | + | |
| The detail records are fresh. | + | |
| Reduced traffic and DBMS workload on each master change. | + | |
| The cached updates preserved on the master change. | + | |
| Works in offline mode. | + | |
| Supports Centralized Cached Updates with propagation | + |
Also, FireDAC offers two cached updates modes for the datasets in a master-detail relationship:
- Decentralized Cached Updates mode: Each dataset tracks the changes independently of others.
- Centralized Cached Updates mode: A few datasets in a master-detail relationship share a single change log. The master dataset can propagate changes to the detail datasets in cascading fashion, including auto-incrementing field values.
Parameters-based M/D
To set up a parameters-based M/D relationship, perform the following steps:
- Drop TFDQuery (or any other FireDAC dataset) on a form.
- Name the dataset qOrders. This is the master dataset.
- Set it up - assign the SQL as follows:
SELECT * FROM {id Orders}
- 4. Drop TDataSource on a form. Name it dsOrders. Set its DataSet property to qOrders.
- 5. Drop TFDQuery on a form. Name it qOrderDetails. This is a detail dataset.
- 6. Set it up - assign the SQL as follows:
SELECT * FROM {id Order Details} WHERE OrderID = :OrderID
- 7. Then set MasterSource to dsOrders. The base setup is finished.
So how does it work? FireDAC builds for qOrderDetails a list of pairs - qOrders fields and qOrderDetails parameters. Elements in each pair:
- When MasterFields is not specified, then they have the same name;
- Otherwise, the pair elements have the same position, the same fields in the MasterFields list, and the same parameters in the Params collection.
When the current qOrders record is changed, FireDAC assigns for each parameter a corresponding field value. In our case, qOrderDetails :OrderID parameter gets the qOrder OrderID field value. After that, the qOrders is reexecuted.
Note: The BeforeOpen and AfterOpen events do not fire for a detail dataset. Use OnMasterSetValue instead.
Range-based M/D
To set up the range-based M/D relationship, perform the following steps:
- Drop TFDQuery (or any other FireDAC dataset) on a form.
- Name it qOrders. This is the master dataset.
- Set it up - assign the SQL as follows:
SELECT * FROM {id Orders}
- 4. Drop a TDataSource on a form. Name it dsOrders. Set the DataSet to qOrders.
- 5. Drop TFDQuery on a form. Name it qOrderDetails. This is a detail dataset.
- 6. Set it up - assign the SQL as follows:
SELECT * FROM {id Order Details}
- 7. Set MasterFields to ORDERID, set IndexFieldNames to ORDERID, and set MasterSource to dsOrders. The base setup is finished.
So how does it work? FireDAC builds for qOrderDetails a list of pairs - qOrders and qOrderDetails fields, where fields in each pair have the same position, master fields in MasterFields, and detail fields in IndexFieldNames.
When the current qOrders record is changed, FireDAC applies the range to qOrderDetails, where details fields are equal to corresponding master fields. In our case, the qOrderDetails OrderID field is equal to the qOrder OrderID field.
Combining Methods
To combine both methods, an application should use both Parameters and Range-based setups and include fiDetails into FetchOptions.Cache. Then FireDAC at first uses range-based M/D. And if a dataset is empty, then FireDAC uses parameter-based M/D. The new queried records are appended to the internal records storage.
Also, you can use the TFDDataSet.OnMasterSetValues event handler to override M/D behavior.
Editing Detail Datasets
When a new record is inserted into a detail dataset, the fields participating in a M/D relationship are filled automatically by the corresponding master dataset field values. The detail dataset field list is defined:
- For parameter-based M/D - DetailFields, if specified. Otherwise - the fields with the same names as the parameters.
- For range-based M/D - the index fields.
To insert a detail record, the master dataset must be in the browsing State (dsBrowse). It is impossible to have both master and detail dataset in inserting (dsInsert) or editing (dsEdit) state.
When master and detail datasets are in the cached updates mode, then the application can use TFDSchemaAdapter to enable Centralized Cached Updates with propagation. For that, master and detail dataset's SchemaAdapter property must point to the same TFDSchemaAdapter, and the detail dataset's FetchOptions.DetailCascade is True.
When an application needs to navigate in a master dataset, the detail dataset is refreshed on each master record change. This process consumes resources, and navigation might be slow. To temporary disable M/D synchronization, an application can call DisableControls / EnableControls for the master dataset:
qOrders.DisableControls;
try
qOrders.First;
while not qOrders.Eof do begin
.....
qOrders.Next;
end;
finally
qOrders.EnableControls;
end;
To force disabled M/D synchronization, call the ApplyMaster method on the master dataset. To temporarily disable M/D synchronization for a specific detail dataset, use the DisableScroll / EnableScroll method of the dataset MasterLink property:
qOrderDetails.MasterLink.DisableScroll;
try
qOrders.First;
while not qOrders.Eof do begin
if qOrders.FieldByName('OrderID').AsInteger = 100 then begin
qOrderDetails.ApplyMaster;
// read qOrderDetails dataset - it is synchronized with qOrders
end;
qOrders.Next;
end;
finally
qOrderDetails.MasterLink.EnableScroll;
end;
GUI applications can benefit from delayed M/D synchronization. So, when a user scrolls in a grid, a detail dataset is not immediately refreshed, but after some delay and only if there are no other navigations. To use delayed synchronization, set FetchOptions.DetailDelay for a detail dataset. To temporarily disable delayed M/D synchronization for a specific detail dataset and use immediate synchronization, use the DisableDelayedScroll / EnableDelayedScroll method of the MasterLink property.
By default, a state change, a non-key field values change, or refreshing of the master dataset does not lead to refreshing of the detail dataset. This arrangement avoids extra refreshes of detail datasets. If your application expects to always refresh detail datasets, then set FetchOptions.DetailOptimize to False.