Solved

Rewrite this sorting code from Crystal X11

Posted on 2006-05-02
Medium Priority
464 Views
Hello,

I need to modify a formula field in a Crystal report. The current version sorts the data alphabetically, where as it should be sorting it numerically.  I'm no expert with this stuff, so can one of you help me?

The result set contains a value for 'Main.ItemNumber' which can be either an integer or a string, depending on wether the item in question is a 'parent' or 'child'

For instance:

Parent Item:   1
|____Child 1: 1.A
|____Child 2: 1.B
|_Child 2.2: 1.B.1

Parent Item:   2
|____Child 1: 2.A
|____Child 2: 2.B

Parent Item:   3

Parent Item:   4
|____Child 1: 4.A
|_Child 4.1: 4.B.1

When there are less than 10 'child' items for a 'parent' item, everything works great. But when there are 10 or more, the ordering gets messed up. It SHOULD BE like this:

1     (i.e. 1.0)
1.A  (i.e. 1.1)
1.B  (i.e. 1.2)
...etc.
1.J  (i.e. 1.10)
1.K  (i.e. 1.11)

...but what happens is this:

1     (i.e. 1.0)
1.A  (i.e. 1.1)
1.J   (i.e. 1.10)
1.K  (i.e. 1.11)
1.B  (i.e. 1.2)
...etc.

Can you 'fix' it so it sorts correctly? I'm not worried if it a completely different formula as long as the intended results are achieved. I have included the formula as provided in the original report:

local N as number
local Last as string
local Counter as number
local Display as string
local CurChar as string
local DecimalCounter as number

N       = len({Main.ItemNumber})
Last    = {Main.ItemNumber}(N)
Counter = 1
DecimalCounter = 0

While Counter <= N
CurChar = {Main.ItemNumber}(Counter)

if IsNumeric(CurChar) then
Display = Display + CurChar
elseif CurChar = "." then
DecimalCounter = DecimalCounter + 1
if DecimalCounter = 1 then
Display = Display + "."
end if
else
Display = Display + CSTR(ABS((Asc(CurChar) - Asc("A")) + 1), 0)
end if

Counter = Counter + 1
wend

if IsNumeric(Display) then
Formula = CDBL(Display)
else
Formula = {Main.ItemIndex}
end if

0
Question by:TunaMaxx

LVL 101

Accepted Solution

mlmcc earned 2000 total points
ID: 16592478
WHy not simply replace this

if IsNumeric(Display) then
Formula = CDBL(Display)
else
Formula = {Main.ItemIndex}
end if

with

Display

What value is in the database?
If it is the 1.A, 1.B, etc then simply sort by the database field but display using your formula.

Numerically and string wise 1.11 comes before 1.2

mlmcc
0

Author Comment

ID: 16592593
The values in the database are such as:

1
1.A
1.A.1
1.A.2
1.B

...etc.

I am going to try your suggestion now.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses
Course of the Month13 days, 20 hours left to enroll