Empty values in calculations


I have done some calculations with empty values to clarify the outcomes for myself.

I also refer to this post Empty value in rule failing?.

I find it difficult to grasp because the beavior is sometimes awkward.

In attachement my demo file.

  • When we empty() by non-empty value, the result is 0. Why is this not empty() as a result?
  • When I do an IF THEN ELSE statement using a rule action versus a calculated column, the outcome is different. In the rule, if it cannot compare with empty, it jumps to the default value. In the calculated column, approach, it raises an error.

In general, my experience is that it is rather difficult to work with empty values. I quickly forget the special outcomes and even simple rules should always be tested for if they can cope with empty values.

Is there a logical explanation on this behavior ? Any training material available ? What’s best practice when working with empty values in complex calculations ?

demo_empty_values_in_calculations.morph (4.9 KB)

Here are the rules for the empty value:

  • For the arithmetical operators (+,-, /, *) the empty value is considered equivalent to 0.
  • For the equality operator, the empty value is equal to an empty text string "".
  • For all text functions in expressions, the empty value is considered equivalent to an empty text string "".
  • In merging/matching, the empty value matches another other empty value (which makes it different from SQL NULL that doesn’t match with itself).

In all other cases, the empty value is a special data type that doesn’t match or is equal/comparable to any other data type.

The “Rule” action seems to work incorrectly in your example. It should’ve failed in 2nd line. We’ll investigate this.