I am working on a saved item search that will show inventory by bin, sorted by location, but also have a column that shows what the preferred bin is. After multiple versions, I feel like I am close, however the formula I am using to show what bin is preferred returns all preferred bins and does not filter by the location. Is there a way to have the preferred bin being returned, only the preferred bin for the location filtered by?
Here is my current setup:
Criteria: Location On Hand is greater than 0
Formula (Numeric) is greater than 0 CASE WHEN NVL({binonhand.quantityonhand}, 0) > 0 THEN 1 ELSE 0 END - this formula is because I was getting zero bin quantity being returned in the report.
Results: BinOnHand:BinNumber, Name, Display Name, BinOnHand:OnHand, BinOnHand:Location.
Preferred Bin - This is a temporary column so I could see what data it was pulling
Formula (Text) CASE WHEN {preferredbin} = 'T' THEN {binnumber} ELSE NULL END - This formula is returning all of the preferred bin numbers regardless of location. I need it to only show the preferred bin for the location filter.
Available Filters: BinOnHand:Location
I would also love a column that would show the total qty of each item, per location. But I'll be happy with just the preferred bin.
Thanks!