Establishing Master-detail Relationships Using Parameters
Go Up to Using Query-type Datasets
To set up a master/detail relationship where the detail set is a query-type dataset, you must specify a query that uses parameters. These parameters refer to current field values on the master dataset. Because the current field values on the master dataset change dynamically at run time, you must rebind the detail set's parameters every time the master record changes. Although you could write code to do this using an event handler, all query-type datasets provide an easier mechanism using the DataSource property.
If parameter values for a parameterized query are not bound at design time or specified at run time, query-type datasets attempt to supply values for them based on the DataSource property. DataSource identifies a different dataset that is searched for field names that match the names of unbound parameters. This search dataset can be any type of dataset. The search dataset must be created and populated before you create the detail dataset that uses it. If matches are found in the search dataset, the detail dataset binds the parameter values to the values of the fields in the current record pointed to by the data source.
To illustrate how this works, consider two tables: a customer table and an orders table. For every customer, the orders table contains a set of orders that the customer made. The Customer table includes an ID field that specifies a unique customer ID. The orders table includes a CustID field that specifies the ID of the customer who made an order.
To set up the Customer dataset
- Add a table type dataset to your application and bind it to the Customer table.
- Add a TDataSource component named CustomerSource. Set its DataSet property to the dataset added in step 1. This data source now represents the Customer dataset.
- Add a query-type dataset and set its SQL property to:
SELECT CustID, OrderNo, SaleDateFROM OrdersWHERE CustID = :ID
- Note that the name of the parameter is the same as the name of the field in the master (Customer) table.
- Set the detail dataset's DataSource property to CustomerSource. Setting this property makes the detail dataset a linked query.
At run time, the :ID parameter in the SQL statement for the detail dataset is not assigned a value, so the dataset tries to match the parameter by name against a column in the dataset identified by CustomersSource. CustomersSource gets its data from the master dataset, which, in turn, derives its data from the Customer table. Because the Customer table contains a column called CustID, the value from the ID field in the current record of the master dataset is assigned to the :ID parameter for the detail dataset's SQL statement. The datasets are linked in a master-detail relationship. Each time the current record changes in the Customers dataset, the detail dataset's SELECT statement executes to retrieve all orders based on the current customer ID.