CANDIDATE(), PRIMARY(), DESCENDING(), FOR(), Sys(2021), KEY(), Sys(14), TAG(), UNIQUE(), ATAGINFO()

These functions give you information about an index, either a stand-alone index or a tag. DESCENDING(), FOR() and, to a lesser extent, UNIQUE(), were welcome additions in FoxPro 2.6. CANDIDATE() and PRIMARY() were added in VFP 3. This group of functions tells you everything you’d need to know about an index in order to re-create it (except, of course, for the shortcomings we point out below). ATAGINFO(), added in VFP 7, fills an array with complete information about the indexes for a table, making the other functions somewhat less important for this purpose now.

These functions can all give information about indexes for the current work area or for another work area. For the specified work area, most let you get index information for the current master index or another index. Some of them let you specify a particular CDX file, as well.

The indexes open for any table are numbered based on the type of index and the order in which it was opened, with a catch. The order of indexes is:

• Stand-alone IDX files, in the order they were opened;

• Tags in the structural CDX file in their internal order;

• Tags in any other CDX files in their internal order, with the CDX’s in the order they were opened. This holds true even if you list a CDX before an IDX in the USE command.

So what’s the catch? If you open additional indexes with SET INDEX, the order may change because stand-alone IDX files always get the lowest numbers.

All these functions take their parameters more or less from the following group, though some functions have more parameters than others.

Usage

lIsCandidate = CANDIDATE( [ nIndexNumber
                          [, nWorkArea | cAlias ] ] )
lIsPrimary = PRIMARY( [ nIndexNumber
                 [, nWorkArea | cAlias ] ] )

These two functions tell you whether the specified index is either a candidate or primary index. Primary indexes can be defined only for tables contained in a database; candidate indexes can be defined for any Visual FoxPro table. Candidate indexes are defined with the ADD UNIQUE clause of ALTER TABLE, the UNIQUE clause of CREATE TABLE, or the CANDIDATE (not the UNIQUE!) keyword of INDEX. Primary indexes are defined with the ADD PRIMARY clause of ALTER TABLE or the PRIMARY KEY clause of CREATE TABLE. Although candidate and primary indexes have a lot in common, a given tag is one or the other. So, for any given tag, at most one of these two functions returns .T.

Only index tags in a compound index file (CDX) can be primary or candidate keys, so these functions always return .F. for stand-alone indexes (IDX). If you omit nIndexNumber, these functions return information about the master index tag (the one you SET ORDER TO).

Example

OPEN DATA TasTrade
USE Customer
* This table has two tags, Company_Na, a regular index
* and Customer_I, the primary index
? CANDIDATE(1)  && Returns .F.
? CANDIDATE(2)  && Returns .F.
? PRIMARY(1)    && Returns .F.
? PRIMARY(2)    && Returns .T.

Usage

lIsDescending = DESCENDING( [ cIndexName, ] nIndexNumber
                            [, nWorkArea | cAlias ] )

It used to be hard to find out if an index was in ascending or descending order. You had to get SET(“ORDER”) and parse for the string “DESCENDING”. If it was there, you had a descending index; if not, it was ascending. Plus, you could ask about only the master index in the current work area. DESCENDING() was added in FoxPro 2.6 and makes life much simpler.

There’s one ugly twist with both SET(“ORDER”) and DESCENDING(), though. They don’t tell how an index was defined; they tell how it is now. There’s no way to be absolutely sure whether an index was defined as ascending or descending except to close the table in every work area where it’s in use, then open it and test the appropriate index immediately.

Example

USE Customer
DISPLAY STATUS  && shows two tags: Company_Na and Customer_I
? DESCENDING(1)  && returns .F.
? DESCENDING(2)  && returns .F.
SET ORDER TO Company_Na DESCENDING  && reverse name order
? DESCENDING(1)  && returns .T.

* Now for the complication
SELECT 0
USE Customer AGAIN ALIAS MoreCust
? DESCENDING(1)  && returns .T.!
? DESCENDING(2)  && returns .F.

The example shows the problem. When you USE a table AGAIN, it picks up the current direction of any indexes opened with it. (It also can close some open indexes, but that has nothing to do with DESCENDING()—see USE for a discussion of that bug.)

In spite of this problem, DESCENDING() is a handy function and we’re glad to have it.

Usage

cFilter = FOR( [ nIndexNumber [, nWorkArea | cAlias ] ] )
cFilter = SYS( 2021, nIndexNumber [, nWorkArea | cAlias ] )

These two functions are almost identical. FOR() is another of the FoxPro 2.6 enhancements. The Help file says that FOR() is there for dBASE compatibility—it lies. We think it’s far better to use FOR() in a program than SYS(2021). Someone reading your program will understand what FOR() does; SYS(2021) will send them running to the manual. The only difference between them is that if you omit the nIndexNumber parameter, FOR() tells you about the master index while SYS(2021) generates an error. FOR() looks better all the time. You can use this feature (omitting the index number) only in the current work area. For information about a filter in another work area, you have to pass the index number as well as the work area or alias.

