Topic group Related topics Example

Links two or more open tables with common fields or expressions.

Syntax

SET RELATION TO
[<key exp list 1> | <expN 1>
 INTO <child table alias 1> [CONSTRAIN]
 [, <key exp list 2> | <expN 2>
  INTO <child table alias 2> [CONSTRAIN] ]
[ADDITIVE] ]

<key exp list 1>

The key expression or field list that is common to both the current table and a child table and links both tables. The child table must be indexed on the key field and that index must be the master index in use for the child table.

<expN 1> INTO <child table alias>

For dBASE tables only, you can specify <expN> to link records in a child table. When <expN> is RECNO(), dBASE Plus links the current table to a child table by corresponding record numbers, in which case the child table doesn't have to be indexed.

INTO <child table alias>

<alias> specifies the child table linked to the current table.

<key exp list 2> | <expN 2> INTO <alias 2> ...]

Specifies additional relationships from the current table into other tables.

CONSTRAIN

Limits records processed in the child table to those matching the key expression in the parent table.

ADDITIVE

Adds the new relation to any existing ones. Without ADDITIVE, SET RELATION clears existing relations before establishing the new relation.

Description

Use SET RELATION to establish a link between open tables based on common fields or expressions.

Before setting a relation, open each table in a separate work area. When a relation is set, the table in the current work area is referred to as the parent table, and a table linked to the parent table by the specified key is called a child table. The child table must be indexed on the fields or expressions that link tables and that index must be the master index in use for the child table.

A relation between tables is usually set through common keys specified by <key exp list>. The relating expression can be any expression derived from the parent table that matches the keys of the child table master index. The keys may be a single field or a set of concatenated fields contained in each table. The fields in each table can have different names but must contain the same type of data. For Paradox and SQL tables, you can specify single or composite index key fields.

SET RELATION clears existing relations before establishing a new one, unless you use the ADDITIVE option. SET RELATION TO without any arguments clears existing relations from the current table without establishing any new relations.

The CONSTRAIN option restricts access in the child table to only those records whose key values match records in a parent table. This is the same as using SET KEY TO on the key field of the child table. As a result, you can't use SET KEY TO and CONSTRAIN at the same time. If a SET KEY TO operation is in effect on the child table when you specify CONSTRAIN with SET RELATION, dBASE Plus returns a "SET KEY active in alias" message. If the CONSTRAIN option is in effect when SET KEY TO is specified, dBASE Plus returns the error "Relation using CONSTRAIN." You can use SET FILTER with the CONSTRAIN option, if you want to specify additional conditions to qualify records in a child table.

More than one relation can be defined from the same table. Also, more than one relation can be set from the same parent table if you use the ADDITIVE option or if you specify multiple relations with the same SET RELATION command. You can also establish additional relations from a child table, thus defining a chain of relations. Cyclic relations aren't allowed; that is, dBASE Plus returns an error if you attempt to define a relation from a child table back into its parent table.

When a relation is set from a parent table to a child table, the relation can be accessed only from the work area that contains the parent table. To access fields of the child table from the current work area, use the alias operator(->) and prefix the name of fields in the child table by its alias name.

If a matching record can't be found in a linked table, the linked table is positioned at the end-of-file, and EOF( ) in the child alias returns true while FOUND( ) returns false. The setting of SET NEAR does not affect positioning of the record pointer in child tables.

When a SET SKIP list is active, the record pointer is advanced in each table, starting with the last work area in the relation chain and moving up the chain toward the parent table.

OODML

Use the Rowset object’s masterFields and masterRowset properties, or the Query object’s masterSource property.