Null Values in Formulas

By default, when an object with a blank field or formula variable is included in a formula, that variable is assigned a zero (0) value, which may produce unexpected results, depending on the kind of formula you're creating. To prevent this, administrators can select the Treat empty values as Null checkbox when creating or configuring formulas, which will ensure any blank variables are treated as being empty and are therefore not included in the calculation.

For example, using the Min function, you need to create a formula that calculates the lowest numerical value of a field variable. There are currently three objects with that field that will be included in the formula: Object 1 with a value of 200, Object 2 with a value of 600, and Object 3 with no value. If you create the formula without selecting the Treat empty values as Null checkbox, the formula's result will be 0 because Object 3 was blank and was automatically assigned 0. However, if the checkbox is selected, the formula will omit Object 3 from its calculations and produce a result of 200.

If a formula requires input from a variable with no data and that variable has been configured to treat empty values as null, the formula will display Input Not Available until the variable contains data.

A new formula. Selecting the Treat empty values as Null checkbox will skip over any objects that contain blank variables.