Sort Record

[korata]

My friends suggested to me I should post this question in this website for someone to take a look at it. They showed and provided me a couple methods they learned form this website to solve this problem unfortunately none of the formulas seem to sort the record us would like it to be. We would like for our records to be sorted first a letter followed by number for example: A400,E0038……..M0058,….V0009 and Dist 1,Dist2….Dist6 ,IN DIS at the end the number will appear in Ascending order. Please if someone has a clear idea what I am talking about help me to solve this problem. This is my first visit to this site and I hope I will be visiting this site frequently. I really appreciate your help. By the way I am using CR 8.5 and my Field type is String.



A400 AMERICAN PLUMBING CO OF WINONA INC450 44TH
E0038 EATON CONSTRUCTIONRR 1 BOX 60 PETERSON
1456 EATON CONSTRUCTIONRR 1 BOX 60
IN DIS INTER PROG
Metro east MAIL STOP 440MAIL STOP 440 ST PAUL
M0058 MCHUGH EXCAVATING & PLUMBINGW 7010
M630 MINNOWA CONSTRUCTION INC850 WICKETT DR
Dist 6 INC850 WICKETT DR N W HARMONY
V0009 VANGUNDY EXCAVATIONG INC LLP3383
Dist 5 VANGUNDY EXCAVATIONG INC

 

[synapsevampire]

You'll want to break this field into 2 fields, one a string, the other a numeric. This might more effctively be done on the database side, but since you didn't share the database type or version, I'll work it out for you in Crystal:

You also didn't give any indication of your report layout, such as whether you have a group, if so, you'll want to reset the values based on the group using something like the following in the group header:

whileprintingrecords;
stringvar TheAlpha:="";
Stringvar TheNum:="";

For the string create a formula such as:

whileprintingrecords;
stringvar TheAlpha;
Stringvar TheNum;
numbervar x;
For x := 1 to len({@nameid}) do(
if not(isnumeric(mid({@nameid},x,1))) then
TheAlpha:= TheAlpha+mid({@nameid},x,1)
);
TheAlpha

For the number create a formula such as:

whileprintingrecords;
stringvar TheAlpha;
Stringvar TheNum;
numbervar x;
For x := 1 to len({@nameid}) do(
if isnumeric(mid({@nameid},x,1)) then
TheNum:= TheNum+ mid({@nameid},x,1)
);
TheNum

Use these fields to do your sortation (Report->Sort Records), with the string first, and then the number in a string format portion.

Note that a string containing a number will sort based on the value of the first numeric value, adn continue with each character, not as a numeric, so that the following:

01
1
100
00200
2

would be the way the secondary sort would occur.

You could convert the value using (VAL), but then it would change things like 00009 to just a number 9.

I intentionally did not convert this to a number to preserve the 0009 type of data.

Hope this resolves, if not post the problem.

-k

 

[korata]

I am using oracle 9i database and i have one group field called callId in the page header.As you can see i am fairly new to CR. So your help is very valuable to me.Please let me know if i am albe to use the above formula without my group field value being reset.once again thank you for the help.

 

[synapsevampire]

Groups aren't created at the page header.

If you have a group, then add the rest in the group header level.

Otherwise the fromulas should work for you, If you're new to database concepts, such as sorting and group bys, etc., you may have a bit of a challenge implementing this, just replace the @nameid with your table.field.

-k

 

[korata]

I saved the formulas as you told me but when i go to record sort order window i do not see the two formulas .any idea?

 

[synapsevampire]

Sorry, take out the whileprintingrecords portion in each.

-k

 

[korata]

I got an error saying a string can be at most 254 character long.

 

[synapsevampire]

Then you didn't reset the value at the group header level.


If there are no groups, then change to:

whileprintingrecords;
stringvar TheAlpha:="";
Stringvar TheNum:="";
numbervar x;
For x := 1 to len({@nameid}) do(
if not(isnumeric(mid({@nameid},x,1))) then
TheAlpha:= TheAlpha+mid({@nameid},x,1)
);
TheAlpha

-k

 

[korata]

I have one group field as i have mentioned to you earlier and i have been struggling to find a formula box to copy this code in the group header level:I do not know ,this code seems a little bit complicated to do understand with a little knowledge i have about CR formula.I really appreciate if you make it simpler for me so i can be able understand it. thank you for being patient with me
1- i have one group field
2- I do not know where to store this below code.there is no fomula box in the group header level
whileprintingrecords;
stringvar TheAlpha:="";
Stringvar TheNum:="";
3- I believe the reason i got the above error message is becasue i have a record over 2000.I am not sure if that is the cause for the program not to function properly

once again thank you