See Also: Understanding File Buffers and DDO Field Buffers, Found indicator, Clear, Constrained_Clear, Constrained_Find, Open, VFind
To retrieve a record from a database table on disk, and place it in the record buffer. The DataDictionary class provides superior functionality that replaces the need for this command.
Find LT | LE | EQ | GE | GT {table} By Recnum | {IndexNumber}
Find LT | LE | EQ | GE | GT {table.column}
Where
{table} is the table name of an open database table; and {table.column} is the tableName.columnName of a database table and column.
{IndexNumber} is the number of the index to use.
The Find command locates a record in {table} based on the data in the record buffer, the index selected, and the find mode (LT, LE, EQ, GE, or GT) specified.
If the Find command is successful (see below), the found record is read from the disk into the record buffer, the record buffer becomes "Active" (it contains a valid record from the database). Subsequent calls to the Boolean expression Found will return TRUE.
If the Find command is not successful, the Boolean expression Found will return FALSE and the record buffer remains as it was prior to the find.
The Find command uses one of 5 modes for the relationship between the data in the buffer and the data in the found record:
LT record data LESS THAN buffer data
LE record data LESS THAN OR EQUAL TO buffer data
EQ record data EQUAL TO buffer data
GE record data GREATER THAN OR EQUAL TO buffer data
GT record data GREATER THAN buffer data
Index comparisons take into account the sorting order of the index segments (ascending or descending) when calculating which value is greater. Consider a database table "Scientists" where index 1 is sorted on a Name column in ascending order and index 2 is sorted on the same column, but in descending order.
Suppose there are five names in the database: "Cindy", "Marcia", "Oscar", "Sean", and "Scott". The example below illustrates how the order of the index affects the behavior of the Find Command.
Open Scientists
Move "Oscar" to Scientists.Name
Find Gt Scientists By 1
// The record just found will be the record with "Sean"
Move "Oscar" to Scientists.Name
Find LT Scientists By 1
// The record just found will be the record with a name column of "Marcia"
Move "Oscar" to Scientists.Name
Find Gt Scientists By 2
// The record just found will be the record with a name column of "Marcia"
Move "Oscar" to Scientists.Name
Find LT Scientists By 2
// The record just found will be the record with a name column of "Sean"
Close Scientists
Find GT on a clear buffer will find the first record of the table for the given index. Find LT will return the last record in the index.
There are two forms of the Find command:
1. Naming the index explicitly, by number:
Find GE customer By 3
In this example, Index 3 is used to find a record in Database File customer. The first record will be found according to Index 3 whose value(s) in the column(s) which make up Index 3 is greater than or equal to (GE) the value(s) previously put into the record buffer for the column(s).
2. Naming the column whose main index is to be used:
Find GE customer.name
The main index for the column "name" of the database table customer is used for the Find command.
For non-standard tables (e.g. Recnum tables), Recnum, the record number, is Field 0 in every table, and may always be found by as though it is an index. For tables lacking actual indexes, it is the only "index".
A Find GT or GE command on a cleared record buffer will find the first record in the table by the index specified. A Find LT or LE will find the last record for the specified index.
A find GT, LE or LT on an index with seed values (values to search for) that exceed the last record in the index will result in finding the last record in the index.
Move 999999 to Customer.Customer_number
// Index 1 is on customer_number
Find GE Customer by 1
In the above example code, if the highest customer number is less than 999999, the last customer record in index 1 will be found.
Find commands must specify an index or indexed column to use. A find command executed on non indexed columns will generate an error.
In the case of a multi segment index, DataFlex uses all segments of the index to execute the find, including the record number, if it is part of the index. This is of particular importance in the case of non-unique indexes, which (for Recnum tables) always include the record number as the last segment.
Indexes may be created in table definitions in the Studio. Existing indexes may be redefined in the Studio and from DataFlex programs. Indexes may be rebuilt with Database Builder or the sort command. The "uppercase" and "descending" options may be applied to the segments that make up an index. The Uppercase option affects the order of finding, and when the descending option is used, it reverses the effects of LT, LE, GE, or GT.
If a Find command is executed on an active, edited record buffer, DataFlex Error 82 (Edited record not saved) will be triggered. The record buffer becomes edited when data has been moved to it. The buffer becomes unedited automatically by saving the record. If you wish to deactivate the buffer and maintain the data without saving, you can do so by moving 0 to tableName.recnum.
A Find GE always finds a record unless the table is empty. A find GE even after the last record in the table will still return the last record. It always sets the Predefined Indicators Found to True and FindErr to False. Note that this behavior is different than for LE. A find LE before the first record in the table will not return a record, and will set the Predefined Indicators Found to False and FindErr to True.
Subsequent calls to the Boolean expression Found after a successful find will return TRUE. After a find error, Found will return FALSE.
Subsequent calls to the Boolean expression FindErr after a successful find will return FALSE. Otherwise, FindErr will return TRUE to show that an error occurred.
Find EQ commands may only be used with unique indexes. If they are used with non unique indexes, they will never find a record.
After a find, programs will not load the record buffer with record data until the program calls for data not contained in a non-case-sensitive segment of the index used for the find. This can improve program performance considerably where no data is called for that would require the record buffer to be loaded. This default behavior of the DataFlex database driver can be changed by using the FLEX_SET_INDEX_OPT function with the call_driver command. Optimization can be turned off, so that the record buffer is always loaded, or it can be set "permissive," in which data in case-insensitive index segments is also eligible for use.
the VFind command can be used to find records using an integer variable, rather than a table and column name.