A simple way of using text-type formulas is to concatenate to strings using an ampersand. As below:
FirstName & " " & LastName
TEXT() converts a Percent, Number, Date, Date/Time, picklist, or Currency field into Text. TEXT() returns output without any formatting, commas, or currency signs. For example, TEXT(percent_value), if percent_value is set to 30%, returns 0.3.
Conversely, VALUE() receives text and returns a string.
SUBSTITUTE(old text, new text) scans for any instances of old text and replaces them with instances of new text. Similar to find and replace on MS word for example. What I want to know is where does the string that it scans go in relation to this?
The following formula is used to rate a lead as either hot, warm or cold based on three criteria: annual revenue, country, and lead source. This is represented graphically in the above diagram.
IF( AnnualRevenue > 1000000 && CONTAINS(CASE(Country, "United States", "US", "America", "US", "USA", "US", "US", "US", "NA"), "US"), IF( ISPICKVAL(LeadSource, "Partner Referral"), "Hot", IF( ISPICKVAL(LeadSource, "PurchasedList") || ISPICKVAL(LeadSource, "Web"), "Warm", "Cold" ) ), "Cold" )
You can use images in formulas with the IMAGE() function.
IMAGE( CASE(Rating__C, "Hot", "/img/samples/stars_500.gif", "Warm", "/img/samples/stars_300.gif", "Cold", "/img/samples/stars_100.gif", "/img/samples/stars_000.gif"), "Unknown")
Here, in the case of the rating being hot, there are 5 stars displayed. Warm is 3 stars. Cold is 1 star.
The following formula uses multiple nested IF() statements to assign a region based on the state abbreviation.
IF(ISBLANK(BillingState), "None", IF(CONTAINS("AK:AZ:CA:HA:NV:NM:OR:UT:WA", BillingState), "West", IF(CONTAINS("CO:ID:MT:KS:OK:TX:WY", BillingState), "Central", IF(CONTAINS("CT:ME:MA:NH:NY:PA:RI:VT", BillingState), "East", IF(CONTAINS("AL:AR:DC:DE:FL:GA:KY:LA:MD:MS:NC:NJ:SC:TN:VA:WV", BillingState), "South", IF(CONTAINS("IL:IN:IA:MI:MN:MO:NE:ND:OH:SD:WI", BillingState), "North", "Other" ))))))
The HYPERLINK() function accepts arguments for the URL and for the link text. And optionally the target: whether it opens up a new window or changes the page.
This formula takes the email address and replaces everything including and to the left of the @ symbol with “www.”. So it automatically generates a URL based on the known email address.
SUBSTITUTE(Email, LEFT(Email, FIND("@", Email)), "www.")
There’s a concept called “helper formula fields“ where you can simplify a formula by calculating part of it in a separate formula field and simply referencing that with the main one. For example this formula:
IF(((NOT(IsClosed)) && (TODAY() - DATEVALUE(CreatedDate) > 20)), "RED", IF((NOT(IsClosed)) && (TODAY() - DATEVALUE(CreatedDate) > 10), "YELLOW", "GREEN"))
Can be replaced by these two:
TODAY() - DATEVALUE(CreatedDate)
IF(((NOT(IsClosed)) && (Case_Age_In_Days__c > 20)), "RED", IF((NOT(IsClosed)) && (Case_Age_In_Days__c > 10), "YELLOW", "GREEN"))
This has an additional benefit because you may like to see the above formula itself, sort by that field, or reference it in other formulas.
I passed the challenge on this Trailhead module with the following formulas:
F( ISBLANK (Email), 0, 1) + IF( ISBLANK (Phone), 0, 1) + IF( ISBLANK (Company), 0, 1) + IF( ISBLANK (Title), 0, 1) + IF( ISBLANK (TEXT(Industry)), 0, 1)
IMAGE( CASE( Lead_Quality_Helper__c , 1, "/img/samples/stars_100.gif ", 2, "/img/samples/stars_200.gif", 3, "/img/samples/stars_300.gif", 4, "/img/samples/stars_400.gif", 5, "/img/samples/stars_500.gif", "/img/samples/stars_000.gif"), "0 stars")
The first one basically creates a scoore up to five, based on how many of the given fields are populated with a value. This is the helper field.
The second one assigns an image based on the score in the helper field. I find the syntax for using IMAGE() with CASE() a bit confusing. I can certainly see how this would be useful though, especially because the 0-5 star images are already in Salesforce.