How do I use "like previous" with wildcard characters?

[staciann]

Hi. I am using Crystal v10
I have the following running total which works great until I get to the end - I want to say:
{@TrimName} like previous ?{@TrimName}?
but I can't quite figure out the syntax that needs to be used with the like, previous and ? characters.
I'd appreciate any help you can give me.

{@TrimName}=previous({@TrimName}) or
sum({@TEST1},{PJPROJ.user1}) > 0 or
{@TrimName}=previous({@TrimName}) or 
{@TrimName}="" or
right({@TrimName},5) like right(previous ({@TrimName}),5)or 
left({@TrimName},5) like left(previous ({@TrimName}),5) or 
mid({@TrimName},5) like mid(previous ({@TrimName}),5) or
{@TEST1}=0 and next({@TEST1})<>0 and right({@TrimName},5)like right(next ({@TrimName}),5) or 
{@TEST1}=0 and next({@TEST1})<>0 and left({@TrimName},5) like left(next ({@TrimName}),5)or 
{@TEST1}=0 and next({@TEST1})<>0 and mid({@TrimName},5) like mid(next ({@TrimName}),5)or
{@TrimName} like previous...

 

[lbass]

There are multiple problems with your formula, including missing parens and redundancy. Why don't you instead show us some sample data and explain what your requirements are?

-LB

 

[staciann]

Thanks LB.

My data includes:

Group 1 {SoAddress.CustId}
Group 2 {PJPROJ.user1}
Group 3 {PJPROJ.project}

The details include:
{#RTotal1}, {@TrimName} (based off of Group 2), {@Company}, {@Address1}...

I have formula @Test1:

if {PJPROJ.start_date}> Date (2007,01,01) 
then 1
else 0

I am using the results as a mailing list so I hide all of the groups and then use the following suppression formula in the details section. What I am doing is pulling all {@TrimName} that had a {PJPROJ.project} between 1/1/04 and 1/1/07 but had no additional {PJPROJ.project}. I am stuck working with a horrible database so I also have to suppress records with names that are close to being duplicated and then suppress the first name since the almost duplicate would be another project.

This suppression formula works perfect and gives me the exact data that I need!

{@TrimName}=previous({@TrimName}) or
sum({@TEST1},{PJPROJ.user1}) > 0 or
{@TrimName}="" or
right({@TrimName},5) like right(previous ({@TrimName}),5)or 
left({@TrimName},5) like left(previous ({@TrimName}),5) or 
mid({@TrimName},5) like mid(previous ({@TrimName}),5) or
{@TEST1}=0 and next({@TEST1})<>0 and right({@TrimName},5)like right(next ({@TrimName}),5) or 
{@TEST1}=0 and next({@TEST1})<>0 and left({@TrimName},5) like left(next ({@TrimName}),5)or 
{@TEST1}=0 and next({@TEST1})<>0 and mid({@TrimName},5) like mid(next ({@TrimName}),5)

The problem comes in with {#RTotal1}. I can't seem to get the count to match up with the data. For example it gives me:

1
3
4
5
8
9
10
12

 

[staciann]

Sorry - I did end up getting the count right (I can't remember this morning what I did last night!)

I still need to suppress additional records though because all of the duplicates aren't being removed. So instead of:

right({@TrimName},5) like right(previous ({@TrimName}),5)

I want to say something like:

{@TrimName} like previous (?{@TrimName}?)

but obviously I don't have the ? where they need to be. I'm not sure of the syntax on using wildcard characters with the like previous clause.

 

[lbass]

You didn't show any sample data, so I'm not exactly sure about this, but you could try:

{@TrimName} in previous({@TrimName})

Or,
{@TrimName} like "*"+previous({@TrimName})+"*"

-LB

 

[staciann]

I thought the second one was going to work great but it still isn't suppressing the ones I'm having problems with.
One example is that I have Frank Decato - say he has the following records:

Mr. Decato
Frank A. Decato
Frank Decato
Mr. Frank Decato
Frank Decata
A. Decato
Mr. Frank A. Decato


With the suppression formula I have now:

{@TrimName}=previous({@TrimName}) or
sum({@TEST1},{PJPROJ.user1}) > 0 or
{@TrimName}="" or
right({@TrimName},5) like right(previous ({@TrimName}),5)or
left({@TrimName},5) like left(previous ({@TrimName}),5) or
mid({@TrimName},5) like mid(previous ({@TrimName}),5) or
{@TEST1}=0 and next({@TEST1})<>0 and right({@TrimName},5)like right(next ({@TrimName}),5) or
{@TEST1}=0 and next({@TEST1})<>0 and left({@TrimName},5) like left(next ({@TrimName}),5)or
{@TEST1}=0 and next({@TEST1})<>0 and mid({@TrimName},5) like mid(next ({@TrimName}),5)

almost everything is being suppressed, showing only the first record belonging to him - Mr. Decato. But it is still showing me A. Decato because the previous record is off by one character (Decata instead of Decato). I can't figure out how to eliminate records off by just a character or two and I have quite a few of them.

Another one is:

Mike Conway
Mr. Gene Artiano
Mr. Mike Conway

It isn't suppressing the Mr. Mike Conway because the previous record is Gene Artiano since they're in alphabetical order. I'm guessing there is some way to solve my first problem, but is there anything I can do about this?

Thanks.

 

[lbass]

This looks like a losing battle--that the database needs to be changed. I think you need to determine whether you need more than the last name to determine a match. You would probably have more success by creating formulas for last name and first name, e.g.,

//{@name}:
stringvar array x := split({table.name}," ");
stringvar lname := x[ubound(x)];
stringvar fname;
if x[1] in ["Mr.","Ms.","Mrs.","Miss"] then
fname := x[2] else
fname := x[1];
fname +" "+lname

Then I would sort records using this formula and then just use the following for section suppression (do NOT check the suppression box in the section expert->details):

{@name} = previous({@name})

When names are misspelled, they should be corrected in the database, rather than adjusted for through programming.

-LB