BUSINESS REQUIREMENTS

When converting leads to Accounts, Contacts & Opportunity we hope to make business out of those. Over the time the created Opportunities don't receive any responses and settle to the bottom of the list.

So how do we know if an Opportunity is still active?

In this Blog we are going to create a custom formula field to rate the Opportunity is Hot, warm or Cold based on the Stage and Last Modified Date of the Opportunity. And create a Opportunity Report with the formula field on it.

STEPS TO CREATE A FORMULA FIELD

1. Setup → Opportunity → Fields & Relationships.

2. Click on New Button to create a New Field.

   

3. Select Formula as the Datatype. Click Next.

   

4. Label the Formula Field as Activity Scale & Select the Formula Return Type as Text.

   

5. Click Next, Enter the Formula

   LOGIC -

   Now there are 3 parts to the formula. Here we will be using IF Conditions

   The syntax of IF Condition - IF(logical_test, value_if_true, value_if_false)

   PART 1

   The formula should be populate (colored/highlighted) the field with Red square if the Opportunity is not Closed and its Last Modified Date is within past week.

   • So the logical_test would be to verify that the Stage of Opportunity is not Closed Won or Closed Lost AND also verify if the Last Modified Date is Last 7 days.
   • If the logical_test is true then we populate the field with a Red square image.
   • If it is false, it goes to the next IF logic.

   PART 2

   In the next IF condition we will populate (colored/highlighted) the Field with Yellow Square if the Opportunity is not Closed and it's Last Modified Date is falling between 8-30 days.

   • So the logical_test would be to verify that the Stage of Opportunity is not Closed Won or Closed Lost AND also verify if the Last Modified Date is between 8-30 days.
   • If the logical_test is true then we populate the field with a Yellow square image.
   • If it is false, it goes to the next IF logic.

   PART 3

   In the next IF condition we will populate the Field with Green Square if Opportunity is not Closed and its Last Modified Date is more than a month away from when it was Last Modified.

   • So the logical_test would be to verify that the Stage of Opportunity is not Closed Won or Closed Lost AND also verify if the Last Modified Date is more than 30 days from the current date.
   • If the logical_test is true then we populate the field with a Green Square image.
   • If it is false, then it will display a text as Opportunity Closed.

   IF(NOT(ISPICKVAL( StageName , "Closed")) && 
   LastModifiedDate > DATETIMEVALUE(TODAY()- 2) , 
   IMAGE("/img/samples/color_red.gif", "red", 30, 30),
   
   IF(NOT(ISPICKVAL( StageName , "Closed")) &&
   (LastModifiedDate < DATETIMEVALUE(TODAY()- 2) && LastModifiedDate > DATETIMEVALUE(TODAY()- 30)), 
   IMAGE("/img/samples/color_yellow.gif", "yellow", 30, 30),
   
   IF(NOT(ISPICKVAL( StageName , "Closed")) && 
   LastModifiedDate < DATETIMEVALUE(TODAY()- 30), 
   IMAGE("/img/samples/color_green.gif", "green", 30, 30),"Opportunity Closed") ) )

6. After adding the formula, Check Syntax.

7. Verify if your formula has any errors.

   

8. Click Next, Select the Profiles to which you want to grant edit access to this field via field-level security.

   

9. Click Next, Select on which Page Layouts of the Opportunity the Custom Field should be displayed.

   

10. Once selected, Click Done.

    

STEPS TO CREATE AN OPPORTUNITY REPORT

1. App Launcher → Sales → Reports → New Report.

   

2. Select Opportunities as the Report Type.

   

3. Under the Outline Tab in the left Column, add Last Modified Date & Activity Scale fields to the Report.

   

4. Save & Run the Report. This is how the Report looks like.

WRAPPING IT UP

In this Blog, we created a custom formula field to Populate a Colored Square based on the Opportunity's Stage & Last Modified Date.