Overview

  • Sugar Logic is designed to allow custom business logic that is easy to create, manage, and reuse on both the server and client.
  • Sugar Logic is made up of multiple components which build off each other and is extensible at every step. The base component is the Sugar Formula Engine which parses and evaluates human readable formulas.
  • The fundamental object is called a Formula. A Formula can be evaluated for a given record using the Sugar Logic parser.
  • Sugar Logic has several fundamental types. They are: numberstringboolean, and enum (lists). Functions may take in any of these types or combinations and output one of these types. Fields may also often have their values set to only a certain type.
  • Fields with calculated values can now be created from within Studio and Module Builder. The values are calculated based on Sugar Logic formulas. These formulas are used to create a new dependency that is executed on the client side in edit views and the server side on save. The formulas are saved in the varies or vardef extensions and can be created and edited directly.

 

How to create a Calculated Field?

  • Create 2 fields labeled as Number1 and Number2 in Studio.

sugarcrm-calculated-fields-number1-field-creationsugarcrm-calculated-fields-number2-field-creation

  • Next, create a field labeled Answer in Studio.

sugarcrm-dependent-fields-demostringanswer-field-creation

  • To create a Calculated Field, click on the Calculated Value check box. After clicking the check box, you will have an option to add a Formula.
  • To insert a formula, select the Edit Formula button and the Formula Builder screen will appear.

sugarcrm-calculated-fields-calculatedvalue-checked

sugarcrm-calculated-fields-formulabuilder-screen

  • To insert a Formula, select a Function from the list. In our example, we use the add function. Summation of Number1 and Number2 will be stored in field Answer.
  • Next, click on add. sugarcrm-calculated-fields-add-function-select
  • After clicking on add, select Fields from Search Fields. Scroll down if your fields are not on screen. In our case, we used Number1 and Number2.

sugarcrm-calculated-fields-field-selection

  • After selecting the first field, use a comma (,) and then select the second field. Formula Builder should appear similar to the image below. Click Save.

sugarcrm-calculated-fields-add-function-formula

  • After clicking Save, the Formula will be added to the field.

sugarcrm-calculated-fields-add-formula-defination

  • Click on the Save button to store the formula in the field definition.

sugarcrm-calculated-fields-save-field-defination

  • Put the fields in EditView and DetailView through studio.
  • Open EditView and enter values in fields Number1 and Number2. In EditView we put all 3 fields those are Number1, Number2, and Answer. But in Answer field, we are not able to add value in it manually because it is Calculated Field and remain disabled as an input control.

sugarcrm-calculated-fields-editview-add-formula

  • Save the record and it will display in DetailView as follows.

sugarcrm-calculated-fields-detailview-add-formula

List of various Number Type functions.

FunctionDescription
absabs(Number num) : Returns the absolute value of num. Example >> abs(-5) = 5
averageaverage (Number n, ...) : Returns the average of the given numbers.Example >> average(2, 5, 11) = 6
ceilceil(Number n) : n rounded up to the next integer. Example >> ceil(5.12) = 6
dividedivide(Number numerator, Number denominator) : Returns the numerator divided by the denominator. Example >> divide(8, 2) = 4
floorfloor(Number n) : Returns n rounded down to the next integer.Example >> floor(5.73) = 5
indexOfindexOf(val, List l) : Returns the position of val in l or -1 if l does not contain val. Examples >> (1)  indexOf("a", createList("a", "b", "c")) = 0 (2) indexOf("d", createList("a", "b", "c")) = -1
logLog(number, base) : Returns the supplied base Log of number.Example >> log(100, 10) = 2
maxmax (Number num, ...) : Returns highest value number passed in.Example >> max(-4, 2, 3) = 3
medianmedian(Number n, ...) : Returns the median of the supplied numbers.Example >> median(4, 5, 5, 6, 7) = 5
minmin(Number num, ...) : Returns lowest value number passed in.Example >> min(-4, 2, 3) = -4
multiplymultiply(Number n, ...) : Multiplies the supplied numbers and returns the result. Example >> multiply(-4, 2, 3) = -24
lnln(Number n) : Returns the natural log of n. Example >> ln(e) = 1
negatenegate(Number n) : Returns negated value of n. Example >> negate(4) = -4
powpow(Number n, Number p) : Returns n to the p power. Example >> pow(2, 3) = 8.
stddevstddev(Number n, ...) : Returns the population standard deviation of the given values. Example >> stddev(4, 5, 6, 7, 10) = 2.06
strlenstrlen(String s) : Returns the number of characters in the String s.Example >> strlen("Hello") = 5
subtractsubtract(Number a, Number b) : Returns a minus b. Example >> subtract(9, 2, 3) = 4
number

