trailhead

Use Basic Logic in Checkbox Formulas

Learning Objectives

After completing this unit, you’ll be able to:
  • Describe what the checkbox formula return type is.
  • Comfortably use the IF(), AND(), NOT(), and OR() functions.
  • Describe use cases for formulas with the checkbox type.
  • Create a formula using the checkbox type.

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.

AND()

The built-in AND() function takes at least two arguments. It returns true if and only if both arguments are true. It’s easy to visualize this logic if we look at it in a table.
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.

  1. From Setup, enter Contacts in the quick find box and select Contacts | Fields.
  2. Scroll to the Contact Custom Fields & Relationships Section and click New.
  3. Select Formula and click Next.
  4. In Field Label, enter Do Not Contact. Field Name populates automatically.
  5. Select Checkbox and click Next.
  6. 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:

    The Do Not Contact checkbox formula

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.

As always, make sure that you’re using the Advanced Formula Editor to enter your formula. You can access the Advanced Formula Editor by selecting the Advanced Formula tab from the formula editor. The Advanced Formula Editor gives you easy access to a wide range of functions and operators that you’ll need throughout this module.
Note

Note

Keep in mind that you can create a formula field as a custom field, but you can also create a formula field as a validation rule. A validation rule lets you prevent users from saving records with invalid data according to your formula. Validation rules can be an effective way to enforce data quality, but with great power comes great responsibility. The last thing you want is unhappy users who can’t save records. So use your power wisely. Learn more about validation rules here.

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.

Note

Note

There’s more than one way to write the AND() function. You can write it as a function with AND() or as an operator by linking two statements with &&. In this case, AND(DoNotCall, HasOptedOutOfEmail) is equivalent to DoNotCall && HasOptedOutOfEmail. Make sure not to mix up &&, which replaces AND(), with &, which concatenates two text strings.

If you want to check more than just two conditions, don’t worry—the AND() operator isn’t limited to two. To display a contact with the Do Not Contact box checked on the condition that the person has selected Do Not Call, Email Opt Out, and Fax Opt Out is no problem for the AND() operator.
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.

OR()

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.
Create a formula field with the type Checkbox and the name Is Executive. Enter the following formula.
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 Title contains the word "Executive", try:
BEGINS(Title, "Chief") ||
CONTAINS(Title, "President") ||
CONTAINS(Title, "Executive")

NOT()

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.

To check if the contact has the information that we’re looking for, use the ISBLANK() function. ISBLANK() takes a field as an argument and returns true if it’s blank, and false if it’s filled.
NOT(
  ISBLANK(FirstName) || 
  ISBLANK(LastName) ||
  ISBLANK(Phone) ||
  ISBLANK(Email) ||
  ISBLANK(MailingAddress)
)
Note

Note

Because spaces and returns between functions and operators don’t matter in formulas, the line breaks and spaces shown here aren’t necessary. However, using line breaks and spaces in your formula makes it easier to read. It also helps anyone else who has to understand or update the formula later.

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.

Note that NOT() can also be written as a simple exclamation point. Using this notation, our formula would look a little different, but have the same effect.
!(ISBLANK(FirstName) || 
ISBLANK(LastName) ||
ISBLANK(Phone) ||
ISBLANK(Email) ||
ISBLANK(MailingAddress))

IF()

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)
You can apply the same if-based logic in your formulas. For example, you want to know if a contact is a primary contact. We define a primary contact as someone who is an executive (using the formula field Is Executive that we created before) and is in the Marketing department. You already know how to do this with ands and ors.
Is_Executive__c && Department = "Marketing"
Using an IF() statement, the formula looks like this:
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.

We figure out which city the contact is based in by looking at the area code of the phone number, with 415 corresponding to San Francisco and 212 corresponding to New York. Using ands and ors, the formula looks like this:
Department = "Marketing" && 
Is_Executive__c &&
BEGINS(Phone, "(415)") ||
Department = "Sales" &&
Is_Executive__c &&
BEGINS(Phone, "(212)")
Note

Note

Logical operators, just like mathematical operators, have an order of operation. AND() is always evaluated before OR(). You can also group logical operators in parentheses to have them evaluated in a particular order.

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

A choice tree representing our logical process
Each branch of the tree corresponds to a condition in an IF() statement of our formula.
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

  1. 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)
  2. 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
  3. 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)
retargeting