Duplicate Record, distinct records doesn't work

[birdprogrammer]

I'm creating a vendor performance report and having problems with duplicate detail line items. The best approach would be to correct the data through linking tables. However, I can't do this because the 2 tables I'm working with only have one link. Let me clarify.

Inventory Receipt Information (table 1)
Receipt# = R0013505
PO Line Item# = 11
Item# = 2197236

Distribution (table 2)
Receipt# = R0013505
Account# = 50000-002

The join between the two tables is by the receipt number. Ultimately, if I had 1 distribution account per receipt, there is NOT any duplicates. But if there is more than 1, it will produce more than one because I can't join on anything but the receipt number.

I've tried using the select distinct records, previous() function but that prompts an error where the data is not populated after the report runs some other function, running total fields but that cannot be used in the selection criteria.

Bottom line, I'm looking for an approach to get rid of the duplicates. I was hoping to add a previous po line# in the detail section (which works) and compare that. Crystal rejects.

Any help is greatly appreciated.

 

[synapsevampire]

This probably isn't a dupe isue, it's row inflation.

Check all of the data returned, and you'll likely see that some field differs.

Nice of you to show the tables, a shame you didn't show what you expected as output.

btw, your example dtaa doesn't show any dupes nor row inflation....

Generally people will GROUP by whatever field they want uniqueness on, suppress the details, and place the fields in the group header or group footer.

Should work for you, if not, show what the tables hold, and what you intend to show as a result. If you show that you just want a few fields, and then later come back and complain that sums aren't working, you didn't take the time to initially show what you have and what you need.

-k

 

[birdprogrammer]

K,

Thanks for ripping on me but you are actually right. Sorry for any confusion. If I review all the fields, it is not duplicate issue. The report is pretty complex and I was hoping there was an easy way out to supress certain records in the detail section based on criteria that isn't available as mentioned above. But with your tip, I believe I will have to modify the report more than hoped and display data either in the group header or gropu footer section.

Thanks.

To be clear on what it looks like now and what I'd like to see...
now:
item# rcpt# poline amt qty ordered
2197236 R0013505 11 $150 50
2197236 R0013505 11 $100 50
would like:
item# rcpt# poline amt qty ordered
2197236 R0013505 11 $150 50

I don't care about the amount. I just care about the qty ordered. This is all in relation to accounting. Hope this clarifies for others that may read this.

 

[synapsevampire]

There is a simpler way, which would be to group on the item#, and then the RCPT, and then the poline, and then in the report->record selection->group place:

{table.amt} = maximum({table.amt},{table.poline})

or you might use:

{table.ordered} = maximum({table.ordered},{table.poline})

Unfortunately this won't eliminate the rows from the report, it will just suppress them from viewing (aggregate functions will still include them).

-k