Key Characteristics

• Calculated Fields: Values computed automatically, not entered manually
• Dynamic: Update in real-time when source data changes
• Read-Only: Users cannot directly edit equation fields
• Powerful: Support complex calculations and transformations
• Flexible: Combine multiple fields and operations

Why Use Equations?

• Automation: Calculate values without manual entry
• Accuracy: Eliminate human calculation errors
• Consistency: Same formula applies to all records
• Efficiency: Save time on repetitive calculations
• Intelligence: Create smart, responsive data

Common Equation Use Cases

• Calculate totals, subtotals, and taxes
• Combine first and last names into full name
• Calculate age from birth date
• Determine days until due date
• Format phone numbers or addresses
• Calculate discounts and final prices
• Determine project duration
• Create formatted display values

1. Equation – Text

• Returns: Text/string values
• Use For: Combining text, formatting strings, text manipulation
• Examples: Full name, formatted address, concatenated values

2. Equation – Number

• Returns: Numeric values
• Use For: Math calculations, counting, averaging
• Examples: Total price, quantity calculations, percentages

3. Equation – Date

• Returns: Date or date/time values
• Use For: Date calculations, adding/subtracting days
• Examples: Due date, age calculation, duration

Field References

Reference other fields using curly braces:

• {field_name} - References field value
• Field names are case-sensitive
• Use exact field name including spaces
• For fields with spaces: {First Name}

Operators

Text in Formulas

Use quotes for literal text:

• Single quotes: 'Hello'
• Double quotes: "World"
• Concatenate with fields: {First Name} + ' ' + {Last Name}

Functions

Functions perform specific operations:

• Function name followed by parentheses
• Parameters inside parentheses
• Example: UPPER({Name})
• Multiple parameters separated by commas
• Example: IF({Status} = 'Active', 'Yes', 'No')

Concatenation (Combining Text)

Combine multiple text fields:

Basic Concatenation

{First Name} + ' ' + {Last Name}

Result: "John Smith"

Full Address

{Street} + ', ' + {City} + ', ' + {State} + ' ' + {ZIP}

Result: "123 Main St, Boston, MA 02101"

Formatted Display

'Order #' + {Order Number} + ' - ' + {Customer Name}

Result: "Order #12345 - Acme Corp"

Text Functions

UPPER() - Convert to Uppercase

UPPER({Company Name})

"Acme Corp" → "ACME CORP"

LOWER() - Convert to Lowercase

LOWER({Email})

"John@Example.COM" → "john@example.com"

PROPER() - Title Case

PROPER({Product Name})

"premium widget" → "Premium Widget"

TRIM() - Remove Extra Spaces

TRIM({Text Field})

" Hello " → "Hello"

LEFT() - Extract Left Characters

LEFT({Phone}, 3)

"555-123-4567" → "555"

RIGHT() - Extract Right Characters

RIGHT({Phone}, 4)

"555-123-4567" → "4567"

MID() - Extract Middle Characters

MID({Phone}, 5, 3)

"555-123-4567" → "123"

LEN() - Count Characters

LEN({Description})

"Hello World" → 11

SUBSTITUTE() - Replace Text

SUBSTITUTE({Phone}, '-', '')

"555-123-4567" → "5551234567"

Text Formula Examples

IF({Title} != '', {Title} + ' ', '') + {First Name} + ' ' + {Last Name}

'(' + LEFT({Phone}, 3) + ') ' + MID({Phone}, 4, 3) + '-' + RIGHT({Phone}, 4)

LOWER({First Name}) + '.' + LOWER({Last Name}) + '@company.com'

LEFT({First Name}, 1) + LEFT({Last Name}, 1)

Basic Arithmetic

Addition

{Subtotal} + {Tax} + {Shipping}

Calculate total price

Subtraction

{List Price} - {Discount}

Calculate sale price

Multiplication

{Quantity} * {Unit Price}

Calculate line total

Division

{Total Cost} / {Quantity}

Calculate unit cost

Order of Operations

Use parentheses to control calculation order:

{Quantity} * ({Price} + {Tax})

Calculates: (Price + Tax) first, then multiplies by Quantity

({Subtotal} - {Discount}) * {Tax Rate}

Calculates: Subtotal minus Discount, then applies tax rate

Number Functions

ROUND() - Round Number

ROUND({Total}, 2)

12.3456 → 12.35 (rounds to 2 decimal places)

FLOOR() - Round Down

FLOOR({Price})

12.95 → 12

CEILING() - Round Up

CEILING({Price})

12.01 → 13

ABS() - Absolute Value

ABS({Balance})

-50 → 50

MIN() - Minimum Value

MIN({Price1}, {Price2}, {Price3})

Returns lowest value

MAX() - Maximum Value

MAX({Price1}, {Price2}, {Price3})

Returns highest value

IF() - Conditional Logic

IF({Quantity} > 10, {Price} * 0.9, {Price})

Apply 10% discount if quantity over 10

Number Formula Examples

ROUND(({Quantity} * {Unit Price}) * (1 + {Tax Rate}), 2)

IF({Discount Percent} > 0, {Subtotal} * ({Discount Percent} / 100), 0)

ROUND((({Selling Price} - {Cost}) / {Selling Price}) * 100, 2)

IF({Quantity} >= 100, {Price} * 0.8,
   IF({Quantity} >= 50, {Price} * 0.9,
      IF({Quantity} >= 10, {Price} * 0.95, {Price})))

