How to handle Nulls in Dynamics CRM Calculated Fields

One of the little wrinkles with calculated fields is that they cannot handle nulls and so if one of the values in the calculation is null then the calculated field value is null. 

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant


In this example I have four custom fields on the opportunity entity to track the customers estimated sales by quarter.

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant

The calculated field that totals the four custom fields.

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant

A way to avoid this is to create a calculated field for each nullable sales field. This new calculated field returns zero if the sales field does not contain data.

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant

I will then use the new fields in the total calculation field.

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant

The total is now calculated even if the user does not enter values for all four sales quarters. I like this approach as it does not require any coding or populating nullable fields with zero values unnecessarily.

How to handle Nulls in Dynamics CRM Calculated Fields Joe Gill Dynamics 365 Consultant

Leave a Reply