To review, the FOR EACH statement in your procedure creates a code block nested inside the implicit main block of the procedure itself. Now you will create a second FOR EACH block, nested inside the first FOR EACH, to display the Order records in the database for each New Hampshire Customer.
To create a nested block, add another FOR EACH block inside the first block, so that your procedure looks like this:
FOR EACH Customer NO-LOCK WHERE Customer.State = "NH" BY Customer.City:
DISPLAY Customer.CustNum Customer.Name Customer.City.
FOR EACH Order OF Customer NO-LOCK: DISPLAY Order.OrderNum Order.OrderDate Order.ShipDate. END. END.
This example shows the code indented so that the new block is visually nested in the outer block, which helps code readability.
First, look at the new FOR EACH statement. The keyword OF is a shorthand for a WHERE clause that joins the two tables together. When you looked at the two tables and their fields in the Dictionary, you saw that both tables have a CustNum field. This is the primary key of the Customer table, which means that each Customer is assigned a unique number for the CustNum field, and this is the primary identifier for the Customer. In the Order table, the OrderNum is the unique Order identifier, and its primary key. The CustNum field in the Order table points back to the Customer the Order is for. It's a foreign key because it points to a record in another table. To retrieve and display the Orders for a Customer, you have to join the two tables on the CustNum field that they have in common. The full WHERE clause for this join would be: WHERE Customer.CustNum = Order.CustNum. This kind of syntax will be familiar to you if you've ever worked with SQL.
The WHERE clause is telling the ABL Virtual Machine (AVM) to select those records where the CustNum field in one table matches the CustNum field in the other. In order to tell the AVM which field is which, both are qualified by the table name, followed by a period.
In ABL, you can use the syntax Order OF Customer as a shorthand for this join if the two tables have one or more like-named fields in common that constitute the join relationship, and those fields are indexed in at least one of the tables (normally they should be indexed in both). You can always use the full WHERE clause syntax instead of the OF phrase if you wish; the effect is the same, and if there is any doubt as to how the tables are related, it makes the relationship your code is using completely clear. In fact, the OF phrase is really one of those beginner shortcuts that makes it easy to write a very simple procedure but which really isn't good practice in a complex application, because it isn't clear just from reading the statement which fields are being used to relate the two tables. You should generally be explicit about your field relationships in your applications.
These simple nested FOR EACH statements accomplish something that would be a lot of work in other languages. To retrieve the Customers and their Orders separately, as you really want to do, you would have to define two separate queries using embedded SQL syntax, open them, and control them explicitly in your code. This would be a lot of work. For example, the straight forward single SQL query to retrieve the same data would be:
SELECT Customer.CustNum, Name, City, OrderNum, OrderDate, ShipDate FROM
Customer, Order WHERE Customer.State = "NH" AND Customer.CustNum = Order.CustNum;
This code would retrieve all the related Customers and Orders into a single two-dimensional table, which is not very useful: all the Customer information would be repeated for each of the Customer's Orders, and you would have to pull it apart yourself to display the information as header and detail, which is probably what you want. By contrast, when you run your very simple ABL procedure, you get a default display that represents the data properly as master (Customer) and detail (Order) information, as shown in the following figure.
Figure 1. Result of running simple sample procedure
The AVM automatically gives you two separate display areas, one for the Customer fields showing one Customer at a time, and one for the Orders of the Customer. These display areas are called frames. You'll learn more about ABL frames in later chapters.
Unlike in the first example, which displays a whole page of Customers, each time you press the SPACE BAR, the AVM displays just the next Customer and its Orders. Why did the AVM do this? The nested FOR EACH blocks tell the AVM that there are multiple Orders to display for each Customer, so it knows that it doesn't make sense to display more than one Customer at a time. So it creates a small frame just big enough to display the fields for one Customer, and then separately creates another frame where it can display multiple Orders. The latter frame is called a down frame, because it can display multiple rows of data as it moves down the page. The top frame is actually referred to as a one down frame because it displays only a single row of data at a time.
You can control the size of the frames, how many rows are displayed, and many other attributes, by appending a WITH phrase to your statement.
To see how the WITH phrase can affect your test procedure:
1. Add the words WITH CENTERED to the DISPLAY statement for the Order frame:
FOR EACH Customer NO-LOCK WHERE Customer.State = "NH" BY Customer.City:
DISPLAY Customer.CustNum Customer.Name Customer.City.
FOR EACH Order OF Customer NO-LOCK:
DISPLAY Order.OrderNum Order.OrderDate Order.ShipDate WITH CENTERED.
END.
END.
2. Run the procedure again. The Order frame is centered in the default display window:
There are lots of frame attributes you can specify here (for a description of frame attributes, read the section on the Frame Phrase in OpenEdge Development: ABL Reference). This book doesn't tell you much more about frame attributes, either now or later, because you won't use most of them in your GUI applications. These attributes are designed to help you define frames for a character mode application, where the interface is basically just a series of 24 or 25 lines of 80 characters each. For this kind of display format, a sequence of frames displayed one beneath the other is an appropriate and convenient way to lay out the screen. But in a GUI application, you instead lay out your display using a visual design tool, such as the OpenEdge AppBuilder or Progress Developer Studio for OpenEdge and it generates the code or data needed to create the user interface at run time. This chapter shows you the basics of how ABL works and how it was designed, even though you will do most of your work a different way in your new applications.
