SET ORDER Command
Designates a controlling index file or tag for a table.
Visual FoxPro does not support SET ORDER for binary indexes.
SET ORDER TO [nIndexNumber | IDXIndexFileName | [TAG] TagName [OF CDXFileName] [IN nWorkArea | cTableAlias] [ASCENDING | DESCENDING]]
Specifies the number of the controlling index file or tag. nIndexNumber refers to the index files as they are listed in USE or SET INDEX. Open .idx files are numbered first in the order in which they appear in USE or SET INDEX. Tags in the structural .cdx file (if one exists) are then numbered in the order in which they were created. Finally, tags in any open independent .cdx files are numbered in the order in which they were created.
The following example illustrates how different index file types and tags are numbered. (The file names are for illustration only and don't necessarily exist.) A table named video.dbf is opened with three indexes (title.idx, costs.cdx, and rating.idx) in the first work area with this command:
USE video INDEX title.idx, costs.cdx, rating.idx IN 1
The video table has a structural compound index file (video.cdx) with two tags, NUMBERSOLD and YEARSOLD. The structural .cdx file is automatically opened when video is opened.
Since .idx files are numbered first, issue SET ORDER TO 1 to make title.idx the controlling index and SET ORDER TO 2 to make rating.idx the controlling index:
SET ORDER TO 1 Controlling index: C:\FOX30\TITLE.IDX SET ORDER TO 2 Controlling index: C:\FOX30\RATING.IDX
The tags in video.cdx are numbered next:
SET ORDER TO 3 Controlling index: C:\FOX30\VIDEO.CDX Tag: NUMBERSOLD SET ORDER TO 4 Controlling index: C:\FOX30\VIDEO.CDX Tag: YEARSOLD
Finally, the tags in the independent file, costs.cdx, are numbered:
SET ORDER TO 5 Controlling index: C:\FOX30\COSTS.CDX Tag: RENTALCOST SET ORDER TO 6 Controlling index: C:\FOX30\COSTS.CDX Tag: BUYCOST
nIndexNumber can also be 0. If you issue SET ORDER TO 0, all index files remain open and are updated when records are added, deleted or modified. However, the records in the table are displayed and accessed in record number order and not in an indexed order. Issuing SET ORDER TO without additional arguments is identical to issuing SET ORDER TO 0.
If nIndexNumber is greater than the number of .idx files and .cdx file tags, Visual FoxPro generates an error message.
Specifies an .idx file as the controlling index file.
- [TAG] TagName[OF CDXFileName]
Specifies a tag of a .cdx file as the controlling tag. The tag name can be from a structural .cdx file or any open independent .cdx file.
If identical tag names exist in open independent .cdx files, use OF CDXFileName to specify the .cdx file containing the tag.
The .idx file takes precedence if duplicate .idx file and tag names exist.
- IN nWorkArea| cTableAlias
Designates a controlling index file or tag for a table open in a work area other than the currently selected work area. nWorkArea specifies the work area number and cTableAlias specifies the alias for a table.
- ASCENDING | DESCENDING
Displays and allows access to table records in ascending or descending order. Including ASCENDING or DESCENDING doesn't change the index file or tag in any way.
A table can have many index files open simultaneously. However, only one single-index (.idx) file (the controlling index file) or tag from a compound index (.cdx) file (the controlling tag) determines the order in which the records in a table are displayed or accessed. SET ORDER makes it possible for you to designate the controlling index file or tag. Certain commands (SEEK, for example) use the controlling index file or tag to search for records.
You can open index files with a table by including the INDEX clause in the USE command. If a table has an associated structural .cdx file, that file is automatically opened with the table. After a table has been opened, you can open and close index files for the table using SET INDEX.
By default, SET ORDER designates the controlling index or controlling tag for the table open in the currently selected work area.