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 ] )

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.

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

Fields do not have to be indexed to use them in a CAN-FIND function. For example, you can use the following CAN-FIND function with the sports2000 database, even though the State field is not indexed:


CAN-FIND(FIRST Customer WHERE Customer.State = "NH")

However, when you use CAN-FIND on a non-indexed field, the response might be slow, as with a FIND.

You can name more than one field as part of the selection criteria. For example, the following CAN-FIND function works with the sports2000 database:


CAN-FIND(Customer WHERE Customer.CustNum = x AND Customer.Name = y)

CAN-FIND supports selection criteria that uses inequality matches. Therefore, you can use Boolean operations in WHERE clauses.

EXCLUSIVE lock is not allowed in a CAN-FIND because CAN-FIND does not return a record.

If you use the CAN-FIND function to find a record in a work table, the AVM disregards the NO-WAIT, SHARE-LOCK, and NO-LOCK options.

You can nest CAN-FIND functions. For example, you can use CAN-FIND(... WHERE CAN-FIND(...WHERE CAN-FIND, etc.

The CAN-FIND function does not cause FIND triggers to execute; hence a procedure can use this function to bypass the FIND trigger and check for the existence of records. Anyone writing a FIND trigger for security reasons should be aware of this.

You cannot use the CAN-FIND function in a query’s WHERE clause. Doing so generates a compiler error.

Within a CAN-FIND function, if you compare tables or fields from multiple databases, you must explicitly specify the database name along with the table and field name.