Scenario
A common warehouse reporting requirement is to create an Item Saved Search that displays:
- Inventory by Bin
- Inventory by Location
- Preferred Bin per Location
- On Hand Quantity by Bin
The challenge occurs when an item has multiple preferred bins across different locations. In many cases, a formula such as:
CASE WHEN {preferredbin} = 'T' THEN {binnumber} ELSE NULL END
returns all preferred bins for the item instead of only the preferred bin tied to the selected inventory location.
This can create duplicate rows and make warehouse reporting difficult to interpret.
Solution
The issue can be resolved by adjusting the formula so the Preferred Bin is matched specifically against the Bin On Hand Location.
Here is a formula that can be added in a Formula (Numeric) field in the Criteria:
CASE WHEN {inventorylocation} = {binonhand.location}
AND ({binnumber.location} IS NULL
OR {inventorylocation} = {binnumber.location})
THEN 1 END
This formula ensures the Saved Search only returns bins associated with the correct inventory location.
How it works
- {inventorylocation} = {binonhand.location}
Ensures the Bin On Hand record belongs to the selected inventory location. - {binnumber.location} IS NULL
Allows items without a preferred bin location to still appear in the results. - {inventorylocation} = {binnumber.location}
Ensures the preferred bin belongs to the same inventory location.
Without this logic, NetSuite may return preferred bins from other locations, creating duplicate or incorrect rows in the Saved Search.
The formula returns 1 only for valid location-matching rows, which can then be filtered in the search criteria using: Formula (Numeric) = 1.
-----
If you find this information useful, let us know by reacting or commenting on this post!