Remove Duplicate lines in crystal reports

Printed From: Crystal Reports Book — Forum Name: Technical Questions

Posted By: paul_birm — 10 Jul 2008 at 2:30am

I have a report based on one data table ... lets call it MRPLOG. The MRPLOG table holds a item called Part - this links nicely to a data table called PURCHITEMS which has a item called P.Part - this data table links to a 3rd table called PURCHORDERS - purch orders holds the all important VENDOR Code I need to add to my existing Crystal 9 report.

the report (solely based on the MRPLOG file) is working well and running at around 700 pages long - when i add the Vendor Code from the Purchorders table, the page count jumps up to 4500 pages. Looking at the data it is showing duplicate records - I need to remove these duplicate records and i cant - tried the old 'suppress if duplicate' but that only removes the field - i need to remove ( or suppress) the whole line.

please, if a question from you, would in the end help me... please ask it!!

Posted By: saoco77 — 10 Jul 2008 at 4:39am

If I understand correctly it sounds like you are picking up duplicates due to table joining.

In the section expert try using a formula like this in the supress field to hide the extra rows

{table.IDfield}=previous({table.IDfield})

Hope this helps.

Sarah

Posted By: bgoven — 10 Jul 2008 at 6:27am

You can try adding a group for the field that is duplicating. I do this often with client id's

Posted By: paul_birm — 10 Jul 2008 at 8:25am

thanks for the quick reply guys.

Bgoven - tried that .. nope .. doesnt work - but thanks anyway

saoco77 - i think you are on the right lines - sorry my crystal ver is 7. in Selection Expert WHERE do i put the formula you suggested

Posted By: saoco77 — 10 Jul 2008 at 10:10am

I haven't used CR7 - so I can't say whether or not the feature exists for this version.

In the section expert do you have an option for "Suppress (No-drilldown).

Place the formula in the formula field for this option.

Hope this helps

Sarah

Posted By: paul_birm — 11 Jul 2008 at 2:03am

thanks Sarah

tried that however the report is now showing the vendor only supplying one part to us instead of a list of parts with their vendor - i think this may be down to the way the ERP system (efacs) is supplying the data to the report - the report is solely based on one datafile and links data tables to pull in the required data.

I could do this in another ERP System ( Sanderson PICS) using a 'join' dictionary item but its different in efacs/crystal reports. So after working on this for a couple of days, i have given in and asked our ERP Vendor for a quote to do the work.

but thanks for your help anyway Sarah!

Posted By: paul_birm — 11 Jul 2008 at 3:54am

sorted!

i played around with the field name to 'suppress on duplicate' and by jove it works when you use the serial number to the first data table as the unique suppressor.

many thanks for you help Sarah!

Posted By: jtownsend — 25 Jul 2008 at 8:24am

I am having a similar problem with CR XI. I have a table (jobdwgbm) that i am pullilng seq#, desc, and weights from. i also am pulling "codes" from a second table (jobcutr). when i put the codes field into the report i am getting duplicate records. i am assuming it is from the join. please help!

Posted By: sagecentral — 26 Apr 2011 at 4:40pm

OMG! I could just kiss you on the lips Sarah! I have been trying to solve this issue on very complicated report on sales invoices combined with costing and payroll for years! I used your solution to suppress the duplicating line item id and wah lah! Thank you so much for sharing!

Posted By: Fredbi — 7 Jun 2011 at 10:10am

Sarah, I cannot thank you enough for the solution. The part that makes it so great is after you try it and it works it is just so amazingly simple and logical.

Thanks

Posted By: khyulitz10 — 15 Aug 2011 at 5:05am

Can you please tell me how did you do it because I am having the same problem..Seems like Sarah deleted her post..thank you...

Posted By: Fredbi — 15 Aug 2011 at 6:23am

To suppress duplicate rows because of description variations

Select the field that is duplicated, use the section expert, be sure to choose “details”,insert formula in the Suppress (no drill-down) formula field (X-2)

Insert formula (based on the duplicated field) =previous, second field statement must be in parenthesis!

My sample formula

{PART_RECENT_WORK_ORD.PART_part_no}=previous({PART_RECENT_WORK_ORD.PART_part_no})

Save, and refresh and they are gone!

The action of the formula is if the field you choose is the same as the previous row the complete row is suppressed.

It took a long time to find the answer to this vexing problem, many thanks to Sarah.

