Custom Record Sort?

[Former Member]

I have a field that is a "String" and I need to sort it a special way. The data contains both numbers and words, and when I sort it using the record sort expert. I get the numbers to show up, then the words show up after all the number records have displayed.

I need to be able to put the word values in A-Z to display first. Then I will need to display the number values after all of the word values; and sort the numbers 1-9.

So restating my question: I need to see words values first, and when all of those records have been sorted. I will need to see the numbers start to sort below the word values. This is all in the same field.

How would I go about doing something like this?

[Former Member · Accepted solution]

create a formula that makes the numbers all ZZZ or something and the rest as they are. And on the field change the display string to be real field. I hope that helps

[Former Member · Accepted solution]

sorry and sort on the formula

[Former Member]

I took some of Brian's advice and here is what I did incase someone stumbles apon this problem in the future.

I created the following formula...

//Words start now

if {PYPCL.CLCY} = "DSDGW" then

"DSDGW" else

if {PYPCL.CLCY} = "DSDRF" then

"DSDRF" else

if {PYPCL.CLCY} = "DSDRT" then

"DSDRT" else

if {PYPCL.CLCY} = "SALBW" then

"SALBW" else

//Numbers start now

if {PYPCL.CLCY} = "178WK" then

"Z1" else

if {PYPCL.CLCY} = "337WK" then

"Z2" else

if {PYPCL.CLCY} = "342WK" then

"Z3" else

if {PYPCL.CLCY} = "343WK" then

"Z4" else

if {PYPCL.CLCY} = "344WK" then

"Z5" else

if {PYPCL.CLCY} = "481WK" then

"Z6" else

if {PYPCL.CLCY} = "497WK" then

"Z7" else

if {PYPCL.CLCY} = "549WK" then

"Z8"

Then I went into the record sort expert and sorted this newly created formula in ASC order.

Then on the actual report I dragged the field from the database and NOT the formula. The formula works with the database field for sorting purposes only and doesn't need to be on the actual report to work.

[Former Member]

Levi,

You can try this:

1) create a formula called TextFirst

ToText(IF isnumeric({Table.StringField} [1]) THEN 2 ELSE 1,"0")&{Table.StringField} 

This will assign a 1 for alphabetic 1st characters and a 2 for numeric first characters and append that value to the front of the text of the text string.

Then just sort on {@TextFirst}

Hope this helps,

Jason