Skip to content

Finding Records

Except for the first and last record of a table (or index), records are found by moving a finding value(s) to the appropriate column(s) of the table's record buffer. The record buffer is established when an open command is executed on the table.

The first record of a table can be found from an empty buffer by executing a find command on its record number with the ge (greater than or equal to) comparison operator. The last record of a table can be found by executing a find le by recnum on an empty (freshly opened or freshly cleared) buffer. The first record by an index can be found from an empty buffer by executing a find ge by the desired index, or on the column whose main index is the desired index. The last record by an index can be found with find le by the index or field. On clear buffers, ge gives the same result as gt, and le gives the same result as lt.

Records other than the first or last are found by moving the key value of the desired record to the appropriate column in the record buffer and then executing a find with the appropriate comparison operator (eq for an exact match, ge for the match if it exists, or the next record if it does not, gt for the next record after the key value, lt for the previous record, or le).

Open Villains
Move "Jekyll" to Villains.Alias
Find eq Villains.Alias

In this example, the villains table is searched for the record whose alias column has the value Jekyll. If no record has a matching value in that column, the predefined indicator found is set to false.

Finding by Ordinal Position

Any record in a table can be found by its ordinal position in the table, an attribute of a record that can be dealt with as though it were a column (the first column, or column 0) with a reserved name of recnum. In certain cases where recnum is used as an account or serial number, or where table relationships are based on record number (both not generally recommended), this may be the appropriate way of finding specific records. It is always faster than finding by an index.

The most common occasion for finding by record number is when simply traversing the entire table in its physical record number order for a report or the like. If, for example, the total of all the records for a certain column were needed, the appropriate way to accumulate the total would be to perform a find gt ... by recnum on a clear buffer, and repeat that find in a loop until no more records were found.

Finding by Index Values

Finding records by the values of indexes, or key columns, is similar to finding records by recnum, and is more often the needed function in real-life situations, especially for finding specific records (rather than touring ranges of records).

In real-life situations, indexes are made up of values from not just one, but more usually several columns. Each column participating in an index represents a segment of that index. When finding by indexes, it is essential that at least the initial segment have a value to find by.

For example, if an index (Index 1) were made up of two columns, firstName and lastName, a find gt ... by 1 after moving the value Park to column lastName (and leaving firstName blank) would find the first record with a lastName, firstName value greater than Park—the first Park with a non-blank firstName, if there were one in the table. If, on the other hand, a find eq command were executed under these conditions, only a Park with a blank firstName would be found.

Executing the same searches (using the same index) with lastName blank and a value in firstName would probably be an error. In any case, the find gt would find the first record in the table having a non-blank lastName (Abernathy), and the find eq would find the first record with a blank lastName—probably an illegal condition that would not exist in the table, producing a find failure.

Using Fill_Field

Fill_field moves the highest or lowest value (as specified in the command) possible for a column's size and type into the record in the buffer for the specified table. This is useful for finding the first or last record in the table by the index based on the column, particularly in tables of a DBMS other than the embedded DataFlex database. Since it is designed for use with tables that may not be embedded DataFlex, this command addresses tables and columns by their numbers, not by their names.

Note

The technique of writing Index.IndexNumber in your code is considered legacy code.

Instead of writing:

Find Customer by Index.2

you should use:

Find Customer by 2

One reason for this is that the legacy technique does not work for indexes higher than 15.

Unique Key Values

All key values in an index must be unique. It is often desired, however, to index records on columns whose values may not be unique. The surnames of persons are a good example of this, and the last and first names, in combination, of persons remain a good example, in most cases. Where an index must be based on non-unique values, a unique element, whether displayed or not, must be added to the index, and should be its last segment. Use of recnum for this purpose increases the number of segments in the index by one, but consumes the least possible space in the index file and imposes the least cost in terms of performance. The sequential output of like-key records in such cases will be by record number, that is, arbitrary in most cases.

When finding by non-unique indexes, the eq (exact match) comparison operator is virtually never appropriate, or effective. Continuing with the lastName, firstName example, Index 1 would be made up of lastName, firstName, and recnum. If a find eq were attempted with recnum clear and only lastName and firstName specified, no record would ever be found, because the recnum of every record satisfying the requirements of the first two segments would not be blank, or zero. Therefore, only finds ge, gt, le, and lt would normally be appropriate.

Defining an Index

When defining an index, you will be called upon to indicate what column the index is Main for. This designation is required for the two situations where a find is specified not by the number of an index, but by the name of a column.

  1. The first situation in which this is done is when a find command is programmed with the syntax find gt| ge| eq| le| lt df_TableName.ColumnName. In order for a find to occur in this situation, ColumnName must have a Main index in the definition of df_ColumnName.

  2. The second situation in which a find is specified by a column rather than an index is when a find is launched by use of a hotkey when the cursor is in a data entry object. When the control is bound to a table column by an entry_item command, DataFlex can identify the column by which the search is to be conducted. The Main index of that column is used. When the column does not have a Main index, an error is reported advising users that the column by which the find was attempted has no index. This does not mean that the column does not participate in any index (it may participate in several), but rather, that no Main index is designated for the column.

