How to Use a UDF in Index with the Trim Functions

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5x, 2.6x
• Microsoft FoxPro for MS-DOS, versions 2.0, 2.5x, 2.6x
• Microsoft FoxPro for Macintosh, versions 2.5x, 2.6a
• Microsoft FoxPro for UNIX, version 2.6

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."
     ?

     ?
     ?
     ? "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))

     ?
     ?
     ? "Below is the list of records indexed using ALLTRIM in the UDF."
     ?

     ?
     ? "Below is a list of records indexed without removing the leading"
     ? "spaces."
     ?

FUNCTION strtrim PARAMETER strin

     ? REPLICATE(CHR(45),80)

   * Note output from the following command:  ALLTRIM is ignored.
   ? "On pass " +ALLTRIM(STR(n))+" the ALLTRIM function IS NOT in effect."

   ? "On pass " +ALLTRIM(STR(n))+" the ALLTRIM function IS in effect."

     ?
     ? 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)
     ?

* End of program.