Creating a Combined Ascending and Descending Index (92553)

SUMMARY

By default, indexes are created in ascending order (for example, alphabetically from A to Z, or by date, from January to December). Indexes can also be created in reverse or descending order (for example, alphabetically from Z to A, or by date, from December to January).

The character field example below demonstrates how to create an index on the concatenation of two fields, where the first field is indexed in ascending order and the second field is indexed in descending order. These techniques also apply to more than two fields.

MORE INFORMATION

To create an index on the concatenation of two fields (as described above), do the following:

Create the MCOMP.PRG file to contain the following code. MCOMP creates a 255 character string that contains the characters in descending order. For example:
```
      PRIVATE char, mdescend
      char = 255
      mdescend = ""
      DO WHILE char >= 1
         mdescend = mdescend + CHR(char)
         char = char - 1
      ENDDO
      RETURN mdescend
						
```
NOTE: The MCOMP.PRG file must be present on the system unless you delete the index created in this procedure.
Assign the results of the MCOMP program to a variable, as follows:
mdesc_ord = mcomp()
NOTE: You must create this variable each time you use the descending index. Otherwise, FoxPro returns a "Variable not found" error.
Create the index using the first field, the results of a SYS(15) function, the variable, and the second field.

For example, if the mdesc_ord variable is set as indicated in step 2 above, FNAME is the first field to be indexed in ascending order, LNAME is the second field to be indexed in descending (reverse) order, and INDEXNAME is the name of the index, you can create the index using the following code:
```
      INDEX ON fname + SYS(15, mdesc_ord, lname) TO file
						
```
The SYS(15, <expC1>, <expC2>) function translates each character of the string <expC2> according to the string in <expC1>.