number(String s) : Returns the numeric value of s. Example >> number("1,200") = 1200

  • abs :: abs(Number num) : Returns the absolute value of num. Take a look at the following formula.

sugarcrm-calculated-fields-abs-formula

  • Open EditView and enter value in Demo Integer field.

sugarcrm-calculated-fields-abs-formula-answer

  • average :: average(Number n, ...) : Returns the average of the given numbers. Take a look at the following formula.

sugarcrm-calculated-fields-average-formula

  • Open EditView and enter value in Demo Integer and Demo Integer 1 field.

sugarcrm-calculated-fields-average-formula-answer

  • ceil :: ceil(Number n) : Returns n rounded up to the next integer. Take a look at the following formula.

sugarcrm-calculated-fields-ceil-formula

  • Open EditView and enter the value in Number1 (float) field.

sugarcrm-calculated-fields-ceil-formula-answer

  • divide :: divide(Number numerator, Number denominator) : Returns the numerator divided by the denominator.  Take a look at the following formula.

sugarcrm-calculated-fields-divide-formula

  • Open EditView and enter value in Demo Integer and Demo Integer 1 field.

sugarcrm-calculated-fields-divide-formula-answer

  • floor :: floor(Number n) : Returns rounded down to the next integer. Take a look at the following formula.

sugarcrm-calculated-fields-floor-formula

  • Open EditView and enter value in Number1 field.

sugarcrm-calculated-fields-floor-formula-answer

  • indexOf :: indexOf(val, List l) : Returns the position of val in l OR -1 if l does not contain val. Take a look at the following formula.

sugarcrm-calculated-fields-indexOf-formula

  • Open EditView and enter value in Number1 field. Return 0, if Number1=10. Return 1, if Number1=20. Return 2, if Number1=30. Return -1if Number1 has values other than 10, 20 and 30.

sugarcrm-calculated-fields-indexOf-formula-answer1 sugarcrm-calculated-fields-indexOf-formula-answer2

  • log :: Log(number, base): Returns the supplied base Log of number.  Take a look at the following formula.

sugarcrm-calculated-fields-log-formula

  • Open EditView and enter value in Number1 field.

sugarcrm-calculated-fields-log-formula-answer1

  • Change the base and will see the effect.

sugarcrm-calculated-fields-log-formula1

sugarcrm-calculated-fields-log-formula-answer2

  • max :: max(Number num, ...) : Returns highest value number passed in. Take a look at the following formula.

sugarcrm-calculated-fields-max-formula

  • Open EditView and enter value in Number1 and Number2 field. Check the value in Answer field.

sugarcrm-calculated-fields-max-formula-answer

  • min :: min(Number num, ...) : Returns lowest value number passed in. Take a look at the following formula.

sugarcrm-calculated-fields-min-formula

  • Open EditView and enter value in Number1 and Number2 field. Check the value in Answer field.

sugarcrm-calculated-fields-min-formula-answer

  • multiply :: multiply(Number n, ...) : Multiplies the supplied numbers and returns the result. Take a look at the following formula.

sugarcrm-calculated-fields-multiply-formula

  • Open EditView and enter value in Number1 and Number2 field. Check the value in Answer field.

sugarcrm-calculated-fields-multiply-formula-answer

  • negate :: negate(Number n) : Returns negated value of n. Take a look at the following formula.

sugarcrm-calculated-fields-negate-formula

  • Open EditView and enter value in Number1 field.

