I am trying to do a BAQ and find or calculate the total on hand inventory. Not the inventory by warehouse/bin. Does anyone know if there is a “pre” calculated field for this or do I have to net the warehouse/bin qty’s to get the total qty? I have searched around a bit but seem to not be finding what I need. Thanks
1 Like
partbin.OnhandQty is my usual go-to.
But yes, you’ll still have to create a sum field because it could split it out if the part is in multiple warehouses, multiple bins, or if you’re tracking by lots, etc.
3 Likes
I had the same requirement a while back and couldn’t find anything, ended up calculating from the warehouse/bin totals. If you’re multi-site, you may need to check per site as well.
I’m not an expert though and didn’t spend long on it, so will follow this thread as will be interested to hear what others have done.
EDIT: Re multi-site, @dcamlin solution of the partbin table includes all sites by the looks of it.
1 Like
This is the safest play. PartBin is almost always correct. PartWhse and PartQy are a bit of a roll of the dice.
4 Likes
Welcome @barryd !
1 Like
Thanks for the feedback. It is very appreciated. I didn’t want to reinvent the wheel if it was not needed.
1 Like
Okay, this is not going to well. lol… I am somewhat of a novice on building calculated BAQ’s. I have been trying to net the different warehouse inventory values to get a net total with no luck. Does anyone have any syntax examples of how to net the warehouse values for the same item number into one net value?
Can you show us your query so far?
We’ll get you where you need to go!
I deleted it in frustration. 
I have two warehouses. The warehouses are CLPR and PLPR. The bin to grab the count in is PR in both warehouses. It is a simple setup but I am a novice when doing something like this. I am looking to get the part number and a net value as an outcome.
I have selected the PartBin table and then selected the PartNum, WarehouseCode, BinNum, and OnhandQty fields.
Here’s a real quick/dirty example:
Here’s example results so far:
Notice I’m seeing two different parts, but a LOT of different records. These only appear in (1) Warehouse, but they show up in multiple bins, and we also lot track these and you’ll get a row based on lot number too… so many rows in the results.
But we can sum those…
Add a Calc Field:
The “isnull” portion isn’t required, but depending on what you’re doing, sometimes null values can screw with you… so, I tend to add that in there so any part with zero quantity doesn’t come back as “null”… it’ll come back as a zero (0) value.
If you click the “Check Syntax” button on that calc field, you’ll get an error like this one:
There’s nothing wrong with the calculated field syntax… this is evaluating the entire query. In this case, we’re adding an aggregate column, so we need to “group” the rest of the query.
So, back on your display columns, add “Group By” to everything except your calculated field.
Now test your query:
I’m still getting all those rows!
This is because I’m asking the query to show those columns, like Lot Number.
If I remove the Lot Number column and re-test… I now get this:
Note the first part in Bin A-106 shows on-hand (12) but TotalOnHand (96). It’s still splitting out the A-003 and A-106 bins. I have a total of 59 in Bin A-003 (regardless of lot number) and 96 in Bin A-106 (regardless of lot number).
So, let’s shave out more columns…I just want the Part Number and TotalOnHand:
Since you have two Warehouses… you’ll need to decide if you want the totals broken out by Warehouse… if so, add that column back in and you’ll get a total in each. If not, take it out and it will sum everything together.
5 Likes
Thank you, I will give this a try and report back. I am about to go into a meeting but will let you know in a couple hrs. Thanks again 
1 Like
David, thanks for your help. This worked out great and was very helpful.
1 Like
Hi Chad, I’m interested in this - I’ve found a couple of reports based on PartWhse and PartQty looks like a useful table. Is there a known issue with these not being accurate, or is personal experience with your clients?
For a very long time, yes. I don’t know the details on what causes the drift, but my assumption is incomplete transactions for some reason not updating these tables even though they do correctly update PartBin. Some people notice it, some do not. Some people experience the drift on an almost daily basis. Relatively speaking, it is a rare occurrence, but it happens often enough that I never use these tables for BAQ’s anymore. PartBin is almost always correct and so when I want an on hand number, I always use that.
It’s enough of an issue that Epicor now provides a utility on the menu to resolve it. The Refresh Part Quantities and Allocations synchs the PartWhse and PartQty tables among a few others.
3 Likes
Thanks very much, don’t think its an issue we’ve experienced but good to know. 