x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 4 replies
  • Answers 1 answer
  • Subscribers 28 subscribers
  • Views 870 views
  • Users 0 members are here
Browse Forums
Announcements
19
topics
3
replies
General Discussion
790
topics
1k
replies
Me Iam

Pervasive & Crystal Reports

SUGGESTED
Posted By

I believe our database is corrupted in some way and I am wondering how to fix it.

I am inserting pictures of a report that I have used for years and an excerpt of this year's report.

The rows in blue are correct. There is a royalty of 20%. But then the report intermittently pulls in the same product information with a zero royalty. Last year it duplicated one product. This year it has duplicated two and triplicated (if that is a word) four products. There are 184 lines on this report, most of which are correct so I don't think it is a problem with the report itself.

For this report, the totals are unaffected but it does make us question the integrity of the data! And whether anything is missing or not is undetermined. I don't think so but I can't be sure.

Any help would be GREATLY appreciated!!!!

Thanks, Leanne

  • 0

    Oh, I did try reorganizing BV but that didn't change anything. Thanks again

  • 0
    SUGGESTED

    In Crystal, go to Database, Database Expert... and check your table joins.
    Most likely, one of these joins is causing "Record Inflation".

  • 0 in reply to ixm7

    Thank you! However, I don't think that is the issue. I am inserting a picture of the links - which is a very short list!

    It is also the randomness that has me mystified.  Product number 12401 gets triplicated every time, whereas product number 32401 is only duplicated. Other products with the same royalty amount are shown correctly.

    Everything else is the same - client name, quantity ordered, royalty amount, even the selling price of $9. I also added a date column to confirm that these are referencing the same invoices. And they are. Company F ordered quantity 10 of item 12401 on Sept 20. (I didn't take a picture of that spreadsheet since I got lazy so you won't see dates in the following pictures.) 

     

    I did notice a new "warning" when I looked at it today, after updating the fields:

    But the report generated hasn't changed. Still duplicates and triplicates.

    Thanks again!!!

    Leanne

  • 0 in reply to Me Iam

    Any Inventory record that has two records in Pricing would cause a 2X duplication.
    Any Inventory record that has three records in Pricing would cause a 3X duplication.
    etc.

    Or any History Detail record that joins to 2 History Header records would cause a 2X duplication. 
    etc.

    So check the logic of your joins.
    You can add some unique fields from these tables to identify the source of the duplication.

    hth,      - ido

*Community Hub is the new name for Sage City