sugarcrm-calculated-fields-negate-formula-answer

  • pow :: pow(Number n, Number p) : Returns n to the power. Take a look at the following formula.

sugarcrm-calculated-fields-pow-formula

  • Open EditView and enter value in Demo Integer and Demo Integer 1 field.

sugarcrm-calculated-fields-pow-formula-answer

  • rollupAve :: rollupAve(Relate link, String field) : Returns the average value of field in records related by link. Example >> rollupAve($opportunities, "amount") = in Accounts, it will return the average amount of all the Opportunities related to this Account.
  • Consider an account record which has following opportunities related.

sugarcrm-calculated-fields-rollup-account-opportunity

  • Our aim is to find out the average value of amount field of all opportunities. Build formula for “Answer” field of Accounts as follows.

sugarcrm-calculated-fields-rollupAve-formula1

  • Open EditView of account and save the record. Answer field contains average value of Opportunities Amount field.

sugarcrm-calculated-fields-rollupAve-formula-answer

  • rollupMax :: rollupMax(Relate link, String field) : Returns the highest value of field in records related by link. Example >> rollupMax($opportunities, "amount") in Accounts, it will return the highest amount of any Opportunity related to the Account.
  • Consider an account record which has following opportunities related.

sugarcrm-calculated-fields-rollup-account-opportunity

  • Our aim is to find out maximum value of amount field from all the opportunities relate with account. Build formula for “Answer” field of Accounts as follows.

sugarcrm-calculated-fields-rollupMax-formula

  • Open EditView of account and save the record. Answer field contains maximum value of amount field of Opportunity.

sugarcrm-calculated-fields-rollupSum-formula-answer

  • rollupMin :: rollupMin(Relate link , String field) : Returns the lowest value of field in records related by link. Example >> rollupMin($opportunities, "amount") in Accounts, it will return the lowest amount of any Opportunity related to the Account.
  • Consider an account record which has following opportunities related.

sugarcrm-calculated-fields-rollup-account-opportunity

  • Our aim is to find out minimum value of amount field from all the opportunities relate with account. Build formula for “Answer” field of Accounts as follows.

sugarcrm-calculated-fields-rollupMin-formula1

  • Open EditView of account and save the record. Answer field contains minimum value of amount field of Opportunity.

sugarcrm-calculated-fields-rollupMin-formula-answer

  • rollupSum :: rollupSum(Relate link, String field) : Returns the sum of the values of field in records related by link. Example >> rollupSum($opportunities, "amount") in Accounts, it will return the sum of the amount of all the Opportunities related to the Account.
  • Consider an account record which has following opportunities related.

sugarcrm-calculated-fields-rollup-account-opportunity

  • Our aim is to find out total value of amount field from all the opportunities relate with account. Build formula for “Answer” field of Accounts as follows.

sugarcrm-calculated-fields-rollupSum-formula

  • Open EditView of account and save the record. Answer field contains total value of amount field of all Opportunities.

sugarcrm-calculated-fields-rollupSum-formula-answer

  • count :: count(Relate link) : Returns the number of records related to this record by link. Example >> count ($opportunities) in Accounts, it will return the number of opportunities related to this account.  Take a look at the following formula.

sugarcrm-calculated-fields-count-formula

  • Open EditView and save the record.Answer field contains number of Opportunities related with account.

sugarcrm-calculated-fields-count-formula-answer

List of various Date Type functions.

FunctionDescription
dayofweekdayofweek(Date d) : Returns the day of week that d falls on. Sun = 0, Mon = 1, ... , Sat = 6
daysUntildaysUntil(Date d) : Returns number of days from now until the specified date.
addDaysaddDays($date, $days) : Returns a date object moved forward or backwards by $days days.Example >> addDays(date("1/1/2010"), 5) = "1/6/2010"
nownow() : Returns a date object representing todays date and the current time.
todayReturns a date object representing todays date.
datedate(String d) : Converts the given string into a date.
  • dayofweek :: dayofweek(Date d). Take a look at the following formula.

sugarcrm-calculated-fields-dayofweek-formula

  • Open EditView and enter date value in Demo Date field.Answer field will contains number of day of week