Navigating a results list

As shown in the table Record Fetching, results lists are associated with the OPEN QUERY and GET statements. However, the AVM only guarantees a results list if you first define the query with the SCROLLING option. For example:

DEFINE QUERY custqry FOR Customer SCROLLING.

This option indicates to the AVM that you want to use the results list for multi-directional navigation.

You can use the REPOSITION statement to specify how many places forward or backward you want to move, so that you can skip over a given number records. It also allows you to move to a specific ROWID.

The REPOSITION statement changes your location in the results list but does not actually fetch the record (unless the query is associated with a browse widget). To actually fetch records in a results list, you use the GET statement. The following example illustrates how the REPOSITION statement works:

DEFINE VARIABLE rid AS ROWID NO-UNDO.   /* to save the ROWID of cust 4 */

DEFINE QUERY q FOR Customer SCROLLING.

OPEN QUERY q FOR EACH cust.
GET NEXT q.                 /* gets cust no. 1 */
GET NEXT q.                 /* gets cust no. 2 */
                            /* query is positioned ON cust 2 */
GET PREV q.                 /* gets cust no. 1 */
REPOSITION q FORWARD 0.     /* query is positioned BETWEEN cust 1 and 2 */
GET NEXT q.                 /* gets cust no. 2 */
                            /* query is positioned ON cust 2 */
REPOSITION q FORWARD 1.     /* query is positioned BETWEEN cust 3 and 4 */
GET NEXT q.                 /* gets cust no. 4 */
rid = ROWID(cust).          /* query is positioned ON cust 4 */
REPOSITION q BACKWARD 2.    /* query is positioned BETWEEN cust 2 and 3 */
GET PREV q.                 /* gets cust no. 2 */
REPOSITION q TO ROWID(rid). /* query is positioned BETWEEN cust 3 and 4 */ GET
NEXT q.                     /* gets cust no. 4 */

After a record is fetched (with a GET statement), the results list position is on the ROWID, so that GET NEXT gets the next record, and GET PREV gets the previous record. After a REPOSITION, the position is always between two records. Thus, REPOSITION FORWARD 0 repositions the results list immediately after the current record. GET NEXT fetches the next record; GET PREV fetches the previous record. REPOSITION FORWARD 1 repositions the results list between the next record and the record after it.

To find the total number of rows in a results list, you can use the NUM–RESULTS function. To find the current position within a results list, you can use the CURRENT–RESULT–ROW function.

As shown in the table Record Fetching, the AVM creates results lists for FOR EACH statements and for DO and REPEAT statements with the PRESELECT phrase. However, you cannot navigate freely through a results list created for the FOR EACH statement. If the results list was created for the FOR EACH statement, then the AVM automatically steps through the results list in sorted order. For example:

FOR EACH Customer NO-LOCK BY Customer.Name:
  DISPLAY Customer.Name Customer.City Customer.State.
END.

Within a PRESELECT block, you can use the FIND statement to move backwards and forwards through the results list:

/* This code fragment displays all Customers in descending order */

DO PRESELECT EACH Customer NO-LOCK:
  FIND LAST Customer.    /* last position in list */
  DISPLAY Customer.CustNum Customer.Name WITH FRAME a DOWN.
  REPEAT:
    FIND PREV Customer.  /* move backward through list */
    DOWN WITH FRAME a.
    DISPLAY Customer.CustNum Customer.Name WITH FRAME a.
  END.
END.