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:
- They can be used for a multitude of complex use cases
- 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:
Find the Most Recent Date among ‘n’ number of 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
Show selected values in a Multi-Select Picklist as CSV
Example Use Case : Show comma separated values for all selections in a Multi Select Picklist:
Formula Outcome : Apple, Orange, Banana, Cherry
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
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:
Add Business Days to a Date
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.
Validate either none or all fields are filled out
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.