Alltrim fails in index

[Michael Humby]

I have a script that fetches through the parent records then adds a child record to each parent based on values in the previous child.

The index for the parent has an order expression of ALLTRIM(LNAME)+ALLTRIM(FNAME)

The script will occasionally get confused and add the child data from a previous parent's child but only when it hits two identical lastnames (not always) even though there are no identical fnames.

If I rewrite the order expresion in the index by prefixing with the ID_NO
LTRIM(STR(ID_NO))+ALLTRIM(LNAME)+ALLTRIM(FNAME)
the script will run fine giving me a new child record for each parent based on values and expressions from the previous child record.

My question is, how far along the line does the order expression in an index look, does it only sort on the first few characters ? Because that is what is happening.

There are times when I cannot prefix the order by a unique ID.
Any advise appreciated

Michael

[Stan Mathews]

RE: Alltrim fails in index

Michael,

Just tried the alltrim() in building an index concatenating two thirty character fields. Saw nothing out of order in the way the records browsed under the index.

I have a hunch you might get better results with:

ALLTRIM(LNAME)+ " " +ALLTRIM(FNAME)

and that theory is supported by the fact that you get consistent results with

LTRIM(STR(ID_NO))+ALLTRIM(LNAME)+ALLTRIM(FNAME)

since you wind up with a space between the id_no and the lname.

It's hard to makeup names that would illustrate what I mean and don't seem "made up". Consider the sorting of "Jone, Sam" and "Jones, Am". They would be equal with your alltrim expression, but are they really? Putting a space between the names could change the order.

Hope this helps,

Stan

[Michael Humby]

RE: Alltrim fails in index

Hello again Stan,

Your explanation makes sense. Can you clarify the following.

1.I was always under the impression that indexes did not like spaces.

2. In your experience, can different makes processors interpret indexes differently.

Michael

[Stan Mathews]

RE: Alltrim fails in index

I believe Alpha, like nature, abhors a vacuum, or a space, in a table name, field name, index tag name, etc. This is probably due to the inherent difficulties in parsing various expressions.

Imagine evaluating the xbasic line:

tbl=table.open("back end") as opposed to

tbl=table.open("back_end")

As for the contents of the field to be indexed, I don't know of any problems with text values containing spaces. Many of our item descriptions are two and three word phrases, vendor names like "The Coleman Company", etc.

Never noticed a processor difference in anyhing other than speed.

Let me know how you get along.

Stan

[Stan Mathews]

RE: Alltrim fails in index

Messed up the html.

Meant to say,

I believe Alpha, like nature, abhors a vacuum, or a space, in a table name, field name, index tag name, etc.

Imagine Alpha trying to parse the line:

tbl=table.open("back end")

as opposed to

tbl=table.open("back_end")

As for the contents of the field to be indexed, I don't know of any problems with text values containing spaces. Many of our item descriptions are two and three word phrases, vendor names like "The Coleman Company", etc.

Never noticed a processor difference in anyhing other than speed.

Let me know how you get along.

Stan

[Tom Cone Jr]

RE: Alltrim fails in index

Michael, the specs indicate that the maximum index key length is 102 characters. I've always assumed that all 102 characters were 'significant', if you see what I mean.

I'm wondering if perhaps there were odd patterns in your data which led to what you observed.

If the problem can be repeated, I'm sure Alpha Software would want to know about it, so they could fix it.

Do you have a repeatable example you could post?

-- tom

[DiFaz]

RE: Alltrim fails in index

Am I missing something? Why would you want to trim your fields in the first place?? You avoid a lot of problems by using the entire fields as you build your key!

[Tony Rutherford]

RE: Alltrim fails in index

I agree with Ray. You could find that your 'trimmed' index sorts in the wrong order e.g.:

untrimmed index:

Smith John
Smith William
Smithson Jack

trimmed index:

SmithJohn
SmithsonJack
SmithWilliam

Tony

[DiFaz]

RE: Alltrim fails in index

Tony,

My point, exactly! One of the strengths of the dbf format is that field lengths are static, thus guaranteeing accurate sorts. Why use superfluous code to undo that?
By the way, Alpha does a character by character read of the entire key. It doesn't read just a few characters, as some have suggested!

[Michael Humby]

RE: Alltrim fails in index

Thanks All,

Lesson learnt on trimming in an index.

I was always of the mistaken belief that indexes preferred trimming. I still think it essential to LTRIM a lastname etc in case a user has accidently preceded the data entry with a space.

My script runs beautiful now since I removed the trims. It flips through all parent records, skips certain conditions, looks at the last child, gets conditional data from the last child and then creates a new child and fills in data based on expressions from the previous child.

Michael