Tutorial 12 - One-to-One Links and Linked Fields

This tutorial describes how to create one-to-one links between two TData controls. In a one-to-one link, a dependent TData control always has at most one record for each record of its master TData control. Under such circumstances, linked fields can be defined in the master control and associated with fields in the dependent (linked) control. Linked fields appear in the master’s records along with "normal" data and calculated fields. They can participate in expressions and can even be modified. A TData control can have multiple linked controls.

The effect of a one-to-one link in a database context can be achieved by other means, namely, by joining multiple tables in SQL. The advantage of the True DataControl one-to-one link is its generality. You can use True DataControl expressions, which are more general than SQL, and you can add arbitrary code to the WillOpenData and OpenData events. The disadvantage is that one-to-one links are not nearly as efficient as SQL JOINs, which makes them unsuitable for large-scale database applications. One-to-one links are ideal for memory-based data sources, however, as Tutorial 12 demonstrates.

To create a one-to-one link, perform the following steps:

Create a special TData control, declaring it to be a linked control by setting its LinkType property to 1 - Outer or 2 - LinkInner (the default setting is 0 - None), and specify the principal TData control as its Master.
Set up a master-detail relationship by means of range conditions, parameterized SQL, or filter conditions, so that the linked control will have no more than one record per master record. (For efficiency reasons, the use of filters is discouraged.)
Add linked fields to the master TData control and associate each linked field with a field of the linked TData control.

A linked control cannot be used as an independent TData control (that is, data-aware controls cannot be bound to it) because it is managed in all respects by its Master control. That is why, if you set the LinkType property to something other than 0 - None, the functionality of the TData control will be restricted. For example, all fields must be data fields; calculated and linked fields are not permitted. In essence, a linked TData control is an addition to its master control.

Outer links allow master records for which there is no corresponding record in the linked control. If this happens, all linked fields are empty and cannot be modified. Inner links filter such master records out, excluding them from the recordset. The terms "outer" and "inner" are comparable to outer and inner joins in SQL.

The following example demonstrates how to add a CustomerName field to a TData control (TData1) based on an Orders table, which does not contain the name. This can be done by creating a linked TData control (TData2) based on the Customers table and connecting it to the master control with the range condition TData2.CustomerID = TData1.CustomerID.

Start a new project.
Place the following controls on the form (Form1) as shown in the figure: two TData controls (TData1, TData2) and a DataGrid control (DataGrid1).

Set properties as follows (you can use the DataSource property page to set TData data source properties, as described in Tutorial 2):

Example Title	Copy Code
TData1.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData1.CommandType 2 – adCmdTable TData1.RecordSource Orders TData2.ConnectionString Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB TData2.CommandType 2 – adCmdTable TData2.RecordSource Customers DataGrid1.DataSource TData1

Example Title

Copy Code

TData1.ConnectionString

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

TData1.CommandType   2 – adCmdTable

TData1.RecordSource  Orders

TData2.ConnectionString

Provider=Microsoft.Jet.OLEDB.3.51;Persist Security

Info=False;Data Source=C:\Program Files\ComponentOne Studio\Common\TDDEMO.MDB

TData2.CommandType   2 – adCmdTable

TData2.RecordSource  Customers

DataGrid1.DataSource TData1

Open the True DataControl General property pages for TData2. Select TData1 in the Master combo box. Select 1 - Outer in the LinkType combo box. You have specified TData2 as linked to the master TData1 control.

Go to the Fields property page for TData2. Select the CustomerID field. Click the right mouse button over the CustomerID field and select New Range Condition from the context menu. Type the following text in the Value Expression text box:

Example Title	Copy Code
TData1.CustomerID

You have specified a range condition

Example Title	Copy Code
TData2.CustomerID=TData1.CustomerID

that will ensure that only one TData2 record corresponds to any given TData1 record.

Open the True DataControl property pages for TData1. Go to the Fields page. Create a new field by clicking the right mouse button and selecting New Field from the context menu. Change the default FIELD_0 name to CustomerName by typing it in the Name text box. Select the Linked radio button. Select TData2.CompanyName from the Linked Field combo box (note that the combo’s label automatically changed from DataSource Field to Linked Field when you selected the Linked radio button). Finally, drag the CustomerName field from the last position in the field list and drop it to the position before EmployeeID. Press OK to close the property pages and save changes. You have created a linked field CustomerName based on the CompanyName field of TData2.

Run the Program and Observe the Following:

The CompanyName information is displayed in the grid along with the CustomerID. This field is not part of the Orders table on which TData1 is based; it is derived from the Customers table that is linked to the Orders table using the range condition specified in Step 5.

Close the program. You have successfully completed Tutorial 12.