asked on

How to Group strings in Crystal Reports by wild cards

I want to be able to create a group based on a formula..

Basically there is a list of Jobs in the database such as;
10269-1
10452-1
R10452-1-1
R10269-1-2
10367-1
10467-2
R10367-1-1
R10269-1-1

Basically I would like to group all the jobs that contain the core number which would be 10269, 10452, 10367, etc. with there rework jobs (these start with 'R')

So in the group for 10269-1 would also include R10269-1-2 and R10269-1-1
and the group for 10452-1 would also include R10452-1-1
and the group for 10367-1 would also include R10367-1-1.

Thanks in advance for the help!

Mike McCracken

Try a formula like this for the group field

If Left({JobNumberField},1) = 'R' then
Mid(Split({JobNumberField},'-')[1],2)
Else
Split({JobNumberField},'-')[1]

mlmcc

Jeremy Campbell

ASKER

Thats actually very close! However, there are also Core Jobs that may also end in a -2. And if possible, I would like for the complete core job to always display, e.g. 10358-1, 10269-2, etc.

In these two examples there could be rework jobs tied to them that would look like;
R10358-1-1
R10358-1-2
R10269-2-1
R10269-2-11

So currently the Formula is only displaying the job #'s as 10358 and 10269. And the 10269 is throwing the 10269-2 in with the 10269-1 job when the -1 and the -2 are actually two different jobs.

Hopefully this makes since and doesn't complicate the formula too much..

Thanks for your help mlmcc!

Jeremy Campbell

ASKER

Oh, and I should mention, (just realized), some of the Rework jobs have letters in the end. I hope this doesn't throw things off too much. They look like the following;

R10452-1-10B
R10452-2-34C
R10452-4-19A

The core job will always be the #####-# though and Rework jobs tied to the Core job will always contain that Core job with an added -# at the end and an 'R' at the beginning.

GJParker

Are the job numbers always 5 digits long ?

ASKER CERTIFIED SOLUTION

GJParker

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeremy Campbell

ASKER

Sometimes they may also have another preceding letter which would either be an 'X' or an 'E'.
X10452-1
X10234-2
E10109-1
E10408-11

Reworks for those jobs would be;
RX10452-1-1
RX10234-2-1
RE10109-1-5
RE10408-11-3

I applied the formula you provided though and it seems to be working despite the extra letters at the front of the job numbers..

This is going to be great since I can eliminate my Subreport which would make this take about 30 seconds to run.. With this method it doesn't take any time at all!