Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Find duplicates entries in multiple fields Post Reply Post New Topic
Author Message
onitapgr
Newbie
Newbie


Joined: 25 Mar 2010
Location: United States
Online Status: Offline
Posts: 3
Quote onitapgr Replybullet Topic: Find duplicates entries in multiple fields
     Posted: 25 Mar 2010 at 5:30am
Hello all.  I'm looking for a way to return a value if there is duplicate records in multiple tables....for example, if I have this report:
 
DATE       AMOUNT         CARD_NUMBER
1/1/10     300.00         XXX8579
1/3/10     250.00         XXX5555
1/3/10     250.00         XXX5555
1/6/10     250.00         XXX8646
1/7/10     300.00         XXX8579
 
I want to add another column that tells me if a credit card charge has been duplicated.
 
DATE       AMOUNT         CARD_NUMBER   DUPLICATE?
1/1/10     300.00         XXX8579       NO
1/3/10     250.00         XXX5555       YES
1/3/10     250.00         XXX5555       YES
1/6/10     250.00         XXX8646       NO
1/7/10     300.00         XXX8579       NO
 
Notice that duplicate charges have the same card number, the same amount, AND the same date.
 
Any help would be greatly appreciated...Thanks!
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 25 Mar 2010 at 5:42am
The only way I can think of doing this is first sort by the CARD_NUMBER then use a formula to check for values on next record and previous record.  It would take a bit of thought to work this out.
 
I hope this helps.
IP IP Logged
onitapgr
Newbie
Newbie


Joined: 25 Mar 2010
Location: United States
Online Status: Offline
Posts: 3
Quote onitapgr Replybullet Posted: 25 Mar 2010 at 6:44am

Yeah, I follow your logic, I just don't know how to write it in code...also, I need to look at the date and the amount, too.

I imagine its something like:
 
For current row:
if count(card_number) > 1 AND count(date) > 1 AND count(amount) > 1
 
Obviously that's totally the wrong code, but the logic is something like that.
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 25 Mar 2010 at 8:07am
I was thinking along the lines of the next and previous functions
 
Something like this.
if {CARD_NUMBER} = next({CARD_NUMBER} or {CARD_NUMBER} = previous({CARD_NUMBER}) then "YES" else "NO"
 
I have not checked this code to make sure it will work correctly in all situations.
IP IP Logged
onitapgr
Newbie
Newbie


Joined: 25 Mar 2010
Location: United States
Online Status: Offline
Posts: 3
Quote onitapgr Replybullet Posted: 25 Mar 2010 at 8:44am

Thanks for your suggestion.  This code works:

if ({@CREDIT CARD NUMBER} = next({@CREDIT CARD NUMBER})
or
{@CREDIT CARD NUMBER} = previous({@CREDIT CARD NUMBER}))
and
({RECEIVABLE.INVOICE_DATE} = next({RECEIVABLE.INVOICE_DATE})
or
{RECEIVABLE.INVOICE_DATE} = previous({RECEIVABLE.INVOICE_DATE})) and
({RECEIVABLE.TOTAL_AMOUNT} = next({RECEIVABLE.TOTAL_AMOUNT})
or {RECEIVABLE.TOTAL_AMOUNT} = previous({RECEIVABLE.TOTAL_AMOUNT}))
then
"YES"
else
"NO"
 
However, it does require that duplicate charges show up next to each other......but for my purpose it works, so thanks!
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | BAEZL

© 2004-2026 Brian Bischof. All Rights Reserved.