Does anyone know of a way to make a criteria for inventory as of a certain date for a BAQ? I’m trying to get my BAQ to report inventory up until the end of last month.
Right now I’m using the PartCost, Part, PartQty, and PartWhse tables but there are no options for dates that I can use for a criteria that I know of. The TranDate field in the PartTran table would work but if I include the PartTran table then it drills down to the transaction level and I just want the report at the part level (much like the Stock Status Report).
The Stock Status report is similar to what I need but my report includes all sites and companies and has additional information not provided in the Stock Status Report. Any possible help would be much appreciated.
If you’re looking for something like the SSR (Stock Status Report), which lets you enter a date to determine the stock levels, you’ll have to query the PartTran table and subtract out the transactions that have happened since the desired date.
For example, if PartBin says you have 100 EA on hand of a part, but you want to know what the QOH was on 1/31/2019.
If there are Part transactions after 1/31/2019, they need to be subtracted from the current QOH.
If you don’t use Std Costing, you’ll have a whole other issue with what the part cost was on that date. You’d have to do another query of the PartTran, to find the last transaction on or before 1/31/2019, and use that as the part cost.
Thanks for the reply and sorry for the delayed response. I’ve been working crazy hours writing queries and doing accounting duties for the first month end close.
I’m not sure what you mean by subtract out transaction that have happened since the desired date. Is there a way I can have this done automatically with a calculated field or would I have to manually adjust the query to subtract out prior and after month end transactions from the last QOH before 1/31/19?
Thankfully, we use std costing so part cost will not be an issue. Wish there was a way to just pull the ‘Running Total’ field from part transactions. Thanks again for your help.
The stock status report should work as is. You enter a date and it gives you the QOH and value of every part, for that date.
E10 doesnt track daily balances, only the current QOH. So if you want to know the QOH for a given prior date, you need to take the current QOH, and “undo” any transactions since that date.
For example:
Date Action QOH
1/1/19 Part created 0
1/2/19 PO Rcpt of 10 10
1/3/19 Ship 5 5
1/5/19 PO Rcpt 10 15
QOH on 1/6 is 15. To find the Inv value on 1/3, take the current QOH (15), subtract recpts since 1/3(10), add shipments since 1/3(0, because 1/3 trans aren’t included).
This yields a 1/3 QOH of 5. The SSR report does this for you!
Edit: i struck the “and value” form the SSR report, because it doesn’t account for any change in cost between the date entered and today.