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.
The MFG-STK really shouldn’t need to be handled much differently that an MFG-CUS transaction.
Make sure you look at this with the JobNum being the key piec of info that you need to relate the PartTran records.
It wiil be much easier if you are allowed to group it by Job. Else you’ll need to have multiple levels of the BAQ.
The lowest being the calculation of what materials are left in the WIP for the specific Job. This is basically the JobMtl.QtyRequired - RequiredPer x qty Shipped (and/or Received to Stock)
The next level needs to sum up the “left in WIP” amounts across all jobs.
I made an Inner Sub Query that sums up the Qty’s of the parents leaving WIP. Basically, the MFG-STK and MFG-CUS trans
The display fields are:
With the calc field being:
The Top Level query is:
The table criteria for JobMtl_Net was just to limit the data returned for testing. You might want to include the criteria: IssuedQty <> 0 no reason to do math on those records.
The calc field is misnamed (the label is more accurate)
Here’s the result (you’ll obviously have to add on to it to get it to be by part)
Note the highlighted line. Mtl P/N TX-0019 required 141, but 170 was issued. and since 1 of the parent (column “FromWIP”) shows one was shipped, it calcs that there is 29 left in WIP. Becuse 170 went in and 141 x 1 came out.
Whoa, thanks! I surely didn’t intend to create that much work on your part. I’ll be honest, it’s tough for this guy to follow the SQL/E10 format, but I think I’m on to something that will work based on your PartTran filter and join based on the jobnum. Many thanks, your help is greatly appreciated!!
