Remove Duplicate lines in crystal reports
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=3660
Printed Date: 25 Apr 2024 at 8:31pm
Topic: Remove Duplicate lines in crystal reports
Posted By: paul_birm
Subject: Remove Duplicate lines in crystal reports
Date Posted: 10 Jul 2008 at 2:30am
|
hi
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!! ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Replies:
Posted By: saoco77
Date Posted: 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
Date Posted: 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
Date Posted: 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
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: saoco77
Date Posted: 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
Date Posted: 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! ------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: paul_birm
Date Posted: 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!
------------- Paul Birmingham England {A friend in need is a pain in the a***} |
Posted By: jtownsend
Date Posted: 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
Date Posted: 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! ------------- Nothing insures success more than a good start! |
Posted By: Fredbi
Date Posted: 07 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 ------------- You can not know everything but you need to know how to find out! |
Posted By: khyulitz10
Date Posted: 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
Date Posted: 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! ------------- You can not know everything but you need to know how to find out! |
Posted By: otsy
Date Posted: 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
Date Posted: 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
Date Posted: 12 Mar 2012 at 9:19am
|
can someone please help??? I am in the situation like sachin12 thanks |
Posted By: DBlank
Date Posted: 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. |
Posted By: tmp_man
Date Posted: 12 Mar 2012 at 9:39am
ok here is my example
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
Date Posted: 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 ------------- Nothing insures success more than a good start! |
Posted By: sagecentral
Date Posted: 12 Mar 2012 at 9:46am
|
and ... you may try to rearrange the order in which your data is read... follow the logic
------------- Nothing insures success more than a good start! |
Posted By: DBlank
Date Posted: 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)
|
Posted By: tmp_man
Date Posted: 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
Date Posted: 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
Date Posted: 12 Mar 2012 at 10:30am
| all sorted now thanks for all the help... this forum is amazing... |
Posted By: Motib
Date Posted: 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
Date Posted: 23 Apr 2014 at 6:49pm
Thanks a lot Sarah. This helped me very much. 
|
Posted By: brreid
Date Posted: 15 Dec 2015 at 5:56am
|
Sarah, thank you very much for this formula, it save me hours of work.
you rock!! Brenda ------------- Thanks for sharing the knowledge |