Announcing a change to how null values in formulas are computed in Zoho CRM

We are announcing a Zoho change to how null values in formulas are computed in Zoho CRM. As you are aware, the formula field offers versatile functionality that allows you to perform diverse computations, ranging from basic mathematical operations to concatenation and field value comparisons.

To gain a deeper understanding of the various capabilities of formula fields, we recommend reading the comprehensive guide on Data types in formula fields.

How did Zoho handle null values in formulas?

In the past, when the value of a participating field was empty, the formula field would erroneously treat it as “0”. This was a logical inconsistency since zero represents a value, whereas null or blank signifies the absence of a value. As a result, this led to inaccurate calculations.

How does Zoho now handle null values in formulas?

Moving forward from May 2023, formula fields without a value will no longer be calculated. As a result, the formula field will return a blank value.

What does that look like in real life?

Zylker storage is a physical unit that rents out storage spaces. To help visitors know the price beforehand, Zylker has configured a formula field.

Formula: Rent= No. of storage units * price * duration

Syntax: (${Leads.Price per unit}*${Leads.Number of units}*${Leads.Period of storage required})

As you can see from the image, the “Period of storage required” or duration field is empty. CRM used to consider no value to be 0 and returned 0.

After the update, the rent field will be blank, per the corrected logic.

In cases where the zero or blank value doesn’t impact the output (for example: addition), the calculated output will consider a lack of input as zero.

Formula: Rent= (No. of storage units * price * duration) + tax

Syntax: (${Leads.Price per unit}*${Leads.Number of units}*${Leads.Period of storage required})+${Leads.Tax}

In the above image, you can see that even if the tax field is empty, it doesn’t hinder the calculation, and so the formula calculates the rent in this case.

That’s all for this announcement. Zoho will roll this behaviour out to all users of all data centres shortly, if you need any help with this change please get in touch. To learn more about formulas, read Building Formula Fields.