A Checkbox Formula is a logical statement that is either True or False. With checkboxes, we have two options – selected or not selected.
We’ll be implementing checkboxes using Formula Fields, so if you aren’t familiar with them, get started on Formula Fields before moving forward.
If we want to know whether an opportunity is a Large Opportunity (it has more than 200 employees and expected revenue of more than $100,000) on the spot, we can use this checkbox formula!
AND ( Account.NumberOfEmployees > 200, ExpectedRevenue >100000 )
If an Opportunity has more than 200 employees and an expected revenue of more than $100,000, the checkbox Large Opportunity is checked on that Opportunity’s detail page. Otherwise, the checkbox is not selected.
Before we jump into creating more complex formulas, let’s acquaint ourselves with some basic logical operators!
Basic Logical Operators
If you take a closer look, you’ll notice that logical functions are a part of our everyday dialogue. Let’s say you’re at your favorite grocery store. We know that a carrot is an orange vegetable and a cucumber is a green vegetable. If you’re looking for something that’s a vegetable AND orange in color, you’ll go for the carrot, but if you’re looking for something orange in color OR a vegetable, you can go for either a carrot or a cucumber. Logical functions are analogous to these conversations, but a stricter adaptation of them.
Let’s get to know the following logical operators for now – AND, OR, NOT, and IF.
The built-in AND() function requires a least two arguments. It returns TRUE only when both arguments are TRUE and returns FALSE if at least one argument is FALSE.
For a better understanding, let’s take a look at the Truth Table for AND :
Let’s say you want Users to look at a Contact and know immediately if it’s okay to get in touch with them via a call or email. If the contact has marked Email Opt Out AND Do Not Call, we want the checkbox, Do Not Contact, to be selected.
Let’s use the AND function to create this checkbox-formula field :
1. From Setup, open Object Manager and select Contact.
2. Open Fields & Relationships from the sidebar.
3. Click New.
4. Select Formula for Field Type, and then click Next.
5. For the Field Label, enter Do Not Contact.
6. For Formula Return Type, select Checkbox.
7. Click Next.
8. Enter the following code into the formula editor ( Make sure you’re using the Advanced Formula Editor):
AND ( DoNotCall , HasOptedOutOfEmail )
9. Click Next, Next, and Save.
Open any Contact Record to view this field under the details tab. This checkbox is selected only if the contact has selected both Email Opt Out and Do Not Call, If the contact has selected only Do Not Call, the checkbox is not selected.
Let’s take a look at the 4 possible outputs :
NOTE: We can create a Formula Field as a custom field or a validation rule. A validation rule is helpful when you want to prevent a user from saving a record unless all the data entered meets the requirements you’ve placed on them
We can create an AND() function by linking two statements together using &&. If we use &&, the formula, Do Not Contact, would look like this :
DoNotCall && HasOptedOutOfEmail
The AND() operator is not limited to two arguments, for example:
AND ( DoNotCall , HasOptedOutOfEmail, HasOptedOutOfFax )
In this case, Do Not Contact is selected only when all three arguments are TRUE.
The OR() function, like the AND() function, requires at least two arguments, but the OR() function returns TRUE if at least one argument is TRUE and returns FALSE only if all arguments are FALSE.
Let’s take a look at the truth table for the OR() function.
Let’s say we want our Users to know right away if a Contact is at the executive level of their organization. Let’s create a checkbox for the Contact details page that’s selected only if the contacts title begins with the word “Chief” (Chief Executive Officer, Chief Operating Officer, etc) OR if their title includes the word “President” ( President, Vice President, etc.).
We’ll be using two text functions to implement this formula:
- BEGINS(text, compare_text) – Checks if a text begins with the compare_text, and if it does, returns TRUE; otherwise, FALSE.
- CONTAINS(text, compare_text) – Checks if a text contains the compare_text, if it does, returns TRUE. Otherwise, FALSE.
Use the following formula to create a formula field with the type checkbox and name Executive?.
OR ( BEGINS(Title , "Chief") , CONTAINS( Title , "President") )
The output should look like this:
We can also create OR() statements by linking two arguments together using ||. If we use || for the formula field, Executive?, it would look like this:
BEGINS(Title , "Chief") || CONTAINS( Title , "President")
Just as with AND(), the OR() function can take more than two arguments. Let’s format the formula we created to check if the Title contains the word “Executive”. For Example:
OR ( BEGINS(Title, "Chief"), CONTAINS(Title, "President"), CONTAINS(Title, "Executive") )
The NOT() function returns the opposite of a given logical or Boolean value. It changes TRUE to FALSE, and FALSE to TRUE.
The Truth Table for the NOT() function is quite simple:
Let’s say you want to display a checkbox on the Account Details page that lets you know right away if the Account card is lacking vital information. We’ll create a checkbox formula field, Complete, that’s selected if the Account information is complete. Otherwise, it’s not selected. The essential fields include → Account Name, Account Number, Billing Address and Employees.
Let’s use the function ISBLANK() to determine if a field is blank, if it is blank, the function returns TRUE.
The formula would look like this:
NOT ( ISBLANK( Name ) || ISBLANK( AccountNumber ) || ISBLANK( BillingAddress ) || ISBLANK( NumberOfEmployees ) )
The formula returns a TRUE only if none of these fields is blank. If any field is blank, the formula returns a FALSE, and the checkbox is not selected.
We can see from the Account detail page below that the checkbox, Complete, was selected only after all essential fields were filled.
The NOT() function can also be replaced by an exclamation point!
Using the exclamation point, our formula would look like this :
!( ISBLANK( Name ) || ISBLANK( AccountNumber ) || ISBLANK( BillingAddress ) || ISBLANK( NumberOfEmployees ) )
The IF() statement takes three arguments in the format IF(test, result, alternate). If the test is TRUE, evaluate the result. Otherwise, evaluate alternate.
The IF() statement can replace a chain of AND() and OR(), making it a convenient alternative.
Remember the example, Large Opportunity? Scroll back up, and you’ll know what I’m talking about.
AND ( Account.NumberOfEmployees > 200, ExpectedRevenue >100000 )
Let’s replace that formula with the IF() statement.
IF( Account.NumberOfEmployees > 200, Expected Revenue >100000, false )
We can also nest IF() statements. An IF() statement inside another IF() statement!
For example, let’s say a primary contact is someone who is an executive and, additionally, is either in the Finance Department in New Delhi or the Sales Department in Mumbai.
We can determine the city a contact is based on with the help of the area code in their phone number. 415 represents New Delhi and 212 represents Mumbai ( We’ll be using the formula field we created, Executive?, if you haven’t already created that formula field, go back and complete it before proceeding).
Let’s create a checkbox that’s selected if the contact is a primary contact. Using ANDs and ORs, the formula would look like this:
Department = "Finance" && Executive__c && BEGINS(Phone, "(415)") || Department = "Sales" && Executive__c && BEGINS(Phone, "(212)")
Let’s implement this formula using an IF() statement. To help us visualize the formula let’s use a decision tree :
First, we’ll check if the contact is an executive. If the contact is identified as an executive, we then check for the department such that it is Finance. If that’s true too, we check the phone area code(415).
On the other hand, if the contact is identified as an executive, but the department isn’t Finance, we then check if the department is Sales. If that’s true, we check the area code for Mumbai (212).
Each branch of this tree corresponds to a condition in the IF() statement. Let’s write the formula now!
IF( Executive__c , IF( Department = "Finance", BEGINS(Phone, "415"), IF( Department = "Sales", BEGINS(Phone, "212"),false)),false )
Checkbox Formula Examples
1. Top Priority – Case
This formula marks a case as Top Priority in case the priority is high and the case is new OR if the case is working and it is either escalated, or it has been over seven days since the last update.
We’ll be using a new function here, ISPICKVAL :
ISPICKVAL(picklist_field, text_literal) – Determines if the value of the picklist field is equal to the text literal specified.
AND( ISPICKVAL(Priority, "High"),ISPICKVAL (Status, "New") ) || AND( ISPICKVAL (Status, "Working") ,(Is Escalated || Days_Since_Update__c > 7))
2. RED – Case
Marks a case as RED based on a case age custom field.
IF ( DaysOpen__c > 20, true, false)
3. Discounted 5% – Opportunity
If the total Product price of an Opportunity is greater than $20,00, offer a Discount of 5%.
IF ( total_product_price__c > 20000, true, false )