Formulas

Introduction to Formulas

Formulas allow you to create dynamic calculations and automated behaviors in your documents. Instead of manually updating values when data changes, formulas automatically recalculate results based on the current state of your document.

Key Benefits:

• Automate calculations and data transformations
• Ensure consistency across your document
• Create intelligent forms that respond to user input
• Reduce manual work and potential for errors

Getting Started with Basic Formulas

Your First Formula

Let's start with a simple example. Imagine you have a form with two fields: price and quantity. You want to automatically calculate the total cost in a third field.

To create this formula:

1. Add a new field for the total
2. Set its formula to: price * quantity

That's it! Now whenever the price or quantity changes, the total will automatically update.

Simple Examples

Here are a few basic formulas to get you started:

// Basic arithmetic
price * quantity                 // Multiplication
subtotal + tax                   // Addition
total_cost / number_of_items     // Division

// Text manipulation
"Hello, " + customer_name        // Concatenation

// Simple conditions
if(score > 70, "Pass", "Fail")   // Conditional logic

Referencing Fields

The real power of formulas comes from their ability to reference other fields in your document.

Basic Field References

To use a field's value in your formula, simply use its name:

price * quantity

In this example, price and quantity are references to other fields in your document.

Field Naming Rules

When referencing fields, remember these rules:

• Field names are case-sensitive
• They must not be wrapped in quotes
• They cannot be reserved words (like true, if, this)
• They must consist of letters, numbers, and underscores
• They cannot start with a number (e.g., item1 is valid, 1item is not)

Self-Referencing with this

You can reference the current field's value using the this keyword:

// Limit a number field to a maximum value of 100
if(this > 100, 100, this)

This formula checks if the current value exceeds 100, and if so, caps it at 100.

Working with Different Data Types

Formulas can work with various types of data:

Numbers

Numbers can be integers or decimals:

42          // Integer
3.14159     // Decimal

Number fields support all mathematical operations.

Text (Strings)

Text values must be enclosed in quotes:

"Hello, World!"     // Double quotes
'Single quotes work too'

Booleans

Boolean values represent true or false:

true
false

Arrays

Arrays are ordered lists of values:

[1, 2, 3]
["apple", "banana", "cherry"]
[true, false, true]

Objects

Objects are collections of key-value pairs:

{ name: "John", age: 30 }

Using Operators

Operators allow you to perform calculations and comparisons.

Arithmetic Operators

Comparison Operators

Conditional Logic

Conditional logic allows your formulas to make decisions based on certain conditions.

The if Function

The most basic conditional function is if:

if(condition, value_if_true, value_if_false)

Examples:

// Set shipping cost based on order total
if(order_total > 100, 0, 10)

// Set status based on score
if(score >= 70, "Pass", "Fail")

// Nested conditions
if(score >= 90, "A", 
   if(score >= 80, "B", 
      if(score >= 70, "C", 
         if(score >= 60, "D", "F")
      )
    )
)

Logical Functions

For more complex conditions:

// AND: All conditions must be true
if(and(age >= 18, has_license == true), "Can drive", "Cannot drive")

// OR: At least one condition must be true
if(or(is_member, total_purchases > 1000), "Apply discount", "Regular price")

// NOT: Invert a condition
if(not(is_weekend), "Open", "Closed")

Working with Text

Text manipulation is a common task in formulas.

Text Functions

Example: Formatting Names

// Format a name as "Last, First"
concat(upper(last_name), ", ", first_name)

Mathematical Operations

Perform calculations with numbers in your formulas.

Basic Math

price * quantity           // Multiplication
subtotal + tax             // Addition
total - discount           // Subtraction
amount / number_of_people  // Division

Math Functions

Example: Calculating Discounts

// Apply a 10% discount if order is over $100
if(order_total > 100, order_total * 0.9, order_total)

// Round a calculation to 2 decimal places
round(price * quantity * (1 - discount_rate), 2)

Date and Time Manipulation

Work with dates in your formulas.

Date Functions

Example: Calculating Due Dates

// Set due date to 30 days from today
dateAdd(now(), 30)

// Format a date as MM/DD/YYYY
formatDate(invoice_date, "MM/DD/YYYY")

Working with Arrays and Collections

Arrays allow you to work with collections of values.

Accessing Array Elements

Use dot notation with the index to access array elements (zero-based):

// Get the first item in an array
items.0

// Get the third item in an array
items.2

Array Functions

Example: Working with Collections

// Calculate total from an array of prices
sum(prices)

// Find the highest price
max(prices)

// Count items with quantity > 0
countIf(items, item -> item.quantity > 0)

// Double all values in an array
map(numbers, n -> n * 2)

Advanced Techniques

Working with Objects

Access object properties using dot notation:

// Get a property from an object
customer.name

// Use object properties in conditions
if(customer.level == "Gold", 0.1, 0.05)

Nested Arrays and Objects

You can access deeply nested data:

// Access nested array elements
orders.0.items.1.price

// Access nested object properties
customer.address.city

Complex Transformations

Combine multiple functions for powerful transformations:

// Calculate average of filtered values
average(filter(scores, score -> score > 50))

// Transform and sum values
sum(map(items, item -> item.price * item.quantity))

Troubleshooting and Best Practices

Handling Empty Values

When a formula references a field that is empty (null), it may cause errors. Use the empty() function to check for empty values:

// Check if a field is empty
if(empty(email), "Please enter email", "Valid")

// Provide default values for calculations
if(empty(price) || empty(quantity), 0, price * quantity)

Formula Debugging Tips

1. Start simple: Build your formula step by step
2. Test with known values: Verify each part works as expected
3. Use parentheses: Clarify the order of operations
4. Break down complex formulas: Use intermediate fields for complex calculations

Best Practices

1. Keep formulas readable: Break complex formulas into smaller parts
2. Use descriptive field names: Makes formulas easier to understand
3. Consider edge cases: Handle empty values and unexpected inputs
4. Document complex formulas: Add comments to explain your logic
5. Test thoroughly: Verify your formulas work with various inputs

Complete Function Reference

Logical Functions

Math Functions

Text Functions

Date Functions

Array Functions