## 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!

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

e.g

@MySort

## CODE -->

Bob Suruncle

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

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 -->

Bob Suruncle

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

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

## CODE -->

Bob Suruncle

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

suggested, just different.BobSurencle## CODE

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

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

Cheers

Pete

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

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

Dik

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

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

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

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

"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

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 -->

Bob Suruncle

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

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

## CODE -->

Bob Suruncle

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

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

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

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

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

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

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

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

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

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