ORDER(), SET ORDER, Set(“Order”), SET INDEX, Set(“Index”), Sys(21), Sys(22)

These commands and functions all relate to putting an index in control and finding out which one is currently in control. ORDER() and SET ORDER are the most up-to-date ways to do this stuff. SET ORDER lets you specify a tag or stand-alone index to control the table. ORDER() lets you find out which one it is. SET INDEX lets you open stand-alone indexes and non-structural compound indexes, as well as setting one of them in control. SYS(21) and SYS(22) are antique ways to find out which index is in charge. Don’t use them because, like all the SYS() Functions, their names give you no clue what they’re about.

When tables or indexes are opened, each stand-alone index and tag is assigned a number, based on its position. Several of these commands use that number to refer to a particular index or tag. See CANDIDATE() for details on this numbering. Like work area numbers, we recommend you avoid these numbers whenever possible and refer to indexes by name.

Usage

cCurrentOrder = ORDER( [ cAlias | nWorkArea
                       [ , nIncludePath ] ] )

ORDER() is the function to use when you want to save the current order before changing it. But it can give you much more information with the optional nIncludePath parameter. With two calls, you can find out both the tag in charge and the name and path of the index file containing that tag.

Usage

SET ORDER TO [ nIndexNumber | IndexFileName
        | [ TAG ] TagName [ OF CompoundIndexFile ]
        [ IN cAlias | nWorkArea ]
        [ ASCENDING | DESCENDING ] ]
SET INDEX TO [ IndexFileList | ?
        [ ORDER nIndexNumber | IndexFileName
          | [ TAG ] TagName [ OF CompoundIndexFile ]
          [ ASCENDING | DESCENDING ]
        [ ADDITIVE ] ]

SET INDEX TO lets you specify index files to open as well as the order to set. SET ORDER specifies just the new order. We don’t use SET INDEX much anymore because we almost always keep all the indexes we need in a single, structural index file.

The ASCENDING/DESCENDING switch here is confusing because you can create indexes in either ascending or descending order and use them both ways as well. The key fact is that the terms “ascending” and “descending” keep their normal meanings and don’t double up. That is, if you create a tag in descending order and then SET ORDER TO that tag DESCENDING, it doesn’t get reversed to ascending. Two descendings don’t make an ascending.

The other confusing thing is that the direction you create the tag matters only when you first open the table any given time. Once you SET ORDER TO that tag once, FoxPro remembers the last direction you used that tag in. If you omit ASCENDING and DESCENDING on a subsequent SET ORDER, you get the orientation you used last. (If you omit them initially, you get the creation direction for that tag.) This effect continues until you close and reopen the table.

Also, keep in mind that a table can’t use the same index/tag in two different orders at the same time. That is, if you USE a table twice and SET ORDER in each to the same tag, you can’t make one ascending and the other descending. The last one set wins—even if the table is used in different data sessions.

Like so many other SET commands, issuing SET ORDER TO without a tag or index name clears the setting. In this case, it indicates that record-number order should be used. SET ORDER TO 0 does the same thing as does SET ORDER TO “”. SET INDEX TO without any files closes open indexes except for the structural CDX.

Usage

cCurrentOrder = SET( "ORDER" )
cIndexList = SET( "INDEX" )

SET(“ORDER”) provides information in a different format than ORDER(). It gives the name of the current tag and the name of the index file that contains it. If order is currently descending, it also includes the word “DESCENDING.” Until FoxPro 2.6 added the DESCENDING() function, this was the only way to find out if an index was being used in descending order. Since the information is available in other forms and parsing the string returned is something of a pain, we suggest you forget about SET(“ORDER”), except for saving and restoring the current order, where it gives you exactly the information you need.

SET(“INDEX”) gives you a string that you can pass back to SET INDEX. It’s a complete list of all the index files (with paths) open for the current table. It even includes the structural CDX. If an order is set, the ORDER clause is included, too. Oddly, SET(“INDEX”) uses lowercase for the information from your table while all the other functions in this group use uppercase.

Usage

nTagNumber = SYS( 21 [, nWorkArea ] )
cTagName = SYS( 22 [, nWorkArea ] )

Since you can get the same result as SYS(21) and SYS(22) with TAGNO() and ORDER(), we can’t see any reason to use these obtusely named functions.

Example

USE Customer
SET ORDER TO Company_Na
? ORDER()                && Returns "COMPANY_NA"
? ORDER("Customer", 1)        && Returns something like
* "C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO\
*    SAMPLES\TASTRADE\DATA\CUSTOMER.CDX"
? SET("ORDER")                && Returns something like
* "TAG COMPANY_NA OF C:\PROGRAM FILES\MICROSOFT VISUAL
* FOXPRO\SAMPLES\TASTRADE\DATA\CUSTOMER.CDX"
? SYS(21)                && Returns 1
? SYS(22)                && Returns "COMPANY_NA"
? SET("INDEX")                && Returns something like:
* " c:\program files\microsoft visual
* foxpro\samples\tastrade\data\customer.cdx ORDER TAG
* company_na OF c:\program files\microsoft visual
* foxpro\samples\tastrade\data\customer.cdx"

See Also

Candidate(), Descending(), Index, TagNo(), Use