Requirement: A user wants to show the Item with Quantity only that will match with Inventory Detail Report. This search should only expose the Item and Quantity Summary Initially but upon clicking on the Item it should expose all the relevant transactions for it.
Solution
Create a Transaction Saved Search by navigating to Reports > Saved Searches > All Saved Searches > New > Transaction
1. On the Criteria tab > Standard sub tab:
- Item: Type | is any of Inventory Item, Assembly/Bill of Materials
- Formula (Numeric) | is not empty | Case when {type} IN (‘Bin Transfer’,’Bin Putaway Worksheet’) then {serialnumberquantity} else (case when {accounttype} = ‘Other Current Asset’ and {posting} = ‘T’ then {serialnumberquantity} else Null end) end
- Transaction Line Type | is not Work in Process
2. On the Results tab > Columns sub tab:
- Created By
- Date Created
- Date
- Type
- Document Number
- Memo (Main)
- Item | Group
- Item: Description
- Quantity
- Transaction Serial/Lot Number Quantity
- Location (no hierarchy)
- Transaction Bin Number
- Transaction Serial/Lot Number
- Formula (Numeric) | Sum | Case when {type} IN (‘Bin Transfer’,’Bin Putaway Worksheet’) then {serialnumberquantity} else (case when {accounttype} = ‘Other Current Asset’ and {posting} = ‘T’ then {serialnumberquantity} else Null end) end
- Transaction Line Type
3. Name the Search
4. Save & Run
Summary Page:
Detail Page:
