New way to handle NULLs and divisions by zero

...that's what we were working on over past couple of months.

When performing numeric calculations TeamDesk used to distinguish between NULL (that's when user left input field blank) and zero. NULL is a special value that does not compare to anything and requires special form of check for it. Moreover result of any arithmetical operation with NULL is also NULL and here we received a lot of questions like "I'm using the formula [A]+[B]+[C] and the result is blank value, why?" The answer is simple: A (or B or C) is blank. Ok, but for non-techie user it's a bit hard to understand.

We had an option that control NULL behavior during the calculations, called "Blank as zero" that substituted NULL values with zeros during the calculations but this option was often turned off.

Another reoccurring problem was the division by zero error. It's not a secret we are performing calculations using underlying database engine (namely, Microsoft SQL Server) and when first division by zero is encountered, it stops data extraction immediately returning the error message. Unfortunately, without any kind of location information. So user ended up with "Division by zero" message on screen with small chance to guess the formula that caused the error to fix it.

Finally we decided to address both problems at once.

For new TeamDesk applications numerical NULL values are always handled like zeros. Period. As the result, the formula can not result to NULL, since you can not produce it performing arithmetical operations. This behavior is quite similar to the spreadsheet calculations. Lookups, summaries and functions that convert from non-numeric types to numeric result to zero on blank input.

Division by zero now generate DIV/0 designator to the cell where error occurred. All subsequent calculations on such a value will also produce DIV/0. As a bonus, we are also generating ERR message for the formulas that do not compile.

For those of you who are curious how it's done and how it affects the performance, keep in mind that a) you can not produce NULL with TeamDesk formulas and b) any database-level arithmetical operation with NULL produce NULL. So, now, in calculations we are using NULL as a designator of division by zero error. You may have achieve the same result before, but you had to keep in mind all those checks for NULLs and zeroes and now TeamDesk does all this for you automatically. We hope you will like it.

Since TeamDesk's duration type is similar to numeric all said above applies to durations as well.

Below is the list of changes we did to implement these features:

Numerical versions of Nz(), IsNull(), NullIf() functions are dropped as their arguments can't calculate to NULL.

Functions and operators, converting/calculating from other types to numeric will return zero on NULL input. These are ToNumber(), Len(), Day(), DayOfWeek(), DayOfYear(), Month(), Quarter(), Year(), Hour(), Minute(), Second() and subtraction operators date - date, time - time, timestamp - timestamp.

Sum(a, b, c) is left for compatibility but identical to a + b + c.

If() and Case() functions resulting to numeric and with omitted  arguments will return 0 instead of NULL.

Numeric summary columns will result to 0 when there are no records to calculate on.

Please note that new applications created from scratch and from templates in application library use new logic by default.

For now, existing applications continue to use old logic. Any copy made from such app will use old logic as well.

While our ultimate goal is to get rid of old code, we undestand that this change heavily affects the compatibility and migration to the new logic will need careful review and will take some time. We are planning to announce formula analysis tools to ease the migration in a next few days.

Comments

[Kevin]

Is there any way to (quickly) scan our code to see where the deprecated formulae are used?

[Kirill Bondar]

Kevin: In setup mode, try Setup tab, Application section, Check NULL handling link.