Ever had this scenario?

You run a saved search, but can’t prove out the data with detailed summaries because the formulas aren’t adding correctly. The correct data is there The bottom line numbers are correct….

What can you do to fix this issue?

Example:

I want to know what I still have outstanding for sales orders that are waiting for committed inventory so I create a search like this.

This reads show me the sum of the Quantity of all items on the SO,  Quantity Committed,  Quantity if any that have been fulfilled already and a formula to show what is still pending or uncommitted.  pretty simple right?

Then we get this??

These numbers obviously don’t make any sense.  WHY?  Well the good news is the blanks clearly show you that you have an issue because there is nothing in the  Pending column.  The bad news,  the seeming random numbers in the other calculations.   It would be easy to miss the error here if the numbers we not so exaggerated. 

WHAT IS  PROBLEM AND HOW DO WE FIX IT. 

NVL to the rescue!

These Blank values are in data terms a NULL value and they can become a problem when you are trying to get Calculations or counts of your data because a NULL value causes your formulas to fail.  Luckily there is an simple enough fix for this.  the NVL  function.    This function is used to detect a NULL value and replace it in your results with a more suitable value, in this case 0 (zero).

In the screenshot below we have added an additional column to our search with the same base formula but we have enclosed each field in the NVL function,  NVL({fieldname}, 0).  Now that formula reads Quantity of all items, OR 0 if this value is NULL – Quantity committed, OR 0 if this value is NULL + Quantity already Fulfilled, OR 0 if this value is NULL.

with NVL properly in place to detect and avoid the null values,  the results look much better

This formula fixed that issue that hidden within the summarized data are NULL values causing the lines that contain that value erranet value to discounted from the formula totally. The new column now shows the proper number of items still remaining to be committed because when it find one of the NULLs it is converted to a Zero and counted properly. I

NetSuite

var sc_project=12472621;
var sc_invisible=1;
var sc_security=”79304066″;
var sc_https=1;

https://www.statcounter.com/counter/counter.js