Technical Questions
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Technical Questions
Message Icon Topic: Remove Duplicate lines in crystal reports Post Reply Post New Topic
Page  of 3 Next >>
Author Message
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet Topic: Remove Duplicate lines in crystal reports
    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***}
IP IP Logged
saoco77
Senior Member
Senior Member


Joined: 26 Jun 2007
Online Status: Offline
Posts: 104
Quote saoco77 Replybullet 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
IP IP Logged
bgoven
Newbie
Newbie
Avatar

Joined: 09 Jul 2008
Location: United States
Online Status: Offline
Posts: 3
Quote bgoven Replybullet 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'sBig%20smile
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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***}
IP IP Logged
saoco77
Senior Member
Senior Member


Joined: 26 Jun 2007
Online Status: Offline
Posts: 104
Quote saoco77 Replybullet 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

IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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***}
IP IP Logged
paul_birm
Newbie
Newbie
Avatar

Joined: 10 Jul 2008
Online Status: Offline
Posts: 25
Quote paul_birm Replybullet 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! Big%20smile
 
Paul
Birmingham
England
{A friend in need is a pain in the a***}
IP IP Logged
jtownsend
Newbie
Newbie
Avatar

Joined: 25 Jul 2008
Location: United States
Online Status: Offline
Posts: 1
Quote jtownsend Replybullet 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!
IP IP Logged
sagecentral
Newbie
Newbie
Avatar

Joined: 26 Apr 2011
Location: United States
Online Status: Offline
Posts: 3
Quote sagecentral Replybullet 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!
IP IP Logged
Fredbi
Newbie
Newbie
Avatar

Joined: 07 Jun 2011
Location: United States
Online Status: Offline
Posts: 2
Quote Fredbi Replybullet 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!
IP IP Logged
Page  of 3 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.