Use the CAN-FIND function

Often you need to verify the existence of a record without retrieving it for display or update. For example, your logic might need to identify each Customer that has at least one Order, but you might not care about retrieving any actual Orders. To do this, you can use an alternative to the FIND statement that is more efficient because it only checks index entries wherever possible to determine whether a record exists, without going to the extra work of retrieving the record itself. This alternative is the CAN-FIND built-in function. CAN-FIND takes a single parameter, which can be any record selection phrase. The CAN-FIND function returns TRUE or FALSE depending on whether the record selection phrase identifies exactly one record in the database.

For example, imagine that you want to identify all Customers that placed Orders before 6/1/2023. You do not need to retrieve or display the Orders themselves, you just need to know which Customers satisfy this selection criterion. The following simple procedure accomplishes this:

FOR EACH Customer NO-LOCK WHERE Customer.Country = "USA":
  IF CAN-FIND(FIRST Order OF Customer WHERE Order.OrderDate < 6/1/2023) THEN
    DISPLAY Customer.CustNum Customer.Name.
  ELSE 
    DISPLAY Customer.CustNum "No Orders before 6/1/2023" @ Customer.Name.
END.

This procedure uses a little display trick you have not seen before. If the Customer has any Orders before 6/1/2023, then the procedure displays the Customer name. Otherwise, it displays the text phrase No Orders before 6/1/2023. If you include that literal value in the DISPLAY statement, it displays in its own column as if it were a field or a variable. To display it in place of the Name field, use the at-sign symbol (@). The following figure shows the result.

The CAN-FIND function takes the argument FIRST Order OF Customer WHERE OrderData < 6/1/2023. Why is the FIRST keyword necessary? The CAN-FIND function returns TRUE only if exactly one record satisfies the selection criteria. If there is more than one match, then it returns FALSE—without error—just as it would if there was no match at all. For example, if you remove the FIRST keyword from the example procedure and change the literal text to be No unique 1997 Order, and rerun it, then you see that most Customers have more than one Order placed in before 6/1/2023:

FOR EACH Customer NO-LOCK WHERE Customer.Country = "USA":
  IF CAN-FIND (Order OF Customer WHERE Order.OrderDate < 6/1/2023) THEN
    DISPLAY Customer.CustNum Customer.Name.
  ELSE 
    DISPLAY Customer.CustNum "No unique Orders before 6/1/2023" @ Customer.Name.
END.

After you page through the results, you see just a few records that don't satisfy the criteria, as shown in the following figure.

Because you do not get an error if there is more than one match, it is especially important to remember to define your selection criteria so that they identify exactly one record when you want the function to return TRUE.

The CAN-FIND function is more efficient than the FIND statement because it does not actually retrieve the database record. If the selection criteria can be satisfied just by looking at values in an index, then it does not look at the field values in the database at all. However, this means that the record referenced in the CAN-FIND statement is not available to your procedure. For example, this variation on the example tries to display the OrderDate from the Order record as well as the Customer fields:

FOR EACH Customer NO-LOCK WHERE Customer.Country = "USA":
  IF CAN-FIND(FIRST Order OF Customer WHERE Order.OrderDate < 6/1/2023) THEN
    DISPLAY Customer.CustNum Customer.Name Order.OrderDate.
  ELSE
    DISPLAY Customer.CustNum "No Orders before 6/1/2023" @ Customer.Name.
END.

This results in the error shown in the following figure, because the Order record is not available following the CAN-FIND reference to it.

If you need the Order record itself then you must use a form that returns it to you:

FOR EACH Customer NO-LOCK WHERE Customer.Country = "USA":
  FIND FIRST Order OF Customer NO-LOCK WHERE OrderDate < 6/1/2023 NO-ERROR.
  IF AVAILABLE Order THEN
    DISPLAY Customer.CustNum Customer.Name Order.OrderDate.
  ELSE 
    DISPLAY "No Orders before 6/1/2023" @ Customer.Name.
END.

When you run this code, you see the OrderDate as well as the Customer fields except in those cases where there is no Order before 6/1/2023, as shown in the following figure.

The samples so far have shown the CAN-FIND function in an IF-THEN statement. You can also use it anywhere where a logical (TRUE/FALSE) expression is valid in a WHERE clause, such as this:

FOR EACH Customer NO-LOCK WHERE Customer.Country = "USA" AND 
  CAN-FIND(FIRST Order OF Customer WHERE Order.OrderDate < 6/1/2023):
  DISPLAY Customer.CustNum Customer.Name.
END.

The next section continues the discussion on building complex procedures, with details on record buffers and record scope.