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

Rewrite this sorting code from Crystal X11

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
TunaMaxx
Asked:
TunaMaxx
1 Solution
 
mlmccCommented:
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
 
TunaMaxxAuthor Commented:
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
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