So what Customer do you expect to see as the next Customer after retrieving the first Customer using the CountryPost index (because of the WHERE clause)? If you remember that the default is always to revert to the primary index, then the result shown in the following figure should be clear.
Figure 19. Result of using the primary index
Looking at the sequence of records displayed in the frame for the REPEAT block, it's clear that the AVM is using the primary index (the CustNum index) to navigate through the records. This is unaffected by the fact that the initial FIND was done using the CountryPost index, because of its WHERE clause.
What if you want to continue retrieving only Customers in the USA? In this case, you need to repeat the WHERE clause in the FIND statement in the REPEAT block:
FIND FIRST Customer NO-LOCK WHERE Country = "USA".
DISPLAY Customer.CustNum Customer.Name Customer.Country.
REPEAT:
FIND NEXT Customer NO-LOCK WHERE Customer.Country = "USA" NO-ERROR.
IF AVAILABLE Customer THEN
DISPLAY Customer.CustNum Customer.Name FORMAT "x(20)" Customer.Country
Customer.PostalCode.
ELSE LEAVE.
END.
Each FIND statement is independent of any other FIND statement, even if it refers to the same table, so the WHERE clause does not carry over automatically. If you do this, then the AVM continues to use the CountryPost index for the retrieval, as the output in the following figure shows.
Figure 20. Result of using the CountryPost index for record retrieval
Because the PostalCode is the second field in the index used, the remaining records come out in PostalCode order.
