Advanced FormulaScript

FormulaScript gives you powerful ways to write cleaner, more maintainable formulas. This guide shows you how to break down complex calculations, chain operations together, and transform nested formulas into readable pipelines.

Store Intermediate Results

When your calculation has multiple steps, store intermediate results using the := operator. This makes formulas easier to read and debug.

Calculate Order Totals

Break down pricing logic with tax and shipping:

subtotal := SUM(LINE_ITEMS)
tax := subtotal * 0.08
shipping := IF(subtotal > 100, 0, 9.99)
subtotal + tax + shipping

The last expression is your result. Each step is clear and reusable within the formula.

Tiered Discount Logic

Apply multiple discount rules based on order value and customer status:

base_total := SUM(ORDER_ITEMS)
is_vip := CUSTOMER_TIER == "VIP"
bulk_discount := IF(base_total > 500, 0.15, IF(base_total > 200, 0.10, 0))
vip_discount := IF(is_vip, 0.05, 0)
total_discount := bulk_discount + vip_discount
base_total * (1 - total_discount)

Breaking this into intermediate results makes the discount logic transparent and easy to modify.

Lambda Functions

Lambda functions let you create custom inline functions for transforming or filtering data. Use them with MAP, FILTER, and other data functions.

Syntax

Create a lambda with the FUNCTION keyword and a colon separator:

FUNCTION(x: x * 2)         // One parameter
FUNCTION(x, y: x + y)      // Multiple parameters
FUNCTION(1 + 1)            // No parameters

Transform Items with MAP

Use a lambda to transform each item in a list:

MAP(ARRAY(1, 2, 3), FUNCTION(x: x * x))
// Returns [1, 4, 9]

For each item, x takes its value, and x * x returns the transformed result.

Filter Items with FILTER

Use a lambda to keep only items that match a condition:

FILTER(ARRAY(1, 2, 3, 4, 5, 6), FUNCTION(x: MOD(x, 2) = 0))
// Returns [2, 4, 6]

The lambda returns TRUE or FALSE for each item. Items where it returns TRUE are kept.

Find Items with FIND

Return the first item that matches a condition:

FIND(ARRAY(10, 20, 30, 40), FUNCTION(x: x > 25))
// Returns 30

Chain Operations with Pipes

The pipe operator | passes the result of one function into the next. This creates readable left-to-right flows instead of nested parentheses.

Transform and Filter Data

Get the sum of all squared values under 50:

[1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
  | MAP(function(x: x * x))
  | FILTER(function(x: x < 50))
  | SUM

This reads naturally: take numbers, square each one, keep only those under 50, sum them up.

Nested vs. Piped Syntax

The same calculation can be written nested or piped. Pipes usually win for readability.

Nested Approach

SUM(MAP(FILTER(VALUES, function(x: x > 0)), function(x: x * 2)))

You have to read from the inside out: filter values, map to doubles, sum them.

Piped Approach

VALUES 
  | FILTER(function(x: x > 0)) 
  | MAP(function(x: x * 2)) 
  | SUM

Same result, but the flow is clear: start with values, keep positive ones, double them, sum.

When to Use Each

Use pipes when you're chaining multiple operations on the same data. Use nested functions when you need to combine independent calculations:

MAX(CURRENT_INVENTORY, MINIMUM_STOCK_LEVEL)

Here, nested syntax makes sense because you're comparing two separate values.

Format Strings for Text Output

Format strings let you embed values and expressions directly in text using backticks and curly braces. This is perfect for creating readable messages, labels, or formatted output.

Basic Syntax

Use backticks (`` ` ``) to create a format string, and wrap variables or expressions in {}:

`Hello {NAME}, we can offer our services for ${QUOTE}`

The text stays readable, and the values are injected wherever you place them.

Embed Calculations

Put any valid expression inside the braces:

`Total: ${QTY * PRICE}`

You can use references, operators and functions:

`Status: {IF(SCORE > 80, "Excellent", "Needs Improvement")}`

Multiple Values

Include as many embedded values as you need:

`Order #{ORDER_ID}: {QTY} items for ${QTY * PRICE} (Tax: ${QTY * PRICE * 0.08})`

Each {} is evaluated independently.

Display Reference Values

Show what a calculated field contains:

`Quality Assurance Level = {QA}`

This displays both the label and the current value.

Escape Literal Braces

If you need to show an actual { character in your output, escape it with a double backslash:

`Use \\{variable} syntax for interpolation`

This renders as: Use {variable} syntax for interpolation

When to Use Format Strings vs CONCAT

Format strings are best for creating readable text with embedded values:

`Welcome back, {CUSTOMER_NAME}! Your balance is ${BALANCE}.`

Use CONCAT when you're programmatically combining strings or working with dynamic segments:

CONCAT(PREFIX, "_", CUSTOMER_ID, "_", TIMESTAMP)