Create an Item Search showing items with Bin On Hand Available less than or equal to a certain number in one Location but greater than or equal to a certain number in another Location

To create an Item Search showing items with Bin On Hand Available less than or equal to a certain number in one Location but greater than or equal to a certain number in another Location:

1. Navigate to Reports > New Search.
2. Click on Item link.
3. Click on Create Saved Search button.
4. In the Saved Item Search screen, navigate to Criteria tab > Standard subtab and add this filter field:

Filter Field = Formula (Numeric)
Formula = CASE WHEN ({binnumber.location}='Location 1' AND {binonhandavail} between 0 and 10) OR ({binnumber.location}='Location 2' AND {binonhandavail} >= 0) THEN 1 ELSE 0 END
Description = equal to 1

5. Navigate to Criteria tab > Summary subtab and the following:
Summary Type = Maximum
Field = Formula (Numeric).
Formula = Case when MAX(Case when {binnumber.location}='Location 1' then {binonhandavail} end) < MAX(Case when {binnumber.location}='Location 2' then {binonhandavail} end) then 1 else 0 end

6. Navigate to Results tab > Columns subtab, add the following column fields:
---Field = Name
Summary Type = Group
---Field = Formula (Numeric)
Formula = Case when {binnumber.location}='Location 1' then {binonhandavail} else 0 end
Summary Type = Maximum
---Field = Formula (Numeric)
Formula = Case when {binnumber.location}='Location 2' then {binonhandavail} else 0 end
Summary Type = Maximum
 
Note:
1. Type in the name of the Location in 'Location 1' and 'Location 2' in the formula.
2. Change "between 0 and 10" and ">= 0" in the formula to desired range and number.
3. {binnumber.location} in the formula may different to other accounts if customer renamed, for example, the Location field to Warehouse field. If that is the case, then it should be {binnumber.warehouse}