Sorting groups based on alpha-numeric fields

[agorjest]

Hello,
Using CR2008. I want to display a list of groups based on a field that has combinations of strings and numbers. Most are sorted fine because they have no numbers or less than 10 numbers in sequence. Once over 10, they do not sort properly. Here are some examples:

Group 1- Field: equipment.location (string field)

Faculty Housing-1
Faculty Housing-10
Faculty Housing-11
etc.......
Faculty Housing-19
Faculty Housing-2
Faculty Housing-20
Faculty Housing-21
etc.......

Another example:

Student Housing-10A
Student Housing-10B
Student Housing-10C
Student Housing-10D
Student Housing-11A
Student Housing-11B
Student Housing-11C
Student Housing-11D
etc.......
Student Housing-19A
Student Housing-19B
Student Housing-19C
Student Housing-19D
Student Housing-1A
Student Housing-1B
Student Housing-1C
Student Housing-1D
Student Housing-20A
Student Housing-20B
Student Housing-20C
Student Housing-20D
Student Housing-21A
etc......


I would like these groups to appear sequentially by number if numbers are contained in the string. There are some that don't have numbers in the string, and some who only go up to 3 or 4 in their number sequence. Those are sorting fine, but it's the ones whose numbers get up in the teens and twenties where it doesn't sort right.

I'd appreciate any help.
Thanks...

 

[Charliy]

if numerictext({yourfield})
then totext(val({yourfield}),"00")
else
totext(val(left({yourfield},len({yourfield})-1),"00")+right({yourfield},1)

 

[lbass]

Try this:

stringvar array x := split({equipment.location},"-");
x[1]+"-"+totext(val(x[2]),"00")+
(
if isnumeric(right(x[2],1) then
"" else
right(x[2],1)
)

-LB

 

[agorjest]

Charliy,
Thanks for your response. I copied your formula and inserted my field name, but I am getting a ") is missing" error. I've tried to insert parentheses in various places, but no luck. Can you see where one is missing?:

if numerictext({equipment.location})
then totext(val({equipment.location}),"00")
else
totext(
val(left({equipment.location},len({equipment.location})-1),"00")+ right({equipment.location},1)
)

 

[agorjest]

lbass,
Thanks for your response. I Copied your formula, and got a "( missing" error. So I inserted an extra ) in line 4 of your formula, and it took the formula with no errors. However, when I tried to insert the formula field (@Sort) as a group or as a field, it takes me back to the formula workshop and I get a "A subscript must be between 1 and the size of the array" error, with the x[2] in line 2 of the formula highlighted.
I'm not sure if I screwed something something up with your formula by adding the ), but here's how it's written now:


stringvar array x := split({equipment.location},"-");
x[1]+"-"+totext(val(x[2]),"00")+
(
if isnumeric(right(x[2],1)) then
"" else
right(x[2],1)
)


Can you see anything that's causing the error?

Thanks for your help...

 

[lbass]

You would get that error if the field does not always contain a hyphen. You could try:

stringvar array x := split({equipment.location},"-");
x[1]+"-"+
if ubound(x)>= 2 then (
totext(val(x[2]),"00")+
(
if isnumeric(right(x[2],1)) then
"" else
right(x[2],1)
)
) else
{equipment.location}

This would correct for a field like:

Student Housing

But wouldn't correct for a situation like this:

Student Housing 23

I'm also assuming this field is never null.

-LB