strange record sorting behavior

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

--
FW

FM Pro 8.5 under Windows XP Pro, connecting to
FM Server 8.0 under Windows 2003 Server

Re: strange record sorting behavior

"FastWolf" schreef in bericht
news:5e9lf311mhbt5vfg0426m83m9r0018vo31@4ax.com...
> 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

Re: Re: strange record sorting behavior

On Thu, 27 Sep 2007 00:41:28 +0200, "Ursus"
wrote:

>
>"FastWolf" schreef in bericht
>news:5e9lf311mhbt5vfg0426m83m9r0018vo31@4ax.com...
>> This question relates to the same configuration I've posted
>> previously:



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

It's a manually entered, field, no calc. Bless you Ursus, the damn
field was configured as number not text and that was the problem. I
feel like a moron, I really am more careful than that. No, really.


--
FW
"apprentice in moronoscopy"