Formulas, Lookup Filters, Custom Labels, and Validation Rules in Salesforce

Up to this point, everything we’ve built is structural — objects, fields, layouts, actions. The data model exists, but it doesn’t enforce anything. A user can leave critical fields blank, enter nonsensical values, select unrelated records in lookup fields, and save without any pushback.

That changes now. Formulas, validation rules, lookup filters, and custom labels are the declarative logic layer of Salesforce. They compute values, enforce business rules, restrict selections, and centralize reusable text — all without writing Apex code.

An Introduction to Formulas in Salesforce

A formula is an expression that computes a value based on other fields, operators, and functions. Formulas are used in multiple places across Salesforce:

• Formula fields — Custom fields whose value is calculated dynamically (read-only, not stored)
• Validation rules — Formulas that evaluate to TRUE when data is invalid
• Workflow rules / Process Builder criteria — (Legacy, but still in many orgs)
• Flow formulas — Calculated values within Flows
• Default field values — Pre-populate a field when a record is created
• Report formulas — Custom calculated columns in reports
• Approval process criteria — Conditions for entering/exiting approval steps

Formula Syntax Basics

Formulas use a combination of field references, operators, functions, and literals.

Field References:

AccountId
Account.Name
CreatedDate
Custom_Field__c
Owner.Profile.Name

• Standard fields use their API name directly
• Custom fields end with __c
• You can traverse relationships using dot notation (up to 10 levels)
• Cross-object references pull values from related records

Operators:

Common Functions:

Text Functions:

• TEXT(value) — Converts a non-text value to text
• VALUE(text) — Converts text to a number
• LEN(text) — Returns the length of a string
• LEFT(text, num) / RIGHT(text, num) — Returns characters from left/right
• MID(text, start, num) — Returns characters from the middle
• SUBSTITUTE(text, old, new) — Replaces occurrences of a string
• TRIM(text) — Removes leading/trailing whitespace
• UPPER(text) / LOWER(text) — Changes case
• CONTAINS(text, search) — Returns TRUE if text contains the search string
• BEGINS(text, search) — Returns TRUE if text starts with the search string
• REGEX(text, pattern) — Returns TRUE if text matches the regex pattern

Date & Time Functions:

• TODAY() — Returns today’s date
• NOW() — Returns current date/time
• DATE(year, month, day) — Constructs a date
• YEAR(date) / MONTH(date) / DAY(date) — Extracts components
• DATEVALUE(expression) — Converts a date/time to a date
• DATETIMEVALUE(expression) — Converts text to date/time

Logical Functions:

• IF(condition, true_value, false_value) — Conditional logic
• CASE(expression, val1, result1, val2, result2, ..., else_result) — Multi-branch switch
• ISBLANK(expression) — Returns TRUE if the field is blank
• ISNULL(expression) — Similar to ISBLANK (use ISBLANK instead — it’s more reliable)
• BLANKVALUE(expression, substitute) — Returns substitute if expression is blank
• NULLVALUE(expression, substitute) — Same but for null (prefer BLANKVALUE)
• PRIORVALUE(field) — Returns the field’s value before the current save (validation rules only)
• ISCHANGED(field) — Returns TRUE if the field’s value changed (validation rules only)
• ISNEW() — Returns TRUE if the record is being created (validation rules only)

Math Functions:

• ABS(number) — Absolute value
• CEILING(number) — Rounds up to nearest integer
• FLOOR(number) — Rounds down to nearest integer
• ROUND(number, places) — Rounds to specified decimal places
• MAX(num1, num2, ...) / MIN(num1, num2, ...) — Returns largest/smallest
• MOD(number, divisor) — Returns remainder of division

Advanced Functions:

• ISPICKVAL(field, value) — Compares a picklist field to a text value (required for picklists — you can’t use = directly)
• INCLUDES(multiselect_field, value) — Checks if a multi-select picklist includes a value
• HYPERLINK(url, label, target) — Creates a clickable link (formula fields only)
• IMAGE(url, alt_text, height, width) — Displays an image (formula fields only)
• VLOOKUP(field, dataset, lookup_field, result_field) — Cross-object lookup (limited use)

Creating a Formula Field

1. Setup → Object Manager → Your Object → Fields & Relationships
2. Click “New”
3. Select “Formula” as the field type
4. Click “Next”
5. Enter the Field Label and Field Name
6. Select the Formula Return Type:
   • Checkbox (Boolean)
   • Currency
   • Date
   • Date/Time
   • Number
   • Percent
   • Text
   • Time
