Calculated Field - Summarizing Dropdowns With a TextField

Overview

This article will walk through how to use the createList() and isInList() functions in a Sugar Logic formula to aggregate the values of several drop-down fields and return a string result. For an overview on Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields. 

Use Case

In our example, we will calculate the combined value of three 'temperature' drop-down fields, each having four potential values, and return an overall 'temperature' for the record. The four 'temperature' values are: none, cold, warm, and hot. The dropdown list item values are as follows:

• Item Name[Display Label]
• 0[None]
• 1[Cold]
• 2[Warm]
• 3[Hot]

Prerequisites

Prior to completing the calculate field, you'll need to create the 3 dropdown fields ( called field1_c, field2_c, and field3_c in our example formula) in Studio and set up their dropdown options to match the above item name and display labels. Then you'll create a 4th field of type TextField and mark it as calculated before entering the following formula.

Formula

A calculated field that will display one of three values (cold, warm, hot) based on the average of three drop down fields using the above list would require the following formula:

ifElse( 
  isInList(add(number($field1_c),number($field2_c),number($field3_c)), 
  createList(1,2,3,4,5)), 
 "cold", 
 ifElse( 
   isInList(add(number($field1_c),number($field2_c),number($field3_c)), 
   createList(6,7)), 
   "warm", 
   ifElse( 
     isInList(add(number($field1_c),number($field2_c),number($field3_c)), 
     createList(8,9)), 
     "hot", 
     "" 
     ) 
   ) 
 )

In this formula, a sum of 1-5 returns "cold," 6-7 returns "warm," 8-9 returns "hot," and 0 will leave the textfield blank.

Formula Breakdown

The number() function converts the dropdown's value to a numeric value that can be added up.

We then use the add() function to sum the values of the tree fields.

The createList() function creates a temporary list of values that we use to compare.

The isInList() function compares the sum to the given list of values and returns "true" if it finds a match.

The ifElse() function checks each range and will return the desired string based on the matching list.