Rewrite this sorting code from Crystal X11

Posted on 2006-05-02
Medium Priority
Last Modified: 2008-03-10

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

  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
    Display = Display + CSTR(ABS((Asc(CurChar) - Asc("A")) + 1), 0)
  end if

  Counter = Counter + 1

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

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)
  Formula = {Main.ItemIndex}
end if



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


Author Comment

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



I am going to try your suggestion now.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question