Re: strange record sorting behavior


"FastWolf" <wolfsofast@xxxxxxxxxxxxxxxxx> schreef in bericht
news:5e9lf311mhbt5vfg0426m83m9r0018vo31@xxxxxxxxxx
This question relates to the same configuration I've posted
previously:

I have a join table (t_join) making a portal between two data tables
(t_draft and t_final):

t_draft fields are:
draft_id (auto-enter serial)
draft_name (text)

t_final fields are:
final_id (auto-enter serial)
final_name (text)

t_join fields are:
join_id (auto-enter serial)
draft_id (number)
final_id (number)

They're joined like this:

t_join::draft_id = t_draft::draft_id
t_join::final_id = t_final::final_id

... where t_draft and t_final each has a one-to-many relationship with
t_join:

t_draft --< t_join >-- t_final


In this case I have a script that organizes records in t_draft so the
user can select one. It's invoked from t_final. The script calls up
a t_draft list layout; shows all records in t_draft; sorts records by
a text field in t_draft (not a key); goes to the first record; then
exits. (A separate script creates and commits the join record.) The
thing is, the records are NOT the way I expect. All I want is
alphanumerical order, ascending. In the example below, the left
column show the strange sort I'm getting, and the right column shows
what I expect to be getting:

[note: dummy sample data I just invented to illustrate the problem]

No 1 way out 2 of a kind
2 of a kind 13th floor
any 2 will do 1001 ways to sniffle
trouble comes in 3 agenda of god
13th floor all for one
gone in 60 seconds any 2 will do
1001 ways to sniffle by your leave
agenda of god contour of crime
all for one dog pound
by your leave gone in 60 seconds
west end have a heart
you're incorrigible I ain't no nice guy
zero hour No 1 way out
dog pound not now
not now sure it does
have a heart trouble comes in 3
sure it does west end
contour of crime you're incorrigible
I ain't no nice guy zero hour

In the left column the sort-by-name has anything with a number in it
sorted first, regardless of where the number appears in the text; and
any new records that are added appear at the bottom of the list (after
"zero hour'), in the order they were added. No matter how many times
or ways I try sort by name this arrangement persists. If I sort by
the key field (number) it sorts perfectly.

Something's going on but I can't see what it is.

thanks in advance


The columns are gone in the post so your efford is not quite clear. are
these calculated results? Or plain text? How does the data get in this
field? Have you tried to sort the records by hand instead of by script? You
should check if the field is realy a textfield. If it is a calculation check
if it returns text AND make sure you use the GetAsText for eacht
number-field to make proper text from any numbers you use.

Keep well, Ursus


.