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.

  AnnualRevenue > 1000000 &&
  CONTAINS(CASE(Country, "United States", "US", "America", "US", 
    "USA", "US", "US", "US", "NA"), "US"),
    ISPICKVAL(LeadSource, "Partner Referral"), "Hot",
      ISPICKVAL(LeadSource, "PurchasedList") ||
      ISPICKVAL(LeadSource, "Web"),
      "Warm", "Cold"
  ), "Cold"

You can use images in formulas with the IMAGE() function.

"Hot", "/img/samples/stars_500.gif",
"Warm", "/img/samples/stars_300.gif",
"Cold", "/img/samples/stars_100.gif",

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("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.

HYPERLINK("https://www.salesforce.com", "Salesforce")

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",

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",

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)
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",
"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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s