Skip to main content
30 votes

What are the compiled costs of each Formula function

Notes: There are a lot of functions to check, so building a complete list will take a bit of work. I've made this a community post so others can contribute if they like. Salesforce changes the ...
19 votes

How to display Picklist Label, instead of Picklist API in a Formula Field

Unfortunately this feature does not seem to exist yet. Vote for this Idea: Allow Picklist Value Labels in Formulas Spring '17 brought "API Names" to picklist values so that integrations can ...
Adrian Larson's user avatar
  • 150k
13 votes

Setting/mocking out formula fields during tests

Since recalculateFormulas() still has a bug, you can use this as a work around to set formula fields: @TestVisible private static SObject setFormulafield(SObject sObj, String fieldName, Object value) ...
Wim's user avatar
  • 574
12 votes
Accepted

Custom MetaData Types reference in Formula Field

Unfortunately, you can't reference custom metadata types in formulas at all. Their primary purpose is to provide metadata that's easy to package and transport between orgs, and primarily intended for ...
sfdcfox's user avatar
  • 495k
12 votes
Accepted

Static resource image in formula field

This example displays the image regardless of the value in any field. IMAGE('/resource/low', "low", 64, 64) This example displays an image of a green square if the amount of an opportunity is ...
Saroj Bera's user avatar
  • 14.8k
11 votes
Accepted

How to reference Choice Value in formula instead of its label?

For whatever reason, as noted here, you seem to get the label if you refer to the field, but you get the value if you pass the field into a record element/variable. As @Mitesh stated, you should be ...
Kris Goncalves's user avatar
10 votes
Accepted

What does a colon ':' mean in a formula field?

Owner is a polymorphic lookup field, which means that you can put there User and Queue values, Owner:Queue says that "treat Owner field value as Queue SObject"
ytiq's user avatar
  • 4,555
9 votes
Accepted

Formula field value, GUI vs API/Apex

This is a Known Issue that has not been resolved. Please log in with your Success credentials (probably your production or developer edition login) and check "This issue affects me" to be notified of ...
sfdcfox's user avatar
  • 495k
9 votes
Accepted

Formula Field - Case Function