Posted By: otsy — 16 Aug 2011 at 8:03am

Sarah,

Do you know how to calculate Sum on the records that you suppress, but without the duplicates?

Nothing that I tried works so far... I get an error that sum can't be calculated on the formula field.

Thank you in advance.

Posted By: sachin12 — 31 Jan 2012 at 12:02pm

Thanks for your valuable suggestions. BUT this doesn't work when page changes.

For example, I have recordset from two tables on the report as below

a 12 address1

address2

address3

b 13 add4

add5

add6

If you see above, it is one to many records which works fine with 'Supress if duplicated' option BUT when data is on two pages, it repeats the main row eg: a 12 address1

address2

PAGE CHAGE

a 12 address3

Can you please how to continue supressing this even on page change? Thanks.

Posted By: tmp_man — 12 Mar 2012 at 9:19am

can someone please help??? I am in the situation like sachin12

thanks

Posted By: DBlank — 12 Mar 2012 at 9:30am

previous() function is not contingent on changing of pages the way the 'suppress if duplicated' option is. You should be able to use the previous() solution to suppress the extra rows. If you are still having trouble please post some sample data and how you wan it to be displayed.

Edited by DBlank - 12 Mar 2012 at 9:31am

Posted By: tmp_man — 12 Mar 2012 at 9:39am

ok here is my example

010019	CA DEPARTMENT OF FISH AND GAME	000270	1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME	000519	1/11/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME	000023	12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME	000208	12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		12/21/2010	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME	000347	1/19/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/19/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/19/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/19/2011	PATTY	PATTY
010019	CA DEPARTMENT OF FISH AND GAME		1/19/2011	PATTY	PATTY

1st row is the customer number
2nd row is the customer name
3rd row is the actual order number

basically I need to have a total of 5 line based on the order number

Posted By: sagecentral — 12 Mar 2012 at 9:43am

HOW TO SUPPRESS DUPLICATE LINES USING SUPPRESSION (ACCESS OR CRYSTALRPTS)

To suppress duplicate rows because of description variations

Select the field that is duplicated, use the section expert, be sure to choose “details”,insert formula in the Suppress (no drill-down) formula field (X-2)

Insert formula (based on the duplicated field) =previous, second field statement must be in parenthesis!

My sample formula
{PART_RECENT_WORK_ORD.PART_part_no}=previous({PART_RECENT_WORK_ORD.PART_part_no})

Save, and refresh and they are gone!

The action of the formula is if the field you choose is the same as the previous row the complete row is suppressed.

It took a long time to find the answer to this vexing problem, many thanks to Sarah.

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3660&PD=0

Posted By: sagecentral — 12 Mar 2012 at 9:46am

and ... you may try to rearrange the order in which your data is read... follow the logic

Posted By: DBlank — 12 Mar 2012 at 9:49am

tmp_man,

if I understand you, in your case I think you want to exclude rows where there is no order_number.

this could be handles by suppression but I think the better solution is to exclude them.

it appears that the rows are null. If that is the case...

in your select expert use

NOT(isnull(table.ordernumber))

or in the suppression option use

isnull(table.ordernumber)

Edited by DBlank - 12 Mar 2012 at 9:50am

Posted By: tmp_man — 12 Mar 2012 at 9:57am

nope it does not work for me.... I end up using the formula from the previous page which is the same formula that you gave me and that is how I end up with the invoice or the 3rd column in my report... even after refresh all the duplicates are still there.

Posted By: DBlank — 12 Mar 2012 at 10:15am

maybe it is not null and just an empty string

try

your select expert to exclude rows use

not (trim(table.ordernumber)="")

or in the suppression option use

trim(table.ordernumber)=""

Posted By: tmp_man — 12 Mar 2012 at 10:30am

all sorted now thanks for all the help... this forum is amazing...

Posted By: Motib — 30 Mar 2013 at 12:09am

Hi Guy's,
I am using CR2008. i have many duplicate values in my report. i remove that values with using the "Suppress if duplicate" from the formatting of the field. but when i create Running total of that field it showing me Suppress values also.....

Is any one here who help me......

Thanks in advance.......

Posted By: pachorkar — 23 Apr 2014 at 6:49pm

Thanks a lot Sarah. This helped me very much.

Posted By: brreid — 15 Dec 2015 at 5:56am

Sarah, thank you very much for this formula, it save me hours of work.

you rock!!

Brenda