Scenario
User would like to create a Saved Search that shows Sales Orders containing only two specific items. The saved search results should return Sales Orders with the following items:
- Item A
- Item B
- Item A and Item B
If the Sales Order contains different items than those mentioned above, or, if those items are in the Sales Order with other items, then it should not be shown in the results.
Solution
1. Under the Criteria subtab > Standard sublist, add the following fields as filters:
- Type = is Sales Order
- Tax Line = is false
- COGS Line = is false
- Shipping Line = is false
- Main Line = is false
2. Under the Criteria subtab > Summary sublist, enter the following:
- Summary Type = Sum
- Field = Formula (Numeric)
- Formula = CASE WHEN {item} IN (‘Item A’, ‘Item B’) THEN 0 ELSE 1 END
- Formula (Numeric) = equal to
- Value = 0
Note: In the formula, replace Item A and Item B with your actual item name.
4. In the Results subtab > Columns sublist, enter the following fields:
- Document Number (Summary Type = Group)
- Formula (Text) (Summary Type = Maximum)Formula = NS_CONCAT({item})
Note: You may add other necessary fields that you prefer.
This saved search will return summary results of Sales Orders that contain only Item A, Item B, or both.