Where a Boolean type field in ISM has been defined as Nullable, it can store three distinct values:
null. ISM also allows for fields to be used in expressions, however
null values can cause unexpected behaviour. How should these values be best accounted for when writing expressions?
I've identified a number of potential methods of handling Nullable Booleans, but I'd welcome some thoughts from others with likely more experience than me.
Using the field as-is
if (<expr>) then <a> else <b>
I wouldn't recommend this under most circumstances. If <expr> evaluates to
null then the returned value is also
null, rather than
Coming from a background with more focus on scripting than databases, I've got a bad habit of using two unary negations to force conversion to Boolean. The idea is that
!(<expr>) will always return a boolean vale, so a second negation allows for conversion of a 'truthy' or 'falsey' value to an explicit
false. However, as
!null evaluates to
null, this doesn't work in ISM.
Boolean(input) converts the input to a Boolean value, and on my system
false, which should make this an ideal candidate. However, the official documentation states that
Boolean(null) should return
null and the function is not supported in all contexts (such as database queries)
Replacement with nvl()
nvl() function evaluates the first parameter, returning it if not
null and the second parameter if it is. Unfortunately, even though
nvl() requires that both parameters be of the same type, I've found that the returned value is of the
DataLayer.DataValue type and attempting Boolean expressions on this will fail. The use of the
DataLayer.DataValue type may be because
nlv() returns the text value
"" when both parameters are
null. Wrapping the expression in a conversion with
Boolean() may resolve this, but
Boolean(""), will error and it suffers the same context shortfalls as above.
Comparison with static values
<expr> == true <expr> != false
null != true and
null != false, these expressions should force
null values to
false respectively while leaving initially
false values unchanged. In my testing I've found this works in some contexts but not others: behaving as expected in Quick Actions and Business Rules but not in Filter Expressions.
Handling as a special case
!IsNull(<expr>) && <expr> <expr> != null && <expr>
This is the solution I end up using. I would prefer a solution without duplication
<expr> calls, as any functions with side effects like
Prompt() will need to be evaluated first and intermediate results stored. The expressions may also require the check for
null to be on the left-hand-side due to
AND short-circuiting. Ultimately though, this works in all the contexts I've needed it to.
Of course, not permitting Null values would solve the whole issue, but sometimes that's not a decision we get to make. Any thoughts from anyone else?