Click above to get retro games delivered to your door ever month!

 INDEX
 Create an index file or tag
------------------------------------------------------------------------------

 Syntax 1    Syntax 1 creates a new .ntx or .idx, depending on the
                 RDD used to open the table in the current work area.

     INDEX ON <expKey> TO <cIndex>
          [FOR <lCondition>]
          [WHILE <lCondition>]
          [ASCENDING | DESCENDING]
          [UNIQUE]
          [USECURRENT]
          [NONCOMPACT]
          [ADDITIVE]
          [CUSTOM]
          [<scope>]
          [EVAL <lCondition> [EVERY <nRecs>]]

 Syntax 2    Syntax 2 creates a new tag in a compound index (.cdx).

     INDEX ON <expKey> TAG <cTagName> [TO <cCDXName>]
          [FOR <lCondition>]
          [WHILE <lCondition>]
          [ASCENDING | DESCENDING]
          [UNIQUE]
          [USECURRENT]
          [ADDITIVE]
          [CUSTOM]
          [<scope>]
          [EVAL <lCondition> [EVERY <nRecs>]]

     <expKey> is an expression that returns the key value to place in the
     index for each record.  The expression can be any valid CA-Clipper
     expression (including a UDF) with a value whose type is character,
     date, logical or numeric.

     TO <cIndex> specifies the name of the index to create.  This clause
     is used to create a single .ntx or .idx index.  If no extension is
     specified, .ntx or .idx is used, depending on the RDD used to open
     the table in the current work area.

     TAG <cTagName> specifies the name of a new tag for a compound
     index (.cdx).  The name can be up to 10 characters long with the same
     constraints as field names.

     TO <cCDXName> specifies the .cdx in which the specified tag is to
     be created.  If TO <cCDXName> is not specified, the new tag is
     added to the structural .cdx file.  The specified or structural .cdx
     file is created if the tag does not already exist.

     FOR <lCondition> is an expression that returns a logical value (.T.
     or .F.) to determine whether or not a key is included in the index.
     Any valid CA-Clipper expression (including a UDF) can be used.

     WHILE <lCondition> specifies another condition that each record must
     meet.  As soon as a record is encountered that causes the condition to
     fail, the command terminates.  If a WHILE clause is specified, the
     table is processed in the controlling index order.  The WHILE
     condition is transient.  It is not stored in the index file and is not
     used for index updates and REINDEXing purposes.  If an index is to be
     built on the server with a WHILE clause, an Advantage header file must
     be included in the application source file.  This is necessary so that
     the <lCondition> string can be passed to the Advantage server for
     processing.  If an index is to be built on the server with a WHILE
     clause, an Advantage header file must be included in the application
     source file.  This is necessary so that the <lCondition> string can
     be passed to the Advantage server for processing.

     ASCENDING specifies that the index is to be built in increasing
     order of value.  If neither ASCENDING nor DESCENDING is specified,
     ASCENDING is assumed.  Although not stored as an explicit part of the
     index file, ASCENDING is an implicit index file attribute that is
     understood by the REINDEX command.

     DESCENDING specifies that the index is to be built in decreasing
     order of value.  Using this keyword is the same as specifying the
     DESCEND() function within <expKey>, but without the performance
     penalty during index updates.  If you create a DESCENDING index, you
     won't need to use the DESCEND() function during a SEEK.  DESCENDING
     is an attribute of the index file, where it is stored and used for
     REINDEXing purposes.  Note: DESCENDING is not supported for
     NONCOMPACT .idx files.

     UNIQUE specifies that only unique keys are to be included in the
     index. If two or more records have the same key value, only the first
     record encountered will be included.  When the index is updated, only
     unique keys will be added.

     NONCOMPACT builds a non-compact FoxPro-compatible .idx file. This
     index type is faster for a smaller number of records since your PC
     does not need to compress and decompress index keys when building and
     accessing the index.  If this is not specified, compact .idx is
     assumed.  This option does not apply to .cdx or .ntx indexes.
     Note:  DESCENDING is not supported for NONCOMPACT .idx files.

     USECURRENT allows you to keep the current index active while
     creating a new index file.  This gives you the ability to create
     "subindexes" based on the currently active conditional index.

     The ADDITIVE clause tells the Advantage RDD not to close any
     previously opened indexes before creating the new index.  If creating
     a tag, the tag will be placed at the end of the order of the .cdx.
     The new index or tag is placed at the end of the index order.

     CUSTOM is used to create an empty index. This is useful if you wish
     to build your own custom index using AX_KeyAdd() and AX_KeyDrop().
     If CUSTOM is specified, then an empty index or tag is created.
     Otherwise, the index or tag is created normally (default).

     CA-Clipper <scope> clauses allow for record scoping when building an
     index.  One of the following scope clauses may be used to select a
     portion of the table from which to build the index:

        ALL              processes all records in the table.  This is
                         the default.
        NEXT <nRecs>     processes the current record and the specified
                         number of records.
        RECORD <nRecNum> processes the specified record
        REST             processes all records from the current record to
                         the end of the file.

     EVAL <lCondition> is an expression (usually a UDF) that is evaluated
     for each record indexed. This allows the program to display some
     type of completion/process meter without having the UDF embedded in
     the index expression. Indexing stops if the EVAL condition returns
     a (.F.).

     The evaluations take place at record 1, 2, etc. or the set EVERY
     parameter (see below). The record number can be tested within your
     UDF using RecNo().

     The EVAL clause commonly contains UDFs to display the status of
     index creation. The UDF is stored on the client while the index
     building occurs on the server. If the EVAL condition is specified,
     the clause is evaluated every 2 seconds on the client. Advantage
     updates the internal record number and the number of keys included
     so far. If the functions RecNo() and/or AX_KeysIncluded() are used,
     the correct results are returned.

     Note:  When using EVAL, CA-Clipper indicates the percentage based
     on how many records have been extracted from the table to
     sort. CA-Clipper extracts a block of records, sorts it, and repeats
     this process until all records in the table have been extracted.
     Once the blocks are sorted, the index is built. Thus, the
     extraction/sorting of records is more significant than the build
     time. Advantage, on the other hand, has shorter record extraction
     times relative to the build. It is possible that when the EVAL
     clause returns a FALSE to abort an index build, it may take longer
     to abort the index build than expected. If the extraction is done,
     the build continues. Otherwise, if a FALSE is returned during key
     extraction, an empty index is created.

     EVERY <nRecs> is used to specify how often the EVAL expression will
     be evaluated. <nRecs> is a numeric value representing the number
     of records to be processed before the EVAL expression is evaluated.
     If this is specified and if the index is built on the server,
     Advantage ignores the record count. Instead, the status of the EVAL
     is returned every 2 seconds.

     Note:  The WHILE, EVAL, USECURRENT and CA-Clipper <scope> clauses
     are transient. They are not stored in the index file and are not used
     for index updates and REINDEXing purposes.

 Description

     INDEX creates a file containing an index of records in the current
     table based on <expKey>.  When the index file is used, the
     table records appear in key expression order; the index does
     not alter the physical order of records within the table.
     INDEX orders character keys in accordance with the ASCII value of
     each character within the string, numeric values in numeric order,
     date values in chronological order with blank dates treated as low
     values, and logical values sorted with true (.T.) treated as high
     values.  Memo fields cannot be indexed.

     When INDEX is invoked, all open index files in the current
     work area are closed (unless the ADDITIVE clause is used), and the
     index file is created.  When the indexing operation finishes, the new
     index remains open and becomes the controlling index (unless
     ADDITIVE is used), and the record pointer is positioned on the first
     record in the index.

     The new index, <cIndex> or <cCDXName>, is opened exclusively.  If
     this fails, a runtime error occurs.

     Caution:  Do NOT attempt to create a new index or tag of the same
     name as the current index or tag.  If you do, the previous tag is
     deleted and the new index or tag takes its place.

     Note:  Because UDFs and memory variables are visible to the client
     only and since Advantage builds indexes entirely on the server, any
     indexes with index expressions that contain UDFs or memory variables
     will NOT be built by Advantage on the server.  They will instead be
     built on the client, where UDFs and memory variables can be evaluated.

 Example:

     #include "DBFCDXAX.CH"

     USE test VIA "DBFCDXAX"
     INDEX ON last TO last            // Creates LAST.IDX and makes it the
                                      // active order

     INDEX ON age TO age ADDITIVE     // Creates AGE.IDX but keeps LAST.IDX
                                      // as the active order

     INDEX ON state TAG state         // Creates a STATE tag within the
                                      // TEST.CDX file.  This creates the
                                      // TEST.CDX file if it didn't already
                                      // exist.  Since the ADDITIVE clause
                                      // was not used, the other two IDX
                                      // files were closed.