Scenario
A common reporting requirement in NetSuite is to display multiple price levels and currencies for items in a single Saved Search row.
For example:
Item | Retail CAD | Dealer CAD | Retail USD | Dealer USD |
|---|
Item A | 100 | 90 | 75 | 65 |
Item B | 120 | 110 | 95 | 85 |
The challenge appears when some items have no pricing records at all, including no Base Price. Users often expect those items to still appear in the Saved Search with blank pricing columns.
Users tested both Item Saved Searches and Pricing Saved Searches and found that items without pricing records were excluded from the results entirely.
Solution
This behavior is tied to how NetSuite stores pricing data internally.
Pricing is not stored directly on the item record as flat fields. Instead, pricing exists as a related child record connected to the item through a one-to-many relationship.
Because of this:
- Once pricing fields are included in a Saved Search, NetSuite expects at least one related pricing row.
- If an item has no pricing entries, there is no pricing record for the search to join against.
- The item is removed from the result set before formulas such as NVL() or conditional logic can evaluate.
The same limitation was observed in:
- Item Saved Searches
- Pricing Saved Searches
- SuiteAnalytics Datasets
- SuiteAnalytics Workbooks
Available Workarounds
Approach | Result |
|---|
Add at least a Base Price (even 0.00) | Item becomes searchable in pricing-based searches |
Use grouped pricing formulas | Works only for items that already have pricing rows |
Store pricing values in custom item fields | Allows reporting directly from the item record |
Export and merge externally | Useful for advanced reporting requirements |
Notes
- This is considered a platform limitation rather than a Saved Search configuration issue. The following are the available enhancement requests related to this limitation:
- Enhancement # 120254: Item Saved Search: ability to filter by price
- Enhancement # 69745: Multi-Currency: allow users to select foreign currency price levels in item search results and reports
- Enhancement # 71132: Search/Reporting: ability to display quantity-based pricing and multi-currency in results
- If your reporting requirement depends on seeing all items regardless of pricing setup, consider implementing a pricing governance process to ensure every item receives at least a minimal pricing record.
-----
If you find this information useful, let us know by reacting or commenting on this post!