Apply a Default Value to Handle Null Value Errors

Learning Objectives

After completing this unit, you’ll be able to:

• Apply a default value to null connector fields.
  
• Handle null values in datasheet fields and worksheet calculations.
  

Apply a Default Value to Null Connector Fields

Occasionally, when you sync or import data, one or more of your fields will contain a null value. When Spiff attempts to run that data through commission calculations, these pesky nulls can cause issues. A null value isn’t the same as a zero.

Let’s say commission calculations require the deal amount to be multiplied by a particular commission rate. If the deal amount is null for one or more records, Spiff can’t do those calculations as null is not a currency or numerical value. Here’s an example of a null value error.

So how do you prevent this from happening? By applying a default value in what we call null handling. Here’s how.

1. Find and select the field you want to set up null handling for. You can search for the field in the global search bar, or in the Fields tab of Data underneath its relevant object.
   
2. Select the field, and make sure the Settings sidebar is open.
   
3. Under the Format section, turn on Apply Default Value.
   

A new menu appears where you can set the default value when the field in question is null. Spiff recognizes the data type from the appropriate connector field and then designates a replacement value for any null field.

But there are different kinds of connector fields. Let’s explore null handling for each type of field in the context of a plan rule.

Number or Currency Field

For a number or currency, Spiff gives you the option to enter a number to replace any null values. For example, a 0 will replace any null values for the ARR__c field.

String or Date Field

For a string, text can be entered to replace any null values. You can populate null values with instructions, such as Please enter the region. For date data types, the default date can be selected from the date picker.

Boolean Field

The null options for a Boolean field are True and False.

Handle Null Values in a Datasheet Field

So far we’ve looked at connector fields, but what if we want to have null handling on a datasheet field that you created in Spiff? Good news! Spiff can handle null values in datasheet fields. You just have to be more specific in what you tell Spiff, as it won’t automatically recognize the data type.

For example, a default value is applied to the ARRComm calculated field. There are four options that can be chosen for this field’s default value, and you select Number.

The Value from Field option is the only new choice for a calculated field. If Value from Field is chosen, you can choose from other datasheet fields or worksheet calculations in Spiff. Choosing a datasheet field or worksheet calculation replaces a null value from the field with the value from that chosen calculation.

If the name of the field used in the Value from Field option is changed, the null handling won’t automatically update and you’ll get an error message. So update the Value from Field to reference the updated field name.

Handle Null Values in a Worksheet Calculation

You handle null values for worksheet calculations in the same way that you do for datasheet fields.

The options are identical, except when using Value from Field. The only option is to use other worksheet calculations. Why? A worksheet calculation returns a single output, and replacing that single output with a datasheet field wouldn’t work.

When you apply a default value to a field, it applies to all plans and rules—it’s a universal change. If you want null handling for a specific rule or plan, create a field for that rule or plan. And then set the null handling for that new field.

There’s one last setting to explore: visibility. You learn about that in the next unit.