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:
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:
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:
Here is the code that created the display:
These notes help to explain the code:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |