Share via

Setting Record Order at Run Time

  • Article

Using the SET ORDER command, you can designate the controlling index file or tag. A table can have many index files open simultaneously. However, you determine the order in which the records in a table are displayed or accessed by setting one single-index (.idx) file (the controlling index file) or tag from a compound index (.cdx) file (the controlling tag) as the controlling index. Certain commands, such as SEEK, use the controlling index tag to search for records. You do not need to SET ORDER for running queries.

Setting Record Order Interactively in a Form

You can use SET ORDER at run time to change the order of records in a form. For example, you might want to enable your application's users to reorder the records in a grid by clicking on the header of the column they want to order by.

To sort the records in a grid by columns

  1. Create a form with a Grid control.
  2. Set the ColumnCount property of the grid to the number of fields you want displayed in the grid.
  3. In the Click event for the header of each column in the grid, insert code that:
    • Sets the record order to an index key based on the column.
    • Refreshes the form.

For example, if you created a form based on the Customer table in the Testdata database with a grid containing four columns — company, contact, postal code, and phone — the grid would first appear sorted alphabetically because the records in that table were entered alphabetically.

You could then enable the user to view the grid in contact or postal_code order by inserting the following code in the Click event of each column header:

Sample event code to order records in a Grid by clicking on the column header

Code Comment 
SET ORDER TO company
GO TOP
THISFORM.Refresh
 In the Company header Click event code, reorder the grid by the company index key and refresh the form to display records in order by company. 
SET ORDER TO contact
GO TOP
THISFORM.Refresh
 In the Contact header Click event code, reorder the grid by the contact index key and refresh the form to display records in order by contact name. 
SET ORDER TO
postalcode
GO TOP
THISFORM.Refresh
 In the Postal_Code header Click event code, reorder the grid by the postalcode index key and refresh the form to display records in order by postal code.
  Because sorting by phone number is not relevant to this application, leave the Phone header Click event code blank.

In this example, when the form is first displayed, the grid appears in alphabetical order by company. When the user clicks the header of the Contact column, Visual FoxPro displays the records in the grid in alphabetical order by contact name.

If the user clicks on the Postal_code column header, the grid is resorted and displayed in order by postal code.

Since there isn't a pressing need in our example application to sort contacts by phone numbers, no SET ORDER code is inserted into the Click event for the phone column header. When the user clicks the Phone column header, the grid display does not change.

See Also

Creating Multiple Indexes | Using Other Index Types | Working with Records | SET ORDER | Grid | ColumnCount | Click | Deleting an Index | Creating One Index | Ordering by Multiple Fields | Index Creation for Tables