Setting Temporary Relationships Between Tables

  • Article

When you establish a temporary relationship between tables, you cause the record pointer of one table (the child table) to automatically follow the movements of the record pointer in the other, or parent, table. This allows you to select a record on the "one" or parent side of a relationship and automatically access the related records on the "many" or child side of the table relationship.

For example, you may want to relate the customer and orders tables so that when you move the record pointer in the customer table to a particular customer, the record pointer in the orders table moves to the record with the same customer number.

You can use table work areas and table aliases to establish relationships between two open tables with the SET RELATION command. If you're using a form to work with tables, you can store these relationships as part of the data environment for the form.

Temporarily Relating Tables

You can use the Data Session window or the language to create temporary relationships between tables.

To temporarily relate tables

  • In the Data Session window, select tables and use the Relations button to create relationships.

    -or-

  • Use the SET RELATION command.

You use the SET RELATION command to establish a relationship between a table open in the currently selected work area, and another table open in another work area. You typically relate tables that have a common field, and the expression you use to establish the relationship is usually the index expression of the controlling index of the child table.

For example, a customer may have many orders. If you create a relationship between the field that is common to both customer and order tables, you can easily see all the orders for any customer. The following program uses a field, cust_id, that is common to both tables and creates a relationship between the two tables based on the field cust_id in the customer table and the cust_id index tag in the orders table.

Using SET RELATION to Establish Relationship Between Two Tables

Code Comments 
USE customer IN 1
 Open the customer table (parent table) in work area 1. 
USE orders IN 2
 Open the orders table (child table) in work area 2. 
SELECT orders
 Select the child work area. 
SET ORDER TO TAG cust_id
 Specify the table order for the child table using the index tag cust_id. 
SELECT customer
 Select the parent work area. 
SET RELATION TO cust_id
INTO orders
 Create the relationship between the parent table and the controlling index in the child table. 
SELECT orders
BROWSE NOWAIT
SELECT customer
BROWSE NOWAIT
 Open two Browse windows; notice that moving the record pointer in the parent table changes the set of data viewed in the child table.

The Data Session window displays the two open tables, Orders and Customer, and the relationship established by the SET RELATION command.

The Data Session window displays open table aliases and temporary relationships.

You created an index on the child table, orders, to organize records in the orders table into groups, according to the customer who placed the order. When you create a relationship between the parent table and the index of the child table, Visual FoxPro selects only those child table records whose index key matches the index key of the parent record you've selected.

The previous example established a single relationship between two tables. You can also use the SET RELATION command to establish multiple relationships between a single parent table and various child tables.

Saving Table Relationships in a Data Environment

If you are creating a form that uses more than one table, you can use the data environment to create table relationships and store them with the form. Relationships you establish in the data environment are opened automatically when you run the form. For information on creating a data environment, see Creating Forms.

Relating Records in a Single Table

You can also create a relationship between records in a single table. This relationship, known as a self-referential relation, can be useful in situations where you have all the information you need stored in a single table. For example, you may want to move through the managers in the Employees table and have the employees who report to each manager automatically change as you move the record pointer from manager to manager.

To temporarily relate records in a single table

  • In the Data Session window, select tables and use the Relations button to create relationships.

    -or-

  • Use the SET RELATION command.

To create a self-referential relation, open the same table twice: once in one work area and then a second time, with the USE AGAIN command, in another work area. Then use an index to relate the records. For example, the following code establishes and browses a self-referential relationship by creating an index tag named mgr_id that orders the Employee table by the reports_to field:

SELECT 0
USE employee ALIAS managers
SELECT 0
USE employee AGAIN ALIAS employees
INDEX ON reports_to TAG mgr_id
SET ORDER TO mgr_id
SELECT managers
SET RELATION TO emp_id INTO employees
BROWSE
SELECT employees
BROWSE

When you move the record pointer in the managers Browse window, the employees Browse window is refreshed to show only those employees who report to the selected manager.

See Also

Working with Table Aliases | Establishment of Persistent Relationships with Indexes | Working with Records | Data Session | SET RELATION | Working with Multiple Tables