Want to identify dupes

[AZdesertdog]

CR10-CE10-MSSQL2k
I have a simple listing of invoices that MUST be sorted by the date field. There is a possiblity of duplicate invoice numbers for different dates and I need to know if these are occuring. My thought is to suppress an asterisk unless a dupe record is identified and then print it next to the invoice number. The problem I have is figuing out my dupes in order to 'star' them.

Desired results: Sorted by Date and asterisks showing any invoices that are in the report more than once regardless of date.

Invoice Date
12345 1/1/04
*12346 1/2/04
12347 1/3/04
12349 1/3/04
*12352 1/4/04
*12346 1/4/04
12407 1/4/04
12432 1/5/04
*12352 1/6/04

Any help appreciated!

 

[fredp1]

I would do this using SQL Expressions.
What you need is to count the invoice number that appears on or before that date. Any invoice/date that has a count gt 1 needs to be excluded. This will give you the 'stars'.
If you can guarantee that you cannot have the same invoice on the same date, then this will work.
If not, we have to find a unique key.
The unique key assumed is Invoice and date.

I'll assume that the invoice table is called tblInvoice and the fields are InvoiceNo and InvoiceDate.
When you insert the table in the report, Crystal assigns an alias to the table, again this will be assumed to be tblInvoice. The alias is used in the sql expression so you may need to change it to suit your name
This sql expression is co-related to the table in the report using the alias.

Create a SQL Expression {%InvoiceRank}

(Select count(t1.InvoiceNo) from tblInvoice t1 where t1.InvoiceNo=tblInvoice.InvoiceNo and t1.InvoiceDate<=tblInvoice.InvoiceDate)

You can now use the expression in the record selection too.
Remember if you only have 2 invoices for on the same date, then it will be exlcuded as the count will be 2. This is why you need a unique key.

Cheers
Fred

 

[Madawc]

If you can't do it in SQL, you could also suppress within Crystal. Right-click on the section and choose Format Section. Then choose the formula icon (x+2 and a pencil) for suppression. Then Invoice<>Previous(Invoice)

You could also use a similar command within a formula field to show an asterisk for duplicates against a full list, if that was what you wanted.

Madawc Williams (East Anglia, UK)

 

[fredp1]

Hi Madawc,
I don't think you can do what your suggesting because the sort is by date then invoice no.
If it was by invoice then date your suggestion would work.


Cheers
Fred

 

[AZdesertdog]

Fred, I understand the creation of the SQL expression but I'm not sure how to implement that into the report to identify the dupes. Could you give me a few more details? Thanks.

 

[fredp1]

oops.. I was just thinking about that.

In your report add a record selection on the sql expression to include only the 'first' copy of the invoice

if {%InvoiceRank} =1 then true else false

Cheers
Fred