7. Set Decimal Places (for Number, Currency, Percent)
8. Click “Next”
9. Write your formula in the editor

The Formula Editor has two modes:

• Simple Formula — Point-and-click field selection and function insertion
• Advanced Formula — Free-text editor where you type the formula directly

10. Click “Check Syntax” to validate
11. Click “Next” → Set field-level security → Add to page layouts → “Save”

Formula Field Examples

Days Until Due:

IF(
  ISBLANK(Due_Date__c),
  NULL,
  Due_Date__c - TODAY()
)

Returns the number of days until the due date. Returns blank if no due date is set.

Full Address (Text):

IF(ISBLANK(BillingStreet), "", BillingStreet & BR()) &
IF(ISBLANK(BillingCity), "", BillingCity & ", ") &
IF(ISBLANK(BillingState), "", BillingState & " ") &
IF(ISBLANK(BillingPostalCode), "", BillingPostalCode & BR()) &
IF(ISBLANK(BillingCountry), "", BillingCountry)

Priority Score (Number):

CASE(
  TEXT(Priority),
  "Critical", 100,
  "High", 75,
  "Medium", 50,
  "Low", 25,
  0
)

Note the use of TEXT(Priority) — picklist fields must be converted to text before string comparison, or you can use ISPICKVAL().

Status Indicator (Text with Image):

IF(
  ISPICKVAL(Status__c, "Completed"),
  IMAGE("/img/samples/flag_green.gif", "Completed", 16, 16),
  IF(
    ISPICKVAL(Status__c, "In Progress"),
    IMAGE("/img/samples/flag_yellow.gif", "In Progress", 16, 16),
    IMAGE("/img/samples/flag_red.gif", "Not Started", 16, 16)
  )
)

Formula Limits

• Maximum formula size: 3,900 characters (compiled)
• Cross-object references: Up to 10 relationships deep
• Formula fields can’t reference: Long Text Area fields, other formula fields that would create circular references, or fields on unrelated objects
• Formula fields are read-only — they can’t be edited by users
• Formula fields are NOT stored — they’re computed on-the-fly, which means they can’t be used in certain filter criteria and may impact performance on large datasets

What Are Validation Rules?

A validation rule is a formula that evaluates to TRUE when the data is invalid. When a user tries to save a record and a validation rule evaluates to TRUE, the save is blocked and an error message is displayed.

Think of it as: “If this condition is TRUE, something is wrong — don’t save.”

How Validation Rules Work

1. User clicks Save on a record
2. Salesforce evaluates all active validation rules on the object
3. If any rule evaluates to TRUE, the save is blocked
4. The error message you defined is displayed to the user
5. If all rules evaluate to FALSE (or blank/null), the save proceeds

Key Characteristics

• Validation rules fire on every save — whether from the UI, API, Apex, Data Loader, or Flow
• They fire after default values are set but before the record is committed to the database
• Multiple validation rules can fire simultaneously — the user sees all errors at once
• They have access to special functions like PRIORVALUE(), ISCHANGED(), and ISNEW()

Creating a Validation Rule with Formulas

Step-by-Step

1. Setup → Object Manager → Your Object → Validation Rules
2. Click “New”
3. Fill in:

Rule Name — API name (e.g., Require_Phone_or_Email)

Active — Check to enable immediately (or leave unchecked to activate later)

Description — What this rule enforces and why. Future-you will thank present-you for a good description.

Error Condition Formula — The formula that returns TRUE when data is invalid.

4. Write the formula. Click “Check Syntax” to validate.

5. Error Message — The text displayed to the user when the rule fires. Be specific and helpful: tell users WHAT is wrong and HOW to fix it.

6. Error Location:

   • Top of Page — Error appears at the top of the record page (default)
   • Field — Error appears next to a specific field. Select the field from the dropdown. This is better UX — the user sees the error right where the problem is.

7. Click “Save”

Validation Rule Examples

Require Phone or Email on Contact:

AND(
  ISBLANK(Phone),
  ISBLANK(Email)
)

Error message: “Please provide at least a Phone number or Email address.” Error location: Top of Page

This fires when BOTH Phone AND Email are blank. The user must fill in at least one.

Prevent Closing an Opportunity Without Products:

