Example Use Case: Tracking Inventory with a Saved Search
Recently, I have had many companies ask for support with tracking inventory with NetSuite Saved Searches. Specifically, they want to have a better idea of what inventory they are out of.
Problem: Empty Fields (quantity = 0)
When trying to show the items with 0 inventory, included in a CASE WHEN formula, the results never seemed to appear correctly. This is because when an item has 0 inventory the quantity fields do not say 0, the fields are empty. To fix this, I used the NVL function.
Solution: NVL Function
A company wanted a stock-in, stock-out saved search. Within this search, they wanted to see any item that had inventory but now has no inventory. To capture this I wanted to show Item Fulfillment and when Item’s Quantity on Hand is 0.
I needed a case when the formula in the Criteria shows Item Fulfillment and when the Item’s Quantity On Hand is 0.
TIP: It is useful to first test these formulas as results so that they are calculated correctly.
My Saved Search Criteria are as follows:
The NVL function is laid out as follows:
- NVL({Field ID}, Value you want to replace the null)
- The = 0 at the end is part of the CASE WHEN expression.
The result of this formula replaces any null value in the Item’s Quantity on Hand with 0’s.
Author: Jack Mannebach
Got stuck on a step in this article?
We like to update our blogs and articles to make sure they help resolve any troubleshooting difficulties you are having. Sometimes, there is a related feature to enable or a field to fill out that we miss during the instructions. If this article didn't resolve the issue, please use the chat and let us know so that we can update this article!