Create a new index tag for a table or modify an index tag. An index tag makes the table appear in a sorted order.

To create an index tag, you must be the only person using the table. Unless you opened the table for exclusive use, when you use this command, dBASE Plus asks if you want to reopen the table exclusively. If the table reopens successfully, the Define Index dialog box appears. If it does not reopen, a message box appears, and you must coordinate with other users so you can gain exclusive access to the table.

After you create the index, if you want to share the table and index with other users, you must reopen the table nonexclusively.

To create a primary index, choose Structure|Define Primary Key.

The options in this dialog box vary, depending on the table type.

Dialog box options

 

Options

Choose a sort order for this index:

Ascending order (A-Z, 1-3) or

Descending order (Z-A, 3-1). Note: This option is unavailable for Paradox tables.

Then choose an indexing method:

Distinct Prevents multiple records with the same key value from being included in the table. Records marked as deleted are never included in a distinct index. Distinct indexes may be created for DBF tables only.

Unique Restricts the row included in the index to those with unique key values. If two or more rows have the same key value, only the first row with the key value is included. The "first" row is determined by row number (the order in which you entered rows). This field does not apply to secondary indexes on Paradox tables. A primary index in a Paradox table requires keys to be unique.

Allow duplicates Permits rows with duplicate values to appear in the index.

You can also use the For Expression option to specify which rows in the specified set to include in the index. Enter a condition that restricts the operation to certain rows. For example, the FOR clause COMPANY = "Santa Cruz Dry Goods" restricts the index to rows for which the COMPANY field evaluates to the string "Santa Cruz Dry Goods".

Index key

When defining a DBF index, you can

Specify from field list Select this option when you want to specify an index by moving a field or fields from the Available Fields list to the Fields Of Index list.

Specify with expression Select this option when you want to specify an index using an expression, then type an expression in the Index Key Expression text box.

Index key expression Specifies the field or fields on which to order the table.

For dBASE tables, the key expression can be a field name in the table, such as CITY, LASTNAME, or BALANCE. You can index on character, numeric, float, or date fields. The key expression can also be a combination of fields, values, and functions, such as UPPER(City), LastName+FirstName, or Balance>10000. The Key Expression can be up to 220 characters; the key it evaluates to must be 100 characters or less.

For Paradox tables, the primary index (.PX) must be the first field name in the table structure, or the first and following consecutive field names separated with commas. You can index on alpha, number, money, date, and short fields. Secondary indexes (.Xnn) can be any field in the table or a comma-separated list of fields in the table.

Available fields

Table fields that you can index. Click the fields you want to include in the index;

Fields of index key

Displays the list of table fields on which the index is based.

Index name

A name you give to a complex or composite index (an index made up of more than one field or also, in the case of DBF tables, an index made up of an expression that includes functions and operators). Note: For dBASE Level 5 tables or earlier, you are limited to 10 characters (without spaces) in the index name.

If you're creating a one-field index, the field name becomes the name of the index, and this text box is unavailable. If you are creating or modifying a Paradox index, this option is unavailable

More...

For a more detailed discussion of indexing methods, see the INDEX Xbase language element.

How to get there: In Table designer, choose Structure|Manage Indexes, then choose the New button.