Table Joins 1

[btamulis]

I am linking two tables (Crystal X) - Table A is a Bill of Material Table - it has multiple records for a distinct item. Table B is a Qty on hand table - it also has multiple records for a distinct item number.

Example:

Table A
PPN ITEM NUMBER
Widget Gadget
Test Gadget

Table B

ITEM NUMBER LOCATION QTY
Gadget NORTH 20
Gadget WAREHOUSE 10

The two tables are linked by Item Number from Table A to Table B

Current Results (in detail section of report)

Gadget NORTH 20
Gadget NORTH 20
Gadget WAREHOUSE 10
Gadget WAREHOUSE 10

Desired Results:
Gadget NORTH 20
Gadget WAREHOUSE 10

Since there are two records in Table A for Gadget - the report duplicates and prints two records for every individual record in Table B.

If only one record for item number was in Table A then report prints fine. If three records in Table A then 3 records for every individual record prints..

I have attempted all types of joins and enforcements - no luck......

Any assistance would be appreciated.





Bron Tamulis, CPIM
Great Plains Mfg Consultant

 

[lbass]

You could try going to database->select distinct records. Or try concatenating the three fields and then insert a group on the formula:

{tableB.ITEM NUMBER}+{tableB.LOCATION}+totext({tableB.QTY},0,"")

Then place the detail fields in the group header or footer instead of the groupname.

Of course, I'm assuming you need TableA for some part of the report, although you don't show it being used.

-LB

 

[btamulis]

Worked like a champ - the select distinct records was grayed out and not an available option. The concenated formula and grouping suggestion works perfectly.

Thank you very much.

Bron Tamulis, CPIM
Great Plains Mfg Consultant