Each order has one or many order lines, each of which relates to a single inventory item. Accounting needs to pull information from the OrderLine and Item tables to get specific information about what was ordered. One more FOR EACH block, inside the existing block, completes the structure.

Once the procedure gathers all the information, accounting needs the procedure to calculate total prices. Nested FOR EACH blocks create sorted groups of information similar to break groups. Therefore, you can use the aggregate phrase to calculate total prices.

The code fragment from i-10-08.p shows the completed structure of nested FOR EACH blocks.

i-10-08.p

      . . .
      FOR EACH Customer NO-LOCK
        WHERE Customer.Balance >= (Customer.CreditLimit * .85) 
        WITH STREAM-IO:
        DISPLAY Customer.Name FORMAT "x(20)" Customer.Contact FORMAT "x(15)" 
          Customer.Balance Customer.CreditLimit WITH NO-BOX.

/*1*/   FOR EACH Order NO-LOCK WHERE Order.CustNum = Customer.Custum
          WITH STREAM-IO:
          DISPLAY Order.OrderNum Order.OrderDate Order.ShipDate
            Order.PromiseDate SKIP(1) WITH 2 COLUMNS.

          FOR EACH OrderLine NO-LOCK
            WHERE OrderLine.OrderNum = Order.OrderNum WITH STREAM-IO:
/*2*/       FIND Item NO-LOCK WHERE Item.ItemNum = OrderLine.ItemNum.
            DISPLAY OrderLine.Qty OrderLine.ItemNum
              Item.ItemName FORMAT "x(13)"
              Item.Price LABEL "Unit Price"
/*3*/         Item.Price * Qty (TOTAL) LABEL "Price" 
/*4*/           FORMAT "$zzz,zz9.99 CR" WITH NO-BOX.
          END./* FOR EACH OrderLine */
        END. /* FOR EACH Order */
      END. /* FOR EACH Customer */
      . . .

These notes explain the code highlights:

  • The WHERE clause of the third FOR EACH relates the OrderLine table back to the Order table.
  • For each OrderLine, there is a single Item record that contains information about the ordered item. A simple FIND statement retrieves this information.
  • The report totals the result of an expression. Notice the absence of the BY break group syntax.
  • The FORMAT option here specifies a fairly complex format string. The result displays a leading dollar sign ($), suppresses the leading zeroes (z), and displays the credit symbol (CR) when the result is a negative value.

The following shows the output of the final version of this report: