Relation Hierarchies

You can use the Data Environment designer to relate two or more Command objects together based on command data. The concept is similar to joining two related tables using a SQL SELECT statement. However, the run-time result is a set of hierarchical recordsets instead of a flat table. This type of hierarchy is called a relation hierarchy.

A relation hierarchy consists of a parent Command object and one or more child Command objects that are related through linking the parent's Field objects to the child's fields and/or parameters. In a relation hierarchy, the child Command objects become fields in the parent Command object.

For example, you might want to create a relation hierarchy between a Customers and Orders table. By relating the Orders table to the Customers table based on the CustID field, the Orders recordset becomes a field in the Customers recordset. Thus, the value of this field in each row becomes a reference to a recordset that contains all the Orders for that particular Customer. This is illustrated by the following figure.

Note   All Command objects used in the relation hierarchy must be associated with the same Connection object. You cannot relate Commands from two different databases.

For more information on relation hierarchies, see Data Shaping.

Creating a Relation Hierarchy from Two Existing Command Objects

Once there are two Command objects at the same level in the Data Environment outline view, you can create a relation hierarchy, as described in the following procedure.

Note   The Command objects involved in a relation hierarchy should be recordset returning. If not, on the Advanced tab of the Command Properties dialog box, choose Recordset Returning, and click OK to apply the changes to the Command object.

To create a relation hierarchy from two existing Command objects

  1. Right-click the Command object that will be the child in the relation, and click Properties on the shortcut menu to open the Command Properties dialog box.

  2. Select the Relation tab and then choose Relate to a Parent Command Object.

  3. In the Parent Command box, select the parent Command object's name. All Command objects that are associated with the same connection are shown, except for any Command object that is a child of the current Command object.

  4. Define the relation between the two Command objects as follows:
    Item Description
    Parent Fields Select a field from the parent Command object. When added, this shows on the left side of the relate expression.
    Child Fields/Parameters Select a field or parameter from the child Command object. When added, this shows on the right side of the key expression.

    Note   You must link all required parameters to fields in the parent Command object. If the required parameters are not linked, the hierarchy cannot be successfully executed.
    Add After you have selected from both the Parent Fields and the Child Fields/Parameters lists, click Add. The new relation pair shows in the Relation Definition relate expression (for example, CustomerID TO CustomerID). Repeat this process until you have all relations defined, as necessary.

    Note   You should relate the two Command objects on fields that contain similar data. For example, if one Command object returns data from an Orders table and another returns data from a Customers table, you should use a field that exists in both tables, such as orderID.
    Remove To remove a relation pair, select the pair in the display area and click Remove.

  5. Click OK to accept the relation definitions and close the dialog box.

    Note   While you can relate any two Command objects, the Data Environment designer does not check for valid input. Therefore, if you specify an invalid relationship, the data retrieved may not be what you expect.

Once a relation is in place, you can use the Microsoft Hierarchical FlexGrid control to display the data as a hierarchy.

Creating a Child Command Object from a Parent Command Object

An easy way to directly construct a relation hierarchy is to create a child Command object from the parent Command object. To create a child Command object directly, first create the parent Command object, as described in Command Objects, and then perform the following procedure.

Note   The Command objects involved in a relation hierarchy should be recordset returning. If not, on the Advanced tab of the Command Properties dialog box, choose Recordset Returning, and click OK to apply the changes to the Command object.

To create a child Command object from a parent Command object

  1. On the Data Environment toolbar, click Add Child Command.

    -or–

    Right-click the parent Command object and click Add Child Command on the shortcut menu to open the Command Properties dialog box.

    Note   The child Command and parent Command objects' connection are the same.

  2. The default Command Name (shown on the General tab of the Command Properties dialog box) of the child Command is the name of the Field object that will be appended to the parent Command object. You can change this name to a more meaningful, unique name. For example, you may wish to name the child "Orders" if it is based on a database object called "orders."

    Note   On the General tab of the Command Properties dialog box, Connection is disabled. This is because the child and parent Command objects must be associated with the same Connection object.

  3. Select the Relation tab, and define the relation by specifying the fields or parameters in each Command object that contain common data.

  4. Click OK to create the child Command object and close the dialog box. If successfully created, the child Command object appears below its parent in the Data Environment outline view.