Omnis Technical Note TNNO0016 May 2007

Avoiding calculations with NULL

For Omnis Studio 3 or after
By Andreas Pfeiffer

It will have happened to many of you: when you try to calculate with a NULL value, that is, a value which has not been assigned, an error will occur. This is correct because, after all, you cannot calculate with a value that is not assigned!

However, having an error occur may not be desirable or may lead to unpredictable results, so in some cases you may want to assume that the NULL value will be handled like the number zero. For example, this can happen when you are fetching data from a server and pass the data in a list to a report. If then your report contains calculated fields or methods that do calculations using the NULL values, you may receive unexpected or really strange results.

An easy way to avoid this problem is to replace the NULL values by the number zero. You can do this either in a loop or you can use the $sendall() method, which can be used to scan through all the lines in a list. For example, to scan through and replace in a list column called "amount" you can use:

Do myPrintList.$sendall($ref.amount.$assign(0)

However, now each cell in the column "amount" will be replaced by a "0", but we only want to replace values that are NULL (have not been assigned), so we can use a filter as a second parameter in the $sendall method. Note that the line number cannot be addressed with $ref and therefore must be retrieved with myPrintList.$line (this expression is set in square brackets). We can use the function isnull() to check whether the line really is NULL:

Do MyPrintList.$sendall(
    $ref.amount.$assign(0),
    isnull(myPrintList.[myPrintList.$line].amount))

A complete example method might look like the following:

Do myPrintList.$cols.$first() Returns lvColRef
While lvColRef
If lvColRef.$coltype=kNumber ;; only number columns
Do myPrintList.$sendall(
    $ref.[LvColRef.$name].$assign(0),
    isnull(myPrintList.[myPrintList. $Line].[lvColRef.$name]))
Do myPrintList.$cols.$next(lvColRef) Returns lvColRef
End While

Note that lvColRef.$name is set in square brackets as well because we want to have this notation to be interpreted before the $sendall() method is executed.