Microsoft KB Archive/129889

= How to Use a UDF in Index with the Trim Functions =

Article ID: 129889

Article Last Modified on 12/1/2003

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft FoxPro 2.0
 * Microsoft FoxPro 2.6a Professional Edition for Macintosh
 * Microsoft FoxPro 2.6 for SCO/UNIX

-



This article was previously published under Q129889



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 ?    ?     ? "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." ? LIST INDEX ON name TAG name ?    ? "Below is a list of records indexed without removing the leading" ? "spaces." ? LIST SET ALTERNATE OFF CLOSE ALL MODIFY FILE udfindex.txt
 * Note that the memory variable created from the field is again 30
 * characters long. It has been padded with spaces to a constant length.
 * Note that the leading spaces are ignored in this output showing that
 * ALLTRIM is working.
 * Note that the leading spaces are not ignored in this list.

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.

Additional query words: VFoxWin FoxWin FoxMac FoxDos database design considerations 2.50 2.50a 2.50b 2.50c

Keywords: kbcode KB129889

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.