How to Use While Reading Records/Evaluate After?

[alexlc]

Hi
I am using Crystal 9 to read an Notes database through ODBC.

The select distinct records option is selected, yet my report is returning duplicate records (even though I can't see them in the database, I am waiting for the DB desigenr to come in).

Can I select distinct records by using the WhileReadingRecord or EvaluateAfter functions? If so where do I place the formula.
This is my current record selection:

{SubShift.PaymentRateCode} in ["NHS Agenda For Change", "NHS Framework Band 3"] and
{BookingSummary.Status} = "Complete" and
{BookingSummary.BookingDate} in Date (2007, 03, 01) to Date (2007, 03, 31)

and I also have a formula for identifying duplicated records:

if Previous ({BookingSummary.AccountsReference}) = {BookingSummary.AccountsReference} then "Duplicate"

I can't select this field in the record selection.

Please can anyone help - I need this data for the NHS by the 15th April.

Cheers Alex

 

[Madawc]

A formula field can't be used for selection if it is based on more than one 'row' or detail line. Selection has to be based on something in a single 'row', though it can be a formula field involving several fields from different tables.

The normal solution is to group on the account number (or whatever else makes it a duplicate). Right-click on the detail line and suppress it. Put a set of detail values in the group header or footer.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[alexlc]

Durr - forgot that.

However I need to sub-total a level above that - this is how I knew the report was wrong, as I have it doing a DistinctCount of {BookingSummary.AccountsReference}, but a Sum of {SubShift.Total Hours} where it came up with 40 hours worked for 2 shifts - oops.

So that's why I need to be able to strip out the duplicate records.

Hate Notes!

Cheers Alex

 

[dgillz]

Please explain your tables and how they are joined. If you are getting duplicates and you cannot see them in your database I am guessing that you have 2 or more tables with a many-to-one relationship.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[alexlc]

I have BookingSummary and SubShift which is further detail on the booking.

I have linked from Summary (one) to Sub-shift (many) via the Summary Unique ID number.
I have just found the Select Distinct on the report option, and this has got rid of the duplicates - can't you tell I'm never in that menu! Previously only set on the general options.

This is an old report that I have made no changes to, other than changing the dates required, which makes me think it is the database.


Thanks for the help anyway.
Alex