Advanced sorting?

[rcrecelius]

The data I work with pertains to part numbers such as...

W89711
W89725
W89742
W8C
W9
W900S
W901S
W902C
W903
W904
Is there a formula I can use to sort numbers like these? As you can see, W8C and W9 should have been at the top of the list. I have this problem with Excel also.
The proper sequence should have been...
W8C
W9
W900S
W901S
W902C
W903
W904
W89711
W89725
W89742
Im using CR v9.

 

[mbarron]

Can the formatting of your numbers change from begining with one letter then numbers?

If it is always as you've shown above, where the number starts and/or ends in a letter, you can use the following formula as your sort field.
val(right({field},length({field})-1)) Mike

 

[rcrecelius]

mbarron,
There are MANY exceptions. I have some part numbers that are 4 letters 2 numbers and anther letter such as...
AGSF22C
AWSF22C
AWSF22FS

Im dealing with auto parts so there are about 48,000 different part numbers that i deal with.

The example you stated, exactly where would I use that?

 

[mbarron]

YOu would have created a formula using the val(right(etc...) and then sorted on the formula.

Since you are using CR9 you can creat a looping formula to create a dummy item number to sort on.

Go to Insert/Fields object. Create a new formula in the edit area paste the following:

numbervar loop;
stringvar input:={your.part.field};
stringvar output;
numbervar numout;
numbervar schars:=0;
numbervar fchars ;
for loop:= 1 to length(input)
do(
if isnumeric(input[loop]) then
(numout:=val(mid(input,loop));
fchars:=length(input)-(schars+length(totext(numout,0,"&quot));
loop:=length(input)+1) else
schars:=schars+1);
output:=left(input,schars) +trim( totext(numout,"000000&quot)+right(input,fchars)


Next, go to Report/Sort Records, Choose the field you just created.

Change the number of blue zeros above to the longer number of digits you may have in a part number. I.e. if your part number is ASD1234ASDF, you would need at least 4 0's. Mike

 

[rcrecelius]

mbarron,
I went back to your original suggestion and it was still a little crazy because some of the numbers would be negatives. I added abs to the front of that and it seems to have worked great.

Now about that loop formula..
I tried pasting that formula in and when checking for errors it said "a variable is expected here" and highlighted loop in the first line.

 

[rcrecelius]

abs(val(right({PARTS.p_part_number},length({PARTS.p_part_number})-1)))

This formula works as long as there is only one Letter at the beginning of the part number. Is there a way to identify how many letters are at the beginning of the part number and then use if statement accordingly?

 

[stormtrooper]

Do you know how many letters there can be?

 

[rcrecelius]

I know for sure there are some part numbers with 4 letters in front, some with 4 letters behind.
Like I stated earlier, we have over 48,000 part numbers so it would take me a while to look at all of them to see how many have leading letters.

I was hoping to do something like...
if {PARTS.p_part_number} starts with 1 letter
then
abs(val(right({PARTS.p_part_number},length({PARTS.p_part_number})-1)))
else
if {PARTS.p_part_number} starts with 2 letters
then
abs(val(right({PARTS.p_part_number},length({PARTS.p_part_number})-2)))
etc...

 

[mbarron]

If the word loop is blue, then it is a function name (it isn't in CR8) Change the variable name to something other than loop, and change all other occurances of "loop" to the new name.

How are your part numbers negative? By what you had posted, I assumed your field was a string. Mike

 

[rcrecelius]

mbarron,
the result of the formula before I added abs was negative on some numbers.
ex...
W89708-13 -8970814.00

It looke like the numbers that had a - in them were the ones that had a negative result.

 

[rcrecelius]

Ok I tried the Loop again. I get results like this...

W2 W000002
W2SAE W000002SAE
W3 W000003

I really cant say that I understand why it works, but it seems to have solved my "sorting" problem.
I appreciate the help.

 

[mbarron]

Try this one then. Replace {num.items} with your field

numbervar looper;
stringvar input:="";
stringvar output:="";
numbervar numout:=0;
numbervar schars:=0;
numbervar fchars:=0 ;



if instr({nums.items},"-&quot>0 then input:=left({nums.items},instr({nums.items},"-&quot-1) else
input:={nums.items};




for looper:= 1 to length(input)
do(
if isnumeric(input[looper]) then
(numout:=val(mid(input,looper));
;
looper:=length(input)+1) else
schars:=schars+1);

if instr({nums.items},"-&quot>0 then fchars:=length({nums.items})-instr({nums.items},"-&quot+1 else
fchars:=length(input)-(schars+length(totext(numout,0,"&quot));

output:=left(input,schars) +trim( totext(numout,"000000&quot)+right({nums.items},fchars); Mike

 

[rcrecelius]

mbarron,
Using the formula above most sorting is good except for numbers like...
W89708-12 sorts ahead of W89708-4

 

[mbarron]

One more time:

numbervar looper;
stringvar input:="";
stringvar output;
numbervar numout:=0;
numbervar schars:=0;
numbervar fchars ;
stringvar adash;
stringvar adout;
stringvar endout;

if instr({nums.items},"-&quot>0 then (input:=left({nums.items},instr({nums.items},"-&quot-1);
adash:=right({nums.items},length({nums.items})-instr({nums.items},"-&quot+1))
else
input:={nums.items};



for looper:= 1 to length(input)
do(
if isnumeric(input[looper]) then
(numout:=val(mid(input,looper));

looper:=length(input)+1) else
schars:=schars+1);
if instr({nums.items},"-&quot>0 then fchars:=length({nums.items})-instr({nums.items},"-&quot+1 else
fchars:=length(input)-(schars+length(totext(numout,0,"&quot));
endout:=right({nums.items},fchars);


if instr({nums.items},"-&quot>0 then(
adout:=totext(val(adash),"0000"
endout:="-"+adout+right(adash,length(adash)-length(totext(tonumber(adout),0,"&quot)));



output:=left(input,schars) +trim( totext(numout,"000000&quot)+endout
Mike

 

[rcrecelius]

Wow, I cant tell ya how much I appreciate your help.
I ran this on several different lines and they all worked great. This will be a huge time saver for me in the future.

Now if I can figure out "why" it all works!

thanks again