Calculated Field - Determining If a Field Is Blank

Overview

When using Sugar, you may wish to know whether a particular field contains a value or is blank. For example, you could create a calculated field to determine and store whether another field is populated in order to identify records with missing information. Another use might be to make a field's visibility dependent on whether a second field is populated or not. Both can be done using a Sugar Logic formula. Sugar's database has two potential values that would make a field display as blank in Sugar: the field can be empty, or it can contain "NULL". The formula provided in this article addresses both situations. For an overview of Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields.

Use Case

As an example, we will determine if a record's Description field is blank or populated and mark a custom checkbox field accordingly. A report or list view filter could then use the checkbox field's value to identify records that need to have a description added.

Prerequisites

• You must be an administrator or have developer-level role access to make the necessary changes in Admin > Studio.
• You must have an existing checkbox field or create a new checkbox field and mark it as calculated prior to entering the following formula.

Formula

The formula shown below will evaluate to true and place a check in your checkbox field if the Description field contains a value.

greaterThan(strlen($description),0)

You can also slightly modify this formula to do the opposite and place a check in your checkbox if the Description field does not contain a value.

not(greaterThan(strlen($description),0))

Formula Breakdown

In this formula, we use the strlen() function to obtain the length of the text or characters in the specified field (e.g., Description). The greaterThan() function then checks to see if the length is greater than "0". If the length of the string is greater than "0", then the formula returns true. If the length of the string is either "0" or "NULL", then the formula returns false.

The second version of the formula returns the opposite by using the not() function, which reverses the result. If the length of the string in the field is either "NULL" or "0", then the formula returns true. If the length of the string is greater than "0", then the formula returns false.  

Application

Once your custom checkbox field is created with the calculated value formula (greaterThan(strlen($description),0)), it will check the box when the Description field contains a value. You can add the checkbox to the module's record view to confirm it is working as expected, but it can be used in reports or list view filtering to identify records with missing data even if it is not shown on the record view.