Alpha/decimal portal sort?

[K1200]

I have a portal that is sorted based on a one-digit field that, occasionally, can contain an Exception Code (A, B, C, etc.). The portal uses a "custom order based on value list" to place the entries with Exception Codes (6 different ones) at the bottom, like footnotes. Any entry with a blank sorts to the middle of the pack.

The value list is 1, 2, 3, ... 9, , A, B, C, D, E, F

This has worked great, so far. But now I'm faced with having to support decimal fractions instead of just integers. The following is an example of what I now need the portal to look like:

1.11

1.74

2.01

2.42

A

B

D

So my question is: is there a way to define a custom value list that is a "hybrid' -- a combination of numeric and alpha -- that would produce this result, plus accommodate the blank entries?

I know this could be done by adding a secondary sort field, but that's out because (here's the clincher) this application is based on the separation model and the data table can't be changed at this point. This one field is all I have to work with.

Any suggestions will be greatly appreciated.

[Ocean West]

create a new field that pads your field to include a few zeros result type of text.

Case ( IsEmpty( YourField ) ; 

"2" & Right("000000" & YourField ; 6)  ; 

Filter ( YourField ; "0123456789." ) = "" ;

"2" & Right("000000" & YourField ; 6) ;

"1" & Right("000000" & YourField ; 6)

)

base your sort on this calculated field.

Edited March 4, 2008 by Guest

[K1200]

(from my post): ... this application is based on the separation model and the data table can't be changed at this point

Are you suggesting that a calculated field can be added to the table definition without disrupting the contents of the separated (data) file? In other words, would the rebuilt runtime GUI would work properly with an installed data file -- one having the older table definition?

That would be great if it's true. Otherwise, I can't modify the table.

[The Shadow]

Well, you could just bite the bullet and create the custom valuelist with the 1000 entries needed to support all numbers to 2-digits precision.

You could write a script or custom function to create the list once, then cut and paste it into the value-list dialog in your solution.

Here's a sample, custom value-list sorted with a large list:

vlSort.fp7.zip

Edited March 4, 2008 by Guest
Added file.

[K1200]

Thanks for the "starter kit" example. I used it to build a test portal and discovered unexpected results with the alpha entries. I had neglected to mention that the field is defined as Numeric -- which worked fine when there was a single integer or character in it. When I use the 1000-entry value list, the numbers sort fine, but the alpha entries do not.

I've since found a forum post about value lists that cautions against mixing data types. Although it related to building a value list on one type and trying to use if for another, it apparently applies equally to a manually-entered value list being used to sort a numeric field.

Here's an updated example of the result that I need:

1.11

1.74

2

2.1

2.42

A

B

Although this has been an interesting investigation, I'm still without a viable solution.

Any other ideas?