1 Reply Latest reply on Jul 20, 2018 9:52 AM by AlasdairRobertson

    Best Practices for Handling NULL in Boolean Fields

    Matthew.Hammick Rookie

      Where a Boolean type field in ISM has been defined as Nullable, it can store three distinct values: true, false, and 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 <a> or <b>.

      Implicit conversion

      !!(<expr>)
      

      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 true or false. However, as !null evaluates to null, this doesn't work in ISM.

      Explicit conversion

      Boolean(<expr>)
      

      The function Boolean(input) converts the input to a Boolean value, and on my system Boolean(null) returns 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(<expr>, false)
      

      The 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
      

      As null != true and null != false, these expressions should force null values to true and false respectively while leaving initially true and 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 OR and 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?

        • 1. Re: Best Practices for Handling NULL in Boolean Fields
          AlasdairRobertson ITSMMVPGroup

          When you care a Boolean field in ISM you should always set it with a default value to remove this issue.  If the field exists in the database already you can add an initialisation rule, if it is a new field an initialisation rule should be created for it.

           

          You could update all float value Boolean fields to either true or false is not true or false, but I hadn't thought of the mechanics of it.  Well something to think about over the weekend.