Sort String with Alpha and Numeric Values 1

[dutchy0513]

In Crystal XI, I have a column that includes 1 or 2-digit numbers, multiples of same separated by comma, and ALL.
Field is string. Need to sort numerically, then alpha. Alpha is at the end, but the numbers are in wrong order, i.e.:
1
2
20
3
34
All

Found help for numeric to string, but nothing references reverse. Everything I have tried failed. I can add leading 0s, but results are same. Cannot change the field ToNumber. I get error THE FORMULA RESULT MUST BE A STRING.

Any help would be appreciated!

 

[synapsevampire]

You do need to convert to a number, the numeric portion.

whileprintingrecords;
Stringvar MyString:={table.field};
Stringvar NOutput
numbervar x;
for x := 1 to len(MyString)do(
if isnumeric(mid(MyString,x,1)) then
NOutput:=NOutput+ mid(MyString,x,1);
);
val(NOutput)

Then create a formula which houses the alpha portion:

whileprintingrecords;
Stringvar MyString:={table.field};
Stringvar SOutput
numbervar x;
for x := 1 to len(MyString)do(
if not(isnumeric(mid(MyString,x,1))) then
SOutput:=SOutput+ mid(MyString,x,1);
);
SOutput

Now sort on these two fields.

-k

 

[lbass]

Or you could create a formula like:

if {table.string} = "All" then 1000000 else
val(replace({table.string},",",""))

Use this formula for sorting, but display your original field.

-LB

 

[synapsevampire]

LB: That wouldn't properly sort the following:

123ZZF
123ABC
123JYT

-k

 

[lbass]

The OP only indicated the values could be numeric or "All".

-LB

 

[synapsevampire]

Ahhh, you're probably correct, I misunderstood what alpha is at then end meant.

-k

 

[dutchy0513]

Thanks for the tips. I am fresh back from vacation and trying to make your suggestions work. Pretty new to Crystal Reports and feeling my way, so if I don't tell you something, please ask.

I am in CR XI. Used synapsevampire's suggestion, as I did find one number with an alpha character following (thank you for making me double-check).

Tried the Numeric sequence, and couldn't get it to work, so tried the Alpha and it worked. Here is my numeric, syntax copied from yours:

whileprintingrecords;
Stringvar SEQ:=
(FEE_CLAIM_PAYMENT_LINES.LINE_ITEM_SEQUENCE};
Stringvar NOutput;
numberVar x;
for x := 1 to length(SEQ)do(
If IsNumeric(mid(SEQ,x,1))THEN
NOutput:=NOutput+ mid(SEQ,x,1);
);
Val(NOutput)

I added the ';' after Line 4 - "StringVar NOutput". If I don't put it in either alpha or numeric, CR says "the remaining text does not appear to be part of the formula".

If I add the ';', it tells me "the formula result must be a string". I have tried everything I can find, to no avail.

Are you still willing to assist?
Thanks.

 

[synapsevampire]

You thanked LB for her post so I'll assume that you meant you were using hers and would like further assistance from her.

-k

 

[dutchy0513]

Actually, no. I thought I would be able to say thanks and send it with a question, but there was no option. I ended up using yours after all because of the numeric/alpha content.

Thanks for your detailed answer. Can you assist with the latest question?

 

[synapsevampire]

This works here:

whileprintingrecords;
Stringvar SEQ:=
"1tr34l4";
Stringvar NOutput;
numberVar x;
for x := 1 to length(SEQ)do(
If IsNumeric(mid(SEQ,x,1))THEN
NOutput:=NOutput+ mid(SEQ,x,1);
);
Val(NOutput)

I really couldn't understand your previous post anyway.


What does :

"I added the ';' after Line 4 - "StringVar NOutput". If I don't put it in either alpha or numeric, CR says "the remaining text does not appear to be part of the formula""

mean?

Put what in either alpha or numeric?

-k

 

[dutchy0513]

To answer your what does this mean question - Maybe this makes more sense: I added a semi-colon at the end of line 4, which read StringVar NOutput, which allowed the application to recognize the rest of the lines. I added it to both the numeric and the alpha sequence you had sent me.

When I try the new language, I still get the error message "the formula result must be a string". It is insistent on the result being a string, no matter how I try to make it change the string to a number.

 

[synapsevampire]

Sounds like you have Basic Syntax set instead of Crystal (select above), or your are placing the formula in the wrong area...

-k

 

[lbass]

I think you could use my formula for the first sort field, and then add the field itself for the second sort field--if the letter(s) always follow the numbers in the field.

-LB

 

[dutchy0513]

I am in the Formula Workshop, Formatting Formulas, Details, Line Item Sequence, added the formula on the white screen.

I also tried a right click on Line Item Sequence directly in the Details section, then going to Format Field and adding it in the Common Tab, formula box next to Tool Tip Text. Ended up in the same place.

I also converted the formula from Basic to Crystal syntax using Length for len, and NumericText for isnumber, but got the same results. The alpha section works, but the numeric section does not.

Any other suggestions? Thanks so much for taking the time to assist!

 

[dutchy0513]

lbass: Thanks, I tried yours as well and still got "the formula result must be a string".

 

[synapsevampire]

This has nothing to do with the line item sequence.

Create a new formula by right clicking the Formulasand selecting new.

-k

 

[lbass]

I meant this formula:

if {table.string} = "All" then 1000000 else
val(replace({table.string},",",""))

There's nothing in this formula requiring it to be a string. You create it in the field explorer->formula->new and then use it in report->sort records. Then add the actual field as the second sort.

-LB

 

[synapsevampire]

I think they're inability to communicate got us both here, the reason she/he used mine is:

"as I did find one number with an alpha character following"

Which I think now means they found a string which starts with numerics and has an alpha at the end.

Hard to know.

-k

 

[dutchy0513]

We the unknowing bow to your greater knowledge...

Thank you so much, lbass, that was exactly what I needed to know! In the Formula Fields, after setting the sort, it worked wonderfully!

synapsevampire: I get the impression from looking at other threads recently that you don't think anyone can communicate...please let me know if there is a better question format I should use in future. Thanks.
Tina (she)

 

[synapsevampire]

Alpha is at the end,"the end of what?

I think that you should try to be more thorough, and LB's solution won't always work based on there being mixed characters, but that's OK, thoroughness obviously isn't important to most, and that's why I end up cleaning up so many Crystal projects..

-k