A better sort formula - CR v8.5 - Code sample included 1

[AlohaMichael]

I have a list of ID's stored in a string field in an sql database. (see lists below) I would like to use a formula to get the proper sorting. The issue is CR is sorting them like below.

Default Desired
Sorting Sorting
RFIID RFIID
001.001 001.001
002 1.005
003.004 002
005.001a 3
006.12 003.004
007.018 3.812
1.005 4.001
100.001 005.001a
101.002 006.12
101.020 007.018
110.003 100.001
110.02 101.002
110.1 101.020
3 110.003
3.812 110.02
4.001 110.1

The issue is having the ID stored as a string and users have a tendency to throw in random letters in to the ID field.

The formula I have to sort the field is as follows
numbervar ctr1;
numbervar rfinum := 999;
for ctr1 := length({Requests for Information.RFI~Number}) to 1 step -1 do
(
if numerictext (left( {Requests for Information.RFI~Number}, ctr1) ) then
(
rfiNUM := tonumber (left({Requests for Information.RFI~Number}, ctr1));
exit for;
)
);

rfiNUM;

Ideally I am trying to get the ID number into the following format 123.123 but if the user doesn't put the ID number in that format I want to be able to handle it as best as possible.

Any suggestions / ideas?

No, I have no way of Normalizing the ID's they are stored on a 3rd Partys database. Additionally there are over 35,000 ID's in the system. I know, I know I am trying to make CR do things it wasn't designed to do.

 

[lupins46]

No guarantees with this but here's my thoughts..
Create two formula fields which contain the firstpart and second part of the sort key and sort on these.
The first key is the bit before the . and the second part is the bit after the .

Sort on keyA then KeyB.

It will not work for entries like:
1.1a
1.12a
If there is an alpha suffix there must be 3 preceding digits.

Change 'mytable' to your own tabl.e name


KeyA:
numbervar x;
numbervar key1;
x:= instr({mytable.rfiid},".");
if x = 0 then
key1 := val({mytable.rfiid})
else
key1 := val(left({mytable.rfiid},x-1));
key1


KeyB:
numbervar x;
stringvar y;
stringvar z;
stringvar key2;
x:= instr({mytable.rfiid},".");
y:="";
if x = 0 then
(key2 := "000 " ) //3 zeroes and a space
else
(y:= mid({mytable.rfiid},x+1);
if isnumeric then
z:= " " //space
else
z:= right({mytable.rfiid},1); //assumes 1 letter only
key2:= left(y & "000" ,3) & z);
key2

 

[mbarron]

You're making this way too hard:

Create the following formula:
val({{Requests for Information.RFI~Number})

Group on the new field. Use your original field to display the data.


The VAL()function converts the number value upto the first letter it would encouter to a number.



Mike

 

[AlohaMichael]

Thanks mbarron.
I was over thinking the problem.
Val function is exactly what I needed.