No column may have more than one Main index, but more than one column may designate the same Main index. The lastName, firstName index would probably be the Main index not only for column lastName, but for column firstName as well.

Index Buffering

The open command provides an option for naming an index of the table on the command line. When an index is so named on the open command line, the index is "buffered"—that is, blocks of the index are moved into memory as records are found so that it is not necessary to access the index file each time another record is to be found.

Open Villains by 1

In this example, the table villains is opened with its Index 1 buffered.

Index buffering is effective when all (or major portions of) the records of a table are to be accessed in order by an index, without gaps, as when traversing the entire table, or ranges thereof. If used for these situations, it improves the performance of the program. If used at other times, such as during random access of records, it may slow performance somewhat, and in any case consumes memory needlessly. If used during data entry, it will frustrate the operation of online indexes, creating the appearance that they are corrupted.

Using Index Data

When after a find in your program, exclusively record data that is contained in the index in use is used, the record buffer is not loaded after the find. The required data is drawn from the index itself, improving the speed of repetitive finds considerably. Such repetitive finds are involved, for example, in selections from the database, such as all agents in Sri Lanka, or all license plates issued in St. Gotthard canton.

By default, the DataFlex database driver does this only where no index segment is designated case-insensitive. Using the call_driver command, you can change the value of the FLEX_INDEX_OPT variable from its default FLEX_INDEX_OPT_ON. If you set it to FLEX_INDEX_OPT_OFF, index data will never be returned to the program; the record buffer will be loaded after every find. If you set it to FLEX_INDEX_OPT_PERMISSIVE, index data will be used even if it is from case-insensitive segments. The data in such segments is solid uppercase letters, which may differ from the data in the record.

If the program calls for any data not in the index in use, the record buffer will be loaded and all data will come from the record itself, rather than from the index.

Finding Records in Ancestor Tables

When finding or creating new records in a table that has one or more ancestor tables, it is often desirable to find the records in the ancestor tables to which the new or existing record relates. For example, a sales transaction table might relate to a parent parts table, where what is being sold is quantities of parts, each line item of which is a record in the parent table. The parts table, in turn, might relate to a parent supplier table. The original child, sales, might also relate to a customer table, containing records of customers to whom parts are being sold.

If all three of these ancestors (two parents and one grandparent) have been opened with the open command, the records for the part, the supplier, and the customer can all be found with a single relate command. The command relate sales would move the related-to records into their respective buffers provided the buffer of sales contained valid values to identify the part and the customer. After the find in parts, of course, it would be necessary for the buffer of parts to contain a valid identifier for a supplier to support the ensuing find at the grandparent level.

The relate command sets a series of finds in motion, first in open parent tables, then in open grandparent tables, and so on up the ancestry tree as far as table relationships reach. If any table in the chain is not open, the chain is broken, and the relate up the branch the non-open table was in halts. Since this is often an intended condition, no error is reported in such situations.

Since the finds done in ancestor tables on a relate are based on relationships defined relative to columns, each related-to column in each ancestor table must have a Main index to find by. Furthermore, indexes used for this purpose must be unique indexes—use of a non-unique index for a relationship would cause ambiguity in which record of ancestor tables was related to by records in the descendent tables.

A companion command, attach, works in the opposite direction, and does not involve finding. It is normally used after a relate command. After the relate sales command, for example, an attach sales command would move data from parents to their children. The parts record, for example, having been found by parts.number, would move down to the sales record such data as parts.description and parts.sellPrice, provided sales contained fields for these items.

Constrain Commands

A number of commands whose names begin with constrain are provided for regulating finds throughout a program or application to subsets of the records in a table or group of related tables.

The first of the most important commands is constrain. With this command, you can specify constraint of the records of one or more named tables to those having values in certain columns within a particular range, or to those that relate to whatever specific record from a parent table is in its buffer, or even to complex Boolean combinations of selection criteria.

Two steps besides the constrain command itself are required to make use of the constraint system. The first is to use the constrained_find command when finding subject to constraints, and the second is to control finding loops according to the state of the found predefined indicator, something that is often done in any case with the standard find command. Unlike the find command, constrained_find will fill the buffer with a new record even though found is set to false. constrained_find sets found to false not only when no record can be found, but also when a record is found that violates the constraints specified.

There are two main benefits to using the constraint system. The first is a convenient way of systematically restricting access to records selectively throughout a program or application. The second is in reports where one or more tables (or ranges within tables) must be scanned record-by-record because the criteria fields are not indexed. Although this can be done without the constraint system, it is faster when done with the constraint system. This record-by-record scanning is done with the as expression phrase of constrain. In this form of the command, expression may not contain any local variables; all variables must be global.

Constrained finding and regular finding do not preclude each other in a program. Both can be used in the same program without interfering with each other.

During application development, you can use two predefined integers to determine how many records are being tested in a constrained find, and how many meet the criteria. In that these integers increment continuously while a program runs, you must initialize them to zero at the point where you wish to begin counting, or they will return incorrect values. constrain_tests_count contains the number of records tested by the constraint system, while constrain_found_count contains the number of tested records that satisfy the constraints.

You can also inspect constrain_tests_count and constrain_found_count by using a more sophisticated debugging tool called DataDictionary Inspector in your application.

See Also