PO (Purchase orders)

POLINE (Individual lines of purchase items for PO)

PR (Purchase Requisition)

PRLINE (Individual lines of purchase items for PR)

 

A Purchase Requisition is created first, which lists the individual purchase items. This has a specific PR #.

 

Once the PR is created, a PO is then created from the PR, which is related by the PO_Number in the POLINE and PRLINE tables.

 

If I create a PO report and place both the PO_Number and PR_Number on the same report, it triples the purchased line items. I've tried changing the link options (Inner Join, Left Outer, Right Outer) but have not had success.

 

Any ideas? Thanks.

the extra rows that appear is based on the 'enforce' issue in crystal. In most systems, when you make a join it is automatically enforced, meaning that the join will impact the returned rows with no further action. In Crystal, joining tables does NOT do this unless you specifically tell it to (enforce options). Otherwise the enforce happens when you use a field from a joined table. (Note: You can use the field in a select statement or in a formula, not just when you drag and drop it onto the canvas). So when you only had a field from PO being used it shows the one row (only one row per PO in the PO table exists). WHen you add the PR_Number field it is from another table that has 3 rows per PO and it enforces the join and makes the extra rows come into the report.

Does that help?

You can try and change the report option to make the data selection only find unique rows

FILE >Report Options

'Select Distinct Records' as True