IF({Weight} 

Adding/Subtracting Days

Add Days

{Start Date} + 30

Adds 30 days to start date

Subtract Days

{End Date} - 7

Subtracts 7 days from end date

Calculate Duration

{End Date} - {Start Date}

Returns number of days between dates

Date Functions

TODAY() - Current Date

TODAY()

Returns current date

NOW() - Current Date and Time

NOW()

Returns current date and time

YEAR() - Extract Year

YEAR({Birth Date})

Returns year from date

MONTH() - Extract Month

MONTH({Order Date})

Returns month number (1-12)

DAY() - Extract Day

DAY({Event Date})

Returns day of month (1-31)

WEEKDAY() - Day of Week

WEEKDAY({Date})

Returns day of week (1=Sunday, 7=Saturday)

DATEADD() - Add Time Period

DATEADD({Start Date}, 3, 'month')

Adds 3 months to start date

DATEDIFF() - Date Difference

DATEDIFF({End Date}, {Start Date}, 'day')

Returns days between dates

Date Formula Examples

FLOOR((TODAY() - {Birth Date}) / 365.25)

{Order Date} + 30

{Deadline} - TODAY()

IF({Due Date} 

IF(MONTH({Date}) 

IF(WEEKDAY({Date}) = 1 OR WEEKDAY({Date}) = 7, 'Weekend', 'Weekday')

DATEADD(DATEADD({Date}, 1, 'month'), -DAY(DATEADD({Date}, 1, 'month')), 'day')

IF() Syntax

IF(condition, value_if_true, value_if_false)

Comparison Operators

Logical Operators

Nested IF Statements

Use multiple IF() statements for complex logic:

IF({Score} >= 90, 'A',
   IF({Score} >= 80, 'B',
      IF({Score} >= 70, 'C',
         IF({Score} >= 60, 'D', 'F'))))

Conditional Examples

IF({Paid} = true, 'Paid', 'Unpaid')

IF({Days Overdue} > 30, 'Critical',
   IF({Days Overdue} > 14, 'High',
      IF({Days Overdue} > 7, 'Medium', 'Normal')))

IF({Total} >= 50, 0, IF({Weight} 

IF({Stock} > 100, 'In Stock',
   IF({Stock} > 0, 'Low Stock',
      IF({Backorder Date} != '', 'Available ' + {Backorder Date}, 'Out of Stock')))

Syntax

{Connection Field.Field Name}

Examples

Customer Name from Order

{Customer.Company Name}

Product Price in Line Item

{Product.Price} * {Quantity}

Manager Email

{Employee.Manager.Email}

Multi-level connection reference

Use Cases

• Pull customer details into order
• Show product information in line items
• Display parent project data in tasks
• Include manager info for employee
• Show vendor details in purchase orders

Pattern 1: Line Item Total

ROUND({Quantity} * {Product.Price}, 2)

Pattern 2: Tax Calculation

ROUND({Subtotal} * {Tax Rate}, 2)

Pattern 3: Grand Total

ROUND({Subtotal} + {Tax} + {Shipping} - {Discount}, 2)

Pattern 4: Full Name

TRIM({First Name} + ' ' + IF({Middle Name} != '', {Middle Name} + ' ', '') + {Last Name})

Pattern 5: Full Address

TRIM({Street} + '\n' + {City} + ', ' + {State} + ' ' + {ZIP} + IF({Country} != 'USA', '\n' + {Country}, ''))

Pattern 6: Age from Birthdate

FLOOR((TODAY() - {Birth Date}) / 365.25)

Pattern 7: Days Until Event

{Event Date} - TODAY()

Pattern 8: Percent Complete

IF({Total Tasks} > 0, ROUND(({Completed Tasks} / {Total Tasks}) * 100, 0), 0)

Pattern 9: Display Status

IF({Active} = true, '✓ Active', '✗ Inactive')

Pattern 10: Dynamic Greeting

'Welcome back, ' + {First Name} + '! Your last login was ' + {Last Login Date}

Clarity

• Use descriptive field names
• Add comments for complex formulas
• Break very complex formulas into multiple fields
• Document formula logic

Accuracy

• Test formulas thoroughly
• Handle edge cases (empty fields, zeros, nulls)
• Use ROUND() for currency
• Validate results against expected values

Performance

• Avoid overly complex nested formulas
• Use appropriate field types
• Consider rollup fields for aggregations
• Minimize connected record references

Maintainability

• Keep formulas as simple as possible
• Use intermediate calculation fields
• Document complex logic
• Test after field name changes

Error Handling

• Check for empty fields before calculating
• Handle division by zero
• Provide default values
• Use IF() to prevent errors

Formula Returns Error

• Check field name spelling
• Verify field names match exactly (case-sensitive)
• Ensure parentheses are balanced
• Check for missing quotes

Unexpected Result

• Verify order of operations
• Check data types (text vs number)
• Test with different record values
• Break formula into steps

Formula Doesn't Update

• Check if source fields changed
• Verify equation field type
• Refresh page or record
• Check for circular references

Blank Results

• Check if source fields are empty
• Verify connection exists
• Add null handling with IF()
• Check field permissions

• Text formulas combine and manipulate text fields
• Number formulas perform mathematical calculations
• Date formulas calculate dates and durations
• Field references pull data from other fields
• Conditional logic creates intelligent calculations
• Common patterns solve typical business needs

Hands-On Exercise (To Be Added)

Exercise placeholders will include practical activities such as:

• Create a full name equation field
• Build a line item total calculator
• Calculate age from birthdate
• Create conditional status indicators
• Build a formatted address equation

Knowledge Check (To Be Added)

Quiz questions will test understanding of:

• Difference between text, number, and date equations
• When to use different text functions
• How to reference connected record fields
• Proper syntax for IF() statements
• Common formula patterns and when to use them