If function will work inside the case statement in formula You can do like this:- CASE( sa_nket__case_text__c , '00', 'Member', '02', 'Member', '03', 'Member', '99', 'Member', '23', IF( ISBLANK(...
sanket kumar's user avatar
  • 15.4k
8 votes

Custom MetaData Types reference in Formula Field

You can reference CMT records in formula fields starting with the Winter '19 release. It follows the same syntax as in validation rules: $CustomMetadata.MyCustomSetting__mdt.DeveloperName....
Vladimir Gerasimov's user avatar
7 votes
Accepted

CASE formula only woking on one ISPICKVAL

Your usage of CASE() isn't quite right. You don't need to use ISPICKVAL() if your CASE() function is operating on a picklist field. The syntax is CASE( [expression], [test value 1], [result 1], [...
Derek F's user avatar
  • 62.4k
7 votes

Custom MetaData Types reference in Formula Field

We are adding support for custom metadata type in formulas. In Spring '18 you will be able to reference CMT records in validation rules (pilot) with the future addition of formula fields, default ...
Vladimir Gerasimov's user avatar
7 votes
Accepted

How to truncate zeroes from an account number

Here it is, most simple way to achieve this IF( BEGINS(Customer_Account_No__c, "6"), TEXT(VALUE(RIGHT(Customer_Account_No__c, LEN(Customer_Account_No__c)-1))), "") Converting Text to ...
Santanu Boral's user avatar
7 votes

Before Trigger & Formula Field Reliability

As an addendum to @adrianLarson comment: Cross-object formula fields may be available in the before trigger context if the parent relationship is based on a required field or on a lookup field with ...
cropredy's user avatar
  • 72k
7 votes

Can a formula field be indexed?

Yes, you can request to have indexes created on custom formula fields provided that they are deterministic - generally speaking, that they don't have cross-object references, dependency on the current ...
David Reed's user avatar
  • 93.2k
7 votes
Accepted

Difference in minutes between two datetime fields

Difference b/w two date time would never be 0 until you have exact date time value. So in your case, you can resolve your issue either by changing the return type of formula field to TEXT or NUMBER(...
Pragati Jain's user avatar
  • 1,570
6 votes
Accepted

Formula field image BETWEEN numbers

Try this IF( NPS__c >= 9, IMAGE("/resource/Misery09", "Promoter"), IF( NPS__c > 7, IMAGE("/resource/Misery05", "Neutral"), IF( NPS__c >= 1, IMAGE("/resource/Misery06", "Detractor"), TEXT(...
Doug B's user avatar
  • 11.5k
6 votes

Text Formula String Concatenation - Omit Trailing Comma

@AdrianLarson I used your suggestion for a similar solution and it worked for a year or so, until I was asked to include more Checkboxes (12 in all) in the formula, then I got: Error: Compiled ...
JLanders's user avatar
6 votes
Accepted

Formula field to separate strings in a Text field?

I would do this in Apex, but if you must use formula fileds, you can use this to get the first three formulas that separate the commas: First_Name__c -> TRIM(LEFT(Names__c , FIND(",", ...
lvelasco's user avatar
  • 104
6 votes

Birthday Alert Every Year

PB is not the way to do this for exactly the reasons you're stating. Unless there's DML performed of some kind on each contact, it won't fire for the contact. I don't know how many contacts you have ...
crmprogdev's user avatar
6 votes
Accepted

Get Difference Between 2 Dates in Months Without Exceeding 5000 Characters

Because my comment appears to have been well-received (and for sake of having an answer on this question), I'll throw in my two cents. I don't think using a formula field is appropriate here. I know ...
Derek F's user avatar
  • 62.4k
6 votes
Accepted

Image formula field in lightning do not display images,

It appears that the way to render text without HTML-escaping is using aura:unescapedHtml. I have not used Lightning, but I'd assume you need to change your page to (assuming OLRS.StatusBar__c is the ...
IllusiveBrian's user avatar
6 votes
Accepted

How to shorten Formula in formula field to stay with character limitations?

The most obvious change off the bat is to collapse your section of the formula using multiple ISPICKVAL/TEXT calls. Instead of: IF( OR( TEXT(Status__c) = "Current", TEXT(...
Adrian Larson's user avatar
  • 150k
6 votes

Can we convert Picklist value into Date format?

DATEVALUE requires the format to be "YYYY-MM-DD". Since your picklist doesn't match that format, you'd need some extensive code: DATEVALUE(RIGHT(TEXT(picklist),4)&"-"&MID(TEXT(picklist),4,2)&...
sfdcfox's user avatar
  • 495k
6 votes
Accepted

Formula field in SOQL - Performance Impact

You should use the Query Plan tool to figure out performance questions for your own org. When I analyzed a nearly identical structure in my own org, there was no difference in performance. It may ...
Adrian Larson's user avatar
  • 150k
6 votes

check if a particular date falls in the current quarter

I am Jeff Du. Please refer below info and get your answer. 1.Get Quarter in Formula: For standard quarters, you can determine which quarter a date falls in using this formula. This formula ...
Jeff Du's user avatar
  • 86
6 votes
Accepted

SOQL returning incorrect value

This is a Known Issue that can occur. It goes way back to the days when the standard profiles were named PTX, where X was a number. They still occasionally end up showing up in odd places, and you've ...
sfdcfox's user avatar
  • 495k
5 votes
Accepted

how do I add 6 months to a date field in a formula?

Adding months to a date is slightly more complicated as months vary in length and the cycle of months restart with each year. Therefore, a valid day in one month (January 31) might not be valid in ...
Saroj Bera's user avatar
  • 14.8k
5 votes
Accepted

Formula Error: Syntax error. Extra ','

The start Bracket ( of IF is missing. Try this one:- IF( (TODAY() > CloseDate && TEXT(StageName)='Donation Likely') || (TODAY() > CloseDate && TEXT(StageName)='...
Ashwani's user avatar
  • 22.6k

Only top scored, non community-wiki answers of a minimum length are eligible