# Crystal REports Sorting numbers

I need to print this thread to show to some people.  We are setting up all our codings (Dept., WC, machine, etc…) and I am having a heck of a time convincing people that they need to either use leading zeros or text codes less than the maximum length or number from100 or 1000 as the situation merits.  It will pay off huge later on for reporting and not require much extra entry because people tend to use drop downs or searches anyway.  Management has trouble with the implementation taking so long (6 months) but does not understand the need to carefully think these things out because we will have to live with the decisions for a long time.  An ounce of prevention as they say.

-Todd C.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, October 01, 2014 1:00 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Crystal REports Sorting numbers

You must be storing those number in a character field judging by what you have below.

Here's a trick that I use in SQL and Crystal when I need to properly sort on numbers stored in character fields:

In Crystal (a formula):    Right( ("0000" + {Your_char_field}), 3)

So in your example below, it would convert all "numbers" to:

001

010

011

012

002

020

021

And then when you sorted on them, it would properly sort as:

001

002

010

011

012

020

021

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, October 01, 2014 1:22 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal REports Sorting numbers

I have a report that I sort a field that has numbers.  How do I get it so that is sort numbers and not as below?

1

10

11

12

2

20

21

Miguel A. Santillan

ERP Analyst

Compass Manufacturing Systems

510-661-6666  Office

I have a report that I sort a field that has numbers.  How do I get it so that is sort numbers and not as below?

1

10

11

12

2

20

21

Miguel A. Santillan

ERP Analyst

Compass Manufacturing Systems

510-661-6666  Office

Are the number values stored as strings?
If so have you tried a convert formula for sorting?

-  field to a number formula @dblSort:  CDbl ({table.field})
- add  formula field @dblSort to the report and sort by that

found this in a google search

```IF IsNumeric({TableName.TextNum}) = False
THEN "0" & {TableName.TextNum} ELSE
IF Len({TableName.TextNum}) < 2
THEN "0" & {TableName.TextNum} ELSE
{TableName.TextNum}```

You must be storing those number in a character field judging by what you have below.

Here's a trick that I use in SQL and Crystal when I need to properly sort on numbers stored in character fields:

In Crystal (a formula):    Right( ("0000" + {Your_char_field}), 3)

So in your example below, it would convert all "numbers" to:

001

010

011

012

002

020

021

And then when you sorted on them, it would properly sort as:

001

002

010

011

012

020

021

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, October 01, 2014 1:22 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal REports Sorting numbers

I have a report that I sort a field that has numbers.  How do I get it so that is sort numbers and not as below?

1

10

11

12

2

20

21

Miguel A. Santillan

ERP Analyst

Compass Manufacturing Systems

510-661-6666  Office

Thanks all.

Miguel A. Santillan

Compass Manufacturing Systems

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, October 1, 2014 11:00 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Crystal REports Sorting numbers

You must be storing those number in a character field judging by what you have below.

Here's a trick that I use in SQL and Crystal when I need to properly sort on numbers stored in character fields:

In Crystal (a formula):    Right( ("0000" + {Your_char_field}), 3)

So in your example below, it would convert all "numbers" to:

001

010

011

012

002

020

021

And then when you sorted on them, it would properly sort as:

001

002

010

011

012

020

021

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com]
Sent: Wednesday, October 01, 2014 1:22 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal REports Sorting numbers

I have a report that I sort a field that has numbers.  How do I get it so that is sort numbers and not as below?

1

10

11

12

2

20

21

Miguel A. Santillan

ERP Analyst

Compass Manufacturing Systems

510-661-6666  Office