Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9101.ZIP
Filename : CPLXINDX.TXT

This article is reprinted from the January 1991 edition of
TechNotes/dBASE IV. Due to the limitations of this media, certain
graphic elements such as screen shots, illustrations and some tables
have been omitted. Where possible, reference to such items has been
deleted. As a result, continuity may be compromised.

TechNotes is a monthly publication from the Ashton-Tate Software
Support Center. For subscription information, call 800-545-9364.

Complex Descending Indexes
Wesley Leung

Creating complex dictionary or descending indexes without sorting to a
database can now be done thanks to the increase in functionality found
in dBASE IV version 1.1. You can create a User Defined Function to
accomplish this task.

When you are only doing a descending index on a single field, you need
only choose the option in the Organize: Create new/Modify existing
index menu. The Order of index option can be toggled to DESCENDING by
pressing Enter while that option is highlighted.

However, if you plan to include more than one field in the index
expression, then these following tips will be helpful in creating the
expression. These formulas will convert all fields to character
fields in order to keep data types consistent. The maximum index
expression is 254 characters and the maximum index string is 100
characters.

Ascending Dictionary:

UPPER(Fieldname)

Descending Dictionary:

Str(999 - Asc(SubStr(Upper(Fieldname), 1, 1)), 3) + ;
Str(999 - Asc(SubStr(Upper(Fieldname), 2, 1)), 3) + ;
Str(999 - Asc(SubStr(Upper(Fieldname), 3, 1)), 3) + ;
Str(999 - Asc(SubStr(Upper(Fieldname), 4, 1)), 3) + ;
Str(999 - Asc(SubStr(Upper(Fieldname),5, 1)), 3)

This expression stops at five iterations of the STR function because
each STR expression above is 47 characters long and 254 is divisible
by 47 about 5 times. Also note that each expression in the index is 3
characters, and 3 times 5 is fifteen which is well within the 100
character maximum. This is where a UDF would be ideal to work around
the 254 character limit in an expression; we'll get to that a little
later in the article. There are still three valid field types that
from which descending indexes can be created.

Descending Numeric/Floating:

Str(99999 - Fieldname, 5 + 1)

Descending Logical:

IIF(Fieldname, "l", "O")

Descending Date:

Str(99999999 - Val(Dtos(mFieldname)), 8)

All these index expressions can be executed from a UDF, but only one
UDF can be used in an index. In dBASE IV version 1.0, a UDF was not
executable in an index expression, but in version 1.1, DBTRAP can be
set OFF allowing the user more functionality. If more than one UDF is
used, "Data Type Mismatch" will appear when the second UDF tries to
return a value. There is currently no workaround for the error and
since DBTRAP is OFF, random errors may appear. "Data Type Mismatch"
is one of those unpredictable errors because even though both UDFs
return character fields, this error will be displayed.

Function Descend
parameters mFieldname, mLength, mDictionary
do case
case type("mFieldname") = "C"
if mDictionary
mFieldname = UPPER(mFieldname)
endif
mCharacter = l
mIndex = ""
mMaxLength = Len(mFieldname)
if mMaxLength > 33 && Max.index character
expression is 100
mMaxLength = 33 && 33 * 3 = 99 which is less
than 100
endif
do while (mLength > mCharacter) .and. mCharacter <=
mMaxLength
mIndex = + str(999 - ASC(SubStr(mFieldname,
mCharacter, 1)), 3)
mCharacter= +l
enddo
return mIndex
case type("mFieldname") = "N" .or. type("mFieldname") = "F"
mMax = val(Replicate("9", mLength))
Return Str(mMax - mFieldname, mLength + l)
case type("mFieldname") = "L"
RETURN IIF(&mFieldname, "1", "0")
case type("mFieldname") = "D"
return str(99999999 - Val(Dtos(mFieldname)), 8)
Otherwise
? "Invalid Descending Sort"
return ""
endcase

Examples:

SET DBTRAP OFF
INDEX ON State + Descend(Zipcode, 5, .F.) TAG Testl
INDEX ON Descend(Lastname + Firstname, 33, .T.) TAG Test2
INDEX ON Descend(Lastname + Firstname, 33, .F.) TAG Test3
INDEX ON Descend(Price, 8, .T.) TAG Test4
INDEX ON Descend(InvoiceDate, 8, .T.) TAG Test5
INDEX ON STR(99999999 - Val(Dtos(InvoiceDate)), 8) + ;
STR(99999.99 - Price, 9) + Descend(City, 20, .T.) TAG Test6

INDEX ON STR(999-ASC(Upper(SUBSTR(Lastname, l, l)))) + ;
STR(999 - ASC(Upper(SUBSTR(Lastname, 2, 1)))) + ;
STR(999 - ASC(Upper(SUBSTR(Lastname, 3, 1)))) + ;
STR(999 - ASC(Upper(SUBSTR(Lastname, 4, 1)))) + ;
STR(999 - ASC(Upper(SUBSTR(Lastname, 5, 1)))) + ;
STR(999 - ASC(Upper(SUBSTR(Lastname, 6, 1)))) TAG Test7

DBTRAP must be OFF for the Descend() function to work. Otherwise, the
error "Operation not allowed with a user-defined function" is
displayed. But there are other errors to be on the lookout for. For
example, take the expression:

Index on Descend(Lastname, 15, .T.) + Descend(Firstname, 15, .T.) Tag
Test2

This will result in the error message "Data Type Mismatch". The
reason for this is that you cannot use two UDFs in an index.

There are also limits to the use of the Descend() function when
indexing. Examine this expression below.

Index on Descend(Lastname + Firstname, 40, .F.) Tag Test3

Although syntactically correct, the index will not be complete since
it can only index up to 100 characters and the UDF generates three for
each letter on a descending character sort. Thus, the UDF will
ignore all letters after the 33rd character.

What is DBTRAP?

DBTRAP is a new SET command incorporated into dBASE IV version 1.1.
In its default state (ON), DBTRAP behaves similarly to dBASE IV
version 1.0. It safeguards the user from making programmatic moves
that could cause fatal errors to the program.

With the additional functionality now given to UDFs and ON KEY
interrupt routines, the capability now exists for very elaborate
routines. Disabling DBTRAP is much like doing a trapeze act without a
safety net. If you're confident enough without the net, then it need
not be there.

For more information, read the article "Inside DBTRAP" in the Release
Edition of TechNotes, included with the materials in your dBASE IV 1.1
software package. s