Command INDEX Foundation
Creates an index file.
INDEX ON <IndexKey> [TAG <cTagName>] ; TO <cIndexFile> ; [FOR <lForCondition>] ; [WHILE <lWhileCondition>] ; [NEXT <nCount>] ; [RECORD <xRecordID>] ; [EVAL <bBlock> [EVERY <nStep>] ] ; [REST] ; [ALL] ; [UNIQUE] ; [ASCENDING | DESCENDING] ; [ADDITIVE] ; [CANDIDATE] ; [SUBINDEX] ; [CUSTOM] Or for index files with multiple indexes: INDEX ON <IndexKey> TAG <cTagName> ; [TO <cIndexFile>] ; [FOR <lForCondition>] ; [WHILE <lWhileCondition>] ; [NEXT <nCount>] ; [RECORD <xRecordID>] ; [EVAL <lExpression> [EVERY <nStep>] ] ; [REST] ; [ALL] ; [UNIQUE] ; [ASCENDING | DESCENDING] ; [ADDITIVE] ; [CANDIDATE] ; [SUBINDEX] ; [CUSTOM]
The index command INDEX ON creates an index in the current work area. The command is similar to DbCreateIndex(), but allows several indexes to be created per index file. However, this capacity depends on the ability of the active database engine (DBE) to support index files containing several indexes. Also, the options FOR, WHILE, EVAL, EVERY and DESCENDING require an appropriate DBE to be available (refer to the "Specifications of DatabaseEngines" chapters in the Xbase++ documentation).
The command INDEX ON closes all open index files in the current work area and recreates the index file <cIndexFile>. The index file is opened exclusively and the index is constructed in it. If the file cannot be exclusively opened (such as in a network environment), a runtime error is generated. Otherwise, after completion of the indexing, the index becomes the controlling index in the current work area and the record pointer is positioned on the first logical record.
When a DBE is used which supports several indexes per index file, <cIndexFile> does not need to be specified if a controlling index is already available in the work area. In this case, the index <cTagName> is added to the index file containing the controlling index.
An index makes possible the logical sorting of records based on the index key <IndexKey>. The value of the index key is contained in the index file <cIndexFile> and directly refers to the corresponding record. The records in the work area then appear in the same order as the index key values appear in the index file. The physical order of the records remains unchanged. This permits very rapid access when searching for specific records.
INDEX ON creates an index with character values sorted according to their lexical order, numeric values sorted by size, date values sorted chronologically and logical values sorted with .F. (false) considered smaller than .T. (true). An index cannot be created based on memo fields.
To create an index in strict alphabetical order, character values must be converted to upper case using the function Upper(). Otherwise, the sorting occurs based on the sorting weight of the characters selected with SET COLLATION.
An index expression must always return a value of constant length. Indexing character values using the function RTrim() leads to a defective index, since the resulting values have varying lengths if they have blank spaces at the end.
When date fields are combined with other fields in an index, date values should always be converted to character values using the function DtoS() and never with DtoC(). Otherwise, chronological order is not preserved.
Only field variables (database fields), and not memory variables, should be used in an index expression. Memory variables exist only at runtime and if they are referenced in an index expression, it will probably lead to a defective index.
Records marked for deletion or filtered out continue to appear in the index file.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.