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 & Microsoft Dynamics MVP


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 & Microsoft Dynamics MVP

The calculated field that totals the four custom fields.

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

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 & Microsoft Dynamics MVP

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 & Microsoft Dynamics MVP

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 & Microsoft Dynamics MVP

Leave a Reply