Ordering by Multiple Fields

To increase the speed of queries or views that have filters on more than one field, you can order your records by specifying multiple fields in the index expression. The fields are evaluated in the same order they appear in the expression. If you create an index expression using numeric fields, the index orders the records by the sum of the fields, not the fields themselves.

To order records using multiple fields

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

2. In the Indexes tab, enter a name and type for the index.

3. In the Expression box, enter an expression that lists the fields you want to order by.

   For example, you might want to order the records by country, postal code within the country, and the company name within the postal code. You can use a plus sign (+) to create the index expression from character fields.

   customer.country + customer.postalcode + customer.company
   

   Notice that the field that changes the least is the first one in the list.

4. Choose OK.

If you want to use fields of different data types, you can convert the non-character fields to characters by enclosing the field with STR( ). For example, you can try ordering the records by the Maximum Order Amount field, then by the company name. In this expression, maxordamt is a Currency field and company is a Character field.

STR(customer.maxordamt,20,4) + customer.company

See Also

Permitting Duplicate Values | Setting Record Order at Run Time | Working with Records | Table Designer | INDEX Command | Using Other Index Types | Creating One Index | Creating Multiple Indexes | Index Creation for Tables