The NVL Function

Sometimes when creating a Saved Search, you will need to get a value from one field if it’s not empty and from a second field if the first is empty. NetSuite has a great SQL function you can use in any Formula field to do just this (which works on both search criteria and search results). Simply add a Formula field in your Criteria or Results tab and use the formula NVL ({my_first_choice}, {my_second_choice_if_null}). What’s in those {} is the field ID—NetSuite will fill those in for you if you use the UI available and select the field you’re looking for from the “Field” select list. Using this method lets you get one value or another in your Saved Search results and criteria. If you’re looking for a third choice, check out NVL2 in the NetSuite Help Docs.

As a side, the most common use for this function is when you are working with a field that may be blank and you want to fill in a 0 if it is. So in a quantity field, you might have NVL({quantitycommitted}, 0).

Use the NVL function in a Formula search field to retrieve a second value if the first is blank.

How to insert the NVL function into your formula; this box appears automatically when using a formula in your criteria.

This works for Saved Search Criteria or Results Fields.

Conclusion

We hope this tip has been helpful; to receive more NetSuite tips directly in your inbox each week, subscribe to our mailing list below!