December 07, 2019

Powerful CASE() Function in Validation Rule and in Formula Field


Hello Trailblazers!
In this blog post, we are going to learn about how to use powerful, mighty CASE() function with some examples.

Validation Rule

  • Validation rule allows you to specify your own-business-specific criteria to prevent users from saving invalid data in one or more fields.

CASE() function

  • CASE() function is useful for writing formulas that have different results based on picklist values. CASE() function can significantly reduce compile / character size, which is important for complex formulas that are approaching the 4K max compiled size limit.

Example 1

  • John Wiseman is working as a System Administrator at AW Computing. He received the following requirement from his Manager that whenever Case is created or edited,  Description field should be required based on Case Reason picklist values.

Solution for above requirement

  • Validation Rule on Case Object
AND(
      CASE(Reason,
           "Installation",     1,
            "Peformance",      1,
            "Breakdown",       1,
            0
        ) = 1,
       ISBLANK(Description)
   )

Go to Setup-> Object Manager->search for Case Object->select Validation Rule->click on New->Create following validation rule. Copy above formula and paste it.



Example 2

  • John Wiseman is working as a System Administrator at AW Computing. He received another  requirement from his Manager that whenever Opportunity is created or edited, Amount should be greater than $100000 if it is in one of the following stages
  • Prospecting
  • Qualification
  • Need Analysis

Solution for above requirement

  • Validation Rule on Opportunity Object
AND(
    Amount < 100000,
    CASE(StageName,
        "Prospecting",       1,
        "Qualification",     1,
        "Need Analysis",     1, 
         0 ) = 1
   )



Example 3

  • John Wiseman is working as a System Administrator at AW Computing. He received another  requirement from his Manager that whenever Case is created or edited, prevent user from saving Case record  if Status is closed for specific record type.

Solution for above requirement

  • Validation Rule on Case Object
AND( 
    CASE($RecordType.DeveloperName,
    "Customer",      1,
    "Competitor",    1,
    "Partner",       1,
    0) = 1,
    TEXT(Status) = "Closed"
  )

Example 4 : Binary Technique

  • Binary Technique is very useful in CASE() function. It reduces compile /character size. It makes code readable and maintainable. Let's see how we can use binary technique in our validation rule.
  • You can see full requirement on this link: https://success.salesforce.com/answers?id=9063A000000a4pCQAQ

Solution for above requirement


CASE(
    IF(TEXT(Building_Permit_is_Required__c)             = "Yes",    "1", "0") +
    IF(NOT(ISBLANK(Permit_Applied_For__c)),                         "1", "0") +
    IF(TEXT(Zoning_Permit_is_Required__c)               = "Yes",    "1", "0") +
    IF(NOT(ISBLANK(Electrical_Permit_Applied_For__c)),              "1", "0") +
    IF(TEXT(Electrical_Permit_is_Required__c)           = "Yes",    "1", "0") +
    IF(NOT(ISBLANK(Zoning_Permit_Applied_For__c)),                  "1", "0"),
    
    "110000", TRUE,
    "001100", TRUE,
    "000011", TRUE,
    "111100", TRUE,
    "001111", TRUE,
    "110011", TRUE,
    "111111", TRUE,
     NULL
)

Example 5

  • John Wiseman is working as a System Administrator at AW Computing. He received another  requirement from his Manager that whenever Session is created or edited, prevent users from scheduling meetings on a Saturday or Sunday.

Solution for above requirement

MOD(number, divisor) and replace number with the field or expression you want divided; replace divisor with the number to use as the divisor.
CASE(MOD(My_Date__c - DATE(1900, 1, 7), 7),
0, 0,
6, 0,
1) = 0

Example 6

  • John Wiseman is working as a System Administrator at AW Computing. He received another  requirement from his Manager that whenever Contact is created or edited, display day of the week in text like Sunday, Monday.. based on custom Date(Date__c) field.
  • Create following custom field on Contact Object.

Custom Fields on Contact
FIELD LABEL FIELD NAME DATA TYPE
Date Date__c Date
Day of the week Day_of_the_week Formula(Text)

Solution for above requirement

  • Formula Field on contact object
CASE(MOD(My_Date__c - DATE(1900, 1, 7), 7),
     0, "Sunday",
     1, "Monday",
     2, "Tuesday",
     3, "Wednesday",
     4, "Thursday",
     5, "Friday",
     6, "Saturday",
     NULL
)



References



4 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Great Article! Thanks for sharing this information.
    Nice article thank you for sharing valuable information.Visit here

    ReplyDelete
  3. The powerful CASE() function is a valuable tool for implementing robust validation rules and creating dynamic formula fields in a commenting website. The CASE() function allows developers to evaluate multiple conditions and specify different outcomes based on those conditions.

    In the context of validation rules, the CASE() function enables administrators to define complex logic to determine whether a comment meets specific criteria before it can be submitted. For example, Best travel cameras, the CASE() function can be used to check if the comment contains inappropriate language, exceeds a certain character limit, or violates any predefined rules. By utilizing the CASE() function, the commenting website can enforce strict guidelines and ensure that only appropriate comments are accepted.

    Moreover, the CASE() function proves highly useful in creating dynamic formula fields. With this function, developers can calculate and display relevant information based on different conditions or inputs. In a commenting website, this can be applied to showcase various attributes of a comment, such as its length, sentiment analysis score, or even the user's reputation. By using the CASE() function, Best cinema cameras, the website can present users with contextual information and enhance their commenting experience.

    ReplyDelete