Need formula to convert part of text records to numbers for numerical sorting 1

[jlr123]

My records are "item codes" - {'ESL_Svc_Codes_'.Item Code} which do not sort in the order I want them.
Example: "/ES103" is positioned after "/ES10".
/ES10
/ES103
/ES12
etc

I want to convert them or do whatever I need to so they will sort as follows:
/ES10
/ES12
/ES103

Please assist with formula. Thanks!

 

[BobSuruncle]

You could create a formula using the Val() function and then sort on that formula.

e.g
@MySort

Val({'ESL_Svc_Codes_'.Item Code})

Bob Suruncle

 

[BobSuruncle]

Sorry, further testing on my system shows that this will only work if the Numbers are the initial part of the string.
Do all of your item codes begin with the same characters, "/ES" ? (or at least do the numbers always start at location 4)
If so, then you could change the formula a little and make it work.

Val(Mid({'ESL_Svc_Codes_'.Item Code},4))

Bob Suruncle

 

[jlr123]

No some of the Item codes have only letters, some have more alpha letters and then numbers.
Items codes examples:
/BBLK
/BLONSITE10
/BL90MTN
/CALADJ
/RM
/bl12
/ES2
/ES103
/ESOS9
/ESOS4
Thanks very much.

 

[BobSuruncle]

OK, there may be a prettier and more efficient way of doing this, but the following should work:

Local StringVar p := {'ESL_Svc_Codes_'.Item Code} ;
Local NumberVar i ;
Local NumberVar L := Len(p) ;
Local StringVar txt := '' ;
Local StringVar nm := '' ;

For i := 1 to L do
(
if NumericText (p[i]) 
then 
    (
    txt := Mid(p,1,i-1) ;
    nm := Mid(p,i) ;
    Exit For
    )
Else
    (
    txt := txt + p[i] ;
    true;
    )
);
nm;
txt  & If NumericText(nm) then ToText(ToNumber(nm),"0000000000") else ''

You won't want to display this formula, just sort on it.


Bob Suruncle

 

[pmax9999]

This looked like an interesting challenge so thought I'd have a go at a solution. There is usually more than one way to achieve the same result in Crystal. I am not suggesting this is a better way to do it than what BobSurencle suggested, just different.

WhilePrintingRecords;
Local StringVar RES := '';
Local NumberVar i;

For i := 1 to Len({Table.Field}) Do
RES := RES +    (
                    If      ASC({Table.Field}[i]) in [48 to 57] 
                    Then    {Table.Field}[i]
                    Else    ''
                );
Val(RES)

The result of this formula is numerical, and can be used to sort the report records on.

Hope it helps.

Cheers
Pete

 

[pmax9999]

Actually, on rethinking this, the above approach will not work because it is not possible to sort on Print Time formulas.

The only way I can think of to achieve this would be to use the Replace function to strip out the Slash and Alpha characters. The following formula will return a numeric result that can be used for sorting. It's not pretty, but should work.

VAL(    
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
    Replace(Replace(Replace(
    UPPERCASE({@Table.Field})
    , 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', '')
    , 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', '')
    , 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', '')
    , 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', '')
    , 'Y', ''), 'Z', ''), '/', '')
    )

Cheers
Pete

 

[jlr123]

Thank you Bob!! Works good. Only wish I didn't have all those zeros. But this is for my own use so no problem.

 

[dik]

You might want to consider writing a program that revises current record numbers in a manner that will display the articles in a sorted fashion.

Dik

 

[jlr123]

Bob,
Hope you read this. I ran the report and it worked one time. Now I keep getting an error. Where do I put the formula to group by but not display it. I have tried to suppressing it as a group, taking it out as a group, and I have tried to use the formula in as a Group Sort Order for the Item Code Group field. When I try to use it as a formula for the Group Name Item Code sort I get an error stating "A constant expression is required here" for the {AR_InvoiceHistoryDetail.ItemCode} field.
Local StringVar p := {AR_InvoiceHistoryDetail.ItemCode} ;
Thanks,

 

