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.
// The example shows various uses of the command
// INDEX ON.
PROCEDURE Main
USE Customer NEW EXCLUSIVE
** Simple index
INDEX ON CustNo TO CustA UNIQUE
** Alphabetical index with index name
INDEX ON Upper(LName+FName) TAG CustName TO CustB
** Create subindex from current index
SEEK "MILLER"
INDEX ON Zip TO CustC ;
WHILE Trim(Upper(LName))=="MILLER"
** Descending index
INDEX ON Zip TO CustD DESCENDING
USE Invoice NEW EXCLUSIVE
** Combined index with date and character value
INDEX ON DtoS(InvDate)+PartNo TO InvA
** Index for subset of records
INDEX ON DtoS(InvDate)+PartNo TO InvB ;
FOR InvDate >= CtoD("01/01/93")
RETURN
// In this sample a custom index is created. The index file
// name is composed using the current date to remind the
// date of snapshot creation. The index itself has all
// customers ordered by their current sales total. Because
// the index is a custom index future changes to the
// TOTALSALES field don't affect the order. Therefore the
// order can safely opened, even if the table gets updated.
//
PROCEDURE Main
USE Customer EXCLUSIVE
GOTO 2
FIELD->TOTALSALES := 120
DbCommit()
GOTO 4
FIELD->TOTALSALES := 10
DbCommit()
GOTO 6
FIELD->TOTALSALES := 500
DbCommit()
INDEX ON TOTALSALES FOR TOTALSALES>0 ;
TO ("SALES_SNAPSHOT."+DTOS(Date())+".NTX" CUSTOM
// the index includes record: 4,2,6
GOTO 2
FIELD->TOTALSALES := 5
DbCommit()
GOTO 5
FIELD->TOTALSALES := 400
DbCommit()
// the index still includes record: 4,2,6
CLOSE ALL
RETURN
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.