Tech Tip: How to display a blank value instead of zero for NULL numeric values

PRODUCT: 4D | VERSION: 11 | PLATFORM: Mac & Win

Published On: April 22, 2010

There are times where it is apporpriate to display a blank in a field or listbox column instead of zero for a numeric field. One good example of this is if the value is NULL and not 0.

Because the option is not presented in the Number Format list in the form object Property List editor it is easy to be unaware that there is the ability in 4D v11 SQL to display a blank field in a numeric field on detail and list forms. This feature is documented in the Chapter 7 of the Design Reference under "Fields and Active Objects in Forms - Formats for Positive, Negative, and Zero":

"A number display format can have up to three parts allowing you to specify display formats for positive, negative, and zero values. You specify the three parts by separating them with semicolons as shown below:
Positive;Negative;Zero

...If you use three parts, the first is for positive numbers, the second for negative numbers, and the third for zero."

The fine point to know is that option can be used as "Blank if zero" to closely emulate "Blank if null" that exists for the display of date and time fields on forms. This is an emulation and not exactly the same because a test is needed to know whether to display a blank or a zero depending on if the field is null or not.

The tests are:

For a detail form...

: ($FormEvt_L=On Load )
    If (Is field value Null([MyTable]NumericField))
       SET FORMAT([MyTable]NumericField;"####,##0 ;###,##0-;")
    Else
       SET FORMAT([MyTable]NumericField;"###,##0 ;###,##0-")
    End if
: `...

For a list form...

: ($FormEvt_L=On Display Detail )
    If (Is field value Null([MyTable]NumericField))
       SET FORMAT([MyTable]NumericField;"####,##0 ;###,##0-;")
    Else
       SET FORMAT([MyTable]NumericField;"###,##0 ;###,##0-")
    End if
: `...

If you wanted to literally tell the user that the field was null you can use the following code in the case of NULL values:

SET FORMAT([MyTable]NumericField;"####,##0 ;###,##0-;NULL")

In the Property List for numeric fields (or for numeric columns of list boxes based on a selection), select a desired numeric display format and then manually add the second semicolon to the end of the format string.

Once a value has been set to a non-null value it can never be set back to null. You should alway use the test as shown above. The format simply follows the reasoning of "Blank if zero" instead of "Blank if null".