Skip to main content

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.

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.

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.

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.


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


So I guess this feature request is redundant 🙂

Reply


Terms & Conditions