Calculated Field Live Formulas: Administrator Guide

Reference Syntax

• [FieldID]: Gets the field's value
• [DISPLAY:FieldID]: Gets the display label (useful for choice fields)
• [Parent:FieldID]: Gets a field value from the parent repeater section

Examples

• [FirstName] + ' ' + [LastName]
  Combines two text fields with a space
  Result: "John Smith"
• 'Customer: ' + [CompanyName] + ' (' + [CustomerID] + ')'
  Creates formatted customer display with prefix and ID in parentheses
  Result: "Customer: Acme Corp (12345)"

Examples

• [Price] * [Quantity]
  Simple multiplication for calculating totals
• [Subtotal] + ([Subtotal] * 0.08)
  Adds 8% tax to subtotal
• ([GrossAmount] - [Discounts]) * 1.2
  Applies discount then adds 20% markup

Examples

• 'Total: $' + ([Price] * [Quantity] + [ShippingCost])
  Combines text prefix with calculated total
  Result: "Total: $150"
• [ProductName] + ' - Qty: ' + [Quantity] + ' @ $' + [UnitPrice]
  Creates detailed product line description
  Result: "Widget - Qty: 5 @ $20"

Examples

• [Quantity] > 100 ? 'Bulk Order' : 'Standard Order'
  Returns "Bulk Order" if quantity exceeds 100, otherwise "Standard Order"
• [Total] >= 1000 ? [Total] * 0.9 : [Total]
  Applies 10% discount for orders of $1000 or more
• [Status] == 'Active' ? 'Yes' : 'No'
  Converts status to Yes/No display
• [Score] >= 90 ? 'A' : [Score] >= 80 ? 'B' : [Score] >= 70 ? 'C' : 'F'
  Nested ternary for grade calculation

Example: Calculate Total with Tax

const price = parseFloat([Price]) || 0;
const quantity = parseInt([Quantity]) || 0;

const subtotal = price * quantity;
const tax = subtotal * 0.08;
return subtotal + tax;

Calculates total with tax using intermediate variables

Example: Apply Percentage Discount

const unitPrice = parseFloat([UnitPrice]) || 0;
const quantity = parseInt([Quantity]) || 0;
const discountPercent = parseFloat([DiscountPercent]) || 0;

const basePrice = unitPrice * quantity;
const discount = discountPercent / 100;
return basePrice * (1 - discount);

Applies percentage discount to base price

Example: Add Optional Fees

let total = parseFloat([BaseAmount]) || 0;

if ([HasPremium] == 'Yes') {
    total += 500;
}
if ([HasSupport] == 'Yes') {
    total += 250;
}
return total;

Adds optional fees based on selections

Example: Sum Over Multiple Years

const years = parseInt([NumberOfYears]) || 0;
const payment = parseInt([AnnualPayment]) || 0;

if (years <= 0) return 0;

let sum = 0;
for (let i = 1; i <= years; i++) {
    sum += payment;
}
return sum;

Calculates total payments over multiple years

Example: Calculate Factorial

const num = parseInt([Number]) || 0;

if (num <= 0) return 1;
if (num > 170) return Infinity; // Prevent excessive computation

let factorial = 1;
for (let i = 2; i <= num; i++) {
    factorial *= i;
}
return factorial;

Calculates factorial of a number

Example: Tax Calculator Function

function calculateTax(amount, rate) {
    if (isNaN(amount) || isNaN(rate)) return 0;
    return amount * (rate / 100);
}

const subtotal = parseFloat([Subtotal]) || 0;
const taxRate = parseFloat([TaxRate]) || 0;

return subtotal + calculateTax(subtotal, taxRate);

Uses a custom function to calculate tax

Example: Tiered Discount Function

function getDiscount(quantity) {
    if (isNaN(quantity) || quantity <= 0) return 0;
    if (quantity >= 100) return 0.20;
    if (quantity >= 50) return 0.15;
    if (quantity >= 25) return 0.10;
    if (quantity >= 10) return 0.05;
    return 0;
}

const qty = parseInt([Quantity]) || 0;
const price = parseFloat([UnitPrice]) || 0;
const discount = getDiscount(qty);

return price * qty * (1 - discount);

Tiered discount based on quantity

Understanding Empty Field Values

When a user has not entered a value, fields may contain:

• null or undefined: Field has never been set
• Empty string (""): Field was cleared or is blank
• NaN: Result of parseInt("") or invalid numeric conversion

Using parseInt() on an empty string returns NaN, which can cause formulas to fail silently or produce unexpected results.

Safe Numeric Parsing

Always provide a fallback value when parsing numbers:

// Unsafe - returns NaN if field is empty
const quantity = parseInt([Quantity]);

// Safe - returns 0 if field is empty or invalid
const quantity = parseInt([Quantity]) || 0;

// Alternative using isNaN check
const quantity = isNaN(parseInt([Quantity])) ? 0 : parseInt([Quantity]);

Validating Function Parameters

Check for invalid values at the start of custom functions:

function calculateCost(quantity, rate) {
    // Guard against NaN, null, undefined, and negative values
    if (isNaN(quantity) || quantity <= 0) return 0;
    if (isNaN(rate) || rate <= 0) return 0;

    return quantity * rate;
}

return calculateCost(parseInt([Quantity]) || 0, parseInt([Rate]) || 0);

Safe Array Operations

When creating arrays based on user input, validate the size first:

function calculatePricing(quantity) {
    // Validate before creating array - prevents "Invalid array length" error
    if (isNaN(quantity) || quantity <= 0) return 0;

    // Now safe to create array
    const prices = new Array(quantity + 1).fill(0);
    // ... calculation logic
    return prices[quantity];
}

return calculatePricing(parseInt([Quantity]) || 0);

Complete Defensive Pattern

A robust formula that handles all edge cases:

function currencyExchange(currency, amount) {
    if (isNaN(amount)) return 0;
    if (currency == "USD") {
        amount = amount * 1.4;
    }
    return Math.ceil(amount);
}

function calculateTotal(price, quantity, taxRate) {
    // Validate all inputs
    const safePrice = parseInt(price) || 0;
    const safeQuantity = parseInt(quantity) || 0;
    const safeTaxRate = parseInt(taxRate) || 0;

    if (safeQuantity <= 0) return 0;

    const subtotal = safePrice * safeQuantity;
    const tax = subtotal * (safeTaxRate / 100);

    return subtotal + tax;
}

return currencyExchange(
    [Currency],
    calculateTotal([UnitPrice], [Quantity], [TaxRate])
);

Tiered Pricing with Volume Discounts

function calculateCost(users) {
    if (isNaN(users) || users <= 0) return 0;

    const baseRate = 1000;
    const rate200To500 = 75;
    const rateOver500 = 50;

    let cost = 0;

    if (users <= 200) {
        cost = Math.ceil(users / 10) * baseRate;
    } else if (users <= 500) {
        cost = 20 * baseRate;
        cost += (users - 200) * rate200To500;
    } else {
        cost = 20 * baseRate;
        cost += 300 * rate200To500;
        cost += (users - 500) * rateOver500;
    }

    return cost;
}

return calculateCost(parseInt([NumberOfUsers]) || 0);

Currency Conversion with Discount

function currencyExchange(currency, amount) {
    if (isNaN(amount)) return 0;
    if (currency == 'USD') {
        amount = amount * 1.4;
    }
    return Math.ceil(amount);
}

let baseValue = parseFloat([Subtotal]) || 0;
const discountValue = parseFloat([DiscountValue]) || 0;

if ([DiscountType] == 'Amount') {
    baseValue -= discountValue;
} else if ([DiscountType] == '%') {
    baseValue -= (baseValue * discountValue / 100);
}

return currencyExchange([Currency], baseValue);

Date-Based Pricing

function getPricing(year) {
    const safeYear = parseInt(year) || 0;
    if (safeYear == 2024) {
        return { small: 1200, medium: 2400, large: 4800 };
    }
    return { small: 1500, medium: 3000, large: 6000 };
}

const prices = getPricing([PricingYear]);
const size = [PackageSize];

if (size == 'Small') return prices.small;
if (size == 'Medium') return prices.medium;
if (size == 'Large') return prices.large;
return 0;

Commission Calculator

function calculateCommission(sales, tier) {
    if (isNaN(sales) || sales <= 0) return 0;

    let rate = 0.05;

    if (tier == 'Gold') rate = 0.08;
    else if (tier == 'Platinum') rate = 0.10;

    let commission = sales * rate;

    if (sales > 100000) {
        commission += (sales - 100000) * 0.02;
    }

    return Math.round(commission * 100) / 100;
}

return calculateCommission(parseFloat([TotalSales]) || 0, [AgentTier]);

Formula Guidelines

• Use meaningful variable names for complex calculations
• Break complex formulas into smaller functions for readability
• Use Math.round() when you need specific decimal precision
• Test formulas with edge cases (zero values, empty fields, negative numbers)
• Arrays are limited to a maximum of 1m elements

Field Behavior

• Empty numeric fields return 0 when referenced directly, but parseInt("") returns NaN - use defensive patterns
• Empty text fields are treated as empty strings ("")
• Null/undefined fields return null - check before using in calculations
• String comparisons are case-sensitive ('Yes' is not equal to 'yes')
• Use [DISPLAY:FieldID] when you need the label of a choice field rather than its underlying value
• Always validate inputs in custom functions - see Defensive Coding section

Blocked Features

The following are explicitly disabled for security:

• Browser APIs: window, document, navigator, location, history, screen, localStorage, sessionStorage, indexedDB, caches
• Network/Communication: fetch, XMLHttpRequest, WebSocket, EventSource, Request, Response, Headers
• Dangerous Functions: eval, Function (as callable), setTimeout, setInterval, setImmediate, requestAnimationFrame, requestIdleCallback
• DOM Manipulation: Element, Node, Document, HTMLElement, MutationObserver, IntersectionObserver, ResizeObserver
• Workers: Worker, SharedWorker, ServiceWorker
• Scope Manipulation: Proxy, Reflect, globalThis, self, .constructor, .prototype, __proto__, with statement, dynamic import
• Other: crypto, postMessage, opener, parent, top, frames

Allowed Features

Safe operations for calculations:

• Math object (Math.ceil, Math.floor, Math.round, Math.max, Math.min, etc.)
• String operations (concatenation, methods)
• Array operations (map, filter, reduce, etc.)
• Object literals and manipulation
• JSON.parse, JSON.stringify
• Number operations and formatting
• Date object for date calculations
• Regular expressions for string matching
• All standard arithmetic and comparison operators
• Control flow (if/else, loops, ternary)
• Function definitions (within the formula)