Option to ‘Hide NULL values’ in simple formula fields

[Jonathan_Schofi]

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.

[Jonathan_Schofi]

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.

[W_Vann_Hall]

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.

[Jonathan_Schofi]

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

So I guess this feature request is redundant :slightly_smiling_face: