I’m working on a query on PartTran and have been asked to get the “Running Total” from the Part Trans History Tracker. However, that’s not a field but calculated. My numbers aren’t matching up so wondering if anyone else has the formula. I couldn’t find it in the Epicor documentation but maybe my brain is just fried.
I made my own version of PartTran History that combines the trans for two part numbers - the current P/N and trans for the “old P/N” the new one replaced.
I’ll take a look in a bit. I do recall that it required a calc field to derive the sign of the Tran Qty based on the TranType.
1 Like
Sorry… I forgot that my custom PartTran dashboard doesn’t have the running sum. 
But for your’s the running sum will need to start with the current QOH, and back-out every part tran from that. I still think you’ll still need to do sign correction to the TranQty. Basically a switch case for each TranType. With the output being +1, -1, or 0. The zeros come from Trans that don’t change the QOH (like CST-ADJ).
1 Like
I don’t blame you, think we’re going to drop that column in the BAQ and just get the current QOH as they don’t want to see non-net bins anyway. /sigh/
Randy,
Did you ever resolve this? I am having the same problem. Trying to recreate the Running Total in a baq. “After” quantity seems to match up with running total value, except when transaction type = STK-CUS. Or MFG-VAR.
Then I seem to get a beginning and ending quantity of 0. Hmmm.
Thanks so much,
Alice
What are you ordering the PartTrans by? Tran Num or TranDate? (note that SystemDate+SysTime should order the same as TranNum).
Do you account for every TranType? Limit to PartTran records with just InventoryTran(?) field?
I am ordering by sys date and sys time. We are accounting for ALL transaction types, not just inventory.
This is the boxed version that we use now on the same part number as my above baq. What is confusing to me, is that the begin qty is defined by Epicor as the on hand quantity before the transaction. So with our STK-CUS transactions, I am unsure why the begin qty would be 0, as we had parts on hand.
I’d add a column to the BAQ to show the “signed TranQty” (TranQty with the sign derieved from the TranType). Make sure your SignedTranQty for STK-CUS isn’t zero.
Thank you Calvin. I will try that.
Do I need to create a calculated field that assigns (1, -1 or 0) to each transaction type or is that value already available to me per Transaction Type in Epicor?
Also, The Beginning Qty doesn’t look right. Shouldn’t it be the RunningTotal from the prior row?
And AfterQty should equal BeginQty + Signed TranQty.
Try ordering by TranNum instead. That will ensure that “simultaneous transaaction” (like the MFG-STK and the associated ADJ-CST) are in the right order.
As a warning, I have found that there are sometimes when an inventory transaction occurs and there is no before or after qty listed. So be careful.
Yes. There are several post on here that give the full list of tran types with the multipler for each.
1 Like
If I were to get the “signed” TranQty programmed correctly, and used (BeginQty + Signed TranQty) it looks as if it would just try to add it to the begin qty, which reads 0 - for these STK-CUS transactions.
1 Like
This is what I am noticing. We had 2,364 on hand before 3/23 STK-CUS transaction and the begin qty reads 0. Hmmmm…
FYI - The Data Dictionary says that the BeginQty
On Hand Quantity before the part costing calculations were run.
SO I’d say that’s only used for part trans that do costing.
Edit
Even stranger…
Issuing material to a job creates a STK-MTL with a positive TranQty, and the Before and After fields are always zero. (Red rectangles in BAQ of PartTRan below)
While Returning material from a job creates a STK-MTL with a negative TranQty, and the Before and After fields have values.(Blue rectangle in BAQ of PartTRan below)
I’d say the Before and After Qty fields can’t be used for running sum.
Thats so funky. I found the baq that Epicor uses in its Part Transaction grid (zES-PartTran) but it does not contain the calculated “Running Total” field. I imagine the grid is a system dashboard but I can’t find it. Thought maybe I could look at the calculation on dashboard level. But alas.
1 Like
Is the end goal to just know the QOH after each PartTran? Or do you need to show changing cost as well?
If it’s just the first, then limit the BAQ to just Trans of the type that affect inventory.
I was thinking that as well. The powers that be are only concerned about inventory. But that leaves me with my issues with the STK-CUS, which doesnt adjust QOH because of emply values got begin and after qtys. Thats the clincher.
Maybe I need to back up a bit. I made a grid to replace the Transaction grid because boss wants to see customer PO number (from OrderHead) so I had to copy original query (Zes_PartTran) and added orderhead. I expected the running total to be calculated at the query level and that I would be able to use Epicor’s existing logic. If there is another way to add a non-related field to the grid collection, I would go that route at this point.