So what do these functions do, anyway? They return the filter expression for a filtered index. That is, if your original INDEX command included a FOR clause, these functions return the expression you placed in the FOR clause. If the index isn’t filtered, the functions return the empty string. Of course, Rushmore can’t use filtered indexes, so most of the time they’re not worth having.

FOR() returns the filter expression in all caps. This is no big deal, except for one thing. Even a string enclosed in quotes in the filter expression is returned in capital letters. That means that you can't necessarily re-create the index using the results of this function. Because SYS(2021) is smart enough to not capitalize quoted strings, we suppose we have to change our minds and our advice and tell you to use SYS(2021) even though FOR() is more readable. ATAGINFO() also works correctly, so if you want more than just the filter expression, use that function instead.

Example

USE Customer
* create a filtered index
INDEX ON UPPER(Company_Name) ;
      FOR Country = "France" ;
      TAG FrComps
? FOR()  && The new index is the master index
         && This returns the filter expression:
         && COUNTRY="FRANCE"
? SYS(2021,3) && Returns: COUNTRY = "France"
? FOR(1) && Returns "" since the first tag, Company_Na
         && has no filter.
* Don't forget to delete this tag

Usage

cKey = KEY( [ [ cIndexName, ] nIndexNumber
            [, nWorkArea | cAlias ] ] )
cKey = SYS( 14, nIndexNumber [, nWorkArea | cAlias ] )

These two functions are also almost identical, but KEY() is more capable. Both return the key expression for the specified index. KEY() allows you to name a particular compound index (CDX) file, while SYS(14) requires an index number from the full set of open indexes. Also, like FOR(), with no nIndexNumber parameter, KEY() returns information about the master index. In the same circumstances, SYS(14) gives an error. For both of these reasons as well as for readability, we recommend you use KEY().

Both functions return the key expression in all caps. If you go too far and there aren’t as many indexes as nIndexNumber, they return the empty string. Very handy for looping through index information.

Example

USE Customer
* Customer has two index tags, Company_Na and Customer_I
? KEY(1)     && Returns "UPPER(COMPANY_NAME)"
? SYS(14,2)  && Returns "CUSTOMER_ID"
SELECT 0     && Go to another work area
? KEY(2,"Customer")  && Returns "CUSTOMER_ID"

Usage

CTagName = TAG( [ [ cIndexName, ] nIndexNumber
                [, nWorkArea | cAlias ] ] )

This function tells you the name of an index. Despite the function name, it also returns names for stand-alone indexes. The name of the tag or the name portion of the IDX filename is returned in all caps.

If you omit nIndexNumber, TAG() returns the name of the master index (the same result as ORDER()).

Example

USE Customer
? TAG(1) && Returns "COMPANY_NA"
? TAG(2) && Returns "CUSTOMER_I"

Usage

lIsUnique = UNIQUE( [ [ cIndexName, ] nIndexNumber
                    [, nWorkArea | cAlias ] ] )

This function tells you whether the index was created with an Xbase unique index. This is not the same at all as the ADD UNIQUE clause of ALTER TABLE or the UNIQUE keyword of CREATE TABLE. Unique indexes in Xbase are a disaster waiting to happen, and we recommend you run like the wind from them. (For details, see INDEX.)

UNIQUE() returns .T. if the specified index was created with the UNIQUE keyword of INDEX or with SET UNIQUE ON. If nIndexNumber is omitted, UNIQUE() tells you about the master index.

Example

USE Customer ORDER Company_Na
? UNIQUE()   && Returns .F.
? UNIQUE(2)  && Returns .F.

Usage

nIndexes = ATAGINFO( ArrayName [, cIndexName,
                     [, nWorkArea | cAlias ] ] )

ATAGINFO(), added in VFP 7, gets all of the information about the tags of a table. It does in a single function what in earlier versions of VFP you had to use all the other functions in this group and about 5,000 lines of code to do. (Well, perhaps that’s an exaggeration, but our version of this function, provided in earlier editions of this book, was over 100 lines.) It even works with non-structural indexes as long as they’re open. This function fills the specified array with one row per tag and the following columns of information, and returns the number of rows in the array.

As mentioned earlier, unlike FOR(), ATAGINFO() is smart enough to keep the case of quoted strings in the returned filter expression (the fourth column of the array).

Specify cIndexName to fill the array with information about the tags in the specified CDX or IDX only; omit this parameter to get information about all tags in all open CDX and IDX files.

ATAGINFO() is ideal for creating metadata that can later be used to re-create the indexes of a table if they become corrupted (although you’ll also need to get information about persistent relationships in the case of a table in a database.)

Example

USE Customer
* Customer has two index tags, Company_Na and Customer_I
* Create another one in an IDX
INDEX ON UPPER(Address) TO Temp
ATAGINFO(laTags)
DISPLAY MEMORY LIKE laTags
* Don't forget to delete this tag

See Also

Alter Table, Create Table, IDXCollate(), Index, Order(), Set Index, Set Order, TagCount(), TagNo(), Use