# Option to ‘Hide NULL values’ in simple formula fields

#1

I have discovered that simple formula fields retain formatting options. For example, something like:

`{Hours}*{Hourly rate}`

…may be formatted as a currency with a preferred level of precision and currency symbol.

But more complex formulas, such as those with conditional logic, do not have formatting options available. For example:

`IF( {Hours}=0, "", "£"&{Hours}*{Hourly rate} )`

…is formatted as left-aligned text whether one likes it or not, even though the result is intended as a currency value.

In my example, all I am trying to achieve is to prevent empty `{Hours}` values from getting reported as £0.

So it would be great if formulas that can retain formatting options were provided a checkbox to Hide NULL values. I use the word ‘NULL’ here to mean any value that equates to empty:

• “” in an empty text field
• 0 in an empty numeric field
• There may be other values worth embracing such as “FALSE” or “NULL”

There will be times when people do want empty value to be visible, which is why I propose this as an option.

#2

Of course, a possibly simpler approach is just to allow the text alignment of formula results to be controlled regardless of the formula’s complexity.

#3

By including the text elements, you force the outcome to text.

Typically, I structure my formulas to avoid having to state an explicit null value. For instance, my version of your example formula would run like this:

`IF({Hours}!=0,{Hours}*{Hourly rate})`

When `{Hours} = 0`, the formula yields an empty field, not a 0 value; otherwise, it returns a numeric value that can then have formatting options applied.

Alternatively, you can specify `BLANK()` instead of `''`; that inserts an explicit null value without casting the result as text. The output of

`IF( {Hours}=0, BLANK(), {Hours}*{Hourly rate} )`

is numeric.

#4

Thanks, good insight, especially on the use of BLANK(), which works for me.

So I guess this feature request is redundant