Progress
Results Administration
and Development Guide


Table Relationships and Multi-table Queries

To build a query that contains information from two or more tables, Results must have a description of how the tables used in the query relate to each other. During the initial build process, Results analyzes the connected databases and defines a default set of relationships between tables in the connected databases. A default relationship between two tables is based on the following conditions:

Results stores the default table relationships in the QC7 file following the initial build process.

Default table relationships in Results are called OF relationships in the Progress 4GL. Two tables that meet the conditions stated above can be programmatically joined using the OF option in a Record phrase in a Progress 4GL statement that reads database records. For more information about record access and the Progress 4GL, see the Progress Programming Handbook.

Choose Query Site Admin Table Relationships to display the Table Relationships dialog box where you can view and edit relationships in Results:

The Table Relationships dialog box contains the following user-interface elements:

Base Table

Lists the names of all tables available to your Results user ID from the connected databases. Select a table from this list to access information about the relationships defined for the table in Results.

Available To Be Related

Lists all the tables available to you in Results that are not related to the current base table. Initially, these are the tables that do not meet the OF requirements.

Related Tables

Lists all the tables available that are related to the current base table.

Relationship Buttons

These buttons perform the following actions:

The OK button confirms all modifications made in the Table Relationships dialog box and writes the modifications to the current QC7 file. The Cancel button cancels all modifications made and exits the Table Relationships dialog box.

The default table relationships that Results creates during the initial build process should provide a strong foundation for most multi-table queries. However, you might want to customize the table relationships available to your users. The Table Relationships dialog box allows you to:

NOTE: If you change or delete a table relationship, saved queries that depend on that relationship become invalid and no longer function.

Choose Query Site Admin Application Rebuild to rebuild existing Results queries based on modifications to the table relationships. This option also produces information about any existing Results queries that cannot be rebuilt as a result of modifications to table relationships. See the "Database Schema Changes" section for more information about rebuilding Results queries.

The remainder of this section describes how to use the Table Relationships dialog box to create, edit, and delete table relationships.

Creating a Table Relationship

Follows these steps to use the Table Relationships dialog box to create a new table relationship in Results:

  1. Select a base table for the new relationship. The Table Relationships dialog box displays the current relationship information for that table.
  2. Select a table from the list of unrelated tables.
  3. Choose the Add button. Results attempts to form a default relationship between the base table and the selected unrelated table.
  4. If Results cannot form a default relationship between the two tables, the Join Construction dialog box appears to help you define the relationship. See the "The Join Construction Dialog Box" section for information about defining a table relationship using the Join Construction dialog box.

    When you finish building the relationship using the Join Construction dialog box and return to the Table Relationships dialog box, the previously unrelated table appears in the list of related tables and the syntax of the new relationship appears in the Relationship field.

  5. Choose OK in the Table Relationships dialog box to write the table relationship to the current QC7 file.
Editing a Table Relationship

Follow these steps to edit an existing table relationship in Results using the Table Relationships dialog box:

  1. Select a base table.
  2. Select a table from the list of related tables.
  3. Choose the Edit button.
  4. The Join Construction dialog box appears. Use it to redefine the relationship between the current base table and the selected related table. See the "The Join Construction Dialog Box" section for information about defining a table relationship.

    When you finish redefining the relationship and return to the Table Relationships dialog box, the syntax of the new relationship appears in the Relationship field.

  5. Choose OK in the Table Relationships dialog box to write the table relationship modifications to the current QC7 file.

Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095