Total On Hand Inv vs Netting Inv By Warehouse/Bin

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 !

Come On In Ryan Phillippe GIF

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. :frowning:

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:
image

image

Here’s example results so far:
image
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:
image

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:
image

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:
image

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:
image

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:
image

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 :slight_smile:

1 Like

David, thanks for your help. This worked out great and was very helpful.

1 Like