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