Blog

Five Ingenious Formulas I Love Using

You are currently viewing Five Ingenious Formulas I Love Using

There was a time where I used to dread writing formulas but thanks to being active on the Answers Community, doing hands-on formula related exercises in a dev org for days and challenging myself with the most complex of use cases which have gradually made me develop a fondness for them. I have responded to more than a thousand formula related questions on the Answers Community till date and I must admit that some of them hold a special place among all the ones that I have built or know of. They are special because of a couple of reasons:

  1. They can be used for a multitude of complex use cases
  2. They employ ingenious(read ‘snazzy’) techniques

So without further ado, here are my six favorite formulas which I keep handy and get to utilize on almost every client project:

  1. Find the Most Recent Date among ‘n’ number of date fields

    Most Recent Date among five date fields

    Example Use Case : Most recent date among the following five dates:
    05/01/2011, 04/02/2011, 06/04/2017, 04/03/2016, 09/01/2018

    Formula Result : 09/01/2018

  2. Show selected values in a Multi-Select Picklist as CSV

    Parsing a Multi-Select Picklist

    Example Use Case : Show comma separated values for all selections in a Multi Select Picklist:

    Mutli Select Picklist Selection

    Formula Outcome : Apple, Orange, Banana, Cherry

  3. Show outcome based on different Permutations/Combinations of field values

    This technique was developed by  Deepak Anand and I swear by it when implementing scenarios which involve multiple permutations/combinations.

    Requirement: Imagine there are two checkboxes on the Lead to categorize the type of package
    – Standard
    – Premium

    Depending on how these checkboxes are checked, a user needs to display intuitive text in a custom field on the Lead record:

    Standard Premium Text to Display
    Not Checked Not Checked Ignore
    Not Checked Checked Warm
    Checked Not Checked Cold
    Checked Checked Hot

    In terms of Boolean Algebra, this is what it translates to:

    A B
    0 0
    0 1
    1 0
    1 1

    Where 0 is for unchecked and 1 is for checked. This is where the Binary Technique shines and you can write the formula as shown below:

    Binary Technique

  4. Add Business Days to a Date

    Date + Business Days

    Example Use Case : Add ‘5’ Business Days to 8/17/2016
    Formula Outcome : 8/24/2016

    Note: This formula doesn’t take corporate holidays into account.

  5. Validate either none or all fields are filled out

    Using basic Maths to validate Full/No data entry

    Example Use Case : Validate that the Mailing Address constituent fields are either all filled out or none are filled out. Partial entry is not allowed.

So there goes the list of some of my favorite formulas that have helped me tackle the most convoluted of use cases on multiple occasions. Now there are a lot more which deserve mention and I will certainly include them in an upcoming blog post.  Feel free to share in the comments below if you handled a similar use case using another approach or formula logic.

Credits: Thanks to Deepak Anand and SteveMo whose posts and answers introduced me to such ingenious techniques and formula logic.