Calculated Field - Adding Empty Fields

Overview

This article will present an example formula for a calculated field which totals the sum of several other fields including those which may be empty or contain NULL values by using the numeric value of zero for any blank fields.

For an overview on Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields.

Use Case

In this example, we will add the values of three Integer-type fields (C1, C2, C3) and allow for the possibility that one or more of the fields is blank by converting an empty or NULL field to a numeric value of zero, which can be added. C1 will have a value of "50", C2 will have a value of "100", and C3 will be empty.

Prerequisites

Prior to completing the calculated field, you will need to create or have existing fields of type Integer, Currency, Decimal or Float. For our example formula, we created three Integer type fields called "C1", "C2", and "C3". You will also need to create a fourth field of type Integer, Currency, Decimal or Float and mark it as calculated before entering the following formula.

Formula

The formula below will calculate the sum of the three Integer-type fields even if one or more of the fields is empty or contains NULL. The total sum is displayed in the custom integer field containing the calculated formula.

add(
  ifElse(or(equal($c1_c,""),equal(strToLower(string($c1_c)),"null")),0,$c1_c),
  ifElse(or(equal($c2_c,""),equal(strToLower(string($c2_c)),"null")),0,$c2_c),
  ifElse(or(equal($c3_c,""),equal(strToLower(string($c3_c)),"null")),0,$c3_c)
 )

Formula Breakdown

The MySQL database has two potential values that would make a field display as empty in Sugar: a blank string ("") or "NULL".

The first equal() function checks to see if the integer field matches the blank string ('''') in the database.

The second equal() function checks to see if the integer field matches the string "null". The toString() function is used to convert the field's value to a string, then strToLower() is used to make all of its characters lower-cased. The result is then compared to "null" where a match indicates that the field contains the NULL database value.

Since both values ("" and NULL) are considered a blank, we use the or() function to say that if either equal() statement returns true, then the integer field is indeed "blank" and should be replaced with a numeric zero for the summation. In that case, the ifElse() function will return the numeric zero. The or() function will evaluate as "false" if the field is not blank, causing the ifElse() function to return the actual value of the given field.

Finally the add() function sums the results of all three ifElse() functions to produce the correct total.

Application

Once your custom Integer, Currency, Decimal or Float field is created with the calculated value formula and added to the module's detailview, it will display the total sum of the integer fields. For our example, since C3 is empty, the formula will convert this to a numeric value of zero during the calculation process. Since C1 has a value of "50" and C2 has a value of "100", the total sum of the three integer fields equal is shown in our calculated field called "Summation": 150 = 50+100+0.