We have a BOM BAQ that uses CTE and builds the BOM properly. I now have to add the last cost I have for the part. In a separate BAQ I have a union between data pulling from POs and the vendor part history. Both queries independently work fine. I am having trouble putting the 2 together to get the cost per part line in the BOM.
The original BOM has a join to the Part table to pull data for each part and that works fine. When I try to join in the subquery for the cost I get all of the data from the cost query, not just the matching part data even though they are joined by the part. I think it may be caused by the union in the cost subquery but I don’t know how to structure the queries to achieve the desired results.
I’m not sure what to include in this to provide more information. Here is a snapshot of the query list
The system keeps every parts standard, last, and average cost regardless of your Costing Method. The costs can be seen in Part Tracker and I believe they are stored in PartCost table.
I believe this will not work for us. A quick search indicates that the cost data only gets set when parts move through inventory. I know it may sound weird, but we don’t actually stock parts in inventory - they are purchased direct for a project and so nothing moves through inventory. When I look at a part the costs are all 0 but I can see the POs where they have been purchased.
This puts me back to figuring out a way to get the cost data on my BOM within my current BAQ.
