Sort in Numerical Order

Panorama X
1

I have a database with 952 records. I need to sort the records in a field called “Records Series Number”. The numbers are assigned by a state agency and I can’t change them, example: 1.1.001, 1.2.001, 1.3.001, 2.1.001, 3.1.001, 4.1.001 ,5.1.001, 6.1.001, 7.1.001, 8.1.001, 9.1.001, 11.1.001, 12.1.001, 13.1.001, 14.1.001, 15.1.001, 16,1.001, 17.1.001, 18.1.001.

The sort ends up like this: 1.1.001, 1.2.001, 1.3.001, 11.1.001, 12.1.001, 13.1.001, 14.1.001, 15.1.001, 16,1.001, 17.1.001, 18.1.001, 2.1.001, 3.1.001, 4.1.001 ,5.1.001, 6.1.001, 7.1.001, 8.1.001, 9.1.001

Is there a way that I can force the sort in numerical order? 1, 2, 3, 4, 5, 6, 7, 8, 9, 11, 12, 13, 14, 15, 16, 17, 18, 19.

I have several workarounds but thought I’d post my question. Thanks

2

I think you will need an extra numeric field for this. Then you could do something like this.

Field "Record Series Number"
SortUp
Field "Extra Field"
FormulaFill val(«Record Series Number»[1,"."])
SortUp

The first SortUp makes sure that 1.1, 1.2, and 1.3 are in that order, and then the second one sorts them by the first number.

3

Thank you Dave, that didn’t work for me - my sort still ended up: 1,11,12,13,14,15,16,17,18,2,3,4,5,6,7,8,9. I did add a different extra field before I posted, named it Sequence, made the right selects and sequenced the 952 records and then sorted up my sequence field. But I was thinking there must be a better way.

4

You could pad the digits before the first period/decimal point with zeros in front. If they do not go above 99, you will only need one zero. Alternatively, if they are all in the format #.#.### or ##.#.###, you could store them as numbers only (######) and sort numerically, displaying them with the periods only when you need to.

5

This routine works. Your data is in the field “Records Series Number” (as text); the field “SORTER” is added and deleted during the procedure execution. The latter field is numerical (integer) and is used to execute the sort prior to being deleted.

The multiplication by 1,000,000 and 1,000 merely serves to isolate each portion of the original text string (as separated therein by “.”) within the resulting integer, in order to facilitate accurate sorting.

field “Records Series Number”
insertfield “RSN_SORT”
fieldtype “integer”
formulafill val(array(«Records Series Number», 1, “.”)) * 1000000 + val(array(«Records Series Number», 2, “.”)) * 1000 + val(array(«Records Series Number», 3, “.”))

// field “RSN_SORT” will be active after it’s inserted.
sortup

deletefield

Alternatively, you could eliminate the deletefield command, and retain the “RSN_SORT” field within the database.

Regards,

Eric

6

Are you sure your extra field wasn’t a text field? Numeric fields don’t sort that way.

7

Thank you Eric!!! That does the trick, except I forgot to mention that I also have 128 “Retired” records that begin with the letter “z” (for example z1.1.041, z2.1.001, z1.1.g, etc) - using the procedure above sorts these retired records to the top of the list - how can I sort these 128 records to the bottom of the list? All other records following the “retired records” are sorted correctly using your procedure.

8

Hi Dave, Oops, sorry… it was very late last night and I “thought” that when I set up the Extra Field I did as numeric, guess I didn’t. I did it again this morning and yes it worked. But the problem of my “Retired” records beginning with the letter “z” sort to the top as I explained to Eric. I would like them sorted to the bottom, if possible. Thanks again

9

I would suggest you use Eric’s solution with this change. Change

val(array(«Records Series Number», 1, “.”)) * 1000000

to

val(replace(array(«Records Series Number», 1, “.”),"z","")) * 1000000 + ?(«Records Series Number» beginswith "z",2000000,0)

Adding an extra two million to the retired records ought to do the trick.

10

Here is a alternative that does not use an extra field in the sorting process:

