SET RELATION, Set(“Relation”), SET SKIP, Set(“Skip”)

The two commands here control temporary relations—that is, relations you set up in a program for a particular purpose, while the corresponding functions tell you about those relations. They have no connection with persistent relations stored as part of a database (except that they may involve the same tables).

SET RELATION turns relations on and off while SET SKIP indicates that a relation is one-to-many. Don’t confuse this form of SET SKIP (SET SKIP TO) with the other version (SET SKIP OF), which affects menus and popups.

Usage

SET RELATION TO [ eExpr1 INTO cAlias1 | nWorkArea1
        [, eExpr2 INTO cAlias2 | nWorkArea2 [, ... ] ]
        [ IN cParentAlias | nParentWorkArea ]
        [ ADDITIVE ] ]
SET RELATION OFF INTO [ cAlias | nWorkArea ]

SET RELATION TO establishes temporary relations. There are two ways to define these relationships between two tables. One of them harks back to the early days of Xbase. In both cases, two tables are involved: a “parent” or controlling table and a “child” or controlled table. Once the relation is established, moving the record pointer in the parent table automatically moves the record pointer in the child table.

The usual way connects an expression from the parent record to an index key of the child record. The order of the child table must be set to the appropriate index before SET RELATION is issued. Once you set the relation, moving the record pointer in the parent issues an implied SEEK eExprn in the child work area. If there is no related record, the child’s record pointer ends up at EOF().

Example

USE Customer
USE Orders IN 0 ORDER Customer_I
SET RELATION TO Customer_Id INTO Orders
* Now moving in Customer moves the record pointer in
* Orders to the first order for that customer

We’ve run across a number of people who think a relation can be based only on a single field or that the related fields of the child and the parent have to have the same name. Neither of these is true. The important thing is that the parent must have appropriate fields to create an expression that is the same as the key for some tag of the child. In some situations, you want to set things up with an expression in the parent that’s only part of the key expression. For example, say the parent is customer and the child is invoice. The invoice table may have a tag on customer_id plus invoice date (CUSTOMER_ID+DTOS(INV_DATE)). As long as SET EXACT is OFF, you can establish a relation based on just the customer_id; you’ll then be able to see the invoices for each customer in date order.

The old, old way of setting a relation uses the record number of the child to connect to the parent. In this case, no order is set in the child table and the relational expression eExprn must be numeric. When the record pointer moves in the parent, an implied GOTO is issued in the child. We use this technique very, very occasionally to connect query results to the original data. (It’s usually better, though, because of the unreliability of record numbers, to include the primary key in the query and base a relation on that field.)

Family relations are more fluid in the database world than in the real world. One table might act as the parent (or controlling) table of another at one time, then the relationship may be reversed later. The terms “parent” and “child” refer to what’s going on at this moment rather than to a permanent state of affairs.

You can establish multiple relations at the same time. That is, one parent can control several children (this works better in FoxPro than in real life). There are two ways to do this. First, you can list several pairs of relational expressions and controlled tables in a single SET RELATION. Alternatively, you can use the ADDITIVE clause to allow new relations to be added without closing existing relations. The example below shows both techniques.

Example

* Open the TasTrade Orders table and relate it to
* the Line items and Shippers tables
OPEN DATA TasTrade
USE Orders
USE Order_Line_Items IN 0 ORDER Order_id
USE Shippers IN 0 ORDER Shipper_Id

* Here's both relations set at once
SET RELATION TO Order_Id INTO Order_line_items, ;
                Shipper_Id INTO Shippers

* This time, the two relations are set separately
SET RELATION TO Order_Id INTO Order_line_items
SET RELATION TO Shipper_Id INTO Shippers ADDITIVE

Issuing SET RELATION TO without any parameters turns off all relations for the current work area. To turn off relations selectively, use SET RELATION OFF and specify which relation is being terminated.

Example

* Turn off the relation into line items
SET RELATION OFF INTO Order_line_items

