String mostly number sort

[tmozer]

I have a table field {LABITEM.Lab Item Number} which is string because dashes are used for sub-items. (1 can become 1-1, 1-1-1, etc.). But, of course, if I sort on Lab Item Number I get 1, 10, 11, 2, 22, 2-2, etc. I need a string to value formula that would sort the field as desired:

1, 1-1, 2, 2-2, 2-2-2, 22, 3, etc.

One more complication (but one I could live without dealing with, just, maybe, ignore), there might be some Items with letters or, more likely, numbers and letters (1a, 1b, 2a).

I am using Crystal Reports 8.5 and talking to Oracle tables.

Can anyone get me started on some formulas what will give me the sort order I need?

 

[vidru]

Create a formula that gets the part of the string before the first "-", and create your group on that:

// @Group
if instr({LABITEM.Lab Item Number}, "-") = 0 then
val({LABITEM.Lab Item Number})
else
val(left({LABITEM.Lab Item Number},instr({LABITEM.Lab Item Number},"-") - 1));

Then, create a second group on {LABITEM.Lab Item Number}.

-dave

 

[lbass]

I like Dave's solution. I wasn't as quick on the draw, but came up with:

if instr({LABITEM.Lab Item Number},"-") <> 0 then
left({LABITEM.Lab Item Number},instr({LABITEM.Lab Item Number},"-")-1) + "." +
replace(mid({LABITEM.Lab Item Number},instr({LABITEM.Lab Item Number},"-")+1),"-","0") else
{LABITEM.Lab Item Number}

This would sort as your example shows:

1, 1-1, 2, 2-2, 2-2-2, 22, 3 //Note the 22 followed by 3

But I'm not sure you really meant that. If you wanted a numerical sort, then you would have to wrap the formula in val().

You would use the formula in report->sort order, but use the actual field for display on your report.

-LB

 

[tmozer]

Yes, of course I meant 1, 1-1, 2, 2-2, 2-2-2, 3, 22, etc.

I will try the formulas tomorrow. Thanks.