AND(
  ISPICKVAL(StageName, "Closed Won"),
  NOT(HasOpportunityLineItem)
)

Error message: “You cannot close an Opportunity as Won without adding at least one Product.” Error location: Field → StageName

Prevent Backdating:

AND(
  ISNEW(),
  Start_Date__c < TODAY()
)

Error message: “Start Date cannot be in the past for new records.” Error location: Field → Start_Date__c

This only fires on new records (ISNEW()). Existing records can keep their historical dates.

Prevent Reopening Completed Projects:

AND(
  ISCHANGED(Status__c),
  ISPICKVAL(PRIORVALUE(Status__c), "Completed"),
  NOT(ISPICKVAL(Status__c, "Completed"))
)

Error message: “Completed projects cannot be reopened. Please create a new project instead.” Error location: Field → Status__c

This uses ISCHANGED() and PRIORVALUE() to detect when someone tries to change Status away from “Completed.”

Email Format Validation:

AND(
  NOT(ISBLANK(Email__c)),
  NOT(REGEX(Email__c, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"))
)

Error message: “Please enter a valid email address (e.g., name@example.com).” Error location: Field → Email__c

Validation Rule Best Practices

1. Write the error message for the user, not yourself. “Validation failed” is useless. “Please enter a Close Date that is today or later” is helpful.

2. Use field-level error locations when the error relates to a specific field. This guides the user’s eye directly to the problem.

3. Account for blank fields. Many formulas behave unexpectedly with blank values. Always wrap comparisons with ISBLANK() checks if the field might be empty.

4. Use ISNEW() and ISCHANGED() to scope rules. Don’t block existing records from saving just because they have legacy data that doesn’t meet new rules. Use ISNEW() to apply rules only to new records, or ISCHANGED() to only validate when a field actually changes.

5. Test with the API, not just the UI. Validation rules fire on API saves too. Test with Data Loader to ensure bulk imports won’t be blocked unexpectedly.

6. Document the “why” in the description. Six months from now, someone will want to deactivate a rule that’s blocking their import. A good description explains why the rule exists, so they can make an informed decision.

What Are Lookup Field Filters?

A lookup filter restricts which records a user can select in a lookup field. Without a filter, a lookup field shows ALL records of the target object. With a filter, you can narrow it to only relevant records.

The Problem They Solve

Imagine a “Project Manager” lookup on the Project object that points to the User object. Without a filter, users can select ANY user — including inactive users, external community users, or the system integration user. A lookup filter can restrict it to only active, internal users.

How to Create a Lookup Filter

1. Setup → Object Manager → Your Object → Fields & Relationships

2. Click on the lookup field you want to filter

3. Scroll down to the “Lookup Filter” section

4. Click “New” (or “Edit” if one exists)

5. Filter Criteria: Build conditions using the format: Field Operator Type Value

   Field — A field on the lookup target object (the object being looked up)

   Operator — equals, not equal to, less than, greater than, contains, starts with, etc.

   Type:

   • Value — A static value (e.g., “Active”)
   • Field — A field on the source record (the record containing the lookup)
   • User — A field on the current user’s record

   Examples:

   • User: Active equals Value: True — Only show active users
   • Account: BillingCountry equals Field: BillingCountry — Only show Accounts in the same country as the current record
   • Owner: Role equals User: Role — Only show records owned by users in the same role as the current user

6. Filter Type:

   • Required — Users CANNOT save a value that doesn’t meet the filter criteria. The filter is enforced.
   • Optional — The filter narrows the search results but users can still manually enter a value that doesn’t match. More of a convenience.

7. Error Message — Custom message shown when a user tries to save a value that doesn’t meet a required filter.

8. Click “Save”

Lookup Filter Examples

Active Users Only (on any User lookup):

• Field: User: Active equals Value: True
• Filter Type: Required
• Error: “Please select an active user.”

Accounts in the Same Country:

• Field: Account: Billing Country equals Field: Source Object: Country
• Filter Type: Optional (helps users find relevant accounts, doesn’t enforce)

Only Open Cases (on a Case lookup):

• Field: Case: Is Closed equals Value: False
• Filter Type: Required
• Error: “You can only link to open Cases.”

Lookup Filter Limitations

• Maximum 10 filter criteria per lookup filter
• Can only reference fields on the lookup target object, the source object, and the current user
• Cannot use formula fields in filter criteria (with some exceptions)
• Cross-object references are limited — you can’t traverse multiple relationships in the filter
• Lookup filters on standard lookup fields have additional restrictions compared to custom lookups
• Performance impact — Complex filters on large datasets can slow down the lookup search

What Are Custom Labels?

Custom labels are reusable text values that can be referenced across formulas, validation rules, Apex code, Visualforce pages, Lightning components, and Flows.

Why Use Custom Labels?

1. Centralized text management. If the same error message or text appears in multiple validation rules, you define it once as a custom label and reference it everywhere. When you need to change it, you change it in one place.

2. Translation support. Custom labels can be translated into different languages. When a user’s language preference is different, Salesforce automatically displays the translated version.

3. Avoid hardcoding. In Apex code, hardcoded strings are fragile. Custom labels can be updated by admins without deploying code.

How to Create Custom Labels

1. Setup → Quick Find → “Custom Labels”
2. Click “New Custom Label”
3. Fill in:
   • Short Description — The label name (used to reference it)
   • Name — API name (auto-generated)
   • Description — What this label is used for
   • Category — Optional grouping for organization
   • Value — The actual text content
   • Protected Component — Check if this is part of a managed package
4. Click “Save”

How to Reference Custom Labels

In Formulas / Validation Rules:

$Label.My_Custom_Label

In Apex:

String msg = System.Label.My_Custom_Label;

In Lightning Web Components (HTML):

<lightning-button label={label.My_Custom_Label}></lightning-button>

In Lightning Web Components (JS):

import myLabel from '@salesforce/label/c.My_Custom_Label';

In Visualforce:

{!$Label.My_Custom_Label}

Custom Label Best Practices

• Use descriptive short descriptions. Error_Phone_Required is better than Label1.
• Use categories to group related labels: “Validation_Errors,” “UI_Labels,” “Email_Templates.”
• Don’t over-use them. If a text string is only used in one place and doesn’t need translation, a custom label adds unnecessary indirection. Use them when text is shared across multiple locations or needs to be translatable.
• Maximum 5,000 custom labels per org.
• Maximum 1,000 characters per label value.

Project: Create a Validation Rule on the Account Object

The Task

Create a validation rule on the Account object that prevents the record from being saved without the Phone and Email fields being filled out.

Step-by-Step Solution

1. Navigate to the Account validation rules:

Setup → Object Manager → Account → Validation Rules → New

2. Configure the rule:

• Rule Name: Require_Phone_And_Email
• Active: Checked
• Description: “Ensures that every Account has both a Phone number and an Email address filled in. Required for communication and compliance purposes.”

3. Error Condition Formula:

OR(
  ISBLANK(Phone),
  ISBLANK(Email__c)
)

This formula returns TRUE (invalid) when EITHER Phone OR Email is blank. Both must be filled in.

Note: The standard Account object doesn’t have an Email field by default. You may need to create a custom Email field (Email__c) first, or adjust the formula to use a different field like Website or a custom field your org uses.

4. Error Message:

“Both Phone and Email are required. Please fill in both fields before saving.”

5. Error Location:

Since the error involves two fields, use “Top of Page” — putting the error on one specific field would be misleading.

6. Save and Test:

• Try to save an Account with both Phone and Email filled in — should succeed
• Try to save with Phone blank — should fail with the error message
• Try to save with Email blank — should fail
• Try to save with both blank — should fail
• Test via the API (Data Loader) to confirm it enforces there too

Bonus: Make It Smarter

If you want to only enforce this on new records (so existing Accounts without email aren’t blocked from any edits):

AND(
  ISNEW(),
  OR(
    ISBLANK(Phone),
    ISBLANK(Email__c)
  )
)

Or, enforce only when the fields are being changed (so users who aren’t touching Phone/Email can still save):

OR(
  AND(
    ISCHANGED(Phone),
    ISBLANK(Phone)
  ),
  AND(
    ISCHANGED(Email__c),
    ISBLANK(Email__c)
  )
)

This prevents users from clearing out Phone or Email, but doesn’t block saves when those fields aren’t being modified.

Next up: Lightning App Builder — the visual tool for designing record pages, home pages, and app pages with drag-and-drop components, dynamic forms, and conditional visibility.

This is Part 7 of the Salesforce series. Next: Lightning App Builder — moving beyond page layouts to build fully customized Lightning pages with components, dynamic forms, and dynamic actions.