Sorting numeric data in a text field with a simple dynamic sort script

[oilcan]

Since upgrading to FM12, I've been looking to utilize a simple lightweight sorting script to handle any field.  What I came up with was a system where I give the field object in the list view a name, then passing this object name into a very simple script when clicking the header to do the work.  The script looks like

Set Variable [$object; Value:Get ( ScriptParameter )]

If [$$sort_object <> $object]

     Set Variable [$$sort; Value:True]

     Set Variable [$$sort_object; Value:$object]

End

Go to Object [Object Name: $object]

If [$$sort]

     Sort Records by Field [Ascending]

Else

     Sort Records by Field [Descending]

End If

Set Variable [$$sort; Value:not $$sort]

 

Very short and sweet.  This script works quite well for a lot of things, and has great advantages over other sort routines I've experimented with.  The one big limitation I have that is keeping me from full adoption of this script is this.

 

We have a couple of user ID fields that generally are numeric data, but must be stored as text due to some internal naming conventions (these user ID fields are separate from the primary keys of the system).  Sometimes there must be alpha extensions, prepensions, etc. so in order to be able to do a proper find, these fields have to be of type text.  However, we would also want these things to sort like numbers instead of text.  In other words we want to see

 

138

12150

13844

 

 

rather than

 

12150

138

13844

 

when sorted ascending; the former is obviously a numeric sort order, and the latter is the unfortunate consequence of a direct sort when having to use a text field with mostly numeric data.

 

My older, laborious scripting controlled for this by essentially having an If branch in a giant script for each field, and in the case of these fields, it would sort by two fields; this field, and an extra number calc field_as_number with calculation GetAsNumber ( field ) - in order to achieve the desired sort order.  This works; but again, the scripts for these are giant awful things, requiring editing the script every time you decide to drop in new fields, and I would much like to move to being able to use this much more lightweight FM12 script, which only requires assigning a field object a name and assigning that name to a header as a script parameter pointing to that script.

 

My thoughts have included having some variation of the field_as_number calc that was used in the much more long-winded scripts, keeping it off the right side of the layout, and sorting by this field instead of the column that the header is actually over to achieve the desired numeric sort.  But I'm afraid I'm not clever enough to figure out what that calc would need to be.

 

Has anyone produced, know of, or can think of, a solution to this problem?  It'd be great to put those big lumbering sort scripts to rest for good, if only I can get the desired sort orders on these text fields containing mostly numbers.

 

Thanks in advance for any insight you may be able to offer here!

 

*Edit*

 

I should add these user ID fields also have more text-y values as well, like ELBM-145 and such; so the desired effect is a hybrid text/number sort, which is where the difficulty comes from.  Things that start with text would ideally go to the top of the sort list, and may contain other number values; so simply sorting by the GetAsNumber calc itself doesn't quite work.

Edited May 24, 2013 by oilcan

[Raybaudi]

Please, add more ID examples.

Could exist ID like:

12-abc-445

abc-12-445

?

[oilcan]

Please, add more ID examples.

Could exist ID like:

12-abc-445

abc-12-445

?

 

Thanks for the response...though you may be sorry you asked!  I'll present a set of actual user IDs in our system that will demonstrate the difficulties.  First, I just want to mention, IT'S NOT MY FAULT!  I inherited the maintenance of this system after a bunch of people with a bunch of conventions had already done their damage, so I have to work with what we've got.  The best I could do was enforce uniqueness (and for the love of all that is holy stop driving relationships from this awful field and use a real primary key). 

 

j.006

j.008

Kedl test B 1

Kedl test B 2

Kedl test D 1

Kedl test D 2

Kedl test H 1

Kedl test H 2

RW9.1

RW9.2

138

1380

4318

4318r.1

11131

11131E

11131Ec

13756.3

13756.3E

13801

 

 

As you can see, the values can be all over the place.  I wouldn't say that I would need everything to be absolutely perfect before I adapted my simpler sort script, but some close semblance of a number/text hybrid sort is the desired effect...push things that start with 'texty' characters to the top and things that start like numbers to sort like numbers.

 

So is this too helpless of a mess?  Or is there some formula I might be able to use to achieve this result, so that I may do away with my old brute force sort scripting for good?

 

*Edit*

 

To my amazement this particular field described does not contain any multiple '.' characters (or '-' characters at all).  However, such is not the case in another table with a similarly messy user ID field, which does have stuff like that here and there...le sigh...

Edited May 25, 2013 by oilcan

[Raybaudi]

Is this sort good for you ?

138
1380
4318

4318r.1

11131

11131E
11131Ec
13756.3
13756.3E

13801

j.006

j.008
Kedl test B 1
Kedl test B 2
Kedl test D 1
Kedl test D 2
Kedl test H 1
Kedl test H 2
RW9.1
RW9.2

If it is, create a new calculation field to sort by, with calc:

Let([
t = YourID ;
a = Filter ( t ; KanjiNumeral ( t ) ) ;
n =  Filter ( t ; ".1234567890" ) ;
pt = Right ( "                    " & a ; 20 ) ;
pn = Right ( "000000000000000000" & n & If ( not PatternCount ( n ; "." ) ; ".0")  ; 20 )
];
Case(
Left ( t ; 1 ) = Left ( n ; 1 ) ; pn & " " & pt ;
pt & " " & pn
)
)

[oilcan]

 

Is this sort good for you ?

138

1380

4318

4318r.1

11131

11131E

11131Ec

13756.3

13756.3E

13801

j.006

j.008

Kedl test B 1

Kedl test B 2

Kedl test D 1

Kedl test D 2

Kedl test H 1

Kedl test H 2

RW9.1

RW9.2

If it is, create a new calculation field to sort by, with calc:

Let([
t = YourID ;
a = Filter ( t ; KanjiNumeral ( t ) ) ;
n =  Filter ( t ; ".1234567890" ) ;
pt = Right ( "                    " & a ; 20 ) ;
pn = Right ( "000000000000000000" & n & If ( not PatternCount ( n ; "." ) ; ".0")  ; 20 )
];
Case(
Left ( t ; 1 ) = Left ( n ; 1 ) ; pn & " " & pt ;
pt & " " & pn
)
)

 

You are awesome!  I can definitely put this to work in my system.  Thanks so much!  

 

Incidentally, should this calc be as type number, text, or does it matter?  I had it stored as number when testing it out, and it seemed to produce the desired effect, just wanted to make sure.

[Raybaudi]

Text.

The calculation can't contemplate all possible cases, so sometimes gives wrong results.

It will be better a recursive custom function.

 

For example:

12

12e1

120

 

sorts:

 

12

120

12e1

[oilcan]

Text.

The calculation can't contemplate all possible cases, so sometimes gives wrong results.

It will be better a recursive custom function.

 

For example:

12

12e1

120

 

sorts:

 

12

120

12e1

 

 I've switched it to text.  I think that it will mostly be okay if it can't predict every scenario, most of the really weird IDs are very old records that are less likely to be in current found sets anyway (there've since been some more sensible rules put in place).  Some semblance of order should be good enough.  Thanks again!