How do I join the PartCosts table to the InvcDtl table to get a part’s many different costs for a report.
I know the InvcDtl record contains one set of cost data for each part. But I would like to get Std, Avg, Last costs, etc. from the Part Costs table on occassion for comparison on reports.
The PartCosts table is indexed by Company,PartNum,CostID. InvcDtl has Company,PartNum. I am trying to find a crosstable but have not been successful so far.
Sincere thanks.
If you query the PartCost table, do you find any CostID’s… other than 1 (the default)?
If all CostIDs are 1, you should be able to link on just the Company and PartNum fields.
Else if you see multiple CostIDs, might want to look at your Plant Cost setup.
Ref:
Production Mgmt --> Engineering --> Setup --> Plant Cost
PlantCost.PlantCostID: Default ID of Cost Set Group (CostGrp).
Material Mgmt --> Inventory Mgmt --> General Operations --> Cost Adjustment
PartCost.CostID: Included in the unique index this value allows the Part to have different costs in different plants. Default value for CostID = 1.
I haven’t checked the indexes for performance, and haven’t thought through if this is the best thing to do, but it will get you the data. You can join InvcDtl to InvcHead, then InvcHead to Plant. Then when you join InvcDtl to PartCost on Company and PartNum, you can add a criteria to PartCost that specifies CostID = Plant.PlantCostID.
