Rewrite this sorting code from Crystal X11

Posted on 2006-05-02
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 100

    Accepted 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


    Author Comment

    The values in the database are such as:



    I am going to try your suggestion now.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    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. …
    I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now