How to Use a UDF in Index with the Trim Functions (129889)

SUMMARY

When creating an index key, it is important to remember that the index keys will have a fixed length even if the length of the field contents are varying lengths. FoxPro does not create or use variable-length keys. Index keys are padded with spaces to a constant size (the length of the field in the table structure).

You can use a User-Defined Function (UDF) in an index expression. For example, you can order the records based on a partial field (for example, the street name in an address field). When a UDF is used to create an index key, the UDF is called twice to initiate the process before the records are processed. Then it is called once for each record in the table.

This article shows by example how to use a UDF in an index expression with the four trim functions.

MORE INFORMATION

The four trim functions, TRIM(), ALLTRIM(), RTRIM(), and LTRIM() are ignored during the first two passes through the UDF. Then they are used to generate the index keys. However, note that the resulting key is still padded with blanks to the constant length of the field in the index. It is still a fixed-length index.

When tracing the UDF through the TRACE WINDOW, the first two passes through the code, as the index is built, calculate the key expressions so all the functions for removing blanks are ignored. The remaining passes through the UDF build the actual index keys. During this phase of building the index keys, the trim functions operate as expected within the UDF.

Sample Code

This sample code creates a table (TEST.DBF) and a text file (UDFINDEX.TXT) containing output that shows the values of the record number and the string's length, using the ALLTRIM function. It also creates a second index and list to compare with the first list, showing that the ALLTRIM function did work in the UDF index. Notice the placement of the record with the name "Joe Brown."

*Beginning of program.

SET ALTERNATE TO udfindex.txt
SET ALTERNATE ON
CLEAR
CREATE TABLE test (name C(30))
INSERT INTO test (name) VALUES ("John Doe")
INSERT INTO test (name) VALUES ("Jane Smith")
INSERT INTO test (name) VALUES ("  Joe Brown")
INSERT INTO test (name) VALUES ("Mary Green")
CLEAR
PUBLIC n
n=1
     ? "THIS FILE IS OUTPUT FROM A PROGRAM SHOWING THE USE OF ALLTRIM IN A"
     ? "UDF USED TO BUILD AN INDEX KEY."
     ?
INDEX ON strtrim(name) TAG testtag
m.name=test.name
* Note that the memory variable created from the field is again 30
* characters long. It has been padded with spaces to a constant length.
     ?
     ?
     ? "FoxPro will pad the key to the size of the field, making a fixed"
     ? "length key."
     ?
     ? "*** The length of the new memory variable using the UDF index is ";
       +STR(LEN(m.name))
* Note that the leading spaces are ignored in this output showing that
* ALLTRIM is working.
     ?
     ?
     ? "Below is the list of records indexed using ALLTRIM in the UDF."
     ?
LIST
INDEX ON name TAG name
* Note that the leading spaces are not ignored in this list.
     ?
     ? "Below is a list of records indexed without removing the leading"
     ? "spaces."
     ?
LIST
SET ALTERNATE OFF
CLOSE ALL
MODIFY FILE udfindex.txt

FUNCTION strtrim
PARAMETER strin
     ? REPLICATE(CHR(45),80)
IF LEN(ALLTRIM(strin)) = LEN(strin)
   * Note output from the following command:  ALLTRIM is ignored.
   ? "On pass " +ALLTRIM(STR(n))+" the ALLTRIM function IS NOT in effect."
ELSE
   ? "On pass " +ALLTRIM(STR(n))+" the ALLTRIM function IS in effect."
ENDIF
? "   Record number is: "+ALLTRIM(STR(RECNO()))
N=N+1
? "   The trimmed string's length is: "+ALLTRIM(STR(LEN(ALLTRIM(strin))))
IF n = 3
     ?
     ? REPLICATE(CHR(42),60)
     ? "*  Notice in the first two iterations, the last record is  *"
     ? "*  evaluated in the UDF strtrim(), not the first record.  *"
     ? REPLICATE(CHR(42),60)
     ?
ENDIF
RETURN ALLTRIM(strin)

* End of program.