ALLTRIM on an index expression? Bad Idea?

[DanEvansJr]

I've been told (by someone who used to work at Microsoft, no less) that using alltrim on an index expression is a bad thing to do. I've been using FP since 1.2 and I've never heard of this. Has anyone else heard this, and can anyone else explain?

 

[chpicker]

Using any kind of function, including ALLTRIM(), within an index expression makes it so FoxPro cannot use Rushmore optimization on it. It will work fine for using SET ORDER TO, but I wouldn't suggest trying to use any SQL statements on it. There might be more reasons, but this is the one I know of.

 

[rgbean]

Also, since any index key must be a fixed length expression, you'll either end up with a key that's padded with spaces, or one that's shorter (and possibly less unique) than you expect.

Rick

 

[DanEvansJr]

Here's the jist of what I've been told. Foxpro sets the length of it's character search based upon the last record found in an index. ie, if you index 10 records that are variable length, and the last record only has 3 characters in it, Foxpro will now only search for the first 3 characters of that indexed expression, no matter what record number it's seeking. If the last record in your table is "SMITH", FP will now ignore the lengths of your other trimmed fields and only recognize the first 5 characters.

I haven't tested this . . . And the way it was explained to me, I wouldn't know how. I was just wondering if anyone else had ever heard of it.

 

[rgbean]

Dan,
I'm not sure where you are getting your info, but you really should test it.
If a key is based strictly on fields, then the length will always be the sum of the lengths of the fields. e.g.
FirstName c(15)
LastName c(15)

INDEX on ALLTRIM(LastName)+ALLTRIM(FirstName) TAG TrimNames
INDEX on LastName+FirstName Tag LastFirst

Both will have keys that are 30 characters long, but the keys themselves will be different for all but those records that have 15 character last names.

The only time things can get "complicated" are when a UDF() [User Defined Function] is used in the expression. Then the lenght of All keys are based on the length first returned value. I have tested this code in most all versions of FoxPro, and that's the way it seems to work.

Note: This length calculation stuff also appears in SQL Selects on variable length expressions - the first record selected will set the field lengths in the resulting table/cursor, so it's sometimes necessary to "pad" out certain fields to get a length that all selected records will be "happy" with.

Rick