Sugar Logic Walkthrough - Discount by Opportunity Sum

Overview

This article will walk through how to combine four Sugar Logic functions (ifElse, isWithinRange, greaterThan, rollupSum) in a formula to add the likely values of all opportunities related to an account and then calculate a corresponding discount based on that value.

Sugar Logic enables administrators to create business logic for field calculations without needing to write PHP code. The field's dynamic value will be automatically updated based on a calculation you provide. For an overview on Sugar Logic and Calculated Fields, please refer to the article Introduction to Calculated Fields. Let's walk through an example to demonstrate the power and the process of Sugar Logic formulas.

Use Case

In this example, a company would like to know what level of discount can be offered to their customers on new opportunities. The discount would be determined using break points based on the total sum amount of opportunities already created. The company would like a new field named "Current Discount" added to the accounts module that would automatically calculate the correct discount. For example, when an Account calls in to do more business with the company, the phone agent would know immediately what discount to give the customer. The field would need to calculate and display the different discount percentages based on specific break points of the total opportunity amounts for the related Account.

• Breakpoint #1 : Accounts that have Opportunities totaling between $10,000 to $24,999 get a 10% discount
• Breakpoint #2 : Accounts that have Opportunities totaling between $25,000 to $49,999 get a 20% discount
• Breakpoint #3 : Accounts that have Opportunities totaling over $50,000 get a 30% discount

Note: Beginning with version 9.1.0, each user's license type (e.g. Sugar Serve) determines what functionality is available as described in the License Types Matrix documentation. The Opportunities module used as an example in this article is not available for Sugar Serve users, but creating the custom calculated field will work similarly for other modules.  

Prerequisites

You will need to be familiar with the following actions which are not explained in this article:

• Adding fields with Studio
• Altering layouts with Studio

This example will utilize the Sugar Logic function named "rollupSum" which will total the amount of all opportunities related to an account regardless of the status (Examples of Opportunity Status: Prospecting, Qualification, Closed Lost, Closed Won). Since we are only interested in Opportunities that are "Closed Won", we are going to assume that the company has a business process of populating the opportunity amount field (in the opportunity record) only when the opportunity status is set to "Closed Won".

Solution

1. Go to Admin > Studio > Accounts > Fields and click "Add Field".
2. Fill in the fields with the following values:
   Data Type : Decimal
   Field Name : current_discount (this field cannot contain any spaces)
   Display Label : Current Discount
   Precision : Change this to 2 (only 2 digits to the right of the decimal will be displayed, i.e. 3.12).
3. Check the "Calculated Value" checkbox, and select the "Edit Formula" button to enter the calculation.
4. In the popup box that appears, add the following formula and save (The calculation formula has been reformatted below to make it easier to read): 
   

   ifElse(
     isWithinRange(rollupSum($opportunities, "amount"),0,9999),
     ".0",
     ifElse(
       isWithinRange(rollupSum($opportunities, "amount"),10000,24999),
       ".1",
       ifElse(
         isWithinRange(rollupSum($opportunities, "amount"),25000,49999),
         ".2",
         ifElse(
           greaterThan(rollupSum($opportunities, "amount"),50000),
           ".3",
           "0"
         )
       )
     )
    )

5. Save the formula, then save the field.
6. Add the Current Discount field to the Account module's detail view via Studio and deploy the changes.
7. Edit and save an account record to see the new field's calculated value.

The following account with one $30,000 Opportunity shows a 20% discount. If a new opportunity record is added, the field will re-calculate when you refresh the page without the need to re-save the account record.

About the Formula

Formula Explanation

This formula uses four Sugar Logic functions (ifElse, isWithinRange, greaterThan, rollupSum). Combining sugar logic functions allows the ability to incorporate more complex logic into your SugarCRM system.

The function "ifElse" is the primary function for this calculation. It basically says if something(a Boolean) is true, then show value1(the discount), otherwise show value2. A function that determines whether something is true or false is called a Boolean. When considering if a function is a Boolean, check to determine if it will return a true or false. With any ifElse function, the Boolean must be evaluated first before it displays the output(value1 or value2). Since we have specific breakpoints to consider, we will need to create several ifElse statements in the calculation that consider each of the breakpoint criteria. In the full calculation formula, the primary function (ifElse) will utilize 2 types of booleans ( isWithinRange and greaterThan ). Boolean "isWithinRange" will check the different ranges of numbers that make up several of the breakpoints while Boolean "greaterThan" will simply check the highest breakpoint with values over 50,000. The value that the Booleans will be evaluating is created by the function "rollupSum". Function "rollupSum" performs a mathematic summation of all the opportunity record amounts and returns a number value.

Formula Breakdown

ifElse syntax: ifElse(Boolean, Value1, Value2)
isWithinRange syntax: isWithinRange(Number num, Number min, Number max)
greaterThan syntax: greaterThan(Number num1, Number num2)                                                                                                                           

By nesting the ifElse statements, we are saying if the first isWithinRange is true, use the ".0" value. If not, evaluate the second ifElse statement and use it's resulting value. After the above, we'll need to close the nested ifElse statements with a final "default" option of 0 to use if none of the above evaluations are true and 4 closing parentheses.