I have a BAQ that I wish to add Max(stk-cus) transaction dates on. I already have a column with Max(Mfg-Stk) date. I am having trouble getting both columns to display data. Ive started with a fresh BAQ to test. I have a top level using JobHead and two PartTran subquery’s. One subquery has table criteria = mfg-stk and the other table criteria stk-cus. Both use a Max calculated field to pull a single highest dated transaction.
Admittedly I am not an expert at linking multiple subquerys. Any help would be appreciated.
I don’t think PartTran will have any records where a STK-CUS transaction also includes a JobNum.
I would try NOT linking PartTran to JobHead… the STK-CUS side of your query (I would assume) will always be empty. STK-CUS has nothing to do with Jobs.
If you’re FIFO, you may be able to use the generated FIFODate & FIFOSeq when a MFG-STK transaction takes place and marry that up to another query that looks for STK-CUS transactions that include that same FIFODate & FIFOSeq.
Yeah if you don’t have FIFO dates this is going to be a tough one. If you need Jobs linked to Shipments, your shop should probably be shipping Jobs rather than receiving jobs to stock > shipping from stock. Unless you have Serial numbers or something, Epicor won’t track which specific item in stock (from which job) the stock comes from on a Customer Shipment.
Thanks guys that was absolutely it. Confirmed via part transaction history tracker stk-cus transaction have no job# link. Logically this makes sense. Simply linking stk-cus sub assembly off an order table and mfg-stk off a job table did the trick.
Kind of a “well duh” moment no that it was pointed out!
Normally the lotnum in inventory is the jobnum, so I use that to get from invoices, to shipments, job, job material, issues from stock, receiving for tariffs
