SET RELATION Command

  • Article

Establishes a relationship between two open tables.

SET RELATION TO [eExpression1 INTO nWorkArea1 | cTableAlias1
   [, eExpression2 INTO nWorkArea2 | cTableAlias2 ...]
   [IN nWorkArea | cTableAlias] [ADDITIVE]]

Parameters

  • eExpression1
    Specifies the relational expression that establishes a relationship between the parent and child tables. The relational expression is usually the index expression of the controlling index of the child table.

    The index for the child table can be a single-entry (.idx) index, a multiple-entry structural compound (.cdx) index, or an independent compound index. If the index is compound, specify the proper index tag to order the child table. SET ORDER can be used to specify the index tag that orders the child table.

    For example, consider the customer and orders tables described in the Remarks section below. Suppose the child orders table has been indexed and ordered on the customer number with this command:

    SET ORDER TO TAG cust_id

    To relate the customer and orders tables on customer number, select the work area containing the customer parent table or include the IN clause to specify the parent table's work area or alias, and issue SET RELATION, specifying the index expression with the following relational expression:

    SET RELATION TO cust_id INTO orders

    The child table must be indexed unless the relational expression is numeric. Visual FoxPro displays an error message if you issue SET RELATION with a non-numeric relational expression and the child table isn't ordered with an index.

    If eExpression1 is numeric, it is evaluated when the record pointer in the parent table is moved. The record pointer in the child table is then moved to record number eExpression1.

  • INTO nWorkArea1 | cTableAlias1
    Specifies the work area number (nWorkArea1) or table alias (cTableAlias1) of the child table.

  • eExpression2 INTO nWorkArea2 | cTableAlias2 ...
    Specifies a relational expression (eExpression2) and a child table or tables to establish an additional relationship between the parent table and child tables. From a single SET RELATION command, you can create multiple relations between a single parent table and various child tables. Precede each relation by a comma.

    nWorkArea2 specifies a work area number and cTableAlias2 specifies a table alias for the child table.

  • IN nWorkArea
    Specifies the work area of the parent table.

  • IN cTableAlias
    Specifies the alias of the parent table.

    The IN clause makes it possible for you to create a relationship without first selecting the parent table's work area. If you omit nWorkArea and cTableAlias, the parent table must be open in the currently selected work area.

  • ADDITIVE
    Preserves all existing relationships in the current work area and creates the specified relationship. If you omit ADDITIVE, any relationships in the current work area are broken and the specified relationship is created.

Remarks

Before you can establish a relationship, one table (the parent table) must be open, and the other table (the child table) must be open in another work area.

Related tables typically have a common field. For example, suppose a table named customer contains customer information. It has fields for name, address, and a unique customer number. A second table named orders contains order information. It too has a field for the customer number, along with fields for dates and shipping information.

SET RELATION relates these two tables on their common field — the customer number field. To set the relation, the child table must be indexed on the common field. After you set the relation, whenever you move the record pointer to a record with a given customer number in the parent customer table, the record pointer in the child orders table moves to the record with the same customer number. If a matching record can't be found in the child table, the record pointer in the child table is positioned at the end of the table.

Issue SET RELATION TO with no arguments to remove all relationships in the currently selected work area. SET RELATION OFF can be used to remove a specific parent-child relationship.

See Also

INDEX | RELATION( ) | SET ORDER | SET RELATION OFF | SET SKIP | TARGET( )