Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: sorting on alpha-numeric field Post Reply Post New Topic
<< Prev Page  of 2
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 May 2009 at 8:35pm

JOhn,

Can you add an if statement to this so if the last character is not numeric then put in 0 else put in your other formula Then do a primary sort on this formula field and a secondary sort on the field itself?
All your items that end in a non numeric value will be listed first as a 0 as the primary sort, then these zero sorted items should sort correctly using the secondary sorting of the original field.
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 30 May 2009 at 10:19pm
Hi DBlank,
 
I have borrowed a formula from Issue 57 of Crystal Clear( News and Hints for Cryatl Reports) by Chelsea Technologies:
 
@sort_order_num
 
local numbervar digit;
local numbervar v;
local stringvar s;
local numbervar i;
v :=0;
for i := 1 to len({LIX.ID}) do (
   s:= mid({LIX.ID},i,1);
   
if isnumeric(s) then
   digit := val(s);
    v:= v*10 + digit;
);
 
v
This works for both prefix and suffix.
 
John
 


Edited by johnwsun - 30 May 2009 at 10:20pm
IP IP Logged
Rasta
Newbie
Newbie
Avatar

Joined: 13 Jul 2009
Location: Australia
Online Status: Offline
Posts: 2
Quote Rasta Replybullet Posted: 13 Jul 2009 at 2:22am
Hello Everyone. I am trying to sort data in a report and one of the problems I am facing is alphanumeric room numbers. eg 01A, 01B, 02A ... and 01.1, 01.2, 02.1, 02.2....
I was wondering if some one could help me because the normal ascending order of sorting a group does not help.

Your help will be a big help and I would like to thank you in advance.

Cheers
SAR
IP IP Logged
johnwsun
Senior Member
Senior Member


Joined: 28 May 2008
Location: Australia
Online Status: Offline
Posts: 179
Quote johnwsun Replybullet Posted: 13 Jul 2009 at 5:15am
Hi SAR,
 
Have you tried the following lines:
local numbervar digit;
local numbervar v;
local stringvar s;
local numbervar i;
v :=0;
for i := 1 to len({LIX.ID}) do (
   s:= mid({LIX.ID},i,1);
   
if isnumeric(s) then
   digit := val(s);
    v:= v*10 + digit;
);
 
v
 
John
IP IP Logged
Rasta
Newbie
Newbie
Avatar

Joined: 13 Jul 2009
Location: Australia
Online Status: Offline
Posts: 2
Quote Rasta Replybullet Posted: 14 Jul 2009 at 1:51pm
Thank you for replying John. I am trying to use the formula that you have given. I needed the report as there was no other way of making work easier and so I added another group which was the room number and then sorted it according to specified list. BUT this means I have numerous reports, one for each facility. I was able to do that as the room numbers are fixed.

I will try this and let you know.. If I get one report then I can replace the .rpt file the database application is using and I can use it through the software.. Now I have to run it through logicity application builder.

Thanks John
SAR
IP IP Logged
<< Prev Page  of 2
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.