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.

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

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

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.

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

Join Now
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