Sorting Issue

numberz

wrong order.png Hi,
Probably entry level but...
I need to be able to sort some titles that have numbers in them.
The FIRST number on left sorts okay but the total number isn't being sorted.
For instance, 1, 4 and 8 are correct but 1, 1201, 1601, 401, 801 aren't...it's looking at the first number, not the entire number.
Any help would be appreciated, thank you.

customaware

Have sent you a short video tutorial Harry.

numberz

Mark, thank you.
I am familiar with what you sent me.
However, it's not what I'm asking.
I have an attribute that basically is text...that includes numbers and letters.
For instance, "101 - 400 - Fishing Supplies"
I want to take a look at the first three numbers in the text box and sort.
So if I have :

101 - 400 - Fishing Supplies
401 - 800 - Hunting Supplies
801 - 1200 - Medical Supplies
1201 - 1600 - Camping Supplies

101 - 400 - Fishing Supplies
1201 - 1600 - Camping Supplies
401 - 800 - Hunting Supplies
801 - 1200 - Medical Supplies

It ends up being sorted as above...and I don't want that. I'd like to see it in numerical order as in top example.

PointsWell

If your field is text then you will not be able to sort them properly unless you prefix the lower numbers with leading zeros.

Your easiest route would be to create two number fields ItemNoClass and ItemNoSubClass and place the first three digits in the Class and the minor 3 digits in SubClass then sort the query based on ItemNoClass ASC and ItemNoSubClass ASC.

Otherwise as fas as text searching is concerned 1 will always be followed by 10 then 100 then 2

numberz

Pointswell, thanks.
But the items are ranges, meaning all of the Fishing Supplies will fall under item range of 101 through 400.
Hmm...

PointsWell

numberz wrote
Pointswell, thanks.
But the items are ranges, meaning all of the Fishing Supplies will fall under item range of 101 through 400.
Hmm...

Your issue is that text will always sort 100 before 2. I do not see any way to fix that that is worth the amount of time effort and code to do so.

You could get away with a rangeStart numeric field. Ideally you'd want to have a rangeEnd as well.

I would probably structure it with 4 fields in total

rangeStart - number
rangeEnd - number
Description - text
displayDescription - text Computed

rangeStart would take 101
rangeEnd would take 400
Description would be Fishing Supplies
displayDescription would be calculated and populated with a business rule

IF BO IS NEW OR  (BO.rangeStart WAS CHANGED or BO.rangeEnd WAS CHANGED or BO.Description was CHANGED) THEN
BO.displayDescription = BO.rangeStart + ' - ' + BO.rangeEnd + ' - ' +BO.Description

This way your user is still typing the same amount of data, you will get your number range for sorting and you will have your description showing correctly.

This approach allows you to then error check that there isn't any overlap between your rangeStart and rangeEnd values which is again much harder to do with straight text fields. You will also be able to do other operations on the range numbers (in a process for example) more easily than if it is one large text field that has to be disassembled and the numbers manipulated back to numerics.

numberz

Thank you.