SET RELATION Command
Establishes a relationship between open tables so that moving the record pointer in the parent table moves the record pointer in the child table.
SET RELATION TO [eExpression1 INTO nWorkArea1 | cTableAlias1
[, eExpression2 INTO nWorkArea2 | cTableAlias2 ...]
[IN nWorkArea | cTableAlias] [ADDITIVE]]
Parameters
eExpression1
Specifies the relational expression that creates a relationship between the parent and child tables. The relational expression is usually the index expression of the controlling index of the child table.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.
Omitting arguments for SET RELATION removes all relationships in the currently selected work area. You can remove a specific parent-child relationship using SET RELATION OFF. For more information, see SET RELATION OFF Command.
INTO nWorkArea1| cTableAlias1
Specifies the work area number or table alias of the child table.eExpression2INTO nWorkArea2| cTableAlias2...
Specifies a relational expression and the work area number or a child table alias so you can create an additional relationship between the parent table and child tables. Precede each relationship definition with a comma.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. Omitting ADDITIVE breaks any relationships in the current work area and creates the specified relationship.
Remarks
Before you can create a relationship, the tables must be open in different work areas. The child table must be indexed on the common field unless the relational expression is numeric. The index for the child table can be a single-entry (.idx) index, a structural compound (.cdx) index, or an non-structural compound index. If the index is a compound index, specify the proper index tag to order the records in the child table using the SET ORDER command.
Note
If you call SET RELATION with a nonnumeric relational expression and the child table has not been ordered with an index, Visual FoxPro generates an error message.
You can typically create relationships between tables that have a common field. You can create multiple relations between a single parent table and various child tables using a single SET RELATION command.
Note
If a matching record is not found in the child table, the record pointer in the child table is positioned at the end of the table.
If the child table has an active index, the data type of eExpression1 must be the same as the child table's active index key. If the child table does not have an active index, then eExpression1 must have a numeric data type, in which case it represents a record number to move to in the child table.
Examples
Example 1
The following example creates a relationship between a customer table and an orders table where one customer record in the customer table can have many order records in the orders table. By creating a relationship using a common field, you can see all the orders for any customer. This example creates relationship using the Cust_ID field in the customer table and the Cust_ID index tag in the orders table. The index in the orders table organizes records by customer.
The following code uses the USE command to open the customer table as the parent table in work area 1 and open the orders table as the child table in work area 2. The SELECT command selects the work area for the orders table. The SET ORDER command specifies the order of the orders table using the Cust_ID index tag:
USE Customer IN 1
USE Orders IN 2
SELECT Orders
SET ORDER TO TAG Cust_ID
The following code uses the SELECT command to select the work area for the customer table. SET RELATION creates the relationship between the parent table and the controlling index in the child table:
SELECT Customer
SET RELATION TO Cust_ID INTO Orders
The following code opens the customer table in a browse window, selects the work area of the orders table and opens a browse window for the orders table:
BROWSE NOWAIT
SELECT Orders
BROWSE NOWAIT
When you move the record pointer in the customer table, the record pointer in the orders table also moves. Opening the Data Session window displays the relationship created between the two tables.
Example 2
The following example creates a relationship within a single table, or a self-referential relationship. By opening the same table with two different aliases in different work areas, you can create a relationship in the same table by using the two aliases. The following code selects the work area of the table with the SELECT command and opens the table with the Manager alias with the USE command. The SELECT command then selects the same work area and opens the table again with the Employee alias with the USE command and the AGAIN keyword. The tables open in two different work areas:
SELECT 0
USE Employees ALIAS Manager
SELECT 0
USE Employees AGAIN ALIAS Employee
The following code creates an index on the based on the Reports_To field with the index tag Mgr_ID for the table using the INDEX command and sets the order in the table to that index using the SET ORDER command. The index created organizes records by manager.
INDEX ON Reports_To TAG Mgr_ID
SET ORDER TO Mgr_ID
The following code selects the Manager alias as the parent using the SELECT command.
SET RELATION creates the relationship between the parent table and the controlling index tag in the child table. The controlling index in the child table organizes records by employee.
SELECT Manager
SET RELATION TO Emp_ID INTO Employee
The following code opens the table with the Manager alias in a browse window, selects the work area of the table with the Employee alias and opens a browse window for the table:BROWSESELECT EmployeeBROWSE
When you move the record pointer in the manager table, the record pointer in the employee table also moves and displays only those employees who report to the selected manager.
See Also
Tasks
How to: Set Temporary Relationships Between Tables