We are working on a workflow to set a custom field on an item to a value based upon the quantity available for one of our locations. The workflows are ready to go (one to set the field to true and one to set the field to false) by testing with a saved search we have written.
What we are struggling with now is to properly write the saved searches. We need to be able to exclude several bins (picked bins, receiving bin, etc.) from the quantity available for the location. We are not having much luck with that.
This is an item saved search we are using. Currently on the criteria, we are using:
Sub-Item Of (since we are looking for child quantities of certain items)
Custom field (true or false depending on which search we are running for the workflow)
Location Available (greater than or equal to 5)
Inventory Location (location to be searched)
Using this, though, it is only taking into account the inventory location quantity. We need to know how to get the total quantity available for the location that is not sitting in the certain bins we need to exclude.
We have tried using a numeric formula on the criteria:
case when {binonhand.binnumber} IN ('BIN1','BIN2') then 0 else {binonhand.quantityonhand} end
Description (not equal to 0)
That did not help us at all. It is only taking into account the location available field on the results. We tested this with one child item. That item currently has a total of 6 available in the location. Of those 6, two of them are in bins that need to be excluded. When running the search, though, it still showed that item on the search results because there are 6 in the location even though only 4 of them are in eligible bins.
What can we do to get these bins excluded so the search only shows results of items with a quantity of 5 or more for the location as long as the quantity is not sitting in any specified bin. We've not been successful in finding anything that would properly exclude certain bins from the location total as of yet. Any help would be greatly appreciate.