Creating One Index

  • Article

When you first create a table, Visual FoxPro creates the table's .dbf file and, if your table includes Memo or General fields, the associated .fpt file. You can choose to quickly add an index to a field as you define the field; otherwise, no index files are generated at that time. Records you enter into the new table are stored in the order you entered them; when you browse the new table, they appear in the order they were entered.

Typically, you'll want to be able to view and access the records in your new table in a specific order. For example, you may want to view the records in your customer table alphabetically by company name. When you want to control the order in which records are displayed and accessed, you create an index file for your table by creating the first ordering scenario, or index key, for your table. You can then set the order of the table to the new index key, and access the table's records in the new order.

To create an index key for a table

  1. In the Project Manager, select the table you want to add an index to, then choose Modify.

  2. In the Table Designer, select the Indexes tab.

  3. In the Name box, type a name for the index.

  4. From the Type list, select Regular as the index type.

  5. In the Expression box, type the name of the field you want to order the records by.

    -or-

    Build an expression by choosing the dialog button at the end of the box to display the Expression Builder.

  6. If you want to select records, enter a filter expression in the Filter box or select the button at the end of the box to build an expression.

  7. Choose OK.

    -or-

  • Use the Index Command.

For example, the following code uses the table customer and creates an index key on the city field. The keyword TAG and the word "city" afterward specifies a name, or tag, for the new index key on the city field.

USE customer
INDEX ON city TAG city

In the previous example, the tag for the index key uses the same name as the field you're indexing. The names don't have to match — you could also choose to give the index key a different name.

When you first create an index using the INDEX command, Visual FoxPro automatically uses the new index to set the order of the records in the table. For example, if you entered some data into the sample table created in the previous example, and then browsed the table, the records would appear in order by city.

Once you create an index for the table, you can use it to order your records.

To order records using an index

  1. In the Project Manager, select the table with the index you created.
  2. Choose Browse.
  3. From the Table menu, choose Properties.
  4. In the Index order box, select the index you want to use.
  5. Choose OK.

The table appears in the Browse window, displaying the records in the order your index specifies. You can also order the results of queries or reports by running them while the index is selected.

See Also

Index Creation for Tables | Index File Creation | Working with Records | Index Information Display | Setting a Primary or Candidate Index | Permitting Duplicate Values | Creating Multiple Indexes | Optimization of Tables and Indexes | INDEX | Ordering by Multiple Fields