Declaration
procedure SetRelation( iChildArea: Integer; sKeyExpr: String );
Description
Defines a relationship between the current table and another table. The current table is the parent. The related table is the child. The relationship between the parent and the child must be many (parent) to one (child) or one to one.
The parent file must be the active table when SetRelation is called.
When using structural index files (.CDX/.NSX), you should use the MasterFields and MasterSource properties instead. Alternatively, using the SetScope or SetRange method are also easier to use then SetRelation for most operations.
If you are trying to establish a master/detail relationship, you should use SetScope or Query instead of SetRelation.
Parameters
iChildArea: This is the work area identifier of the child file that is created when the file is opened. This value can be found in TApolloTable's Handle property, or from the TApolloTable's WorkArea property. The child file must have an active index in order to set up a relationship.
sKeyExpr: Defines an xBase expression that will be used to construct a key from parent data into the active child index.
When a record movement occurs in the parent file, a pending relational move is posted to the child file work area. If the child data must be accessed (which is forced by a field reference to it, for example), a key is first constructed from the parent data by evaluating sKeyExpr and then performing Seek on the child file. Relational moves do not respect the settings of SetExact and SetSoftSeek.
If a relational move is posted to a child file and the seek for the key fails, the child file record pointer is set to EOF, as are any children in its relational chain. If the seek is successful, Found will return True for the child area.
More than one relationship may be set up for a given parent file (no limit), and child files may themselves be parents to other relationships. Circular references in the relational chain are of course forbidden (unless you enjoy infinite looping as a pastime).
To clear relations in the current work area, pass iChildArea as zero and pass a space in the sKeyExpr parameter.
Delphi Example
Ths sample defines a relationship between a file that contains serial numbers of products owned by our customers (the Parent or Master) and an inventory file that describes those products (the Child or Detail). Each parent record contains a product code (field part_num) but no product description. Once the relationship is established, every time a new parent record is read, a corresponding movement takes place in the child, positioning the child to the record whose key matches the parent's part_num field Using the child's table object, we can reference the product description directly, as if it were part of the parent file's record structure.
// ---------------------------------------
procedure TForm1.FormClose(Sender: TObject; var Action: TCloseAction);
begin
Parent.Close;
Child.Close;
end;
// ---------------------------------------
procedure TForm1.FormCreate(Sender: TObject);
begin
// Parent
Parent.DataBaseName := ExtractFilePath(Application.ExeName);
Parent.TableName := 'Parent.Dbf';
Parent.TableType := ttSXNTX;
Parent.Open;
// Child
Child.DataBaseName := ExtractFilePath(Application.ExeName);
Child.TableName := 'Child.Dbf';
Child.TableType := ttSXNTX;
Child.Open;
Child.IndexOpen( 'Child.NTX' );
Child.SetOrder(1);
Parent.SetRelation( Integer(Child.Handle), 'CUST_ID' );
DbNavigator1.DataSource := dsParent;
{ This is a many-to-one relation, so the parent table now becomes
the child table. SetRelation only supports many-to-one and
One-to-One relations. }
end;
// ---------------------------------------
procedure TForm1.dsChildDataChange(Sender: TObject; Field: TField);
begin
if Child.Active then
begin
Parent.UpdateCursorPos;
Child.UpdateCursorPos;
end;
{ Since Delphi is not aware of any table movement made by
SetRelation, the update to position must be done manually. }
end;
// ---------------------------------------
procedure TForm1.dsParentDataChange(Sender: TObject; Field: TField);
begin
if Child.Active then
begin
Parent.UpdateCursorPos;
Child.UpdateCursorPos;
Child.Refresh;
end;
end;
See Also
SetScopeSetScope Query