×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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!

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

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

e.g
@MySort

CODE -->

Val({'ESL_Svc_Codes_'.Item Code}) 

Bob Suruncle

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

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.

CODE -->

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

Bob Suruncle

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

(OP)
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.


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

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

CODE -->

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

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

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.

CODE

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

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

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.

CODE

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

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

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

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

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

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

(OP)
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,

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

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

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

(OP)
This is great!!! Thank you so much.

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

(OP)
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.

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

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.

CODE -->

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

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

(OP)
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.

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

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

CODE -->

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

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

(OP)
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.

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

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

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

(OP)
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.

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

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

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

Yes, that seems correct.
I'm unsure why you'd get a database error like this.

Bob Suruncle

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

(OP)
Bob, I created a cross tab report and the formula now works! Thanks again.

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

(OP)
Bob, I am not familiar with some of the syntax and would like to know more about the formula. I understand local variables but not i p nm etc. I want to learn how to do this myself. Can you assist or tell me where to go to learn this? Thanks

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

Hi,
Variables are placeholders, and can make a formula more efficient and easier to read.
Variables can be named with whatever name you want. The only rules are that they must start with a letter, and may not be the same name as a built-in function.
Your variable name can't be "sum", or "len", or "length", as all of these are built in funtions
"i" is very common, usually used as a counter.
I used "nm" to stand for "Name"

There are 3 scopes to variables - Local, Global, and Shared

Local variables are available only in the formula that is being executed- their values cannot be used in other formulas.
Because of that, the same Local variable name can be used in multiple formulas without any problems.

Global variables are the default variable scope.
If you forget to define a scope, it will be Global by default.
Global variables that are set in one formula can be read and used by another formula.

Shared variables are used to pass values from a subreport back to the main report. (or vice versa)
the Shared scope forces the formula to be executed in the WhilePrintingRecords pass.

I would suggest that you read George Peck's book, "The Complete Reference" for whatever version of Crystal that you're using.

Bob Suruncle

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close