I have a query I’ve been fighting for quite some time with no luck…it’s time to beg for help or just give up.
The materials dept wants a report that will show the total qty of any given material currently in WIP. I’ve managed to get pretty close with one exception. I can account for partial shipments from the job and reduce the material count in WIP when we ship directly from the job. Some of our jobs are not shipped from directly, but are instead moved to stock and shipped from there later. This is where my query breaks, I cannot figure out how to handle the MFG-STK transaction for the top level part and reduce the material WIP quantities accordingly. Adding the PartTran table to my query pretty much destroys the query no matter how/where I attempt to link. I thought about making PartTran a summary table, and creating a calculated field that only totals transactions of MFG-STK, but as mentioned, even if I knew the correct syntax for the calc field, simply adding the part tran table does terrible things to my query.
I’ve tried to link PartTran to the mtl table, jobhead, part…using both the jobnum and partnum to join, but all of my attempts have been in vain.
I don’t expect too much help on this one as it’s probably a rather lengthy fix, but does anyone have anything similar they use? Maybe I’m missing something simple here, but there doesn’t seem to be a simple way to provide this data. The built in function in part tracker only populates WIP data if the operation is currently clocked into to, so that doesn’t work for us, and I really need something that would allow me to account for multiple jobs containing the same material.
Thank you for any input.