Sometimes you just want a single record from a table. In that case, you can use the FIRST or LAST keyword in place of EACH, or possibly use no qualifier at all. For example, if you want to retrieve Orders and their Customers instead of the other way around, you can leave out the keyword EACH in the Customer phrase, because each Order has only one Customer:
FOR EACH Order NO-LOCK WHERE Order.ShipDate NE ?, Customer OF Order NO-LOCK:
DISPLAY Order.OrderNum Order.ShipDate Customer.Name.
END.
When you use this form, make sure that there is never more than one record satisfying the join. Otherwise, you get a run-time error telling you that there is no unique match.
If you'd like to see just the first Order for each Customer in New Hampshire, you can use the FIRST qualifier to accomplish that:
FOR EACH Customer NO-LOCK WHERE Customer.State = "NH",
FIRST Order OF Customer NO-LOCK:
DISPLAY Customer.CustNum Customer.Name Order.OrderNum Order.OrderDate.
END.
Be careful, though. This form might not always yield the result you expect, because you have to consider just what is the first Order of a Customer? The AVM uses an index of the Order table to traverse the rows.
