Crystal Reports XI - formula to remove duplicate records

Posted on 2010-11-29
Last Modified: 2012-05-10
can someone please help me to create this formula in order to remove duplicate records.

I have Part Number, Formula A and Formula B.

Formula A states:
if Code='A' then show value from VALUE field

Formula B states:
if Code='B' then show value from VALUE field

Both of these formulas as in my report details

so the output will create 2 records to each part.

line 1: |Part 123|  Coke| 'blank'|
line 2: |Part 123|  'blank'| 'Cisco'|

how can I combine these 2 lines so that it will show only data like this:

line 1: |Part 123|  Coke| Cisco|

thank you
Question by:Palmer_Admin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
LVL 100

Accepted Solution

mlmcc earned 250 total points
ID: 34234014
Are the lines separate records?

Try this idea.

Group by part number

In the group header add a formula
Global StringVar strpartA;
Global StringVar strpartB;
strpartA := "";
strpartB := ""

In the detail section add a formula
Global StringVar strpartA;
Global StringVar strpartB;
If {CodeField} = "A" then
    strpartA := {ValueField};
If {CodeField} = "B" then
    strpartB := {ValueField};

Remove the fields from the details section except the above formula
You can then suppress the detail section if blank

In the group footer
Put the fields you want to display
Add 2 formulas to display the strings
Global StringVar strpartA;
Global StringVar strpartB;

LVL 35

Assisted Solution

James0628 earned 250 total points
ID: 34236792
Another approach:

 Group on the Part Number and suppress the detail section.  Put FormulaA and FormulaB in the detail section.  Right-click on each formula and select Insert > Summary and put a Maximum summary for each formula in the group footer.  I think that should work, and it's a bit simpler than using variables.


Author Comment

ID: 34239300
thank you guys for your help

James, I can't use FormulaA as Insert Summary field. it is not letting me to grab it as an option.

mlmcc, i still get duplicates, is there a another way?
also, can it be done without grouping?

Will the Alias work in this case?

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button


Author Comment

ID: 34239693
my problem is that my part #'s keep repeating because there are order by date as well, so when I try to group it the data is off
LVL 100

Expert Comment

ID: 34239916
Unless you can sort them so the duplicates are together there is no easy way since the records aren't duplicates in the sense that all fields are not the same.

You may have to group based on enough fields so that the "duplicate" records are grouped together.


LVL 35

Expert Comment

ID: 34245658
 > I can't use FormulaA as Insert Summary field.

 In your original post, you said that Formula A was:

if Code='A' then show value from VALUE field

 If it really is as simple as "if {field1} = 'A' then {field2}", then you can do a summary on it.  If Formula A or B is not that simple, it may affect your options, and the solution, so we'd need to see the exact formulas.


Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit If you want to manage em…

751 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