• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 468
  • Last Modified:

Rewrite this sorting code from Crystal X11


  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

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

TunaMaxxAuthor Commented:
The values in the database are such as:



I am going to try your suggestion now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now