Searching data for the first matching record

You can search an ACL table for the first record that matches your search criteria, or to locate a specific record number. All search operations can be used with character fields. Only the Locate If operation can be used with datetime or numeric fields.

Indexing a character field, in ascending order, is required before using the Find Literal or Seek Expression operations. Both operations search in the indexed field only. If a table is indexed by more than one field (a nested index), only the primary key field is searched, assuming it is a character field indexed in ascending order. If an index is conditional, any records excluded from the view are also excluded from the search.

Partial matching is supported when searching character fields, however the search string must appear at the start of the field. For example, Vendor_Name = "Uni" could find “United Equipment”, but Vendor_Name = "Equip" would not.

For a comparison of the features of ACL search operations, see A comparison of ACL search methods.

To search an ACL table to isolate all matching records, see Searching data to isolate all matching records.

Note

When used to search character fields, all search operations are case-sensitive.

To search data for the first matching record:

  1. Select Data > Search.
  2. Do one of the following:
    • Select Locate Record to find a specific record number in the table.

    • Select Locate If to search an unindexed character field, a datetime or numeric field, or multiple fields in combination.

    • Select Find Literal to use a character literal value to search an indexed character field.

    • Select Seek Expression to use a character-type expression, or a character literal value, to search an indexed character field.

    Note

    The Find Literal and Seek Expression operations are enabled only if an index has been activated.

  3. If you selected Locate Record, type the record number in the Expr text box and click OK.

    Enclosing the record number in quotation marks is optional.

    If the specified record number is found, the table is positioned at that record. If the specified record number is not found, the table is positioned at the first record in the table.

  4. If you selected Locate If, do one of the following:
    • Enter an expression in the Expr text box and click OK.

    • Click Expr to open the Expression Builder, create an expression, click OK, and click OK again.

    The expression can be as simple or as complex as required, can involve one field or multiple fields, and can mix data types. For example:

    • Vendor_Name = "United Equipment"

    • Invoice_Amount > 1000

    • Vendor_Name = "United Equipment" AND Invoice_Amount > 1000 AND Invoice_Date > `20140930`

    You must enclose character literal values in quotation marks, and datetime values in backquotes.

    If the specified value is found, the table is positioned at that record. If the specified value is not found, the table is positioned at the first record in the table.

  5. If you selected Find Literal, type a character literal value in the Expr text box and click OK.

    Do not enclose the character literal value in quotation marks unless the quotation marks are part of the data in the field. For example:

    • United Equipment

    • R (to find the first value starting with “R”)

    If the specified value is found, the table is positioned at that record. If the specified value is not found, the message “No index matched key” is displayed. The table is positioned at the first record with a key field value greater than the specified value, or at the first record in the table if no value is greater than the specified value.

  6. If you selected Seek Expression, do one of the following:
    • Enter a character-type expression or a character literal value in the Expr text box and click OK.

    • Click Expr to open the Expression Builder, create an expression, click OK, and click OK again.

    For example:

    • v_vendor_name

    • "United Equipment"

    You must enclose character literal values in quotation marks.

    If the specified value is found, the table is positioned at that record. If the specified value is not found, the message “No index matched key” is displayed. The table is positioned at the first record with a key field value greater than the specified value, or at the first record in the table if no value is greater than the specified value.

Related concepts
About searching data
Related tasks
Searching data to isolate all matching records
Related reference
A comparison of ACL search methods
Examples of search expressions


(C) 2015 ACL Services Ltd. All Rights Reserved.