Calculated Field Live Formulas: Administrator Guide

Reference Syntax

• [FieldID]: Gets the field's value
• [DISPLAY:FieldName]: Gets the display label (useful for choice fields)
• [Parent:FieldName]: 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 subtotal = [Price] * [Quantity];
const tax = subtotal * 0.08;
return subtotal + tax;

Calculates total with tax using intermediate variables

Example: Apply Percentage Discount

const basePrice = [UnitPrice] * [Quantity];
const discount = [DiscountPercent] / 100;
return basePrice * (1 - discount);

Applies percentage discount to base price

Example: Add Optional Fees

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

Adds optional fees based on selections

Example: Sum Over Multiple Years

let sum = 0;
for (let i = 1; i <= [NumberOfYears]; i++) {
    sum += [AnnualPayment];
}
return sum;

Calculates total payments over multiple years

Example: Calculate Factorial

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

Calculates factorial of a number

Example: Tax Calculator Function

function calculateTax(amount, rate) {
    return amount * (rate / 100);
}
return [Subtotal] + calculateTax([Subtotal], [TaxRate]);

Uses a custom function to calculate tax

Example: Tiered Discount Function

function getDiscount(quantity) {
    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 discount = getDiscount([Quantity]);
return [UnitPrice] * [Quantity] * (1 - discount);

Tiered discount based on quantity

Tiered Pricing with Volume Discounts

function calculateCost(users) {
    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([NumberOfUsers]);

Currency Conversion with Discount

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

let baseValue = [Subtotal];

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

return currencyExchange([Currency], baseValue);

Date-Based Pricing

function getPricing(year) {
    if (year == 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) {
    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([TotalSales], [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 are treated as 0 in calculations
• Empty text fields are treated as empty strings
• String comparisons are case-sensitive ('Yes' is not equal to 'yes')
• Use [DISPLAY:FieldName] when you need the label of a choice field rather than its underlying value

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)