The optional IN clause lets you establish relations where the parent is in a work area other than the current work area. You can’t turn relations off this way, though—for that, you have to be in the right work area. We find this clause most useful when establishing several levels of relationships. Using IN, all the work can be done without changing work areas.

Multiple levels of relations can be specified. Think of parent–child–grandchild–great-grandchild, and so on. For example, in TasTrade, you might set up relationships from Customers into Orders, then from Orders into Order_Line_Items and then from Order_Line_Items into Products. Moving the record pointer in Customers would then move the record pointers for Orders, Order_Line_Items and Products.

Usage

SET SKIP TO [ cAlias1 [, cAlias2 [ , ... ] ] ]

This command turns a relation into a one-to-many relation. It indicates that issuing SKIP in the parent should proceed to the next record in the child and not go on to the next parent record until there are no more child records for this parent. This allows you to process all the children for a given parent. SET SKIP TO by itself changes the relation back to one-to-one.

SET SKIP is most useful for Browses and reports, where it lets you display all the children of a parent.

Example

* Set up a one-to-many relation
* between Customers and Orders
USE Customer
USE Orders IN 0 ORDER Customer_I
SET RELATION TO Customer_Id INTO Orders
* This Browse shows the first order for each company
BROWSE FIELDS Company_Name, ;
              Orders.Order_Number,Orders.Order_Date
* Now make it one-to-many
SET SKIP TO Orders
* This Browse shows all orders for each company
* and shows each company name only once
BROWSE FIELDS Company_Name, ;
              Orders.Order_Number,Orders.Order_Date

When multiple levels of relations are in place, you can set one-to-many relations at any level. To make all the relations in the chain one-to-many, issue SET SKIP TO followed by the alias of each table other than the parent. In the example above with Customers, Orders, Order_Line_Items and Products, to make all three relations one-to-many, you’d issue:

SET SKIP TO Orders,Order_Line_Items,Products

You can mix and match relations, making some relations in a chain one-to-one and others one-to-many. The best way to see the differences is to open a few tables, set the relations, then try different parameters to SET SKIP, issuing BROWSE each time to see the result.

Usage

cRelationPhrase = SET( "RELATION" )

The undocumented SET(“RELATION”) function returns a string that’s exactly what you need to put in the SET RELATION command to establish the existing relationships for the current work area. The string shows every existing relation, including both the relational expression and the target.

The function accepts a second parameter, but it doesn’t change the output.

Example

OPEN DATA TasTrade
USE Orders
USE Order_Line_Items IN 0 ORDER Order_Id
USE Shippers IN 0 ORDER Shipper_Id
SET RELATION TO Order_Id INTO Order_Line_Items
SET RELATION TO Shipper_Id INTO Shippers ADDITIVE

?SET("RELATION")
* "shipper_id INTO shippers, order_id INTO order_line_items"

You can use SET(“RELATION”) to store the existing relations for a table while you set different ones, then restore the old ones when you’re done. To get the same information broken into its components, use TARGET() and RELATION().

Usage

cSkipAliases = SET("SKIP")

SET(“SKIP”) is also undocumented. It tells you which child tables have one-to-many relationships with the current table. Like SET(“RELATION”), it returns a character string that you can plug into the corresponding command. In this case, the string is a comma-delimited list of aliases.

Example

OPEN DATA TasTrade
USE Orders
USE Order_Line_Items IN 0 ORDER Order_Id
USE Shippers IN 0 ORDER Shipper_Id
SET RELATION TO Order_Id INTO Order_Line_Items
SET RELATION TO Shipper_Id INTO Shippers ADDITIVE

SET SKIP TO Order_Line_Items

?SET("SKIP")        && Returns "Order_line_items"

As with SET(“RELATION”), you can use SET(“SKIP”) to grab current information before you change it, then restore it later. Unlike SET(“RELATION”), there’s no other way to get the data returned by SET(“SKIP”).

See Also

Alter Table, Create Table, Relation(), Set Order, Target(), Use