Commands and Statements

Command SET RELATION Foundation

Creates a relational link between two work areas.

Syntax
SET RELATION TO [<cRelation1> | <xRecordID1> INTO <cAlias1> ;
           [TAG  <cTag1>] ] ;
         [, [TO] <cRelation2> | <xRecordID2> INTO <cAlias2> ;
           [TAG  <cTag2>]...] ;
         [ADDITIVE] [SELECT[IVE]]
Scope
thread-local, current work area
Parameters
<cRelation>
<cRelation> is an expression whose value creates the link with the child work area. The record pointer in the child work area is synchronized with the record pointer in the current or parent work area. The return value of <cRelation> is used to search the child work area using DbSeek(). Since DbSeek() is employed, an appropriate index must be active in the child work area.
<xRecordID>
As an alternative to <cRelation>, an expression <xRecordID>can be specified whose value corresponds to a record ID in the child work area. A record ID is a value which uniquely identifies a data record. This value depends on the current database engine (DBE). With the DBFDBE, it is the record number (RecNo()). The synchronization of the record pointer in the child work area then occurs using DbGoto() instead of DbSeek(). This linking is used when no index file is open in the child work area.
<cAlias>
<cAlias> is the alias name of the child work area. It can be specified as a literal or as a character expression in parentheses.
<cTag>
<cTag> is a string indicating the tag name of the index in the child work area to be used for searching the return value of <cRelation>. If not specified, the tag name of the controlling index of the child work area is used for <cTag>.
Following SET RELATION TO, a list of links can be specified, separated by commas. A call to SET RELATION TO without argument removes all defined links to child work areas from the current work area.
ADDITIVE
The option ADDITIVE adds the new link to any existing ones. If this option is not included, existing links are first removed and then new links are defined.
| SELECT
If this option is specified, navigation of the record pointer in the child work area is restricted to those records where the expression <cRelation>results in the same value for both parent and child work area.
Description

The command SET RELATION TO defines links between the current work area and one or more other work areas. The dependent, or child, work areas are specified by the argument <cAlias>. The controlling, or parent, work area is the current work area. The relation synchronizes the data record pointers in the child work areas with the data pointer in the parent work area.

The synchronization of the record pointer in the child work areas occurs using the value <cRelation>. The child work areas are automatically searched for this value using DbSeek() whenever the record pointer in the parent work area moves. This requires that the controlling index in the child work area have an index expression returning values that correspond to <cRelation>. The setting of SOFTSEEK is ignored. If the value of <cRelation> is not found in the controlling index of the child work area, the record pointer in the child work area is positioned after the last record and the function Eof() returns the value .T. (true) for this work area. If the value is found, the function Found() instead returns the value .T..

If no index is active in the child work area, synchronization occurs using the function DbGoto(), which attempts to set the record pointer in the child work area to the record ID corresponding with the value of <xRecordID>.

The range for navigating the record pointer in the child work area can be restricted to a subset of records by specifying the SELECT option. In this case, only those records are accessible in the child work area where the link expression <cRelation> results in the same value for both work areas. When attempting to move the record pointer in the child work area outside this defined range, either Bof() or Eof() is set to .T. (true). In this way, a time consuming filter expression can be avoided.

A parent work area may not be linked with itself directly or indirectly. Cyclical links are recognized and not allowed.

SET RELATION TO .. SELECT is identical to SET RELATION TO, but it sets up a relational restriction on the child work area at the same time the relation is set up. This means that whenever you select the child work area, only the records related to the current parent record will be visible.

Notes

It is possible to use scopes and filters when operating on the child area even if the child is part of a relational restriction set up with SET RELATION TO ... SELECT.

Using DbRSuspendSelect()/DbRResumeSelect() temporarily disables the relational restriction on the childarea to perform queries or else that require all records to become visible in the child work area.

The SELECT feature in conjunction with DbRSuspendSelect() allows straightforward handling of one-to-many and one-to-one relationships, and makes it possible the first time to setup a datamodel once at application startup. This, in turn, makes the database kernel working more like a relational database system.

Relations cost no runtime when navigating on the parent, only when the child-area is accessed the relational rules are applied. Different parents are allowed, the last parent navigation determines the relational restriction on the child area (last-come first serve).

The functions DbSetRelation() and DbClearRelation() are the functional equivalents of the command SET RELATION TO.

Examples
SET RELATION TO
// In the example, a link between an invoice file, 
// a part file and a customer file is created. Then 
// the invoice dates for the customers which have no 
// payments recorded are printed out. Finally, the 
// relation is removed. 

PROCEDURE Main 
   USE Customer ALIAS Cust NEW EXCLUSIVE 
   INDEX ON CustNo TO CustNo 
   SET INDEX TO CustNo 

   USE Part ALIAS Part NEW EXCLUSIVE 
   INDEX ON PartNo TO PartNo 
   SET INDEX TO PartNo 

   USE Invoice ALIAS Inv NEW 

   SET RELATION TO CustNo INTO Cust , ; 
                TO PartNo INTO Part 

   LIST Cust->CustNo  , ; 
        Cust->LName   , ; 
        Cust->FName   , ; 
        Inv ->InvNo   , ; 
        Inv ->InvDate , ; 
        Part->PartNo  , ; 
        Part->Part      ; 
    FOR Inv ->Payment == 0 ; 
     TO PRINTER 

   SET RELATION TO 

   CLOSE DATABASES 

RETURN 

Feedback

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.