[rvasquez]

I'm trying to create a search that basically shows items that have 0 quantity in one location, but have quantities in another location.

So let's say you wanted to see all the items that have 0 inventory in the showroom but have inventory in the warehouse. If the items is in stock in the showroom, or there are none in the warehouse, don't show it. Someone could then use this list to move things from the warehouse to the showroom.

What I started with is a saved search for items that have (location = showroom AND quantity = 0), OR (location = warehouse AND quantity > 0), using the bin on hand field

That sort of works, but a location can have something in the warehouse and show up on the list even if the showroom has a quantity. And it can show up with the showroom quantity being 0 but the warehouse is also zero (it just doesn't show those lines)

Item: Location: Quantity:

TIRE Warehouse 1

TIRE Showroom 0

WHEEL Warehouse 1

DOOR Showroom 0

In the above results, TIRE is the only one I would want on the list. There are quantities of WHEEL in the showroom (so it doesn't show up on the list) so I don't want to see the warehouse quantities) and there are 0 DOOR in the showroom, but I don't want to see that one as there is nothing in the warehouse.

I tried to use an AND instead of an OR but it doesn't do what I want because an item in a search result only has one location
This is a cached copy. Click here to see the original post.

[khultquist]

You have two criteria, which gives you four permutations:

Warehouse = 0, Showroom = 0

Warehouse > 0, Showroom = 0

Warehouse = 0, Showroom > 0

Warehouse > 0, Showroom > 0

But I have to say I'm a little confused about which one(s) you're looking for, since these are conflicting statements

0 inventory in the showroom but have inventory in the warehouse

(location = showroom AND quantity > 0), OR (location = warehouse AND quantity = 0)

[rvasquez]

Originally posted by khultquist

View Post

You have two criteria, which gives you four permutations:

Warehouse = 0, Showroom = 0

Warehouse > 0, Showroom = 0

Warehouse = 0, Showroom > 0

Warehouse > 0, Showroom > 0

But I have to say I'm a little confused about which one(s) you're looking for, since these are conflicting statements

Sorry – I had those backwards. It should be

(location = showroom AND quantity = 0), OR (location = warehouse AND quantity > 0)

[khultquist]

If you only have two locations, you can do this with 3 criteria lines, making use of 'Inventory Location' and 'Location On Hand'

[rvasquez]

Actually we have 3 showroom locations and 3 warehouse locations

[khultquist]

With multiple locations, you can probably accomplish what you are trying by making it a Summary Search and using Summary Criteria, and some SQL formulas.

[Author]

Posts