CAN-FIND function

Returns a TRUE value if a record is found that meets the specified FIND criteria; otherwise it returns FALSE. CAN-FIND does not make the record available to the procedure. You typically use the CAN-FIND function within a VALIDATE option in a data handling statement, such as the UPDATE statement.

You can use CAN-FIND to see if a record exists with less system overhead than that of a FIND statement. The query capabilities are similar. CAN-FIND is also useful for implementing inner joins among database tables.

Syntax

CAN-FIND 
  (
    [ FIRST | LAST ] record [ constant ]
    [ OF table ] [ WHERE expression ] [ USE-INDEX index ]
    [ USING [ FRAME frame ] field
        [ AND [ FRAME frame ] field ] ...
    ]
    [ SHARE-LOCK | NO-LOCK ] [ NO-WAIT ] [ NO-PREFETCH ]
  )

You can specify the OF, WHERE, USE-INDEX, and USING options in any order.

FIRST
Returns TRUE if CAN-FIND locates a record that meets the specified criteria; otherwise returns FALSE.
LAST
Returns TRUE if CAN-FIND locates a record that meets the specified criteria; otherwise returns FALSE.
record
The record buffer you are checking for existence.

To use CAN-FIND to locate a record in a table defined for multiple databases, you might have to qualify the record's table name with the database name. See the Record phrase reference entry for more information.

constant
The table you want to use has a primary index; the constant is the value of the last component field of that index for the record you want.
OF table
Qualifies the records to use by relating the record to a record in another table.
WHERE expression
Qualifies the record that CAN-FIND searches for. The expression must return a TRUE or FALSE value.
USE-INDEX index
Identifies the index you want CAN-FIND to use to find a record. If you do not use this argument, the AVM selects an index to use based on the criteria specified with the WHERE, USING, OF, or constant arguments.
USING [ FRAME frame ] field [ AND [ FRAME frame ] field ]
One or more names of fields you want to use to search for a record. The field you name in this argument must have been previously entered into a screen field, usually with a PROMPT-FOR statement. The field must be viewed as a fill-in or text widget.
SHARE-LOCK
Specifies that CAN-FIND determines whether the record can be SHARE-LOCKed. If you use this option without the NO-WAIT option, and if the record is EXCLUSIVE-LOCKed, CAN-FIND waits until that lock is released before returning. If you use SHARE-LOCK with the NO-WAIT option, then CAN-FIND returns a FALSE value immediately if the record is EXCLUSIVE-LOCKed.
NO-LOCK
Specifies that CAN-FIND determines whether the record can be accessed with the NO-LOCK option. This is the default for CAN-FIND.
NO-WAIT
Causes CAN-FIND to return immediately and return FALSE if the record is locked by another user.

If you use NO-WAIT together with a SHARE-LOCK and the record found is EXCLUSIVE-LOCKed, the CAN-FIND function does not wait and returns FALSE.

NO-PREFETCH
Specifies that only one record can be sent across the network at a time. If you do not specify this option, the AVM might send more than one record from the server to the client in each network packet.

Example

In the following procedure, the UPDATE statement uses the VALIDATE option to make sure that the salesrep entered matches one of the salesreps in the database. The VALIDATE option uses the CAN-FIND function to find the record.

r-canfnd.p

REPEAT:
  CREATE Customer.
  UPDATE Customer.CustNum Customer.Name Customer.SalesRep
    VALIDATE(CAN-FIND(SalesRep.SalesRep 
      WHERE SalesRep.SalesRep = Customer.SalesRep),
      "Invalid sales rep -- please re-enter").
END.

Notes

See also

FIND statement