Working with Reference Fields

Reference fields store a pointer or reference to another ADT object. This ADT object is a single record of another object table. Reference fields always refer to a single record in a dataset (object table). The data in the referenced object is actually returned in a nested dataset, but can also be accessed via the Fields property on the TReferenceField.

Displaying reference fields

In a TDBGrid control a reference field is designated in each cell of the dataset column, with (Reference) and, at runtime, an ellipsis button to the right. At runtime, clicking on the ellipsis brings up a new form with a grid displaying the object associated with the current record's reference field.

This form can also be brought up programmatically with the DB grid's ShowPopupEditor method. For example, if the seventh column in the grid represents a reference field, the following code will display the object associated with that field for the current record.

Copy Code

DBGrid1.ShowPopupEditor(DBGrid1.Columns[7]);

Copy Code

DBGrid1->ShowPopupEditor(DBGrid1->Columns->Items[7], -1, -1);

Accessing data in a reference field

You can access the data in a reference field in the same way you access a nested dataset.

To access data in a reference field

Create a persistent TDataSetField object by invoking the Fields editor for the parent dataset.
Create a dataset to represent the value of that dataset field.
Set that DataSetField property of the dataset created in step 2 to the persistent dataset field you created in step 1.

If the reference is assigned, the reference dataset will contain a single record with the referenced data. If the reference is null, the reference dataset will be empty.

You can also use the reference field's Fields property to access the data in a reference field. For example, the following lines are equivalent and assign data from the reference field CustomerRefCity to an edit box called CityEdit:

Copy Code

CityEdit.Text := CustomerRefCity.Fields[1].AsString;
CityEdit.Text := CustomerRefCity.NestedDataSet.Fields[1].AsString;

Copy Code

CityEdit->Text = CustomerADDRESS_REF->NestedDataSet->Fields->Fields[1]->AsString;

When data in a reference field is edited, it is actually the referenced data that is modified.

To assign a reference field, you need to first use a SELECT statement to select the reference from the table, and then assign. For example:

Copy Code

var
  AddressQuery: TQuery;
  CustomerAddressRef: TReferenceField;
begin
  AddressQuery.SQL.Text := 'SELECT REF(A) FROM AddressTable A WHERE A.City = ''San   Francisco''';
  AddressQuery.Open;
  CustomerAddressRef.Assign(AddressQuery.Fields[0]);
end;

Copy Code

AddressQuery->SQL->Text = "SELECT REF(A) FROM AddressTable A WHERE A.City = "San Francisco"";
AddressQuery->Open();
CustomerAddressRef->Assign(AddressQuery->Fields->Fields[0]);