let temp=""
startdatabasechange "ALLRECORDS","Sort Field"
field «Records Series Number»
arraybuild temp,cr(),"",replace(«Records Series Number»,"z","9999")
arraynumericsort temp, temp, cr()
temp=replace(temp,"9999","z")
formulafill array(temp,seq(),cr())
11

Thank you Gary, I’ll try your alternate procedure too. Pan gives us lots of different ways to get the results we need - and I appreciate everyone’s help! In the meantime I modified Dave’s procedure and ended up with what I needed:

Field «Record Series Number»
SortUp
Field "ExtraField"
FormulaFill val(«Record Series Number»[1,"."])
Field «ExtraField»
Select «ExtraField» = 0
Field ExtraField
Fill "20"
SelectAll
Field ExtraField
SortUp
12

Dusty,

I assumed that you also needed to sort the remaining numbers after the first period, in your text string. That doesn’t appear to be the case and the simplified solution you have created from Dave’s example will work well.

A more generalized solution, where several numbers, separated by a delimiter (ie, “aaaa.bbb.cc”) need to be sorted will be solved by the procedure I provided above.

Note that the 1000000 and 1000 multipliers are somewhat arbitrary, with the proviso that they must be powers of 10 and that the minimum power of ten must contain the maximum value for the substring following the string of interest (ie, for string “aaaa” that maximum would be 99999 which is the length of “bbb” and “cc” concatenated). For a text string “aaaa.bbb.cc” (where a, b and c are numbers) the minimum multipliers for substring “aaaa” would be 10^5; similarly, for substrings “bbb” and “cc” the minimum multipliers would be 10^2 and 10^0, respectively. The minimum multiplier, then, can be generalized to 10^(length of the following substring’s digits).

Thus, to transform a string “aaaa.bbb.cc” to the integer aaaabbbccc would require that the value “aaaa” be multiplied (at the minimum) by 10^(3+2), the value “bbb” be multiplied by 10^(2) and the value “cc” be multiplied by the value 10^0; these values would then be summed to obtain the sortable integer aaaabbbcc.

Alternatively, if aaaa, bbb and cc have leading zeroes (such that they always have the same number of digits, regardless of their value … compare “0024.001.01” to “24.1.1”), then the procedure can be simplified further by using:

val(striptonum(«Record Series Number»))

Regards,

Eric

13

For what it is worth I have altered my version of the solution to sort on all digits of the string as well as placing all the “z” records at the end - also sorted.

let temp=""
startdatabasechange "ALLRECORDS","Sort Field"
field «Records Series Number»
arraybuild temp, cr(), "", striptonum(replace(«Records Series Number»,"z","9999"))
arraynumericsort temp, temp, cr()
temp=replace(temp,"9999","z")
formulafill array(temp,seq(),cr())[1,-5]+"."+array(temp,seq(),cr())[-4;1]
    +"."+array(temp,seq(),cr())[-3,-1]

The startdatabasechange statement is just so you can revert back to the presorted condition if necessary. The only thing my version has going for it is that it does not rely on an additional field to work.

14

Your procedure doesn’t seem to be sorting the database. The Record Series Field is being put into a sorted order, but the remaining fields in the database remain in their original order. That will break the association between the Records Series Number and the rest of the record.

15

Yikes! I forgot about that part. Scratch that solution.

16

Thank you Eric, Dave, and Gary.

17

One final attempt to salvage my flailing reputation and come up with a scheme that does not need another field to work properly. This version processes the text in a numeric format and then changes the fieldtype to integer, sorts, changes fieldtype back to text and then re-formats the text back to the original pattern. As previously, I added the startdatabasechange feature and now also use noshow/endnoshow to eliminate extra flashing as the field type changes sorts and re-formats.

startdatabasechange "All Records","Test Sort"
noshow
field «Records Series Number»
formulafill striptonum(replace(«Records Series Number»,"z","999"))
fieldtype "INTEGER"
sortup
fieldtype "TEXT"
formulafill replace(«Records Series Number»,"999","z")[1,-5]+"."+
    «Records Series Number»[-4;1]+"."+«Records Series Number»[-3,-1]
showpage
endnoshow

Works with my sample data and keeps all the original record’s data sorted in all the other fields. Fini Smile Skull Tiny Emoji

18

I made the field a text field, aligned it to the right and it sorted properly.