Numeric Text Sort

[gary]

I’m a fan of the new native implementation of the the arraynumericsort( function. This new version is lightning fast and handles more numeric text variations than ever. It does not, however, handle the thousands separator so that 1,000 is interpreted as 1. I wanted to use this function to do a sort of a text field containing assorted numeric text values. My solution added a couple of additional traps and seems plenty fast in the limited tests I ran.

My scheme basically involved building an array containing each of the target field’s values plus a tab and the current sequence number. I then did an arraynumericsort( on that array followed by an arrayfilter to remove the first column containing the original values and adding a new second column containing the new sequence order. Another arraynumericsort) on this array gave me the desired sort order in the second column.

At this point I had to use a somewhat heavy handed method to actually sort the target field. This required adding a temporary integer field, filling it with the new desired sort order, sorting on this temporary field and then deleting the temporary field. These actions are hidden between the noshow and showpage/endnoshow statements.

My final code has traps to ignore the user’s thousands character as well as the user’s currency symbol. There is also a trap to handle a target field name with spaces and such to enclose the name in chevrons. This is the code I came up with:

startdatabasechange "ALLRECORDS", "Numeric Text Sort"
let _startField=info("fieldname")
let _currentField=chevronquoted(_startField)
let _tempOrder=arraynumericsort(replacemultiple(arraybuild(cr(),"",
    _currentField+{+tab()+seq()}),info(“thousandsseparator”)+";"+
    info(“currencysymbol”),";",";"),cr())
arrayfilter _tempOrder,_tempOrder,cr(),array(import(),2,tab())+tab()+seq()
_tempOrder=arraynumericsort(_tempOrder,cr())
_tempOrder=arraycolumn(_tempOrder, 2, cr(), tab())
noshow
addfield "_temp_"
field "_temp_"
fieldtype "INTEGER"
formulafill array(_tempOrder,seq(),cr())
sortup
deletefield
showpage
endnoshow

Here is the Numeric Text Sort in action on a field containing many numeric tex variations:

There may be a more elegant way to handle this but since I have no access to the native arraynumericsort( code (not that I would know what to do with it if I had!), this is the best I could figure out and trimmed down as best I can see. Might be handy for someone else down the line.

[KJM]

Gary, it is always interesting to follow your examples. May I add some remarks?

1. When I use your code on my German system, PanX does not recognise the “$” as a valid currency symbol — it is obviously expecting an “€” symbol. So this value will be treated as text (and sorted as value 0).
   
   
2. If I use (text with) numbers in German format, there are some more problems appearing: the numbers marked in orange in my following screenshot seem to be sorted alphabetically, not numerically.
   
   
   .
3. Your code is already dealing with info(“thousandsseparator”) and
   info(“currencysymbol”). I think you / we will have to deal with info(“decimalseparator”), too.

[gary]

@KJM

Your code is already dealing with info(“thousandsseparator”) and
info(“currencysymbol”). I think you / we will have to deal with info(“decimalseparator”), too.

I was just happy to get things to work as robustly as thearraynumericsort( function does and even expanded on that somewhat. Of course more traps could be added for more unusual situations by going with more entries in the replacemultiple( section of the code. I’ll leave that to you or others that want to further enhance this project. Thanks for taking the time to experiment and point out some possible shortcomings.

[dave]

I think this could be done more simply without the ArrayNumericSort. We can just make our temporary field a floating point field, transfer the values with a FormulaFill, and sort.

startdatabasechange "ALLRECORDS", "Numeric Text Sort"
let _startField=info("fieldname")
noshow
addfield "_temp_"
field "_temp_"
fieldtype "Float"
formulafill val(fieldvalue("",""+_startField)["0-9,-",-1])
sortup
deletefield
field "" + _startField
showpage
endnoshow

[gary]

Well sure, but my code looks so much more impressively complex. Your solution works better to cover more variations of original numeric text than mine does so it is not only simpler but more inclusive as well. The only thing I had to change with your code was to move the field “” + _startField line after the endnoshow line or it would not go back to the starting field.

[KJM]

Well, I like to see what’s happening and what the results of my calculations are, so I omitted the temporary adding a field, setting it up, noshow and endnoshow commands. And I like to keep it simple.

As long I have the numbers in the “Test Numbers” text field formatted according to my system’s settings, I am getting the correct result with this simple procedure. The field “Values” is a floating point field.

Field Values
formulafill val(«Test Numbers»)
sortup

[CooperT]

I uploaded to the Database exchange quite some time ago a custom statement, and function form, that will sort an array based on any column, will perform a numeric or alphabetic array, will sort up or down, will properly handle thousands separators other than a “,”, will handle four currency symbols, (dollar, euro, pound and yen), and will handle negative numbers. It’s called arraysortbycolumn. Michael Kellock was a big help in developing the statement. I use it all the time to sort arrays used to display data in a text list. You can sort a text list on any column. I usually set up a toggle so it will sort up then down, etc.