[BobSuruncle]

You didn't mention that you'd be grouping on this, just sorting by it.

I think that if you group on it, and the order comes out correctly, you're 90% of the way there.
Next step would be to use Change Group and on the "Options" tab, select "Use a Formula as a Group Name"
In the X2 button, just reference the original Field Name {'ESL_Svc_Codes_'.Item Code}


Bob Suruncle

 

[jlr123]

This is great!!! Thank you so much.

 

[jlr123]

Ok Bob, This is crazy. It worked until I saved the report, closed it and reran it. Now it gives me the same error message as before:
"Failed to retrieve data from the database. Details: S0000:[ProvideX][ODBC Driver][FILEIO]Unable to allocate memory to perform requested operation [Database Vendor Code:14]"

This report has also been run on our server and still receives the same message.
Once the formula is removed, the report runs again.

 

[BobSuruncle]

Hi,
It looks like you have some data that is different that what you had first described.

Specifically, there are likely ItemCodes that begin with numbers.
The formula that I gave you will choke on these.

I also noticed that one of the examples that you gave has text, then numbers, and then text again.
How should these be sorted?

Could you provide 15 or 20 sample ItemCodes that cover all of the situations that you're going to encounter?

The following will work, but will likely not produce the correct output in the case of the text-number-text situation that I described.

Local StringVar p := {ESL_Svc_Codes.ItemCode}  ;
Local NumberVar i ;
Local NumberVar L := Len(p) ;
Local StringVar txt := '' ;
Local StringVar nm := '' ;
Local StringVar final ;
For i := 1 to L do
(
    if NumericText (p[i]) 
    then 
        (
        nm := nm & Mid(p,i,1) ;
        txt := txt ;
        )
    else
        (
        nm := nm ;
        txt := txt & Mid(p,i,1)
        )
);
final := Right("0000000000" & nm , 10);
txt & final

Bob Suruncle

 

[jlr123]

Please find all the item codes I need. In addition, the name of the field is actually {AR_InvoiceHistoryDetail.ItemCode} not {ESL_Svc_Codes.ItemCode}as previously stated.

FYI all the codes attached have a / prior to the item code in our database.

Thanks again for your assistance.

 

[BobSuruncle]

The following should account for all of the values that you sent.

Local StringVar p := {AR_InvoiceHistoryDetail.ItemCode}  ;
Local NumberVar i ;
Local NumberVar L := Len(p) ;
Local StringVar txt := '' ;
Local StringVar nm := '' ;
Local StringVar txt2 := '' ;
Local NumberVar x := 0 ;
Local StringVar final ;
For i := 1 to L do
(
    if NumericText (p[i]) 
    then 
        (
        x:=1;
        nm := nm & Mid(p,i,1) ;
        txt := txt ;
        )
    else
        (
        nm := nm ;
        If x=1 then txt2 := txt2 & Mid(p,i,1)
        else txt := txt & Mid(p,i,1);
        )
);
final := Right("0000000000" & nm , 10);
txt & final & txt2

Bob Suruncle

 

[jlr123]

Bob,

It is not sorting nummerically. I went in to Change Group Options from {AR_InvoiceHistoryDetail.ItemCode}to (Selected Options) Customize Group Name Field - Use a Formula as Group Name and entered your formula.

It is entering the zeros into the field but not sorting correctly. See attached. Thanks. I will out of the office tomorrow returning on Monday. thanks again.

 

[BobSuruncle]

Please modify your group so that the report is grouped on this formula.
You can modify the Group so that it displays the original field as the Group Name.

Bob Suruncle

 

[jlr123]

It worked until I closed the report after saving it and reran. Now I get the "Failed to retrieve data from the database. Details: S0000:[ProvideX][ODBC Driver][FILEIO]Unable to allocate memory to perform requested operation [Database Vendor Code:14]" error again.

 

[BobSuruncle]

I don't know what would be causing this database error.
I'm testing on Microsoft SQL Server using the codes that you had provided.

Anyone else have any ideas?


Bob Suruncle