Formula Fields
Add calculated columns to your saved searches using formula expressions.
Formula Field Types
FORMULA FIELD TYPES
═══════════════════════════════════════════════════════════════════════════════
┌────────────────────┬─────────────────────────────────────────────────────────┐
│ Formula (Text) │ Returns text/string values │
│ │ Example: Concatenate fields, CASE statements │
├────────────────────┼─────────────────────────────────────────────────────────┤
│ Formula (Numeric) │ Returns numbers │
│ │ Example: Calculations, date differences │
├────────────────────┼─────────────────────────────────────────────────────────┤
│ Formula (Date) │ Returns date values │
│ │ Example: Date arithmetic, TRUNC │
├────────────────────┼─────────────────────────────────────────────────────────┤
│ Formula (Currency) │ Returns currency/money values │
│ │ Example: Price calculations │
├────────────────────┼─────────────────────────────────────────────────────────┤
│ Formula (Percent) │ Returns percentage values │
│ │ Example: Margin %, completion % │
└────────────────────┴─────────────────────────────────────────────────────────┘
Field References
Use curly braces {} to reference fields in formulas.
FIELD REFERENCE SYNTAX
═══════════════════════════════════════════════════════════════════════════════
Standard Fields:
{fieldname} → Field value
{fieldname.fieldname} → Joined field value
Examples:
{trandate} → Transaction date
{amount} → Transaction amount
{entity} → Entity ID
{customer.email} → Customer's email (joined)
Special Values:
{today} → Current date
{currentuser} → Current user ID
Numeric Formulas
Basic Arithmetic
NUMERIC FORMULA EXAMPLES
═══════════════════════════════════════════════════════════════════════════════
Addition:
{quantity} + {quantitybackordered}
Subtraction:
{amount} - {amountpaid}
Multiplication:
{quantity} * {rate}
Division:
{amount} / {quantity}
Percentage:
({amount} - {cost}) / {amount} * 100
Combined:
({quantity} * {rate}) - {discount}
Date Calculations
DATE DIFFERENCE FORMULAS
═══════════════════════════════════════════════════════════════════════════════
Days between dates:
{today} - {trandate} → Days since transaction
{duedate} - {today} → Days until due
{closedate} - {trandate} → Days to close
Examples:
Days Outstanding (Invoice):
┌─────────────────────────────────────────────────────────────────────────────┐
│ Formula (Numeric): {today} - {trandate} │
│ Label: Days Outstanding │
│ Result: 45 (if invoice is 45 days old) │
└─────────────────────────────────────────────────────────────────────────────┘
Days Overdue:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Formula (Numeric): {today} - {duedate} │
│ Label: Days Overdue │
│ Result: 15 (if 15 days past due) │
│ Note: Negative = not yet due │
└─────────────────────────────────────────────────────────────────────────────┘
NVL Function (Null Handling)
NVL FUNCTION
═══════════════════════════════════════════════════════════════════════════════
Syntax: NVL(field, default_value)
Replace null with default:
NVL({quantity}, 0) → Use 0 if quantity is null
NVL({memo}, 'No memo') → Default text if empty
NVL({rate}, {baseprice}) → Use base price if rate empty
Avoid division by zero:
{amount} / NVL({quantity}, 1) → Divide by 1 instead of null
OR use NULLIF:
{amount} / NULLIF({quantity}, 0) → Returns null instead of error
ROUND Function
ROUND FUNCTION
═══════════════════════════════════════════════════════════════════════════════
Syntax: ROUND(value, decimal_places)
Examples:
ROUND({amount} / {quantity}, 2) → Round to 2 decimal places
ROUND({margin} * 100, 1) → Round percent to 1 decimal
ROUND({amount}, 0) → Round to whole number
ROUND({amount}, -2) → Round to nearest 100
Text Formulas
Concatenation
TEXT CONCATENATION
═══════════════════════════════════════════════════════════════════════════════
Using || operator:
{firstname} || ' ' || {lastname} → "John Smith"
{tranid} || ' - ' || {entity} → "SO-1234 - Acme Corp"
{city} || ', ' || {state} || ' ' || {zip} → "Seattle, WA 98101"
With NVL for null handling:
NVL({firstname}, '') || ' ' || NVL({lastname}, '')
CASE Statements
CASE STATEMENT SYNTAX
═══════════════════════════════════════════════════════════════════════════════
Simple CASE:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE {status} │
│ WHEN 'A' THEN 'Open' │
│ WHEN 'B' THEN 'Pending' │
│ WHEN 'C' THEN 'Closed' │
│ ELSE 'Unknown' │
│ END │
└─────────────────────────────────────────────────────────────────────────────┘
Searched CASE:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE │
│ WHEN {amount} > 10000 THEN 'High Value' │
│ WHEN {amount} > 5000 THEN 'Medium Value' │
│ WHEN {amount} > 0 THEN 'Low Value' │
│ ELSE 'Zero' │
│ END │
└─────────────────────────────────────────────────────────────────────────────┘
Date-based CASE:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE │
│ WHEN {today} - {duedate} > 90 THEN '90+ Days' │
│ WHEN {today} - {duedate} > 60 THEN '61-90 Days' │
│ WHEN {today} - {duedate} > 30 THEN '31-60 Days' │
│ WHEN {today} - {duedate} > 0 THEN '1-30 Days' │
│ ELSE 'Current' │
│ END │
└─────────────────────────────────────────────────────────────────────────────┘
Text Functions
TEXT FUNCTIONS
═══════════════════════════════════════════════════════════════════════════════
SUBSTR (substring):
SUBSTR({tranid}, 1, 2) → First 2 characters: "SO"
SUBSTR({phone}, -4) → Last 4 digits
UPPER / LOWER:
UPPER({companyname}) → "ACME CORPORATION"
LOWER({email}) → "john@acme.com"
INITCAP (title case):
INITCAP({firstname}) → "John"
LENGTH:
LENGTH({memo}) → Character count
TRIM:
TRIM({name}) → Remove leading/trailing spaces
REPLACE:
REPLACE({phone}, '-', '') → Remove dashes from phone
LPAD / RPAD (padding):
LPAD({documentnumber}, 10, '0') → "0000001234"
Date Formulas
Date Functions
DATE FUNCTIONS
═══════════════════════════════════════════════════════════════════════════════
TO_DATE:
TO_DATE('2024-01-01', 'YYYY-MM-DD') → Convert string to date
TRUNC (truncate time):
TRUNC({trandate}) → Date without time
ADD_MONTHS:
ADD_MONTHS({trandate}, 3) → Add 3 months to date
LAST_DAY:
LAST_DAY({trandate}) → Last day of that month
EXTRACT:
EXTRACT(YEAR FROM {trandate}) → Year number: 2024
EXTRACT(MONTH FROM {trandate}) → Month number: 12
EXTRACT(DAY FROM {trandate}) → Day number: 25
TO_CHAR (format date):
TO_CHAR({trandate}, 'YYYY') → "2024"
TO_CHAR({trandate}, 'MM') → "12"
TO_CHAR({trandate}, 'Mon YYYY') → "Dec 2024"
TO_CHAR({trandate}, 'Day') → "Wednesday"
Date Formatting Masks
DATE FORMAT MASKS
═══════════════════════════════════════════════════════════════════════════════
Year:
YYYY → 2024
YY → 24
Month:
MM → 12 (number)
Mon → Dec
Month → December
Day:
DD → 25 (day of month)
Day → Wednesday
DY → Wed
DDD → 359 (day of year)
Time:
HH24 → 14 (24-hour)
HH → 02 (12-hour)
MI → 30 (minutes)
SS → 45 (seconds)
AM → PM
Examples:
TO_CHAR({trandate}, 'MM/DD/YYYY') → "12/25/2024"
TO_CHAR({trandate}, 'YYYY-MM-DD') → "2024-12-25"
TO_CHAR({trandate}, 'Month DD, YYYY') → "December 25, 2024"
Common Formula Examples
Aging Bucket Formula
AGING BUCKET FORMULA
═══════════════════════════════════════════════════════════════════════════════
Formula (Text):
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE │
│ WHEN {today} - {duedate} <= 0 THEN 'Current' │
│ WHEN {today} - {duedate} <= 30 THEN '1-30 Days' │
│ WHEN {today} - {duedate} <= 60 THEN '31-60 Days' │
│ WHEN {today} - {duedate} <= 90 THEN '61-90 Days' │
│ ELSE '90+ Days' │
│ END │
└─────────────────────────────────────────────────────────────────────────────┘
Label: Aging Bucket
Margin Calculation
MARGIN FORMULAS
═══════════════════════════════════════════════════════════════════════════════
Margin Amount:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Formula (Currency): {amount} - ({quantity} * {costestimate}) │
│ Label: Gross Margin │
└─────────────────────────────────────────────────────────────────────────────┘
Margin Percentage:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Formula (Percent): │
│ CASE WHEN {amount} = 0 THEN 0 │
│ ELSE ({amount} - ({quantity} * {costestimate})) / {amount} * 100 │
│ END │
│ Label: Margin % │
└─────────────────────────────────────────────────────────────────────────────┘
Order Status Label
STATUS LABEL FORMULA
═══════════════════════════════════════════════════════════════════════════════
Formula (Text):
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE {status} │
│ WHEN 'SalesOrd:A' THEN 'Pending Approval' │
│ WHEN 'SalesOrd:B' THEN 'Pending Fulfillment' │
│ WHEN 'SalesOrd:C' THEN 'Cancelled' │
│ WHEN 'SalesOrd:D' THEN 'Partially Fulfilled' │
│ WHEN 'SalesOrd:F' THEN 'Pending Billing' │
│ WHEN 'SalesOrd:G' THEN 'Billed' │
│ WHEN 'SalesOrd:H' THEN 'Closed' │
│ ELSE 'Unknown' │
│ END │
│ Label: Order Status │
└─────────────────────────────────────────────────────────────────────────────┘
Priority Flag
PRIORITY FLAG FORMULA
═══════════════════════════════════════════════════════════════════════════════
Formula (Text):
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE │
│ WHEN {amount} > 50000 AND {today} - {trandate} < 7 THEN 'URGENT' │
│ WHEN {amount} > 25000 THEN 'HIGH' │
│ WHEN {amount} > 10000 THEN 'MEDIUM' │
│ ELSE 'NORMAL' │
│ END │
│ Label: Priority │
└─────────────────────────────────────────────────────────────────────────────┘
Credit Status
CREDIT STATUS FORMULA
═══════════════════════════════════════════════════════════════════════════════
Formula (Text) - Customer Search:
┌─────────────────────────────────────────────────────────────────────────────┐
│ CASE │
│ WHEN NVL({creditlimit}, 0) = 0 THEN 'No Limit' │
│ WHEN {balance} > {creditlimit} THEN 'OVER LIMIT' │
│ WHEN {balance} > {creditlimit} * 0.9 THEN 'Near Limit' │
│ ELSE 'OK' │
│ END │
│ Label: Credit Status │
└─────────────────────────────────────────────────────────────────────────────┘
Formula Filters
Use formulas in the Criteria tab (not just Results).
FORMULA FILTER EXAMPLE
═══════════════════════════════════════════════════════════════════════════════
Find transactions older than 30 days:
Criteria tab:
┌─────────────────────────────────────────────────────────────────────────────┐
│ Filter: Formula (Numeric) │
│ Formula: {today} - {trandate} │
│ Operator: greater than │
│ Value: 30 │
└─────────────────────────────────────────────────────────────────────────────┘
Find high-margin items (> 40%):
┌─────────────────────────────────────────────────────────────────────────────┐
│ Filter: Formula (Numeric) │
│ Formula: ({rate} - {cost}) / NULLIF({rate}, 0) * 100 │
│ Operator: greater than │
│ Value: 40 │
└─────────────────────────────────────────────────────────────────────────────┘
Formula Tips
FORMULA BEST PRACTICES
═══════════════════════════════════════════════════════════════════════════════
DO:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✓ Handle null values with NVL() │
│ ✓ Prevent division by zero with NULLIF() or CASE │
│ ✓ Use meaningful labels for formula columns │
│ ✓ Test formulas with Preview before saving │
│ ✓ Keep formulas as simple as possible │
└─────────────────────────────────────────────────────────────────────────────┘
AVOID:
┌─────────────────────────────────────────────────────────────────────────────┐
│ ✗ Complex nested formulas (hard to debug) │
│ ✗ Formula filters on large datasets (slow) │
│ ✗ Referencing fields not available for record type │
│ ✗ Leaving formula columns unlabeled │
└─────────────────────────────────────────────────────────────────────────────┘
DEBUGGING:
┌─────────────────────────────────────────────────────────────────────────────┐
│ • Use Preview to test formula output │
│ • Check field names are correct (case-sensitive) │
│ • Verify field is available for the record type │
│ • Test with simple data first │
│ • Break complex formulas into parts │
└─────────────────────────────────────────────────────────────────────────────┘
Next Steps
| Goal | Go To |
|---|---|
| Set up alerts and highlighting | Advanced Features → |
| Return to filter reference | Filters & Operators → |
| Back to overview | Saved Search Overview → |