What’s the best way to setup a BAQ to pull all quantities on open orders of a material of a material of a parent part?
What kind of order? Sales Order, Job? Purchase Order? If you want all “Demands” (Requirements) no matter where they are coming from, then you can query the PartDtl table which will show SO, PO & Job & Transfer Order Demands. You will need to filter out the SUPPLY orders becuase they are there as well.
Sales Orders
I need a 2nd level materials list part number to show only
to see the second level, you will need to either explode the bom, or look at the jobs that are created for the sales order. This starts getting tricky because of revision control, Make Direct jobs, etc. there are another 1/2 dozen questions to ask before getting to a final answer, so this is not necessarily easy.
but assuming you are going to use a BOM, you would create a query looking at the Sales order Detail table, joined to the PartMtl table using the OrderDtl.PartNum and ORderDtl.RevisionNum fields to join to the PartMtl records… then get the Child part number from there. You will also need to do some math for the qty on the BOM with the Qty on the order.
1 Like