Sorting combined alpha-numeric fields properly

[TJIT]

I have a list of manufacturing part numbers similar to this:

P-R-NTRL1
P-R-NTRL4
P-R-NTRL10

However, CR wants to sort them like this:

P-R-NTRL1
P-R-NTRL10
P-R-NTRL4

I get why it does that but am not sure if/how it can be done correctly.

 

[kray4660]

It is how strings are ordered. I do not know of an easy solution.

 

[briangriffin]

This may not work depending on your data, but you could create two formulas.

This one strips the numbers off the end of the part number:

whileprintingrecords;
stringvar v_textonly;
numbervar v_counter := 1;

while v_counter <= len({PartNumber})
do
(if not(isnumeric({PartNumber}[v_counter]))
then v_textonly := v_textonly + {PartNumber}[v_counter];
v_counter := v_counter + 1;)
;

v_textonly

And this one returns the number only:

whileprintingrecords;
stringvar v_texttonumber;
numbervar v_counter := 1;

while v_counter <= len({PartNumber})
do
(if isnumeric({PartNumber}[v_counter])
then v_texttonumber := v_texttonumber + {PartNumber}[v_counter];
v_counter := v_counter + 1;)
;

tonumber(v_texttonumber)

Then sort on formula 1 as primary, formula 2 as secondary.

This only works if the numbers are always at the end. Anyway, it might give you some ideas.

 

[Charliy]

If all of your numbers are in that same format, then you can do something like this:

left({table.partno},8) &
totext(val(mid({table.partno},9)),"000")

This will give you P-R-NTR001, etc.