Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

Option to ‘Hide NULL values’ in simple formula fields

cancel
Showing results for 
Search instead for 
Did you mean: 
Jonathan_Schofi
6 - Interface Innovator
6 - Interface Innovator

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.

3 Comments
Jonathan_Schofi
6 - Interface Innovator
6 - Interface Innovator

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
13 - Mars
13 - Mars

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
6 - Interface Innovator
6 - Interface Innovator

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

So I guess this feature request is redundant :slightly_smiling_face: