Formula Magic With Salesforce
Formulas are not only a very important field type, they are also used in the creation of validation rules, workflow rules, and buttons for customization of Salesforce instances. You can get really creative with the combination of formulas, roll-up summary fields, and workflow rules to avoid turning to custom development. Here are a few formulas I’ve come across in the community (modified to work for my requirements) and general ones that are just good to have in your arsenal to utilize. Taking into consideration the field API names may be different for you….
Purpose: End Date calculation based on Start Date and Picklist
Situation: Calculate an end date based on a start date plus a certain time frame set in a picklist. This end date had to be exact, meaning, days in a month and leap year both had to be taken into consideration. Thanks to the Salesforce Success Community, and with the help of Steve Molis and Chris Edwards (Thanks Guys!), a good starting point was found at this location. The issue however is that the situation called for a need to account for a pick list, so as a result I ended up revising the formula to include the addition of ‘CASE’ scenario. Be aware however, if the Start Date is a formula field you may run over in characters. In which case you would want to create a workflow rule and put this formula into a workflow rule to update the end date instead of creating an end date formula field. Here’s what the end result looked like:
Purpose: Calculate the % of opportunities won on an individual account
Situation: You want to figure out for accounts what percent of opportunities related to them are closed won. The two fields used are roll up summary fields on the account looking at the opportunity object. It looks like this:
IF( Total_Opportunities__c > 0, Won_Opportunities__c / Total_Opportunities__c, 0)
Purpose: Close date is in the past
Situation: This is a simple check to see if an opportunity is still open but the close date is in the past. It looks like this:
AND(IsClosed = FALSE, CloseDate < TODAY())
Purpose: Calculating the age of a contact
Situation: This is a common formula we typically use to calculate a persons age based on their birth date. It looks like this:
IF(MONTH(TODAY())>MONTH(Birthdate),YEAR(TODAY())-YEAR(Birthdate),IF(AND(MONTH(TODAY())=MONTH(Birthdate),DAY(TODAY())>=DAY(Birthdate)),YEAR(TODAY())-YEAR(Birthdate),(YEAR(TODAY())-YEAR(Birthdate))-1))
Purpose: Change the default settings in Lead Conversion Button
Situation: This was a request to change some of the default setting in the Lead conversion button. The request included setting the name of the task subject, set the task as ‘high’ and ‘In Progress’, a default due date, and unchecking the reminder box. What you need to do is create a custom button on the lead. There are several elements you can change which you can find here. Be aware however this is considered a hack and with every Salesforce release you would want to check to make sure the elements are still valid. This is what the formula ended up looking like:
window.location.href="/lead/leadconvert.jsp?IsReminderSet_fu=0&sem=0&tsk5_fu='Contact New Converted Lead: {!Lead.LastName}&tsk4_fu={!Today()+3}&tsk13_fu=High&tsk12_fu=In Progress&id={!Lead.Id}&RetURL=/{!Lead.Id}";
The Salesforce Success Community is an excellent resource when you are trying to troubleshoot how to build a specific formula or determine why your formula is erroring out. I recommend a few locations in the community like Answers, or Groups like Who owes me a beer?!? and Formulas - Help, Tips and Tricks. Searching for a similar issue first in any of these locations is always a best way to start then posting in any of these locations.
Do you have challenging or interesting formulas in your back pocket? Please feel free to share in comment below, on our Facebook page, or directly at me on Twitter @LeiferAshley or in the Success Community.
