Sorting fields 2

[gmman]

Is there a way to get Crystal to sort fields with letters and numbers.

This is the results that I am getting:

2-B1
2-B10
2-B11
2-B12
2-B13
2-B14
2-B15
2-B16
2-B17
2-B2
2-B3

This is what I want:

2-B1
2-B2
2-B3
2-B10
2-B11
2-B12
2-B13
2-B14
2-B15
2-B16
2-B17

Thanks for any help

 

[bdreed35]

Is the format you have provided, consistent throughout your records?

You can create a formula that takes a piece of the field, and then groups on it.

Assuming that "2-B" is always the first 3 characters, your formula would look like this:

WhileReadingRecords;
Val(Mid({table.field},4,Length({table.field})-3));

The next step would be to create a group on this formula.
You could easily suppress both the new group header and footer to keep your report looking the same as before.

~Brian

 

[gmman]

The first character can be from "1 to 8". The next character is always a "-". The third character can be from "A to E".

 

[bdreed35]

In that case, modify the formula to this:

Left({table.field},3) + ToText(Val(Right({table.field},Length({table.field})-3)),"000000");

This will buffer out the number portion to 6 digits before sorting.
If you need it larger, just expand the "000000" with as many extra zeros as you need. I can't imagine you would nee many more.

~Brian

 

[gmman]

I tried the formula and when I sorted by it there was an error: String length is less than 0 or not an integer.

 

[lbass]

Or you could create a formula like the following and enter it as your sort field, while using your original field for display:

left({table.field},3) + totext(val(mid({table.field},4)),"00&quot

This assumes that there are 2 characters before the letter, and that the value to the right of the letter does not exceed 99.

-LB

 

[bdreed35]

I am guessing there are situations when the field is null or empty. We need to add some logic fix the probelm.

if isnull({table.field}) or trim({table.field}) = "" then
    ""
else
    Left({table.field},3) + ToText(Val(Right({table.field},Length({table.field})-3)),"000000");

~Brian

 

[gmman]

lbass your formula worked just right, thanks a bunch!!

Brian I'll try yours to see if it works, thank you for quick response!!

 

[gmman]

Well I thought that fixed it until I found the number before the "-" goes up to 20 in some locations so that messes the formula up.

 

[lbass]

Then try:

left({table.field}, instr({table.field,"-&quot+1)+ totext(val(mid({table.field},4)),"00&quot

You could add some extra zeros to "00" if the number on the right exceeds 99.

-LB

 

[bdreed35]

lbass:
I think that there will an issue with the second part of your formula when the first part is 2 digits coming in.

gmman:
This will work:

stringVar startString := {table.field}; //replace with your field
numberVar hyphenPos;

hyphenPos := instr(startString,"-");

ToText(Val(Left(startString,hyphenPos-1)),"00") + Mid(startString,hyphenPos,2) + totext(val(mid(startString,hyphenPos+2)),"00");

~Brian

 

[lbass]

Oops, I whipped that out too soon--should have carried the logic all the way through, also. You're right, Brian. I think it could be changed to:

totext(val(left({table.field}, instr({table.field,"-&quot+1)),"0000&quot + totext(val(mid({table.field},instr({table.field},"-&quot+2)),"0000&quot

-LB

 

[gmman]

lbass I had to change your formula to:
left({INVENTORY.BINNUM}, instr({INVENTORY.BINNUM},"-&quot+1)+ totext(val(mid({INVENTORY.BINNUM},5)),"00&quot

I was just getting "00" as the last 2 digits. Also it does not sort by the first 2 digits. It starts with 10, 11, 12 ect. then 1, 2, 3 ect.


Brian I tried your formula and I still get the same error, there is not any nulls in the field.
String length is less than 0 or not an integer.

 

[lbass]

Sorry, still too quick on the draw. I tested the following:

totext(val(left({table.field}, instr({table.field,"-&quot-1)),"0000&quot + mid({table.field}, instr({table.field},"-&quot,2)+ totext(val(mid({table.field},instr({table.field},"-&quot+2)),"0000&quot

You might not need all those "0's" at the front, but if you only use this as the sort field, it doesn't hurt, and it allows for that prefix to increase in the future.

-LB

 

[gmman]

lbass when run it before I put it in the sort the data looks ok, but when I add it to the sort I get this error:

String length is less than 0 or not an integer.


Thanks for all the help so far, I am shutting down for the night so I will check in tomorrow. It looks like it is getting close

 

[lbass]

I guess you fixed the missing bracket around {table.field} so that the formula reads:

totext(val(left({table.field}, instr({table.field},"-&quot-1)),"0000&quot + mid({table.field}, instr({table.field},"-&quot,2)+ totext(val(mid({table.field},instr({table.field},"-&quot+2)),"0000&quot

I think if you are still having trouble, that you must have some data that doesn't conform to the structure:

#-B#

You might have a null or an instance where some component is missing. I think you should display your data and check for variations in the structure. Despite all the previous errors in my formula, I don't think that the formula is the issue any longer...

-LB

 

[gmman]

After looking through the field I found a lot of them with spaces, once I removed them it worked without an error.

 

[gmman]

There is also some other data in that field that does not have the "-" in it. Is there a way to ignore that so that I don't get the error:

String length is less than 0 or not an integer.

 

[gmman]

Brian

I tried your formula after I got rid of the spaces and it worked fine when I filter out the data that does not have the "-" in it. If that data is included I get the error:

String length is less than 0 or not an integer.

 

[bdreed35]

That would make sense. In your second post, you did state that the second character was always a "-". Is that bad data in the field? If so, it ought to be corrected in the database.

~Brian