Null Values

 

Initial Value for Fields

For all four of the major field types (Boolean fields, number fields, DateTime fields, and text fields), the initial value is null. It remains as a null value when you save the record unless you manually set the value or the value is set by an available rule (such as the initialization rule or saving rule.)

 

Setting a Field Value to Null

The following table summarizes the ways in which you can try to set a field value to null, with a description of whether the given syntax is valid or not:

Syntax	Valid	Comments
$(null)	Y	This is the standard method of representing null values. The word null is in all lower case letters.
$(NULL)	Y	Similar to the above case, NULL is in all upper case letters.   When you save, the system automatically converts $(NULL) to $(null), so the behavior is the same as entering null.
$(Null)	N	Null with only the initial character capitalized.   When you save, the system interprets Null as a field reference, rather than as a null value. As a result, you get the error "Field Null is not found in table <business object>".
$("null") $('null') $("NULL") $('NULL') $("Null") $('Null')	N	Null specified in lower case letters, upper case letters, or with the initial character capitalized. The null is surrounded in either single or double quotes.   For any such permutations, the system saves the expression as the text value of "null" rather than treating it as an actual null value. As a result, when attempting to update a Boolean field using this syntax (for example, when running an update object quick action), you get the following error "Error while saving data: Conversion failed when converting the varchar value 'null' to data type bit."
"null" 'null' null "NULL" 'NULL' NULL "Null" 'Null' Null	N	Null specified in lower case letters, upper case letters, or with the initial character capitalized. The null is optionally surrounded in either single or double quotes.   For any such permutations, when you save the definition (for example, when you save the update object quick action), the system returns an error similar to the following (when specified relative to a Boolean field) "Exception: Value must be an 'true' or 'false'. 'null' cannot be converted to 'true' or 'false'."

The results from the above table demonstrate that the only way to properly set a field value to null is to use either $(null) or $(NULL).  All other means for expressing a null value result in either a design time or runtime error.

 

Null Value Comparison

HEAT supports if() expressions, so the expression can return different values depending on whether the given condition is satisfied.

To check against null values, a common expression is:

$(iif((TestBool == null), "The logical field is null", "The logical field is not null"

))

In the above example, assume that there are two fields defined: one Boolean field called "TestBool" and another text field called "TestText" that stores the results of the above expression when evaluated via the saving rule.

Assuming the TestBool field still has a null value, when you save the record, the logical comparison incorrectly evaluates to a false value, and the system returns the message from the "else" branch ("The logical field is not null").

This is the case for all four of the major field types. Regardless of whether the field value is null, the comparison always returns false and the system always executes the "else" branch.

If you invert the condition in the expression and swap the "then else" portions of the iff expression, the expression is:

$(iif((TestBool != null), "The logical field is not null", "The logical field is null"

))

The results are similar to before.  Assuming the TestBool field still has a null value, when the record is saved, the logical comparison incorrectly evaluates to a true value, and the message from the then branch is returned ("The logical field is not null").

This is the case for all four of the major field types. Regardless of whether the field value is null, the comparison always returns true, and the system always execute the "then" branch.

You cannot directly compare against the null value using the equality or inequality operators.

 

With the current behavior, the only way to properly perform null comparisons is to use the nvl() function. The syntax of the nvl() function is as follows:

nvl(expr1, expr2)

If expr1 is null, then the nvl() function returns the value of expr2; otherwise, the system returns the original value of expr1.

For example, the expression can be used as follows:

nvl(TestBool, "")

If TestBool has a value of null, the system returns an empty string; otherwise, the system returns the value of the TestBool field (in this case, true or false).

You can use the nvl() function with the earlier iff expressions and they will work correctly:

$(iif((nvl(TestBool, "") == ""), "The logical field is null", "The logical field is not null"))

--or--

$(iif((nvl(TestBool, "") != ""), "The logical field is not null", "The logical field is null"))

Alternatively, if you want to avoid comparing null values, you can initialize the field to a sensible value (for example, false, in the case of Boolean fields), so that the system performs exact value comparisons. This technique is only feasible if there is no distinction between the null value and the alternate value for the given field.