Let the platform do the work

Sugar Logic

Overview

Sugar Logic allows administrators to write simple formulas and logic checks to provide a calculation for a field or to set the visibility of a field. For more information regarding the types of fields that you can use Sugar Logic with, please refer to the Field Options documentation. A calculated field uses a formula to derive its value based on the values of other fields as well as mathematical or logical operators. A dependent field uses a formula to determine whether or not the field should be displayed. These formulas are automatically recalculated when the record is updated. In addition, when a record containing a formula's input field is updated, the formula is automatically recalculated.

Note: When creating a calculated formula for a Currency field, the currency symbol should not be used for a value (e.g., $100) in the formula. The formula should only define the numeric value (e.g., 100), as including the currency symbol will result in improperly calculated values.

When a calculated or dependent field is created or its formula is modified, all existing records in the module will not use the new formula until they have undergone a save action. To update the existing records to use the new formula, the module's list view includes a Recalculate Value action. Admin users or users with developer-level access can use this feature to cause existing records to recalculate affected fields without having to perform an update on each record individually. For more information, please refer to the User Interface documentation in the Application Guide.

Administrators can access the formula builder for the fields and options that accept formulas by selecting "Edit Formula".

To browse all of the functions available, refer to the Complete List of Sugar Logic Functions section of this page. For more information and examples using Sugar Logic, please refer to the Sugar Logic section of the Knowledge Base, which includes articles covering the use of dependent and calculated fields. Topics range from introductory information to example Sugar Logic formulas and detailed walkthroughs of how to construct various fields and formulas in the formula builder.

Functions

Functions are listed on the lower left-hand corner of the formula builder and provide a variety of calculations, logic checks, and formatting options. To locate the function you need, simply scroll through the list or enter search criteria to narrow the list. Refer to the Complete List of Sugar Logic Functions section of this page to browse all of the available functions.

To see how to use a function, simply hover over the function to view the syntax, parameters, and a brief description of what the function does.

The function name is followed by a comma-separated list of parameters to the function. The list specifies the data type expected for each parameter. For example, the function "daysUntil" expects one parameter of Date type.

The description in the hover text will typically specify what data type is being returned, but if it does not, the symbol to the right of the function also represents the function's data type.

The following is a list of the possible return type symbols and the data types they return:

Symbol Description
# Returns a Number
Calendar Returns a Date object with a date and time
% Returns a Boolean (true or false) value
A Returns Text
[ ] Returns a List
? Returns a variable data type that could be any of the above

Fields

Fields are listed on the lower right-hand corner of the formula builder and contain the fields for the current module. Fields can be referenced in formulas and will start with a $. To locate the field you need, simply scroll through the list or enter search criteria to narrow the list.

To add a field to the formula, simply click on the field to be added. The variable name for the field will be placed at the end of the formula. You can also type the variable names manually in the formula.

Related fields are available to be added to formulas via the related function. You can manually add related function calls to your formula or you can build the syntax for the related function by clicking "Related Field".

This will open a dialog box that will assist in building the syntax to use the related function. Select a module and a field within that module to pull the related field from and click "Insert".

The resulting syntax for the selected options will be added to the formula.

Note: If a record has multiple related records to a module, the related function will pull the field from one of the related records. It is not recommended to rely on the related function to pull the same related records for a many relationship.

Rollup

In addition to adding related fields, you can also add aggregate functions on related fields to formulas via the rollup functions. You can manually add rollup function calls to your formula or you can build the syntax for the rollup function by clicking "Rollup".

This will open a dialog box that will assist in building the syntax to use the rollup function. Select the type of rollup function (Sum, Average, Minimum, or Maximum) to perform on the module field. To perform a count of related records, please refer to the count function listed in the Functions section. Select a module and a field within that module to perform the rollup function on and click "Insert".

The resulting syntax for the selected options will be added to the formula.

Note: Rollup functions can only aggregate number type fields.

Complete List of Sugar Logic Functions

Some functions require a given number of parameters while others are more flexible. Most parameters require a specific data type to be passed in for the function to work. Functions with the same data type can be nested in a single formula.

The following functions and their parameters (multiple parameters are represented by "...") are available in Sugar:

Function Description Parameters Return Type
abs Returns the absolute value of $param1. Number $param1 Number
add Returns the sum of the given parameters. Number $param1, … Number
addDays Returns $date moved forward or backward by $days. Date $date, Number $days Date
and Returns true if and only if all given parameters are true. Boolean $param1, … Boolean
average Returns the average of the given parameters. Number $param1, … Number
ceil Returns $param1 rounded up to the next integer. Number $param1 Number
concat Returns all of the given parameters appended together in the order passed. String $param1, … Text
contains Returns true if $needle is within $haystack. String $haystack, String $needle Boolean
count Returns the number of records related to this record by $module Relate $module Number
countConditional Returns the number of records related to this record by $link and that match the value of a specific field. Relate $link, Field $string, Values $list Number
createList Returns a list of the given parameters. Generic $param1, … List
currentUserField Returns the value of a field in the Users module ($field) for the currently viewing user (e.g., currentUserField("department")). 
Note: Field values that can be evaluated in the Users module are limited to fields that have the "Make visible for calculations" field option enabled.
String $field Variable
date Returns $param1 as a Date object. String $param1 Date
dayofweek Returns the number of the day of the week that $param1 falls on. Date $param1 Number
dayofmonth Returns the number of the day of the month as extracted from a date or datetime field. Date $param1 Number
daysUntil Returns the number of days from now until $param1. Date $param1 Number
divide Returns the $numerator divided by the $denominator. Number $numerator, Number $denominator Number
equal Returns true if $param1 is equal to $param2. Generic $param1, Generic $param2 Boolean
floor Returns $param1 rounded down to the next integer. Number $param1 Number
forecastIncludedCommitStages Returns all the included commit stages for the Forecast module.   List
forecastSalesStages Returns the values of all not-closed sales stages and any included closed sales stages for the Forecast module from the sales_stage_dom dropdown list. If $includeWon is true and $includeLost is false, it will return all sales stage values that are not "Closed Lost". Boolean $includeWon, Boolean $includeLost List
forecastOnlySalesStages Exclusively returns the values of included sales stages for the Forecast module from the sales_stage_dom dropdown list. If $includeWon is true and the remaining parameters are false, it will return all "Closed Won" values. Boolean $includeWon, Boolean $includeLost, Boolean $includeEverythingElse List
getDropdownKeySet Returns a List of the keys in the dropdown named $param1. This list must be defined in the Dropdown editor. String $param1 List
getDropdownValue Returns the value for the $key found in the dropdown named $list. This list must be defined in the Dropdown editor. String $list, String $key Text
getDropdownValueSet Returns a List of the values in the dropdown named $param1. This list must be defined in the Dropdown editor. String $param1 List
getListWhere Returns the matched array from lists. String $trigger, Enum $lists List 
greaterThan Returns true if $param1 is greater than $param2. Number $param1, Number $param2 Boolean
hoursUntil Returns the number of hours from now until $param1.  Date $param1 Number
ifElse Returns $return1 if $condition is true or $return2 if $condition is false. Boolean $condition, Generic $return1, Generic $return2 Variable
indexOf Returns the position of $needle in $haystack or -1 if $haystack does not contain $needle. The index starts at 0. Generic $needle, List $haystack Number
isAfter Returns true if $param1 is after $param2. Date $param1, Date $param2 Boolean
isAssigned Returns true if current record is assigned.   Boolean
isBefore Returns true if $param1 is before $param2. Date $param1, Date $param2 Boolean
isForecastClosed Returns true if $status is in the forecast config for sales_stage_won or sales_stage_lost String $status Boolean
isForecastLost Returns true if $status is in the forecast config for sales_stage_lost  String $status Boolean 
isForecastWon Returns true if $status is in the forecast config for sales_stage_won String $status Boolean 
isInList Returns true if $item is contained within $list. $list can be a dropdown or multiselect field type. Generic $item, List $list Boolean
isOwner Returns true if current record is assigned to current user.   Boolean
isValidDate Returns true if $param1 is a valid date string. String $param1 Boolean
isValidEmail Returns true if $param1 is in a valid email address format. String $param1 Boolean
isWithinRange Returns true if $value is greater than or equal to $min and less than or equal to $max. Number $value, Number $min, Number $max Boolean
ln Returns the natural log of $param1. Number $param1 Number
log Returns the $base Log of $value. Number $value, Number $base Number
max Returns highest value of the given parameters. Number $param1, … Number
maxRelatedDate Returns the highest value of $field in records related to $module. Relate $module, String $field Date
median Returns the median of the given parameters. Number $param1, … Number
min Returns lowest value of the given parameters. Number $param1, … Number
monthofyear Returns the number of the month that $param1 is in. Date $param1 Number
multiply Returns the value of the given parameters multiplied together. Number $param1, … Number
negate Returns the negated value of $param1. Number $param1 Number
not Returns true if $param1 is false, and false if $param1 is true. Boolean $param1 Boolean
now Returns a Date object representing todays date and the current time.   Date
number Returns the numeric value of $param1. String $param1 Number
or Returns true if any given parameters are true. Boolean $param1, … Boolean
pow Returns the $value raised to the power of $exponent. Number $value, Number $exponent Number
prorateValue Returns the $baseValue prorated using $dv1 and $du1 as the duration value and unit of the numerator and $dv2 and $du2 as the duration value and unit of the denominator. Supports day, month, or year as duration units. Number $baseValue, Number $dv1, String $du1, Number $dv2, String $du2 Number
related Returns the value of $field in the related $module. Relate $module, String $field Variable
rollupAve Returns the average value of $field in records related to $module. Relate $module, String $field Number
rollupConditionalSum Returns the sum of the values of $field in records related by $link where $conditionField contains something from $conditionalValues. Relate $link, String $field, String $conditionField, List $conditionalValues  Number 
rollupMax Returns the highest value of $field in records related to $module. Relate $module, String $field Number
rollupMin Returns the lowest value of $field in records related to $module. Relate $module, String $field Number
rollupSum Returns the sum of the values of $field in records related to $module. Relate $module, String $field Number
strlen Returns the number of characters in $param1. String $param1 Number
strReplace Returns modified $subject after replacing occurrences of $search within $subject string with the value specified by $replace. Include true as the fourth argument to make the search case-sensitive. String $search, String $replace, String $subject, Boolean $case-sensitive (optional) Text
strToLower Returns $param1 converted to lower case. String $param1 Text
strToUpper Returns $param1 converted to upper case. String $param1 Text
subStr Returns the portion of $value specified by $start and $length. The position starts at 0. String $value, Number $start, Number $length Text
subtract Returns $param1 minus $param2. Number $param1, Number $param2 Number
timestamp Returns the passed in datetime string as a unix timestamp.   Date
today Returns a Date object representing todays date.   Date
toString Returns $param1 converted to a string. Generic $param1 Text
translateLabel Returns the translated value of $label for $module. String $label, String $module Text
valueAt Returns the value at position $index in $haystack. Number $index, List $haystack Variable
year Returns the year portion of $param1 in YYYY format. Date $param1 Number