Use Basic Logic in Checkbox Formulas
Introduction to Formula Fields
Formulas are a powerful tool that let you perform calculations and other operations on your organization’s data. Using formulas, you can embed hyperlinks, perform arithmetic with existing fields, or use conditional logic to display an amount. If you’ve never used formulas before, get started with Using Formulas Fields before moving on to this unit.
Introduction to the Checkbox Type in Formulas
When it comes to checkboxes, you only have two options: selected or not selected. What does this mean for your formula? Well, a checkbox formula is just a logical statement that’s either true or false. If you want to know right away if an account has at least 500 employees on it, for example, you can use a simple checkbox formula.
NumberOfEmployees >= 500
If an account has more than or equal to 500 employees, the checkbox Large Account is checked on that account’s page. Otherwise, Large Account is not checked.
Most of the time, the logical statements in your formula are a lot more complicated than that. To take full advantage of the checkbox formula data type, you need some basic logical tools.
Basic Logical Operators
Logical functions are more rigid adaptations of words that you already use in almost every conversation: and, or, not, and if. You know, for example, that an apple is a red fruit, while a banana is a yellow fruit. If you’re looking in the grocery store for something that’s red and a fruit, you’re going to grab an apple. If you’re looking for something that’s red or a fruit, however, you’d be fine with either an apple or a banana.
You can use the same kind of logic in formulas with the NOT(), AND(), OR(), and IF() functions.
|First Argument = True||First Argument = False|
|Second Argument = True||True||False|
|Second Argument = False||False||False|
For instance, say you want your users to look at a contact and know right away whether it’s okay to contact them via email or phone. If the contact in question has marked Do Not Call and Email Opt Out, we want to select another checkbox, Do Not Contact . Because we want this checkbox to be checked only if the contact selected Do Not Call and Email Opt Out, we use the AND() function.
Create a checkbox formula the same way that you would create any other formula field in Salesforce.
- From Setup, enter Contacts in the quick find box and select Contacts | Fields.
- Scroll to the Contact Custom Fields & Relationships Section and click New.
- Select Formula and click Next.
- In Field Label, enter Do Not Contact. Field Name populates automatically.
- Select Checkbox and click Next.
- Copy the following code snippet and paste it into the formula editor.
AND(DoNotCall, HasOptedOutOfEmail)This is how it’ll look in the formula editor once you’re done:
A quick note. The formula samples we’re giving you throughout this module will range from relatively simple to very complex. To make it easier for you, we’ll be giving you all the formula samples as snippets rather than screenshots of how they’ll look in the formula editor. This will allow you to copy and paste the snippet right into the formula editor. To copy the snippet, just hover over the snippet until three icons appear in the upper right corner. Then use the "copy to clipboard" icon to copy the snippet.
Using this formula, if someone has selected Do Not Call and Email Opt Out, the contact card displays another selected checkbox, Do Not Contact . If the person selected only one of the two, the Do Not Contact box is not checked.
AND(DoNotCall, HasOptedOutOfEmail, HasOptedOutOfFax)
Using this formula, the Do Not Contact checkbox is checked only if all three checkboxes—Do Not call, Do Not Email, and Do Not Fax —are selected. If any one (or both or all three) is not checked, Do Not Contact appears unchecked on the contact’s page.
The OR() function is similar to the AND() function. It also takes at least two arguments. But unlike AND(), OR() returns true if at least one of the conditions is true, and only returns false if all arguments are false.
|First Argument = True||First Argument = False|
|Second Argument = True||True||True|
|Second Argument = False||True||False|
Let’s look at another example with the contact object. Say that you want to display a checkbox that’s selected if a contact is at the executive level of an organization. We know someone’s at the executive level if their title begins with "Chief" (such as Chief Executive Officer, Chief Operating Officer, etc.) or includes the word "President" (such as President, Vice President, etc.). Notice how we’re already using the word or to describe the problem. This is a good clue that you need the OR() operator in this formula.
To make this formula work, we’re going to use a couple of text functions that you can learn more about in Using Text Formulas:
- BEGINS(text, compare_text) tells you if a string (text) begins with another string (compare_text).
- CONTAINS(text, compare_text) tells you if a string (text) contains another string (compare_text) anywhere inside it.
OR(Begins(Title, "Chief"), CONTAINS(Title, "President"))
Just as with the AND() function, you can write OR() more than one way. You can write BEGINS(Title , "Chief") || CONTAINS(Title , "President").OR(), like AND(), can take more than two arguments. To also check if a contact’s Titlecontains the word "Executive", try:
BEGINS(Title, "Chief") || CONTAINS(Title, "President") || CONTAINS(Title, "Executive")
The NOT() function does exactly what you’d expect: It changes anything that’s true to false, and anything that’s false to true. Say that you want to display a checkbox on a contact’s page that indicates if the contact card is lacking essential information. For this example, we consider a contact complete if it includes a first name, last name, phone number, email address, and mailing address. The formula evaluates to true only if none of those fields are blank.
NOT( ISBLANK(FirstName) || ISBLANK(LastName) || ISBLANK(Phone) || ISBLANK(Email) || ISBLANK(MailingAddress) )
And there you have it! You can test out this formula by looking at a contact in your organization. If all the relevant contact information is complete, the contact is marked as complete, and the Contact Complete checkbox that you created is selected. If one or more of the fields we checked is blank, the checkbox is unchecked.
!(ISBLANK(FirstName) || ISBLANK(LastName) || ISBLANK(Phone) || ISBLANK(Email) || ISBLANK(MailingAddress))
Instead of stringing together logical operators like AND() and OR(), you could use IF() statements. IF() takes three arguments, in the format IF(test, result, alternate). In layman’s terms: If test is true, evaluate result. Else, evaluate alternate.
So you’re back at the grocery store, and once again you’re looking for a red fruit. You know how to write out this scenario with AND() and OR(), but you can also think about it in terms of IF() statements. First, you pick up a piece of produce and check if it’s a fruit. If it is, you check if it’s red. If you wrote a produce-picking formula using IF() statements, your decision-making process would look like this:
IF(Mystery_Produce = "Fruit", Mystery_Produce_Color = "Red", false)
Is_Executive__c && Department = "Marketing"
IF(Is_Executive__c, Department = "Marketing", false)
You can nest IF() statements. Any argument within an IF() statement can be another logical statement, including another IF() statement. For this example, we define a primary contact as someone who is an executive, and in addition to that, is either in the Marketing Department in San Francisco or the Sales Department in New York. We’re going to create a checkbox that is selected only if these conditions are met.
Department = "Marketing" && Is_Executive__c && BEGINS(Phone, "(415)") || Department = "Sales" && Is_Executive__c && BEGINS(Phone, "(212)")
We want a formula that is true if a contact meets all the requirements to be a primary contact. First, we want to check if the contact is an executive using the Is Executive checkbox we created earlier. If the contact is an executive, we then check if the department is Sales. If that’s true, too, then the last thing we want to check is the phone number’s area code. On the other hand, if the contact is an executive but the department isn’t Sales, we check if it’s Marketing. If that’s the case, we check if the phone number has a San Francisco area code. It’s often easier to visualize a series of IF() statements with a choice tree. Drawing one out is a great first step to take when you’re writing a formula that uses IF().
IF(Is_Executive__c, IF(Department = "Sales", BEGINS(Phone, "(212)"), False), IF(Department = "Marketing", BEGINS(Phone, "(415)"), False))
In formulas like this one, it’s especially important to include clear line breaks and spaces. Then, whoever is reading your formula later knows how your IF() statements are nested.
Checkbox Formula Examples
- This checkbox formula designates an opportunity as small if the probability is less than 50% (0.5), and the expected revenue is less than 10,000.
AND(Probability < 0.5, ExpectedRevenue < 10000)
- This formula marks a case as top priority if the priority is marked as high, the case isn’t closed, and it is either escalated or has not been updated in more than a week. This formula performs differently based on where the parentheses are placed.
ISPICKVAL(Priority, "High") && NOT(IsClosed) && (IsEscalated || Days_Since_Last_Update__c > 7)This formula, for example uses slightly differently positioned parentheses to check if a case is high priority, closed, and escalated, or it’s been over seven days since the case was last updated.
(ISPICKVAL(Priority, "High") && NOT(IsClosed) && IsEscalated)|| Days_Since_Last_Update__c > 7
- This formula displays a checkbox, Okay to Call, that is checked if it is okay to call a contact. The formula checks if the contact has selected Do Not Call. If the contact hasn’t, the formula ensures that the phone number field isn’t blank.
IF(NOT(DoNotcall), NOT(ISBLANK(Phone)), false)