[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Suppress records if first 5 characters are duplicated

Posted on 2004-11-25
Medium Priority
Last Modified: 2008-05-28
Hi All,
I have a mailing list database that I was to suppress duplicates on if the first 5 characters of the ADDR field match the one in the previous (or any record!?) record ADDR field.

I can sort by ADDR easily enough, and 'Suppress if Duplicated', but it will not suppress the second entry if they are as follows
1 Alinda Road
1 Alinda Rd.

If I could just compare the first 5 or 6 characters of the field with a previous (or better yet, any record), and suppress (or highlight) then manual supression is a cinch.

Can ye help?
Question by:holdsworthbros
  • 2
  • 2
LVL 77

Assisted Solution

peter57r earned 80 total points
ID: 12673518
Hello holdsworthbros,

You could create a group based on a formula field which extracts the first 5 characters of the address field.
Then put your data into the group header rather than the detail line. Suppress the detail line.
This will result in one record for each unique value of the first five characters.  Just the first record for each group will be shown.


Expert Comment

ID: 12674824
If you right click on the field you would like to suppress and go to the FORMAT FIELD item a popup will display. There is a check box that says suppress; don't check it, just follow it across to the X+2. Click on that and a formula box will pop up. Try writing a formula to do what you want.

Accepted Solution

Makman25 earned 800 total points
ID: 12674925
Use this in the Suppress Formula Popup using Crystal Syntax:

left({Address.Address},4)  = left(next({Address.Address}),4)
LVL 101

Assisted Solution

mlmcc earned 120 total points
ID: 12677994
What version of Crystal?  What type is the ADDR field?

If this is for CR8.5 or before and a memo field then the formulas will not work since memo fields aren't allowed in formulas.

You could change the SQL to retrieve the first 5-6 characters and sort on the field and then compare

LVL 101

Expert Comment

ID: 12681410
Glad i could help


Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question