ABL has four statements you can use to define a set of one or more database records: FIND, FOR, REPEAT, and OPEN QUERY. FIND fetches a single record when the statement is called. FOR , REPEAT, and OPEN QUERY identify a set of records. You can then loop through the records one at a time.

In this topic, we introduce FIND, FOR, and REPEAT. You learn about OPEN QUERY in the next topic on queries. In this topic, you also learn about ROWID, a unique internal identifier for records.

FIND statement

The FIND statement is a very powerful way to retrieve individual records from the database without having to set up a query or result set definition. FIND fetches a single record from a database table and moves that record into a record buffer. The basic (simplified) syntax for the FIND statement is:

FIND [ FIRST | LAST | NEXT | PREV ] record
     [ WHERE expression ]
record
Database table name.
FIRST | LAST | NEXT | PREV
Finds the first, last, next or previous record in the table that meets the specified characteristics.
WHERE expression
Restricts the query to only those rows in the database table that match the specified expression.

In the following example code, the first record from the Customer table, whose name starts with "A", is retrieved:

FIND FIRST Customer WHERE Customer.Name BEGINS "A".
DISPLAY Customer.Name.

For more information, see Data access without looping: the FIND statement in Develop ABL Applications.

FOR block

The FOR block iterates through a set of related records in the database table and moves each one in turn into the record buffer. The block iterates through all records that match the specified criteria and for as many iterations as requested. The FOR statement is very powerful and includes options for sorting, collating, and record-locking. When the block begins, the AVM evaluates the expression and retrieves the first record that satisfies it. This record is scoped to the entire block. Each time the block iterates, the AVM retrieves the next matching record and makes it available to the rest of the block. When the set of matching records is exhausted, the AVM automatically terminates the block. You don't have to add any checks or special syntax to exit the block at this point. The FOR block ends with an END statement. The basic (simplified) syntax of the FOR block is:

FOR [ EACH | FIRST | LAST ] record [ WHERE expression ]:
  /* ABL statements */
END.
EACH | FIRST | LAST
  • EACH starts an iterating block, finding a single record on each iteration.
  • FIRST finds the first record.
  • LAST finds the last record.
record
Database table name.
WHERE expression
Restricts the query to only those rows in the database table that match the specified expression.

The following FOR EACH block displays the Customer.Name field for every record in the Customer table where the Customer.CustNum field is less than 100:

FOR EACH Customer WHERE Customer.CustNum < 100:
  DISPLAY Customer.Name.
END.

REPEAT block

The REPEAT block is a set of statements that are processed repeatedly but it does not automatically read records as it iterates. This block lets you navigate through a set of records yourself, rather than simply proceeding to the next record automatically on each iteration. The REPEAT block ends with an END statement.

Often a REPEAT statement statement is used with a PRESELECT phrase to select the records that meet the criteria you specify. PRESELECT creates a result list of ROWID (unique identifier) values, so that records are then retrieved by ROWID.

Typically a FIND statement is used within a REPEAT (PRESELECT) block to read a record on each iteration. There are some best practices to follow when using a REPEAT block:
  1. Use the NO-ERROR qualifier on the FIND statement. This suppresses the error message that you would ordinarily get when you are at the last record.
  2. Use the AVAILABLE function to check for the presence of a record. Provide a matching ELSE statement to LEAVE the block when there is no record available.

The following example code uses a REPEAT block to loop through the customer records where the Country is “USA”. When the last matching record is read, the LEAVE statement breaks out of the REPEAT block.

FIND FIRST Customer NO-LOCK WHERE Customer.Country = "USA".
DISPLAY Customer.CustNum Customer.Name Customer.Country.

REPEAT:
  FIND NEXT Customer WHERE Customer.Country = "USA" NO-LOCK NO-ERROR.
  IF AVAILABLE Customer THEN
    DISPLAY Customer.CustNum Customer.Name Customer.PostalCode.
  ELSE LEAVE.
END.

ROWID

Every database record has a unique internal identifier known as the ROWID. This identifier has the data type, ROWID. You use the ROWID function to retrieve the ROWID of the database record currently in the record buffer.

The following example demonstrates using the ROWID function to retrieve the identifier of the record currently in the buffer. The identifier is later used to re-fetch the record with locking so it can be updated.

VAR ROWID custrid.

FIND FIRST Customer NO-LOCK.
custrid = ROWID(Customer).  // Get the rowid and save it, so it can be refetched.

IF Customer.balance > 0 THEN DO:
  FIND Customer WHERE ROWID(Customer) = custrid EXCLUSIVE-LOCK.
  Customer.Comments = "Balance remaining".   // Update the Comments field in the Customer record
  DISPLAY Customer.Name Customer.Balance Customer.Comments FORMAT "X(20)".
  RELEASE Customer.
END.