# 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 Field

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 |

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 |

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 |

Last modified: 2022-10-20 18:07:40