SugarCRM offers Calculated Fields
Calculated Fields are built into SugarCRM
SugarCRM is not only one of the most flexible CRM systems when it comes to integration options. But Sugar also contains amazing customization options – right within the field definition. If you feel comfortable with Excel formulas, then you will love these examples below for SugarCRM field calculations.
| Function | Description | Example |
| strlen(String s) | Returns the number of characters in the String s. | strlen(“Hello”) = 5 |
| ln(Number n) | Returns the natural log of n. | ln (e) = 1 |
| divide(Number numerator, Number denominator) | Returns the numerator divided by the denominator. | divide(8, 2) = 4 |
| negate(Number n) | Returns negated value of n. | negate(4) = -4 |
| number(String s) | Returns the numeric value of s | number(“1.200”) = 1200 |
| median(Number n, …) | Returns the median of the supplied numbers | median(4, 5, 5, 6, 7) = 5 |
| pow(Number n, Number p) | Returns n to the p power | pow(2, 3) = 8 |
| add(Number n, …) | Returns the sum of the given numbers. | add(2, 1, 3) = 6 |
| subtract(Number a, Number b) | Returns a minus b. | subtract(9, 2, 3) = 4 |
| Log(number, base) | Returns the supplied baseLog of number. | log(100, 10) = 2 |
| floor(Number n) | Returns n rounded down to the next integer. | floor(5.73) = 5 |
| multiply(Number n, …) | Multiplies the supplied numbers and returns the result. | multiply(-4, 2, 3) = -24 |
| max(Number num, …) | Returns highest value number passed in | max(-4, 2, 3) = 3 |
| min(Number num, …) | Returns lowest value number passed in | min(-4, 2, 3) = -4 |
| abs(Number num) | Returns the absolute value of num. | abs(-5) = 5 |
| ceil(Number n) | Returns n rounded up to the next integer. | ceil(5.12) = 6 |
| average(Number n, …) | Returns the average of the given numbers | average(2, 5, 11) = 6 |
| contains(String haystack, String needle) | Returns true if needle is within haystack. | contains(“Hello World”, “hello”) = true |
| equal(Generic item1, Generic item2) | Returns true if “item1″ is equal to “item2″. | equal( “one”, “one”) = true, equal(1, “one”) = false |
| greaterThan(Number num1, Number num2) | Returns true num1 is greater than num2. | greaterThan( 3, 5) = false |
| isValidEmail(String email) | Returns true if email is in a valid email address format. | isValidEmail(“invalid@zxcv”) = false |
| isValidEmail(“good@test.com”) = true | ||
| and(boolean1, …) | Returns true if and only if all parameters are true. | and( true, true) = true, and(true, false) = false |
| not(Boolean b) | Returns false if b is true, and true if b is false. | not (false) = true |
| or(boolean1, …) | Returns true if any parameters are true. | or( false, true) = true |
| isWithinRange(Number num, Number min, Number max) | Returns true if num is greater than or equal to min and less than or equal to max. | isWithinRange( 3, 3, 5) = true |
| isWithinRange( 2, 3, 5) = false | ||
| getDropdownKeySet(String list_name) | Returns a collection of the keys in the supplied dropdown list. This list must be defined in the DropDown editor. | valueAt( 2, getDropdownKeySet(“my_list”)) |
| createList | Returns list made up of passed in variables. | create list(123, “Hello World”,“three”, 4.5) |
| getDropdownKeySet(String list_name) | Returns a collection of keys in the supplied dropdown list. This list must be defined in the DropDown Editor. | valueAt(2, getDropDownKeySet(“my_list”)) |
| ifElse (Boolean c, Val2, Val2) | Returns Val1 if c is true or Val2 if c is false | ifElse (true,”first”,”second”) = “first” |
| ifElse(false,”frist”,”second”) = “second | ||
| valueAt(Number index,createList values) | Returns the value at position index in the collection values | valueA(1,createList(:”a”,”b”,”c”) =”b” |
| strToUpper(String s) | Returns s converted to upper case | strToLower(“Hello World”) = “HELLO WORLD |
| strToLower(String s) | Returns s converted to lower case | strToLower(“Hello World”) = “hello world” |
| concat(String s, …) | Appends two or more pieces of text together | concat(“Hello”, ” “, “World”) = “Hello World” |
| toString(val) | Converts the given value to a string | toString(5.5)=”5.5” |
| translateLabel(String label, Strng module) | Returns the translated version of a given label key. | transateLabel(“LABEL_NAME”,”Accounts”) = “Name” |
| getDropdownValue(String list_name, String key) | Returns the translated value for the given key found in the list_name DropDown list. This list must be defined in the DropDown editor. | getDropdownValue(“my_list”, “foo”) |
| date(String d) | Converts the given string in the current user’s date format into a date | date(“5/20/2011″) |
| today() | Returns a date object representing todays date | |
| now() | Returns a date object representing today’s date and the current time | addDays(now(), 7) = (one week from now) |
| addDays($date, $days) | Returns a date object moved forward or backwards by $days days | addDays(date(“1/1/2010″), 5) = “1/6/2010″/ |
| isAfter(Date day1, Date day2) | Returns true day1 is after day2 | isAfter(date(“1/1/2001″), date(“2/2/2002″)) = false |
| isBefore(Date day1, Date day2) | Returns true day1 is before day2 | (date(“1/1/2001″), date(“2/2/2002″)) = true |
| dayofweek(Date d) | Returns the day of week that d falls on | Sun = 0, Mon = 1, … , Sat = 6 |
| daysUntil(Date d) | Returns number of days from now until the specified date | |
| monthofyear(Date d) | Returns the month of year that d is in | Jan = 1, Feb = 2, … , Dec = 12
|
Some Examples of Calculated Value formulas
Here are some examples of creating formulas for Calculated Value fields.
Sugar-Commission Amount for Opportunities
This formula calculates the value for an estimated sales commission@10%, based on the opportunity amount.
multiply ($amount, 0.1)
Lead Scores
This formula scores Leads using the following criteria:
- 10 points for entering a name longer than 5 characters (probably a real name).
- 5 points for having a mobile or work phone number (10 points for both).
- 10 points for being referred.
- 10 points for lead opportunities worth 1,000 or more
add (
ifElse(greaterThan(strlen(concat($first_name, $last_name)), 5), 10, 0),
ifElse(equal($phone_mobile, “”), 0, 5),
ifElse(equal($phone_work, “”), 0, 5),
ifElse(equal($refered_by, “”), 0, 10),
ifElse(greaterThan(number($opportunity_amount), 999), 0, 10)
)
Account Rating
This formula rates accounts as small, medium, and large based on the number of employees; 0-50 employees = Small, 50-100 employees = Medium, more than 100 employees = Large.
ifElse(equal($employees, “”), “Small”,
ifElse(greaterThan(number($employees), 100), “Large”,
ifElse(greaterThan(number($employees), 50), “Medium”,
“Small”)))
Account Region
This formula creates a Region field and populates it automatically based on the State.
ifElse(isInList(strToUpper($billing_address_state),
createList(“CA”, “AZ”, “WA”, “OR”, “NV”, “UT”, “ID”)), “West”,
ifElse(isInList(strToUpper($billing_address_state),
createList(“MT”, “WY”, “CO”, “NM”, “ND”, “SD”, “NE”, “KS”, “TX”)), “Mountain”,
ifElse(isInList(strToUpper($billing_address_state),
createList(“ME”, “NY”, “PA”, “WV”, “VA”, “NC”, “SC”, “GA”, “DL”)), “East”,
“Misc”)))
Displaying field values across multiple tabs of a layout
You can use Calculated Value fields to display a field value across tabs in Detail View and Edit View.
In this example, using the Accounts module, we mirror the value of the Industry field from the More Information tab to the Other tab, in Detail View and Edit View.
a. Create a new Calculated Value field named Industry.
b. In the Formula Builder, select $industry from the Fields list.
Sugar displays the field in the text area.
c. Add the field to the Other tab of the Detail View and Edit View layouts.
When the value of Industry field on the More Information tab changes, the value of the Industry field on the Other tab changes as well.
Creating a Dependent Value field in SugarCRM
Formulas can be created to determine whether a field is visible within the Edit and Details View layouts. The created formulas have outputs that are either True or False. Fields created using dependent values will display only when the output of the formula is True. Follow the steps listed below to create dependent fields for example in the Date Created field in Accounts module
1. Click Studio in the Developer Tools section of the Administration page.
2. Select Fields under Accounts in the Modules section.
3. Select date_entered in the Edit Fields pane.
This displays the Edit Field pane.
4. Select the Dependent checkbox in the Edit Field pane.
This displays the Visible if field.
5. Click the Edit Formula button.
This displays the Formula Builder pop-up box. Items listed in the Functions column display the function details, description and examples when you mouse-over them to help you create functions.
6. Select functions and fields from the Functions and Fields columns and click Save in the Formula Builder pop-up box.
7. Click Save in the Edit Field pane.
SugarCRM field Types for Calculated Fields
Free SugarCRM 6 Assessment PDF
Click here to access and download our Free Sugar CRM 6 Evaluation PDF.
With this document, you will be able to identify the key enhancements within Sugar CRM.
Topics addressed include:
- Calculated Fields
- Favorites and Favorites Dashlet
- Twitter and Social Media integration
- Reporting (filtering, scheduling)
- User interface improvements (there are many!)
- Sugar Mobile 1.0 details
- and much more
TCO Calculator for CRM Projects-FREE
Click here to access and download our Free CRM TCO Calculator Tool.
With this tool, you will be able to identify the key TCO line items that are relevant for any CRM implementation. We have also included data for comparing CRM software systems such as Microsoft CRM, Salesforce.com, SugarCRM, Sage CRM, Sage SalesLogix and more. You will be able to view the 3 Year and 5 Year Total Cost of Ownership for both SaaS (Online/On-Demand) and Licensed (On-premise) pricing models.
TCO line items include:
- Annual License/User price
- Support
- Hardware (incl. Server and Users)
- Implementation
- Staff
- Network
- Mobile
- and much more
Spreadsheet Tool to Compare CRM Systems-FREE
Click here to access and download our Free Excel CRM Comparison Tool.
With this tool, you will be able to identify the key similarilites and differences between 9 of the top CRM systems, including Microsoft Dynamics CRM, Sage SalesLogix, GoldMine, Sage ACT!, Pivotal and more.
Comparison Topics include:
- Key CRM Features
- Pricing (Licensing, etc..)
- Available Modules (e.g. SFA, Marketing, Service, etc)
- Feature Details (Sync, Call Center, Quoting, E-commerce, Lead Management, Security, Workflow, and much more.
- Hardware and Network support (Microsoft, Linux, etc…)
- Industry/NAICS
Search terms for Compare CRM:
- sugarcrm formula builder
- sugarcrm formula builder examples
- sugarcrm formulas add month
- sugarcrm formula date add a year
- sugarcrm ce photo field
- sugarcrm date object
- add a year to date object in sugarcrm
- formula for calculating age from date of birth in sugarcrm
- if else strlen(string) > 0 in sugar
- sales calculation in SugarCRM
