Sugar Logic Walkthrough - Next Call Due Date

Overview

This article will walk through how to use the greaterThan(), rollUpSum(), addDays(), and several other functions in a Sugar Logic formula to populate a custom date field based on the value of a related module field. Specifically, the date field will calculate when to set a call related to a contact. The call record will be created via Sugar Workflows and the number of days out we set the date will be dependent on the contact's related account type.

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

Charlie needs to make sure that he is keeping in touch with his active contacts. We're going to build a "Call Due Date" field to calculate when he next needs to be in touch with each contact. Charlie categorizes his accounts into 3 types: small, medium, and large. He'd like to speak with contacts of "large" accounts at least once every 10 days, "medium" accounts every 20 days, and "small" accounts every 30 days.

Prerequisites

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

Adding fields with Studio
Altering layouts with Studio
Creating a workflow

Before we can build a formula to do this, we'll need to keep track of when the contact was most recently called:

Add a Date-type field to the Contacts module called last_call_date_c
Create a workflow based on the Calls module which updates the related contact record by setting the last_call_date_c to the current date

Whenever a new call is logged, the related Contact will now have the last_call_date_c field automatically updated.

Solution

Now we can create call_due_date_c as a second Date-type field on the Contacts module. Selecting the "Calculated Value" checkbox and clicking "Edit Formula" will open the formula builder. On the bottom left you'll see the available functions we can use as well as an icon indicating what type of value each returns. Hovering on each function will display information about how the formula is used. The bottom right contains a list of available fields for this module. On the top are two buttons which provide assistance for the related and rollup functions.

To start, Charlie only wants to call contacts which have a related opportunity. We will use the ifElse(Boolean c, Var1, Var2) function. If there are no opportunities, we want to return a blank date. To insert a function, start typing its name or select it from the list:
```
ifElse( 
 /*check for existing opportunity here*/ , 
 /*if true, calculate the due date here*/ , 
 date("") 
 )
```
There are a number of ways to check for an existing opportunity. Let's say that if this contact has an opportunity including a dollar amount, the contact needs to be receiving phone calls. Click on the "Rollup" button to "Sum" the related opportunities' amount field. Clicking Insert will produce the following:
```
rollupSum($opportunities, "amount")
```
We will then use the greaterThan(num1, num2) function check if the opportunities' amounts total to a positive number. If there are no opportunities or the sum of their amounts is 0 or less, the following will return as false:
```
greaterThan(rollupSum($opportunities, "amount"),0)
```
Finally, we need to calculate the due date using the addDays(date, days) function with the last_call_date_c field:
```
addDays($last_call_date_c, 30)
```
But, instead of setting the next call 30 days out for all contacts, we want to take into consideration the priority of the contact's account. We can use the related(link, field) function to pull the related account's "Account Type" field:
```
related($accounts,"account_type")
```
Then we'll compare it to the options of the dropdown list to decide which category the account matches using the equal(item1, item2) function
```
equal(related($accounts,"account_type"), "Large")
```
By nesting several ifElse functions, we can return the desired number of days based on the account_type. Here we check if the type matches "Small". If so, return 30. If not, check if the type equals "Medium", and so forth. If the account_type matches none of these options, then 15 will be returned as a default.
```
ifElse(equal(related($accounts,"account_type"), "Small"), 
 30, 
 ifElse(equal(related($accounts,"account_type"), "Medium"), 
   20, 
   ifElse(equal(related($accounts,"account_type"), "Large"), 
     10, 
     15 
     ) 
   ) 
 )
```

Placing the above as the second input of our addDays() function and wrapping the result in our original ifElse() produces the final formula:

ifElse(
  greaterThan(rollupSum($opportunities, "amount"),0),
  addDays($last_call_date_c,
    ifElse(equal(related($accounts,"account_type"), "Small"),
      30,
      ifElse(equal(related($accounts,"account_type"), "Medium"),
        20,
        ifElse(equal(related($accounts,"account_type"), "Large"),
          10,
          15
        )
      )
    )
  ),
  date("")
 )

Application

The Call Due Date field can then be added to the Contacts module's detailview, listview, and other layouts to serve Charlie's business needs. He can even create a Contacts dashlet to display what contacts are due for a call in the coming week: