Progress
Language Tutorial
for Windows


Relating with OF

Relating records in a query means that you are building a meaningful data set with data from more than one table. The examples for the rest of this chapter display the data sets you build in browse widgets, but the task of combining data for reports is also a very important one. "Creating Reports," discusses this important topic.

One of the sales reps asks you to write a browse procedure that shows all the outstanding orders for each customer. That way, when a customer calls about an order, the customer doesn’t necessarily have to have the order number.

The name of each customer is in the Customer table in the Name field. The order number is in the Order table in the Order-Num field. To put the data set together, you need to search for and compare records from two tables in one process. You can’t make the search and comparison unless there is a key between the two tables. A key is a field that holds common data.

Both the tables have a Cust-Num field, so a key does exist. If the key has the same name in both tables and is indexed in at least one, you can use the easiest method for relating the records, the OF option.

NOTE: For the OF keyword to properly detect a relationship between two tables, only one such relationship is allowed. If there are two or more fields in two tables with the same name and at least one of each is indexed, a relationship will not be detected.

The OPEN QUERY statements you’ve seen so far search through one table. You need to search through two. So, it makes sense that you need two search phrases as the following code example shows:

OPEN QUERY My-Query FOR EACH Customer, EACH Order 

This is where you use the EACH phrase. Each EACH searches a different table. But, this syntax still doesn’t relate the tables. You have to use OF as the following code example shows:

OPEN QUERY My-Query FOR EACH Customer, EACH Order OF Customer 

OF relates the second EACH back to the first EACH. The result is that the first Customer record is found. Next, Progress finds every Order record that has the same Cust-Num as the first Customer record.

Follow these steps for a demonstration that implements the above scenario:

  1. Open lt-09-04.p and run it. The following display appears:
  2. Scroll through the records. The browse lists only Customers with current orders, and lists every order number for customers with multiple orders.
  3. Choose Exit, then press SPACEBAR to return to the Procedure Editor.

Here is the code that created the display:

lt-09-04.p
      /**********  DEFINE QUERIES  **********/
/*1*/  DEFINE QUERY New-Query FOR Customer FIELDS (Name Cust-Num),
                   Order FIELDS (Order-Num Cust-Num).

      /**********  DEFINE WIDGETS  **********/
      DEFINE BROWSE New-Browse QUERY New-Query
          DISPLAY Name SPACE(3) Order-Num WITH 15 DOWN.
      DEFINE BUTTON btn-Exit LABEL "Exit".

      /**********  DEFINE FRAMES  **********/
      DEFINE FRAME Frame1 
          New-Browse AT ROW 1 COLUMN 2
          btn-Exit AT ROW 5 COLUMN 50
              WITH NO-BOX CENTERED THREE-D.

      /**********  MAIN LOGIC  **********/
/*2*/  OPEN QUERY New-Query FOR EACH Customer, 
                         EACH Order OF Customer BY Name.
      DISPLAY New-Browse WITH FRAME Frame1.
      ENABLE ALL WITH FRAME Frame1.
      WAIT-FOR CHOOSE OF btn-Exit.   

These notes help to explain the code:

  1. Since you need to work with more than one table, you have to list the tables in the DEFINE QUERY statement.
  2. This code searches for and does an implicit comparison on the Cust-Num key field.

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