Show/Hide Toolbars


NoteThis command is defined in a header file and will be preprocessed by the X# preprocessor to a function call. If you disable the standard header (-nostddefs) files then this command will not be available. If you tell the compiler to use a different standard header file (-stddef ) then this command may also be not available


Create an index file and add an order to it.


INDEX ON <uKeyValue> [TAG <xcOrder>] [TO <xcIndexFile>]
[<Scope>] [WHILE <lCondition>] [FOR <lCondition>]
[EVAL <cbEval> [EVERY <nInterval>]


Note: Although both the TAG and the TO clauses are optional, you must specify at least one of them.


<uKeyValue>The order key expression.  The data type of the key expression and all other limitations, including the length of the key and the key expression, are determined by the RDD.


TAG <xcOrder>The name of the order to be created.  For single-order index files, the file name without an extension or path, is the default order name.  For multiple-order index files, the order name is required.


TO <xcIndexFile>The name of the target index file, including an optional drive, directory, and extension.  See SetDefault() and SetPath() for file searching and creation rules.  The default extension is determined by the RDD and can be obtained using DBOrderInfo(DBOI_INDEXEXT).
In RDDs that support production indexes , the production index file is assumed if <xcIndexFile> is not specified.
If <xcIndexFile> does not exist, it is created.
If <xcIndexFile> exists, the INDEX command must first obtain exclusive use of the file.  If the attempt is unsuccessful because, for example, the file is open by another process, NetErr() is set to TRUE.
If the attempt is successful and the RDD specifies that index files can only contain a single order, the current contents of the file is erased before the new order is added to it.  If the RDD specifies that index files can contain multiple orders, <xcOrder> is added to <xcIndexFile> if it does not already exist; otherwise it is replaced.


<Scope>The portion of the current database file to process.  The default scope is all records.  INDEX ignores the DbSetFilter() and SetDeleted() settings, as well as any filter imposed by the current controlling order.
The scope is not stored in the index file and is not used for reindexing or update purposes.


WHILE <lCondition>A condition that each record within the scope must meet, starting with the current record.  As soon as the while condition fails, the process terminates.  If no <Scope> is specified, having a while condition changes the default scope to the rest of the records in the file.
The while condition is not stored in the index file and is not used for reindexing or update purposes.


FOR <lCondition>A condition that each record within the scope must meet in order to be processed.  If a record does not meet the specified condition, it is ignored and the next record is processed.  Duplicate key values are not added to the index file when a for condition is used.
Unlike the while condition and the scope, the for condition is stored as part of the index file and is used when updating or rebuilding the order with DbReindex() or REINDEX.  Any limitations on the for condition are determined by the RDD.
Note:  If no <Scope>, while condition, or for condition is specified, the index uses the condition specified by DBSetOrderCondition(), if any.


EVAL <cbEval>A code block that is evaluated at intervals specified by EVERY <nInterval>.  The default interval is 1.  This is useful in producing a status bar or odometer that monitors the ordering progress.  The return value of <cbEval> must be a logical value.  If <cbEval> returns FALSE, indexing halts.


EVERY <nInterval>A numeric expression that determines the number of times <cbEval> is evaluated.  This option offers a performance enhancement by evaluating the condition at intervals instead of for every record processed.  The EVERY keyword is ignored if you specify no EVAL clause.


UNIQUECreates the order with uniqueness as an attribute, which means that only those records with unique key values will be included in the order.  If UNIQUE is not specified, SetUnique() is used to determine the order's uniqueness attribute (refer to SetUnique() for more information on how unique orders are maintained).
Note that keys from deleted records are also included in the index, and may hide keys from non-deleted records.


ASCENDINGSpecifies that the keys be sorted in increasing order.  If neither ASCENDING nor DESCENDING is specified, ASCENDING is assumed.


DESCENDINGSpecifies that the keys be sorted in decreasing order.  Using this keyword is the same as specifying the Descend() function within <uKeyValue>, but without the performance penalty during order updates.  If you create a DESCENDING index, you will not need to use the Descend() function during a SEEK.
Whether an order is ascending or descending is an attribute that is stored in the index file and used for reindexing and update purposes.


USECURRENTSpecifies that only records in the controlling order — and within the current range as specified by OrdSetScope() — will be included in this order.  This is useful when you have already created a conditional order and want to reorder the records which meet that condition, and/or to further restrict the records meeting a condition.  If not specified, all records in the database file are included in the order.


ADDITIVESpecifies that any open orders should remain open.  If not specified, all open orders are closed before creating the new one.  Note, however, that the production index file is never closed.


CUSTOMFor RDDs that support them, CUSTOM specifies that a custom built order will be created.  A custom built order is initially empty, giving you complete control over order maintenance.  The system does not automatically add and delete keys from a custom built order.  Instead, you explicitly add and delete keys using OrdKeyAdd() and OrdKeyDel().  This capability is excellent for generating pick lists of specific records and other custom applications.


NOOPTIMIZESpecifies that the FOR condition will be optimized.  If NOOPTIMIZE is not specified, the FOR condition will be optimized if the RDD supports optimization.


After it is created (or replaced), the new order is added to the order list for the work area.  Other orders already associated with the work area, including the controlling order, are unaffected.

If no order list exists for the work area, the type of RDD determines how the controlling order is set.  For RDDs that support only single-order index files (such as DBFNTX), the controlling order is set to the order in the specified index file.  For RDDs that support multi-order index files (such as DBFMDX), the controlling order is normally set to the first order in the index file.


RDD support:  Not all RDDs support all aspects of the INDEX command.  


The following example creates a single-order index based on the Acct field:


USE customer NEW
INDEX ON Customer->Acct TO CuAcct


This example creates a conditional order based on a for condition.  This index will contain only records whose TransDate is greater than or equal to January 1, 1999:


USE invoice NEW
INDEX ON Invoice->TransDate TO InDate ;
  FOR Invoice->TransDate >= CTOD("01/01/2020")


The next example creates an order in a multiple-order index file:


INDEX ON Customer->Acct TAG CuAcct TO customer


This example creates an order that calls a routine MyMeter() during its creation:


DEFINE Mtr_Increment := 10
  USE customer NEW
  INDEX ON Customer->Acct TO CuAcct EVAL ;
     {|| MyMeter() } EVERY Mtr_Increment
  STATIC nRecsDone := 0
  nRecsDone += Mtr_Increment
  ? (nRecsDone/LastRec()) * 100




See Also

CLOSE, DToS(), DBCreateIndex(), DbCreateOrder() DbOrderInfo(), DbSeek(), DbSetIndex(), DbSetOrder(), DbSetOrderCondition(), DbReindex(), OrdCondSet(), OrdKeyAdd() OrdKeyDel(), OrdScope(), REINDEX, SEEK, SET INDEX, SET ORDER,SetDefault(), SetPath() SORT, Soundex() USE