Replacing Null Values in a NetSuite Saved Search

When working with NetSuite Saved Searches, one common frustration is dealing with blank or null values in your results, especially when you're reporting on inventory quantities, financial data, or custom fields. These empty cells can skew your data interpretation or lead to miscommunication with stakeholders. Fortunately, NetSuite provides a straightforward way to address this using formula fields. In this article, we’ll explore a practical method for replacing null values with more useful default values, helping you maintain clean and actionable reports.

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.

Conclusion

Replacing null values in a saved search using a formula field is a simple yet powerful way to enhance data visibility and consistency. Whether you're prepping a report for a colleague or automating a dashboard feed, having clean data is key. This quick fix using a CASE WHEN statement ensures your searches return meaningful values every time—no more surprises with blank rows. As always, the more refined your saved searches are, the more valuable your insights will be.

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!