Populate a column in a BAQ with a static value based on a dynamic value

Epicor ERP 10
, ,
1

I hope that title makes sense!

So im creating a BAQ for our raw material handlers so they dont have to use a manual excel sheet anymore. For the most part its a pretty simple baq creation. But my big roadblock comes from trying to keep the On-hand qty static for the current month. In other words, they want to see our months starting amount while also seeing the current dynamic On-hand Qty. Is this possible?

I assume there’s someway to “print” the value of a field into a calculated cell but im not finding that command online. i tried to search for “dynamic to static field” (and other wording) but all i get revolves around website design.

Attached is a snap shot of our current manual sheet. The first Inventory field never changes as its our months starting amount. the Inventory column on the far right changes when the warehouse manager moves raw materials to whatever department requests it. is there a way to do this in a BAQ?

image
image1035×121 4.01 KB

2

This inventory info seems to be for each part? Why not have a UD field added to the Part table where you could set this value in part maintenance (or via DMT to start with… )

Then use this field in your query.

Pierre

3

i did have that idea but I, or someone, would need to do this every month which obviously isnt hard. But i want to remove the human side of this so its impossible to forget to update it.

i guess i can make a BPM that every new month itll “print” the On-hand qty to my new part field but at that point id rather use this “print” command to update the field in the baq.

4

Oh …ok, I understood it was static and always of same value…

It all depends on what basis this value is generated?

Is it the last inventory value of the day before the first of the month? if so, you could get the value from the parttran table maybe?

Pierre

5

I have a nightly sql job that looks at the fiscal period table to check if today is the end of the period. If it is then it populates UDxx with the On Hand value. This started in E9 and I have not moved it to a more E10 worthy solution like calling an UBAQ from a script triggered from task scheduler in windows which is probably the best approach to this…

6

Another way to do this: You could filter the PartTran table to return all records for the current month (TranDate >= Constants.FirstDayOfMonth) and group and sum according to transaction type.

Then subtract out the transaction totals that added to stock ("-STK"), and add in any transactions that removed from stock ("STK-"), along with quantity adjustments (ADJ-QTY). This will let you back-calculate what you had in stock at the beginning of the month, without needing to collect a snapshot every month.