> ## Documentation Index
> Fetch the complete documentation index at: https://docs.elementum.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Calculations

> Build formulas to transform values, aggregate related records, and evaluate conditions—in automations, layouts, and anywhere calculations are supported.

Calculations let you combine fields, operators, and functions to derive values from your data. Use them in automations, layouts, and data processing when you need totals, text formatting, date math, or conditional logic.

## Where to use calculations

Calculations can be configured in several places in Elementum:

* **Automations** — Use the **Run Calculation** action to evaluate expressions from triggers or previous actions (see [Automation actions reference](/workflows/automation-actions-reference)).
* **Element layouts** — Add formulas to derive values on records.
* **Calculated columns in tables** — For example, `Total = Quantity × Price` (see [Tables](/data/tables)).
* **Reports** — Add formulas to Excel reports (see [Reports](/data/reports)).

<CardGroup cols={2}>
  <Card title="Quick Start" icon="rocket" href="#quick-start">
    Get started with basic calculations
  </Card>

  <Card title="Function Reference" icon="book" href="#function-reference">
    Complete list of all available functions
  </Card>

  <Card title="Business Examples" icon="briefcase" href="#business-examples">
    Real-world calculation scenarios
  </Card>

  <Card title="Troubleshooting" icon="wrench" href="#troubleshooting">
    Common issues and solutions
  </Card>
</CardGroup>

## Quick Start

New to calculations? Start with these patterns for totals, text, dates, and conditional logic.

### Most Used Functions

<AccordionGroup>
  <Accordion title="Basic Math: SUM, AVERAGE, COUNT">
    ```javascript theme={null}
    // Sum all invoice amounts
    SUM(INVOICES."Amount")

    // Average customer rating
    AVERAGE(REVIEWS."Rating")

    // Count completed tasks
    COUNT(TASKS."ID")
    ```
  </Accordion>

  <Accordion title="Text Operations: CONCAT, UPPER, LOWER">
    ```javascript theme={null}
    // Create full name
    CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

    // Standardize email format
    LOWER(CUSTOMERS."Email")

    // Format product codes
    UPPER(PRODUCTS."SKU")
    ```
  </Accordion>

  <Accordion title="Date Operations: NOW, DATEADD, DATEDIF">
    ```javascript theme={null}
    // Current date and time (there is no TODAY() function — use NOW())
    NOW()

    // Add 5 days to today
    DATEADD(DAY, 5, NOW())

    // Subtract 1 month from a due date
    DATEADD(MONTH, -1, TASKS."DueDate")

    // Days since order placed
    DATEDIF(ORDERS."OrderDate", NOW(), 'D')

    // Convert text to date (input must be YYYY-MM-DD)
    DATEVALUE(CUSTOMERS."SignupDate")
    ```
  </Accordion>

  <Accordion title="Conditional Logic: IF, AND">
    ```javascript theme={null}
    // Customer status based on spending
    IF(CUSTOMERS."TotalSpent" > 1000, 'VIP', 'Standard')

    // Eligible for discount
    AND(CUSTOMERS."TotalSpent" > 500, CUSTOMERS."MembershipLevel" = 'Gold')

    // Nested conditions for customer tiers
    IF(CUSTOMERS."TotalSpent" > 5000, 'Enterprise',
       IF(CUSTOMERS."TotalSpent" > 1000, 'Premium', 'Standard'))
    ```
  </Accordion>
</AccordionGroup>

### Common date recipes

The most-asked date questions, with the exact syntax to use.

#### Get today's date

Elementum does not have a `TODAY()` function. Use `NOW()` for the current date and time, and wrap it in `DATE()` if you need just the date portion:

```javascript theme={null}
// Current date and time
NOW()

// Just today's date (no time component)
DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))
```

#### Get the current date and time

`NOW()` returns the current timestamp. It takes no arguments:

```javascript theme={null}
NOW()
```

Use it inside other functions to derive values against the current moment:

```javascript theme={null}
// Days since a stored date
DATEDIF(ORDERS."OrderDate", NOW(), 'D')

// Current year for reporting
YEAR(NOW())
```

#### Add days to a date

Use [`DATEADD(unit, value, date)`](#dateadd---add-or-subtract-time-from-a-date) with an unquoted unit token like `DAY` or `MONTH`:

```javascript theme={null}
// Add 5 days to today
DATEADD(DAY, 5, NOW())

// Expected ship-by, 30 days after order
DATEADD(DAY, 30, ORDERS."OrderDate")
```

#### Subtract days from a date

Pass a negative value to `DATEADD`:

```javascript theme={null}
// 7 days ago
DATEADD(DAY, -7, NOW())

// One month before a due date
DATEADD(MONTH, -1, TASKS."DueDate")
```

`DATEADD` handles month and year rollovers automatically — adding days across the end of a month advances into the next month as expected.

## Business Examples

<Tabs>
  <Tab title="Sales & Revenue">
    ### Calculate Monthly Sales Performance

    ```javascript theme={null}
    // Total monthly revenue
    SUM(ORDERS."Amount")

    // Average order value
    AVERAGE(ORDERS."Amount")

    // Top performing month
    MAX_AGGREGATE(MONTHLY_SALES."Revenue")

    // Sales growth percentage
    ROUND((THIS_MONTH."Revenue" - LAST_MONTH."Revenue") / LAST_MONTH."Revenue" * 100, 2)

    // High-value customer identification
    IF(CUSTOMERS."TotalSpent" > 5000, 'Enterprise', 
       IF(CUSTOMERS."TotalSpent" > 1000, 'Premium', 'Standard'))
    ```
  </Tab>

  <Tab title="Customer Management">
    ### Customer Insights & Segmentation

    ```javascript theme={null}
    // Customer full name
    CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

    // Days since last purchase
    DATEDIF(CUSTOMERS."LastPurchaseDate", NOW(), 'D')

    // Customer lifetime value
    SUM(ORDERS."Amount")

    // Customer age
    DATEDIF(CUSTOMERS."BirthDate", NOW(), 'Y')

    // At-risk customer flag
    IF(DATEDIF(CUSTOMERS."LastPurchaseDate", NOW(), 'D') > 90, 'At Risk', 'Active')
    ```
  </Tab>

  <Tab title="Inventory & Operations">
    ### Inventory Management

    ```javascript theme={null}
    // Stock level status
    IF(PRODUCTS."StockLevel" < PRODUCTS."ReorderPoint", 'Low Stock', 'OK')

    // Days of inventory remaining
    ROUND(PRODUCTS."StockLevel" / AVERAGE(DAILY_SALES."Quantity"), 0)

    // Total inventory value
    SUM(PRODUCTS."StockLevel" * PRODUCTS."Cost")

    // Product performance score
    ROUND((PRODUCTS."Revenue" / PRODUCTS."Cost") * 100, 2)

    // Reorder recommendation
    IF(PRODUCTS."StockLevel" < PRODUCTS."ReorderPoint", 
       CONCAT('Reorder ', PRODUCTS."ReorderQuantity", ' units'), 
       'Stock OK')
    ```
  </Tab>
</Tabs>

## Function Reference

<Warning>
  Functions are organized by category. Use the search function (Ctrl/Cmd + K) to quickly find specific functions.
</Warning>

### Logical Functions

<AccordionGroup>
  <Accordion title="AND - All conditions must be true">
    Tests multiple conditions and returns TRUE only if all are TRUE.

    **Syntax:** `AND(condition1, condition2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Check if customer is eligible for discount
    AND(CUSTOMERS."TotalSpent" > 500, CUSTOMERS."MembershipLevel" = 'Gold')

    // Validate complete order
    AND(ORDERS."PaymentStatus" = 'Paid', ORDERS."ShippingAddress" != '')

    // Employee bonus eligibility
    AND(EMPLOYEES."SalesTarget" <= EMPLOYEES."ActualSales", EMPLOYEES."Tenure" > 1)
    ```

    **Arguments:**

    * `condition1, condition2, ...`: Logical expressions that evaluate to TRUE/FALSE

    <Info>
      If any condition is blank, the result will be blank.
    </Info>
  </Accordion>

  <Accordion title="OR - At least one condition must be true">
    Tests multiple conditions and returns TRUE if any are TRUE.

    **Syntax:** `OR(condition1, condition2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Flag tickets that are urgent or overdue
    OR(TICKETS."Priority" = 'Urgent', TICKETS."DaysOpen" > 7)

    // Identify customers worth re-engaging
    OR(CUSTOMERS."DaysSinceLastPurchase" > 90, CUSTOMERS."OpenTickets" > 0)

    // Apply discount when any qualifying condition is met
    OR(ORDERS."DiscountCode" != '', ORDERS."LoyaltyTier" = 'Gold')
    ```

    **Arguments:**

    * `condition1, condition2, ...`: Logical expressions that evaluate to TRUE/FALSE

    <Info>
      If any condition is blank, the result will be blank.
    </Info>
  </Accordion>

  <Accordion title="IF - Conditional logic">
    Returns different values based on a condition.

    **Syntax:** `IF(condition, value_if_true, value_if_false)`

    **Business Example:**

    ```javascript theme={null}
    // Customer status based on spending
    IF(CUSTOMERS."TotalSpent" > 1000, 'VIP', 'Standard')

    // Shipping cost calculation
    IF(ORDERS."Amount" > 100, 0, 9.99)

    // Performance rating
    IF(EMPLOYEES."SalesTarget" <= EMPLOYEES."ActualSales", 'Exceeded', 'Below Target')

    // Nested conditions for customer tiers
    IF(CUSTOMERS."TotalSpent" > 5000, 'Enterprise', 
       IF(CUSTOMERS."TotalSpent" > 1000, 'Premium', 'Standard'))
    ```

    **Arguments:**

    * `condition`: Logical expression
    * `value_if_true`: Value returned when condition is TRUE
    * `value_if_false`: Value returned when condition is FALSE

    <Tip>
      For more than two outcomes, use `IFS` instead of nesting multiple `IF` statements—it's flatter and easier to read.
    </Tip>
  </Accordion>

  <Accordion title="IFS - Multiple conditions in order">
    Evaluates multiple conditions in order and returns the value corresponding to the first condition that is TRUE.

    **Syntax:** `IFS(condition1, value1, condition2, value2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Customer tier based on spending
    IFS(
      CUSTOMERS."TotalSpent" > 5000, 'Enterprise',
      CUSTOMERS."TotalSpent" > 1000, 'Premium',
      TRUE(), 'Standard'
    )

    // Order priority based on age
    IFS(
      ORDERS."DaysOpen" > 14, 'Critical',
      ORDERS."DaysOpen" > 7, 'High',
      ORDERS."DaysOpen" > 3, 'Medium',
      TRUE(), 'Low'
    )

    // Shipping band based on order amount
    IFS(
      ORDERS."Amount" >= 250, 'Free',
      ORDERS."Amount" >= 100, 'Discounted',
      TRUE(), 'Standard'
    )

    // Evaluation walkthrough — returned value depends on field values
    IFS(
      BAT."Number" = 0, '0',
      BAT."Number" > 4, '>4',
      BAT."Status" = 'New', 'New'
    )
    // Number=0,    Status='New' → '0'    (first condition matches)
    // Number=1,    Status='New' → 'New'  (skips 0 and >4, matches Status)
    // Number=5,    Status='New' → '>4'   (matches >4 before Status is checked)
    // Number=null, Status='New' → 'New'  (null comparisons don't match)
    // Number=null, Status='Old' → blank  (no condition matches)
    ```

    **Arguments:**

    * `condition1, condition2, ...`: Logical expressions evaluated in order
    * `value1, value2, ...`: Value returned for the corresponding condition when it is the first to evaluate to TRUE

    <Tip>
      When no condition is TRUE, `IFS` returns blank. Include a final `TRUE()` condition as a catch-all to guarantee a value is always returned.
    </Tip>
  </Accordion>
</AccordionGroup>

### Numeric Functions

<AccordionGroup>
  <Accordion title="SUM - Add numbers from related records">
    Calculates the total sum of values in a related field.

    **Syntax:** `SUM(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Total revenue from all orders
    SUM(ORDERS."Amount")

    // Total hours worked by employee
    SUM(TIMESHEETS."Hours")

    // Total inventory value
    SUM(PRODUCTS."StockLevel" * PRODUCTS."UnitCost")

    // Customer lifetime value
    SUM(CUSTOMER_ORDERS."Amount")
    ```

    **Arguments:**

    * `related_field`: Field from related records to sum

    <Warning>
      This function only works with related fields, not individual values. For adding individual values, use the '+' operator.
    </Warning>
  </Accordion>

  <Accordion title="AVERAGE - Calculate mean value">
    Returns the numerical average of values in a related field.

    **Syntax:** `AVERAGE(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Average customer rating
    AVERAGE(REVIEWS."Rating")

    // Average order value
    AVERAGE(ORDERS."Amount")

    // Average employee salary by department
    AVERAGE(EMPLOYEES."Salary")

    // Average project completion time
    AVERAGE(PROJECTS."CompletionDays")
    ```

    **Arguments:**

    * `related_field`: Field from related records to average

    <Info>
      Blank values are automatically excluded from the calculation.
    </Info>
  </Accordion>

  <Accordion title="COUNT - Count related records">
    Counts the number of non-null values in a related field.

    **Syntax:** `COUNT(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Number of orders placed
    COUNT(ORDERS."ID")

    // Number of completed tasks
    COUNT(TASKS."CompletedDate")

    // Number of active customers
    COUNT(CUSTOMERS."LastLoginDate")

    // Number of products in stock
    COUNT(PRODUCTS."StockLevel")
    ```

    **Arguments:**

    * `related_field`: Field from related records to count

    <Info>
      Use the ID field to count total records, or use a specific field to count only non-null values.
    </Info>
  </Accordion>

  <Accordion title="COUNTIF - Count records meeting criteria">
    Counts non-null values in a related field that meet a specified condition.

    **Syntax:** `COUNTIF(related_field, criterion)`

    **Business Example:**

    ```javascript theme={null}
    // Count high-value orders
    COUNTIF(ORDERS."Amount", '>1000')

    // Count 5-star reviews
    COUNTIF(REVIEWS."Rating", '=5')

    // Count overdue tasks
    COUNTIF(TASKS."DueDate", '<' + TEXT(NOW()))

    // Count products with low stock
    COUNTIF(PRODUCTS."StockLevel", '<10')
    ```

    **Arguments:**

    * `related_field`: Field from related records to count
    * `criterion`: Condition to meet (supports comparison operators)

    <Info>
      Alternative syntax: `SUM(IF(RELATED."Field" = 'Paid', 1, 0))`
    </Info>
  </Accordion>

  <Accordion title="COUNTUNIQUE - Count unique values">
    Counts the number of unique values in a related field.

    **Syntax:** `COUNTUNIQUE(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Number of unique customers
    COUNTUNIQUE(ORDERS."CustomerID")

    // Number of different product categories
    COUNTUNIQUE(PRODUCTS."Category")

    // Number of unique sales reps
    COUNTUNIQUE(DEALS."SalesRep")

    // Number of unique support ticket types
    COUNTUNIQUE(TICKETS."Type")
    ```

    **Arguments:**

    * `related_field`: Field from related records to count unique values

    <Info>
      Null values are excluded from the count.
    </Info>
  </Accordion>

  <Accordion title="MAX - Find maximum value">
    Returns the maximum value from a given set of values.

    **Syntax:** `MAX(value1, value2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Highest of three scores
    MAX(PERFORMANCE."Q1Score", PERFORMANCE."Q2Score", PERFORMANCE."Q3Score")

    // Maximum shipping cost between options
    MAX(SHIPPING."Standard", SHIPPING."Express", SHIPPING."Overnight")

    // Latest date from multiple fields
    MAX(CUSTOMER."LastPurchase", CUSTOMER."LastContact", CUSTOMER."LastLogin")
    ```

    **Arguments:**

    * `value1, value2, ...`: Values to compare

    <Info>
      Blank values are ignored. For aggregate calculations, use MAX\_AGGREGATE.
    </Info>
  </Accordion>

  <Accordion title="MAX_AGGREGATE - Find maximum in related field">
    Finds the maximum value from a related field or calculation.

    **Syntax:** `MAX_AGGREGATE(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Highest order amount
    MAX_AGGREGATE(ORDERS."Amount")

    // Best employee performance score
    MAX_AGGREGATE(EMPLOYEES."PerformanceScore")

    // Peak sales month
    MAX_AGGREGATE(MONTHLY_SALES."Revenue")

    // Highest customer satisfaction rating
    MAX_AGGREGATE(SURVEYS."SatisfactionScore")
    ```

    **Arguments:**

    * `related_field`: Field from related records to find maximum

    <Info>
      This is the aggregate version of MAX for related data.
    </Info>
  </Accordion>

  <Accordion title="MIN - Find minimum value">
    Returns the minimum value from a given set of values.

    **Syntax:** `MIN(value1, value2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Lowest of three prices
    MIN(PRICING."Standard", PRICING."Discount", PRICING."Wholesale")

    // Earliest date from multiple fields
    MIN(PROJECT."StartDate", PROJECT."PlannedStart", PROJECT."ActualStart")

    // Minimum required inventory
    MIN(PRODUCT."SafetyStock", PRODUCT."ReorderPoint", 10)
    ```

    **Arguments:**

    * `value1, value2, ...`: Values to compare

    <Info>
      Blank values are ignored. For aggregate calculations, use MIN\_AGGREGATE.
    </Info>
  </Accordion>

  <Accordion title="MIN_AGGREGATE - Find minimum in related field">
    Finds the minimum value from a related field or calculation.

    **Syntax:** `MIN_AGGREGATE(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Lowest order amount
    MIN_AGGREGATE(ORDERS."Amount")

    // Shortest project duration
    MIN_AGGREGATE(PROJECTS."Duration")

    // Lowest inventory level
    MIN_AGGREGATE(PRODUCTS."StockLevel")

    // Minimum customer age
    MIN_AGGREGATE(CUSTOMERS."Age")
    ```

    **Arguments:**

    * `related_field`: Field from related records to find minimum

    <Info>
      This is the aggregate version of MIN for related data.
    </Info>
  </Accordion>

  <Accordion title="ROUND - Round numbers">
    Rounds a number to a specified number of decimal places.

    **Syntax:** `ROUND(number, [decimal_places])`

    **Business Example:**

    ```javascript theme={null}
    // Round currency to 2 decimal places
    ROUND(ORDERS."Amount", 2)

    // Round percentage to whole number
    ROUND(SALES."GrowthRate" * 100, 0)

    // Round to nearest thousand
    ROUND(REVENUE."Annual", -3)

    // Round average rating
    ROUND(AVERAGE(REVIEWS."Rating"), 1)
    ```

    **Arguments:**

    * `number`: Number to round
    * `decimal_places`: \[OPTIONAL] Number of decimal places (default: 0)

    <Info>
      Negative decimal\_places rounds to left of decimal point (e.g., -1 rounds to tens).
    </Info>
  </Accordion>

  <Accordion title="STDEV - Calculate standard deviation">
    Calculates the standard deviation of a related field.

    **Syntax:** `STDEV(related_field)`

    **Business Example:**

    ```javascript theme={null}
    // Variability in order amounts
    STDEV(ORDERS."Amount")

    // Consistency of employee performance
    STDEV(EMPLOYEES."PerformanceScore")

    // Product rating consistency
    STDEV(REVIEWS."Rating")

    // Sales performance variability
    STDEV(SALES_REPS."MonthlySales")
    ```

    **Arguments:**

    * `related_field`: Field from related records to calculate standard deviation

    <Info>
      Standard deviation measures how spread out values are from the average.
    </Info>
  </Accordion>

  <Accordion title="SUMIF - Sum values meeting criteria">
    Returns the sum of values in a field that meet a specified condition.

    **Syntax:** `SUMIF(related_field, criterion)`

    **Business Example:**

    ```javascript theme={null}
    // Revenue from high-value orders
    SUMIF(ORDERS."Amount", '>1000')

    // Total hours for completed tasks
    SUMIF(TASKS."Hours", TASKS."Status" = 'Completed')

    // Revenue from premium customers
    SUMIF(ORDERS."Amount", CUSTOMERS."Tier" = 'Premium')

    // Sales from specific region
    SUMIF(SALES."Amount", SALES."Region" = 'North')
    ```

    **Arguments:**

    * `related_field`: Field from related records to sum
    * `criterion`: Condition values must meet

    <Info>
      Use operators like greater than, less than, greater than or equal to, less than or equal to, and equal to in your criteria.
    </Info>
  </Accordion>
</AccordionGroup>

### Date and Time Functions

<AccordionGroup>
  <Accordion title="NOW - Current date and time">
    Returns the current date and time.

    **Syntax:** `NOW()`

    **Business Example:**

    ```javascript theme={null}
    // Timestamp for new records
    NOW()

    // Days since order placed
    DATEDIF(ORDERS."OrderDate", NOW(), 'D')

    // Current year for reporting
    YEAR(NOW())

    // Age calculation
    DATEDIF(CUSTOMERS."BirthDate", NOW(), 'Y')
    ```

    <Info>
      This function takes no arguments and always returns the current moment. There is no separate `TODAY()` function — use `NOW()` and, if you need date-only, wrap it in `DATE(YEAR(NOW()), MONTH(NOW()), DAY(NOW()))`.
    </Info>
  </Accordion>

  <Accordion title="DATEADD - Add or subtract time from a date">
    Adds a value in a given unit to a date or datetime. Pass a negative value to subtract.

    **Syntax:** `DATEADD(unit, value, date)`

    **Business Example:**

    ```javascript theme={null}
    // Add 7 days to today
    DATEADD(DAY, 7, NOW())

    // Subtract 1 month from a due date
    DATEADD(MONTH, -1, TASKS."DueDate")

    // Expected ship-by, 30 days after an order
    DATEADD(DAY, 30, ORDERS."OrderDate")

    // Ninety-day follow-up after a customer's last purchase
    DATEADD(DAY, 90, CUSTOMERS."LastPurchaseDate")
    ```

    **Arguments:**

    * `unit`: Date/time unit token — for example, `DAY` or `MONTH`. Passed unquoted.
    * `value`: Number of units to add. Use a negative number to subtract.
    * `date`: Starting date or datetime

    <Info>
      `DATEADD` handles month and year boundaries automatically — adding days across the end of a month or year rolls forward as expected. This is the recommended way to shift a date by a fixed amount.
    </Info>
  </Accordion>

  <Accordion title="DATE - Create date from components">
    Returns a date value based on provided year, month, and day.

    **Syntax:** `DATE(year, month, day)`

    **Business Example:**

    ```javascript theme={null}
    // Create fiscal year start date
    DATE(YEAR(NOW()), 4, 1)

    // Build date from separate fields
    DATE(ORDERS."Year", ORDERS."Month", ORDERS."Day")

    // Create quarter end date
    DATE(2024, 3, 31)

    // Generate report date
    DATE(REPORTS."ReportYear", REPORTS."ReportMonth", 1)

    // Extract the date portion from a datetime field
    // (use this to populate a Date field from a DateTime field
    // in an Update Record Fields automation action)
    DATE(
      YEAR(ORDERS."OrderDateTime"),
      MONTH(ORDERS."OrderDateTime"),
      DAY(ORDERS."OrderDateTime")
    )
    ```

    **Arguments:**

    * `year`: Four-digit year
    * `month`: Month (1-12)
    * `day`: Day of month (1-31)

    <Info>
      Values exceeding normal ranges automatically adjust (e.g., month 13 becomes January of next year).
    </Info>

    <Tip>
      To shift a date by a fixed amount (add or subtract days, months, and so on), use [`DATEADD`](#dateadd---add-or-subtract-time-from-a-date) rather than building a new `DATE` with adjusted components.
    </Tip>

    <Tip>
      There is no direct "datetime to date" conversion option in calculations or in the **Update Record Fields** automation action. To populate a Date field from a DateTime field, wrap the datetime value in `DATE(YEAR(...), MONTH(...), DAY(...))` as shown above.
    </Tip>
  </Accordion>

  <Accordion title="DATETIME - Create datetime from components">
    Returns a datetime value in the company's timezone.

    **Syntax:** `DATETIME(year, month, day, hour, minute, second)`

    **Business Example:**

    ```javascript theme={null}
    // Create meeting start time
    DATETIME(2024, 3, 15, 9, 30, 0)

    // Build timestamp from fields
    DATETIME(EVENTS."Year", EVENTS."Month", EVENTS."Day", EVENTS."Hour", 0, 0)

    // Create deadline
    DATETIME(TASKS."DueYear", TASKS."DueMonth", TASKS."DueDay", 23, 59, 59)

    // Sentinel "max" datetime — 12/31/9999 11:59 PM
    DATETIME(9999, 12, 31, 23, 59, 0)

    // Sentinel "min" datetime — 1/1/1901 12:00 AM (midnight)
    DATETIME(1901, 1, 1, 0, 0, 0)

    // Add 1 hour to now
    DATETIME(YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()) + 1, MINUTE(NOW()), 0)
    ```

    **Arguments:**

    * `year`: Four-digit year
    * `month`: Month (1-12)
    * `day`: Day of month (1-31)
    * `hour`: Hour (0-23)
    * `minute`: Minute (0-59)
    * `second`: Second (0-59)

    <Info>
      Time is set in your company's timezone.
    </Info>
  </Accordion>

  <Accordion title="DATEDIF - Calculate date differences">
    Calculates the difference between two dates in specified units.

    **Syntax:** `DATEDIF(start_date, end_date, unit)`

    **Business Example:**

    ```javascript theme={null}
    // Customer age
    DATEDIF(CUSTOMERS."BirthDate", NOW(), 'Y')

    // Days since last purchase
    DATEDIF(CUSTOMERS."LastPurchaseDate", NOW(), 'D')

    // Project duration in months
    DATEDIF(PROJECTS."StartDate", PROJECTS."EndDate", 'M')

    // Employee tenure
    DATEDIF(EMPLOYEES."HireDate", NOW(), 'Y')
    ```

    **Arguments:**

    * `start_date`: Beginning date
    * `end_date`: End date
    * `unit`: 'Y' for years, 'M' for months, 'D' for days

    <Warning>
      Returns negative values if start\_date is after end\_date.
    </Warning>
  </Accordion>

  <Accordion title="DATETIME_TRUNC - Truncate datetime">
    Truncates a datetime to a specified unit.

    **Syntax:** `DATETIME_TRUNC(datetime, unit)`

    **Business Example:**

    ```javascript theme={null}
    // Start of month for reporting
    DATETIME_TRUNC(ORDERS."OrderDate", 'MONTH')

    // Start of day for daily summaries
    DATETIME_TRUNC(EVENTS."EventTime", 'DAY')

    // Start of quarter
    DATETIME_TRUNC(SALES."SaleDate", 'QUARTER')

    // Start of year
    DATETIME_TRUNC(EMPLOYEES."HireDate", 'YEAR')
    ```

    **Arguments:**

    * `datetime`: Datetime to truncate
    * `unit`: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND

    <Info>
      Useful for grouping data by time periods.
    </Info>
  </Accordion>

  <Accordion title="DATEVALUE - Convert text to date">
    Converts text date value into a DATE object.

    **Syntax:** `DATEVALUE(text_date)`

    **Business Example:**

    ```javascript theme={null}
    // Convert imported date text
    DATEVALUE(IMPORTS."DateString")

    // Parse date from external system
    DATEVALUE(EXTERNAL."FormattedDate")

    // Convert user-entered date
    DATEVALUE(FORMS."SubmissionDate")
    ```

    **Arguments:**

    * `text_date`: Text representation of a date

    <Warning>
      Returns null if the text cannot be parsed as a date.
    </Warning>

    <Warning>
      **Accepted format:** input must be in `YYYY-MM-DD` form — date only, no time component. Other formats (`MM/DD/YYYY`, written-out months, ISO 8601 strings with a time component) are not parsed and return blank. See [Calculations troubleshooting](/data/calculations-troubleshooting#datevalue-only-accepts-year-month-day-input) for patterns.
    </Warning>
  </Accordion>

  <Accordion title="DAY - Extract day from date">
    Returns the day of the month (1-31) from a date.

    **Syntax:** `DAY(date)`

    **Business Example:**

    ```javascript theme={null}
    // Extract day for daily reports
    DAY(ORDERS."OrderDate")

    // Get payment day
    DAY(INVOICES."DueDate")

    // Extract birth day
    DAY(CUSTOMERS."BirthDate")
    ```

    **Arguments:**

    * `date`: Date to extract day from

    <Info>
      Returns a number between 1 and 31.
    </Info>
  </Accordion>

  <Accordion title="MONTH - Extract month from date">
    Returns the month (1-12) from a date.

    **Syntax:** `MONTH(date)`

    **Business Example:**

    ```javascript theme={null}
    // Extract month for monthly reports
    MONTH(ORDERS."OrderDate")

    // Get birth month
    MONTH(CUSTOMERS."BirthDate")

    // Extract fiscal month
    MONTH(TRANSACTIONS."TransactionDate")
    ```

    **Arguments:**

    * `date`: Date to extract month from

    <Info>
      Returns a number between 1 (January) and 12 (December).
    </Info>
  </Accordion>

  <Accordion title="YEAR - Extract year from date">
    Returns the year from a date.

    **Syntax:** `YEAR(date)`

    **Business Example:**

    ```javascript theme={null}
    // Extract year for annual reports
    YEAR(ORDERS."OrderDate")

    // Get hire year
    YEAR(EMPLOYEES."HireDate")

    // Extract birth year
    YEAR(CUSTOMERS."BirthDate")
    ```

    **Arguments:**

    * `date`: Date to extract year from

    <Info>
      Returns a four-digit year number.
    </Info>
  </Accordion>

  <Accordion title="HOUR - Extract hour from datetime">
    Returns the hour (0-23) from a datetime.

    **Syntax:** `HOUR(datetime)`

    **Business Example:**

    ```javascript theme={null}
    // Extract hour for time-based analysis
    HOUR(ORDERS."OrderTime")

    // Get meeting hour
    HOUR(MEETINGS."StartTime")

    // Extract login hour
    HOUR(USERS."LastLogin")
    ```

    **Arguments:**

    * `datetime`: Datetime to extract hour from

    <Info>
      Returns a number between 0 (midnight) and 23 (11 PM).
    </Info>
  </Accordion>

  <Accordion title="MINUTE - Extract minute from datetime">
    Returns the minute (0-59) from a datetime.

    **Syntax:** `MINUTE(datetime)`

    **Business Example:**

    ```javascript theme={null}
    // Extract minute for precise timing
    MINUTE(MEETINGS."StartTime")

    // Get appointment minute
    MINUTE(APPOINTMENTS."ScheduledTime")

    // Extract timestamp minute
    MINUTE(EVENTS."EventTime")
    ```

    **Arguments:**

    * `datetime`: Datetime to extract minute from

    <Info>
      Returns a number between 0 and 59.
    </Info>
  </Accordion>

  <Accordion title="SECOND - Extract second from datetime">
    Returns the second (0-59) from a datetime.

    **Syntax:** `SECOND(datetime)`

    **Business Example:**

    ```javascript theme={null}
    // Extract second for precise timing
    SECOND(TRANSACTIONS."Timestamp")

    // Get event second
    SECOND(EVENTS."EventTime")

    // Extract log second
    SECOND(LOGS."LogTime")
    ```

    **Arguments:**

    * `datetime`: Datetime to extract second from

    <Info>
      Returns a number between 0 and 59.
    </Info>
  </Accordion>

  <Accordion title="WEEKDAY - Get day of week">
    Returns the day of the week (1-7) for a date.

    **Syntax:** `WEEKDAY(date, [type])`

    **Business Example:**

    ```javascript theme={null}
    // Get weekday for scheduling
    WEEKDAY(MEETINGS."MeetingDate", 2)

    // Analyze sales by day of week
    WEEKDAY(SALES."SaleDate")

    // Check if order was placed on a specific day
    WEEKDAY(ORDERS."OrderDate") = 1
    ```

    **Arguments:**

    * `date`: Date to get weekday from
    * `type`: \[OPTIONAL] 1=Sun-Sat (1-7), 2=Mon-Sun (1-7), 3=Mon-Sun (0-6)

    <Info>
      Type 1 (default): Sunday=1, Monday=2, ..., Saturday=7
    </Info>
  </Accordion>
</AccordionGroup>

### Text Functions

<AccordionGroup>
  <Accordion title="CONCAT - Join text together">
    Joins multiple text values into a single string.

    **Syntax:** `CONCAT(text1, text2, ...)`

    **Business Example:**

    ```javascript theme={null}
    // Customer full name
    CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

    // Product description
    CONCAT(PRODUCTS."Brand", ' - ', PRODUCTS."Model", ' (', PRODUCTS."Color", ')')

    // Order summary
    CONCAT('Order #', ORDERS."OrderNumber", ' - ', ORDERS."Status")

    // Address formatting
    CONCAT(CUSTOMERS."Street", ', ', CUSTOMERS."City", ', ', CUSTOMERS."State")
    ```

    **Arguments:**

    * `text1, text2, ...`: Text values to join together

    <Info>
      Various field types are automatically converted to text for concatenation.
    </Info>

    <Warning>
      `CONCAT` is the only way to join text values. **The `+` operator is for numeric addition only** — it does not concatenate strings, even though it does in some other languages. See [Calculations troubleshooting](/data/calculations-troubleshooting#concatenating-strings).
    </Warning>
  </Accordion>

  <Accordion title="UPPER - Convert to uppercase">
    Converts text to uppercase letters.

    **Syntax:** `UPPER(text)`

    **Business Example:**

    ```javascript theme={null}
    // Standardize product codes
    UPPER(PRODUCTS."SKU")

    // Format state abbreviations
    UPPER(CUSTOMERS."State")

    // Consistent department names
    UPPER(EMPLOYEES."Department")

    // Normalize country codes
    UPPER(ADDRESSES."CountryCode")
    ```

    **Arguments:**

    * `text`: Text to convert to uppercase

    <Info>
      Useful for standardizing data entry and comparisons.
    </Info>
  </Accordion>

  <Accordion title="LOWER - Convert to lowercase">
    Converts text to lowercase letters.

    **Syntax:** `LOWER(text)`

    **Business Example:**

    ```javascript theme={null}
    // Standardize email addresses
    LOWER(CUSTOMERS."Email")

    // Consistent username format
    LOWER(USERS."Username")

    // Normalize search terms
    LOWER(SEARCH."Query")

    // Standardize domain names
    LOWER(WEBSITES."Domain")
    ```

    **Arguments:**

    * `text`: Text to convert to lowercase

    <Info>
      Use when you need case-insensitive comparisons or normalized text (for example, email addresses).
    </Info>
  </Accordion>

  <Accordion title="LEFT - Extract from start of text">
    Extracts characters from the beginning of a string.

    **Syntax:** `LEFT(text, number_of_characters)`

    **Business Example:**

    ```javascript theme={null}
    // Extract first 3 characters of product code
    LEFT(PRODUCTS."SKU", 3)

    // Get first initial
    LEFT(CUSTOMERS."FirstName", 1)

    // Extract area code from phone
    LEFT(CUSTOMERS."Phone", 3)

    // Get first part of order number
    LEFT(ORDERS."OrderNumber", 4)
    ```

    **Arguments:**

    * `text`: String to extract from
    * `number_of_characters`: Number of characters to extract

    <Info>
      Returns the entire string if requested length exceeds string length.
    </Info>
  </Accordion>

  <Accordion title="RIGHT - Extract from end of text">
    Extracts characters from the end of a string.

    **Syntax:** `RIGHT(text, number_of_characters)`

    **Business Example:**

    ```javascript theme={null}
    // Extract last 4 digits of credit card
    RIGHT(PAYMENTS."CardNumber", 4)

    // Get file extension
    RIGHT(ATTACHMENTS."FileName", 4)

    // Extract year from date string
    RIGHT(RECORDS."DateString", 4)

    // Get last part of account number
    RIGHT(ACCOUNTS."AccountNumber", 6)
    ```

    **Arguments:**

    * `text`: String to extract from
    * `number_of_characters`: Number of characters to extract

    <Info>
      Returns the entire string if requested length exceeds string length.
    </Info>
  </Accordion>

  <Accordion title="MID - Extract from middle of text">
    Extracts substring from specified position.

    **Syntax:** `MID(text, start_position, number_of_characters)`

    **Business Example:**

    ```javascript theme={null}
    // Extract middle digits from account number
    MID(ACCOUNTS."AccountNumber", 5, 4)

    // Get month from date string (MM/DD/YYYY)
    MID(RECORDS."DateString", 4, 2)

    // Extract product category from code
    MID(PRODUCTS."SKU", 3, 2)
    ```

    **Arguments:**

    * `text`: String to extract from
    * `start_position`: Starting position (1-based)
    * `number_of_characters`: Number of characters to extract

    <Info>
      Position counting starts at 1, not 0.
    </Info>
  </Accordion>

  <Accordion title="FIND - Find text position (case-sensitive)">
    Returns position of first case-sensitive substring match.

    **Syntax:** `FIND(search_text, text_to_search, [start_position])`

    **Business Example:**

    ```javascript theme={null}
    // Find @ symbol in email
    FIND('@', CUSTOMERS."Email")

    // Find dash in product code
    FIND('-', PRODUCTS."SKU")

    // Find space in full name
    FIND(' ', CUSTOMERS."FullName")
    ```

    **Arguments:**

    * `search_text`: Text to find
    * `text_to_search`: Text to search within
    * `start_position`: \[OPTIONAL] Starting position for search

    <Info>
      Returns 0 if text not found. Case-sensitive search.
    </Info>
  </Accordion>

  <Accordion title="SEARCH - Find text position (case-insensitive)">
    Returns position of first case-insensitive substring match.

    **Syntax:** `SEARCH(search_text, text_to_search, [start_position])`

    **Business Example:**

    ```javascript theme={null}
    // Find 'premium' in product name (any case)
    SEARCH('premium', PRODUCTS."Name")

    // Find 'manager' in job title
    SEARCH('manager', EMPLOYEES."JobTitle")

    // Find 'urgent' in support ticket
    SEARCH('urgent', TICKETS."Subject")
    ```

    **Arguments:**

    * `search_text`: Text to find
    * `text_to_search`: Text to search within
    * `start_position`: \[OPTIONAL] Starting position for search

    <Info>
      Returns 0 if text not found. Case-insensitive search.
    </Info>
  </Accordion>

  <Accordion title="SUBSTITUTE - Replace text">
    Replaces text occurrences in a string.

    **Syntax:** `SUBSTITUTE(text, old_text, new_text)`

    **Business Example:**

    ```javascript theme={null}
    // Replace dashes with spaces in product codes
    SUBSTITUTE(PRODUCTS."SKU", '-', ' ')

    // Replace old company name in addresses
    SUBSTITUTE(CUSTOMERS."Address", 'Old Corp', 'New Corp')

    // Clean phone number formatting
    SUBSTITUTE(CUSTOMERS."Phone", '(', '')
    ```

    **Arguments:**

    * `text`: Original text
    * `old_text`: Text to replace
    * `new_text`: Replacement text

    <Info>
      Replaces ALL occurrences of old\_text with new\_text.
    </Info>
  </Accordion>

  <Accordion title="TRIM - Remove leading and trailing spaces">
    Removes whitespace from the beginning and end of a string.

    **Syntax:** `TRIM(text)`

    **Business Example:**

    ```javascript theme={null}
    // Clean up imported customer names
    TRIM(IMPORTS."CustomerName")

    // Normalize email entries before comparison
    TRIM(LOWER(CUSTOMERS."Email"))

    // Remove padding from form input
    TRIM(FORMS."CommentField")
    ```

    **Arguments:**

    * `text`: Text to trim

    <Info>
      Useful for cleaning imported data or user input that may contain accidental whitespace. Spaces between words are preserved.
    </Info>
  </Accordion>

  <Accordion title="LEN - Get text length">
    Returns the number of characters in a string.

    **Syntax:** `LEN(text)`

    **Business Example:**

    ```javascript theme={null}
    // Check if password meets minimum length
    LEN(USERS."Password") >= 8

    // Validate phone number length
    LEN(CUSTOMERS."Phone") = 10

    // Check product code format
    LEN(PRODUCTS."SKU") = 8

    // Validate input length
    LEN(FORMS."Description") <= 500
    ```

    **Arguments:**

    * `text`: Text to measure

    <Info>
      Useful for data validation and formatting checks.
    </Info>
  </Accordion>

  <Accordion title="STRING_AGG - Concatenate related values">
    Concatenates multiple related strings with a delimiter.

    **Syntax:** `STRING_AGG(related_field, delimiter)`

    **Business Example:**

    ```javascript theme={null}
    // List all order items
    STRING_AGG(ORDER_ITEMS."ProductName", ', ')

    // Create skill list for employee
    STRING_AGG(EMPLOYEE_SKILLS."SkillName", ', ')

    // List customer tags
    STRING_AGG(CUSTOMER_TAGS."TagName", ', ')
    ```

    **Arguments:**

    * `related_field`: Field from related records to concatenate
    * `delimiter`: Text to put between each value

    <Info>
      Builds a single text value from related records, separated by the delimiter you choose.
    </Info>

    <Warning>
      **Accepted input types:** single-value text only, supplied via a related-field aggregation. Passing a `MULTI_PICKLIST` field or an array-shaped value raises `Invalid Type Error`. There is no supported way to aggregate a multi-picklist field directly — see [Calculations troubleshooting](/data/calculations-troubleshooting#aggregating-a-multi-picklist-into-a-delimited-string).
    </Warning>
  </Accordion>

  <Accordion title="STRING_AGG_UNIQUE - Concatenate unique values">
    Concatenates unique values from a related field.

    **Syntax:** `STRING_AGG_UNIQUE(related_field, delimiter)`

    **Business Example:**

    ```javascript theme={null}
    // List unique product categories
    STRING_AGG_UNIQUE(ORDER_ITEMS."Category", ', ')

    // List unique customer locations
    STRING_AGG_UNIQUE(CUSTOMERS."City", ', ')

    // List unique skills
    STRING_AGG_UNIQUE(EMPLOYEE_SKILLS."SkillName", ', ')
    ```

    **Arguments:**

    * `related_field`: Field from related records to concatenate
    * `delimiter`: Text to put between each value

    <Info>
      Automatically removes duplicates before concatenating.
    </Info>

    <Warning>
      **Accepted input types:** single-value text only, supplied via a related-field aggregation. Passing a `MULTI_PICKLIST` field or an array-shaped value raises `Invalid Type Error`. There is no supported way to aggregate a multi-picklist field directly — see [Calculations troubleshooting](/data/calculations-troubleshooting#aggregating-a-multi-picklist-into-a-delimited-string).
    </Warning>
  </Accordion>

  <Accordion title="SPLIT - Split text into parts">
    Splits a string into an array of substrings using a delimiter.

    **Syntax:** `SPLIT(text, delimiter)`

    **Business Example:**

    ```javascript theme={null}
    // Split a multi-value tag field
    // If PRODUCTS."Colors" is 'Red|Green|Blue', returns ['Red', 'Green', 'Blue']
    SPLIT(PRODUCTS."Colors", '|')

    // Parse comma-separated labels
    SPLIT(CUSTOMERS."Tags", ',')

    // Break apart a structured order reference
    SPLIT(ORDERS."Reference", '-')
    ```

    **Arguments:**

    * `text`: String to split
    * `delimiter`: Character or string to split on

    <Info>
      Returns an array of substrings. An empty delimiter (`''`) splits the text into individual characters. Returns blank if the input is blank.
    </Info>

    <Warning>
      `SPLIT` returns an array, but **selecting a single element from the result is not currently supported** in calculations. To extract "the part before the dash" or similar, use `LEFT`/`RIGHT`/`MID` combined with `FIND`/`SEARCH` instead — see [Calculations troubleshooting](/data/calculations-troubleshooting#split-returns-an-array-you-cant-index-into) for patterns.
    </Warning>
  </Accordion>

  <Accordion title="TEXT - Convert to text">
    Converts numbers or dates to text format.

    **Syntax:** `TEXT(value)`

    **Business Example:**

    ```javascript theme={null}
    // Convert order amount to text
    TEXT(ORDERS."Amount")

    // Convert date to text
    TEXT(ORDERS."OrderDate")

    // Convert ID to text for concatenation
    TEXT(CUSTOMERS."ID")
    ```

    **Arguments:**

    * `value`: Number or date to convert

    <Info>
      Useful when you need to treat numbers as text for concatenation.
    </Info>

    <Warning>
      **`TEXT` does not accept a format string.** It returns the value's default text representation — there is no second argument to control output (no `'YYYY-MM-DD'`, `'MM/DD/YYYY'`, or similar). To produce a specific date or datetime string, extract the parts with `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND` and assemble them with `CONCAT`:

      ```javascript theme={null}
      // 'YYYY-MM-DD' from a date or datetime field
      CONCAT(
        TEXT(YEAR(ORDERS."OrderDate")), '-',
        TEXT(MONTH(ORDERS."OrderDate")), '-',
        TEXT(DAY(ORDERS."OrderDate"))
      )
      ```

      Pad single-digit months and days yourself if you need zero-padding (for example with `IF(MONTH(...) < 10, CONCAT('0', TEXT(MONTH(...))), TEXT(MONTH(...)))`).
    </Warning>
  </Accordion>

  <Accordion title="VALUE - Convert text to number">
    Converts text to a number.

    **Syntax:** `VALUE(text)`

    **Business Example:**

    ```javascript theme={null}
    // Convert text amount to number
    VALUE(IMPORTS."AmountText")

    // Convert text quantity to number
    VALUE(FORMS."QuantityInput")

    // Convert text ID to number
    VALUE(EXTERNAL."IDString")
    ```

    **Arguments:**

    * `text`: Text to convert to number

    <Warning>
      Returns blank if text cannot be converted to a number.
    </Warning>
  </Accordion>

  <Accordion title="REPT - Repeat text">
    Repeats a string a specified number of times.

    **Syntax:** `REPT(text, number_of_times)`

    **Business Example:**

    ```javascript theme={null}
    // Repeat a character to match a rating or count
    REPT('*', PRODUCTS."Rating")

    // Create padding
    REPT(' ', 10)

    // Create separators
    REPT('-', 20)
    ```

    **Arguments:**

    * `text`: String to repeat
    * `number_of_times`: Number of repetitions

    <Info>
      Use for padding, separators, or repeating a character a fixed number of times.
    </Info>
  </Accordion>

  <Accordion title="REGEXEXTRACT - Extract with regex">
    Extracts text using a regular expression pattern.

    **Syntax:** `REGEXEXTRACT(text, pattern)`

    <Warning>
      **Escape backslashes in regex patterns.** Calculation strings parse `\` as an escape character, so any regex metacharacter that uses a backslash must be written with a doubled backslash. Use `'\\d'` (not `'\d'`), `'\\s+'`, `'\\.'`, `'\\(\\d{3}\\)'`, etc. A single backslash will be stripped before the regex engine sees the pattern, causing the match to silently fail and return an empty value. For literal characters that don't strictly need escaping in regex (such as a pipe), prefer a character class — `'[|]'` — to sidestep escaping entirely. This applies to `REGEXEXTRACT`, `REGEXMATCH`, and `REGEXREPLACE`.
    </Warning>

    **Business Example:**

    ```javascript theme={null}
    // Extract phone area code
    REGEXEXTRACT(CUSTOMERS."Phone", '\\((\\d{3})\\)')

    // Extract email domain
    REGEXEXTRACT(CUSTOMERS."Email", '@(.+)')

    // Extract order number
    REGEXEXTRACT(ORDERS."Reference", 'ORD-(\\d+)')
    ```

    **Arguments:**

    * `text`: Text to extract from
    * `pattern`: Regular expression pattern

    <Warning>
      Requires knowledge of regular expressions. Use with caution.
    </Warning>

    <Warning>
      **Returns the first match only**, not a list of matches. There is no built-in way to extract every match in one call.
    </Warning>

    <Info>
      Patterns follow **Java-style regex syntax**. Most expressions port unchanged from JavaScript, Python, or PCRE, but watch for escape handling and a few advanced constructs. See [Calculations troubleshooting](/data/calculations-troubleshooting#regex-syntax-java-style) for the differences that matter.
    </Info>
  </Accordion>

  <Accordion title="REGEXMATCH - Test regex pattern">
    Tests if text matches a regular expression pattern.

    **Syntax:** `REGEXMATCH(text, pattern)`

    **Business Example:**

    ```javascript theme={null}
    // Validate email format
    REGEXMATCH(CUSTOMERS."Email", '^[\\w\\.-]+@[\\w\\.-]+\\.[a-zA-Z]{2,}$')

    // Check phone format
    REGEXMATCH(CUSTOMERS."Phone", '^\\(\\d{3}\\) \\d{3}-\\d{4}$')

    // Validate product code
    REGEXMATCH(PRODUCTS."SKU", '^[A-Z]{3}-\\d{4}$')
    ```

    **Arguments:**

    * `text`: Text to test
    * `pattern`: Regular expression pattern

    <Info>
      Returns TRUE if pattern matches, FALSE otherwise.
    </Info>

    <Warning>
      **`REGEXMATCH` does not enforce input format.** It only returns a boolean — it does **not** block saving a record, display a validation error to the user, or revert a bad value on its own. To act on a non-match, use the result in an automation (for example, block a stage transition, send a notification, or set a status flag when `REGEXMATCH` returns `FALSE`). To require a format on the form itself, use the **Required** flag and **Helper Text** in the [Form Builder](/workflows/form-builder) — there is no built-in regex mask on text fields.
    </Warning>

    <Info>
      Patterns follow **Java-style regex syntax**. See [Calculations troubleshooting](/data/calculations-troubleshooting#regex-syntax-java-style) for syntax differences if you're porting patterns from another flavor.
    </Info>
  </Accordion>

  <Accordion title="REGEXREPLACE - Replace with regex">
    Replaces text using regular expression patterns.

    **Syntax:** `REGEXREPLACE(text, pattern, replacement, [case_insensitive])`

    **Business Example:**

    ```javascript theme={null}
    // Format phone numbers
    REGEXREPLACE(CUSTOMERS."Phone", '(\\d{3})(\\d{3})(\\d{4})', '($1) $2-$3')

    // Clean product codes
    REGEXREPLACE(PRODUCTS."SKU", '[^A-Z0-9-]', '')

    // Standardize names
    REGEXREPLACE(CUSTOMERS."Name", '\\s+', ' ')
    ```

    **Arguments:**

    * `text`: Text to modify
    * `pattern`: Regular expression pattern
    * `replacement`: Replacement text
    * `case_insensitive`: \[OPTIONAL] TRUE for case-insensitive matching

    <Warning>
      Advanced feature requiring regex knowledge.
    </Warning>

    <Info>
      Replaces **all** matches of the pattern in the string, not just the first.
    </Info>

    <Info>
      Patterns follow **Java-style regex syntax**. Backreferences in the replacement string use `$1`, `$2`, etc. See [Calculations troubleshooting](/data/calculations-troubleshooting#regex-syntax-java-style) for syntax differences if you're porting patterns from another flavor.
    </Info>
  </Accordion>

  <Accordion title="JSON_ESCAPE - Escape JSON string">
    Escapes special characters in a string for use in JSON.

    **Syntax:** `JSON_ESCAPE(text)`

    **Business Example:**

    ```javascript theme={null}
    // Escape a string with quotes and newlines
    JSON_ESCAPE('Hello "world" with\nnewlines')
    // Result: "Hello \\"world\\" with\\\\nnewlines"
    ```

    **Arguments:**

    * `text`: Text to escape

    <Info>
      This function is useful for safely embedding text into JSON payloads.
    </Info>

    <CardGroup cols={2}>
      <Card title="Send API Request" icon="paper-plane" href="/workflows/automation-actions-reference#send-api-request">
        Learn how to send JSON data to external systems.
      </Card>

      <Card title="JSON File Reader" icon="file-lines" href="/workflows/json-file-reader">
        Learn how to parse and import data from JSON files.
      </Card>
    </CardGroup>
  </Accordion>

  <Accordion title="JSON_UNESCAPE - Unescape JSON string">
    Unescapes special characters in a JSON string.

    **Syntax:** `JSON_UNESCAPE(text)`

    **Business Example:**

    ```javascript theme={null}
    // Unescape a JSON-escaped string
    JSON_UNESCAPE('Hello \\"world\\" with\\\\nnewlines')
    // Result: "Hello \"world\" with\nnewlines"
    ```

    **Arguments:**

    * `text`: Text to unescape

    <Info>
      This is the inverse of JSON\_ESCAPE, useful for parsing data from JSON payloads.
    </Info>

    <CardGroup cols={2}>
      <Card title="Send API Request" icon="paper-plane" href="/workflows/automation-actions-reference#send-api-request">
        Learn how to send JSON data to external systems.
      </Card>

      <Card title="JSON File Reader" icon="file-lines" href="/workflows/json-file-reader">
        Learn how to parse and import data from JSON files.
      </Card>
    </CardGroup>
  </Accordion>
</AccordionGroup>

### Mathematical Functions

<AccordionGroup>
  <Accordion title="POWER - Raise to power">
    Raises a number to a specified power.

    **Syntax:** `POWER(base, exponent)`

    **Business Example:**

    ```javascript theme={null}
    // Calculate compound interest
    POWER(1.05, YEARS."Investment")

    // Calculate area of square
    POWER(DIMENSIONS."Side", 2)

    // Calculate exponential growth
    POWER(GROWTH."Rate", PERIODS."Number")
    ```

    **Arguments:**

    * `base`: Base number
    * `exponent`: Power to raise to

    <Info>
      Any number raised to the power of 0 equals 1.
    </Info>
  </Accordion>

  <Accordion title="SQRT - Square root">
    Calculates the square root of a number.

    **Syntax:** `SQRT(number)`

    **Business Example:**

    ```javascript theme={null}
    // Calculate standard deviation component
    SQRT(VARIANCE."Value")

    // Calculate distance formula component
    SQRT(COORDINATES."X" * COORDINATES."X" + COORDINATES."Y" * COORDINATES."Y")

    // Calculate geometric mean component
    SQRT(METRICS."Value1" * METRICS."Value2")
    ```

    **Arguments:**

    * `number`: Number to find square root of

    <Warning>
      Returns null if the number is negative.
    </Warning>
  </Accordion>
</AccordionGroup>

### Special Functions

<AccordionGroup>
  <Accordion title="TRUE - Boolean TRUE">
    Returns the Boolean value TRUE.

    **Syntax:** `TRUE()`

    **Business Example:**

    ```javascript theme={null}
    // Set default active status
    TRUE()

    // Use in conditional logic
    IF(CUSTOMERS."Status" = 'Active', TRUE(), FALSE())

    // Initialize flags
    TRUE()
    ```

    <Info>
      Useful for setting boolean field values and conditional logic.
    </Info>
  </Accordion>

  <Accordion title="FALSE - Boolean FALSE">
    Returns the Boolean value FALSE.

    **Syntax:** `FALSE()`

    **Business Example:**

    ```javascript theme={null}
    // Set default inactive status
    FALSE()

    // Use in conditional logic
    IF(ORDERS."Amount" > 0, TRUE(), FALSE())

    // Initialize flags
    FALSE()
    ```

    <Info>
      Useful for setting boolean field values and conditional logic.
    </Info>
  </Accordion>

  <Accordion title="BLANK - Return blank value">
    Returns a blank/null value.

    **Syntax:** `BLANK()`

    **Business Example:**

    ```javascript theme={null}
    // Clear a field conditionally
    IF(ORDERS."Status" = 'Cancelled', BLANK(), ORDERS."ShipDate")

    // Set default empty value
    BLANK()

    // Use in conditional assignments
    IF(CUSTOMERS."Type" = 'Guest', BLANK(), CUSTOMERS."LoyaltyPoints")
    ```

    <Info>
      Represents the absence of data, different from empty string.
    </Info>
  </Accordion>

  <Accordion title="ISBLANK - Test if blank">
    Tests if a value is blank/null.

    **Syntax:** `ISBLANK(value)`

    **Business Example:**

    ```javascript theme={null}
    // Check if customer has phone number
    ISBLANK(CUSTOMERS."Phone")

    // Validate required fields
    ISBLANK(ORDERS."ShippingAddress")

    // Check for missing data
    ISBLANK(PRODUCTS."Description")

    // Conditional logic based on blank values
    IF(ISBLANK(CUSTOMERS."Email"), 'No Email', 'Has Email')
    ```

    **Arguments:**

    * `value`: Value to test for blankness

    <Info>
      Returns TRUE if the value represents an absence of data — an empty text value (`''`) or a true null both count as blank. Returns FALSE if the value contains any actual data, including a text field whose content is the four-character string `null` (an actual text value that's different from a true absence of value) — see [Calculations troubleshooting](/data/calculations-troubleshooting#detecting-missing-values-including-the-literal-text-null) for the pattern to detect that case.
    </Info>
  </Accordion>

  <Accordion title="UUID - Generate unique ID">
    Generates a random UUID (Universally Unique Identifier).

    **Syntax:** `UUID()`

    **Business Example:**

    ```javascript theme={null}
    // Generate unique transaction ID
    UUID()

    // Create unique reference number
    UUID()

    // Generate API key
    UUID()
    ```

    <Info>
      Returns a string in format: 'f81d4fae-7dec-11d0-a765-00a0c91e6bf6'
    </Info>
  </Accordion>
</AccordionGroup>

## Troubleshooting

<AccordionGroup>
  <Accordion title="Common Error: Blank Results">
    **Problem:** Your calculation returns blank instead of expected values.

    **Causes & Solutions:**

    * **Blank input data**: Check that referenced fields contain data
    * **Invalid field references**: Ensure field names are correct and properly quoted
    * **Type mismatches**: Verify you're using the right function for your data type

    **Example Fix:**

    ```javascript theme={null}
    // Instead of this (might return blank):
    AVERAGE(ORDERS."Amount")

    // Try this (handles blanks better):
    IF(COUNT(ORDERS."Amount") > 0, AVERAGE(ORDERS."Amount"), 0)
    ```

    **Prevention:**

    * Always test with sample data
    * Use ISBLANK() to check for missing data
    * Validate field names match exactly
  </Accordion>

  <Accordion title="Common Error: Function Not Working with Current Record">
    **Problem:** Functions like SUM, COUNT, AVERAGE don't work with fields from the current record.

    **Solution:** These functions only work with related fields. For current record calculations, use operators:

    ```javascript theme={null}
    // Wrong - won't work:
    SUM(CURRENT."Field1", CURRENT."Field2")

    // Right - use operators:
    CURRENT."Field1" + CURRENT."Field2"

    // Right - for related data:
    SUM(RELATED_RECORDS."Field")
    ```

    **Key Point:** Aggregate functions (SUM, COUNT, AVERAGE, etc.) are designed for related data, not individual field operations.
  </Accordion>

  <Accordion title="Common Error: Date Calculation Issues">
    **Problem:** Date calculations returning unexpected results.

    **Common Fixes:**

    * **Text dates**: Use `DATEVALUE()` to convert text to proper dates
    * **Timezone issues**: Ensure consistent timezone handling
    * **Format problems**: Check date format consistency

    **Example Fix:**

    ```javascript theme={null}
    // If date is stored as text:
    DATEDIF(DATEVALUE(CUSTOMERS."SignupDate"), NOW(), 'D')

    // For consistent date creation:
    DATE(YEAR(NOW()), MONTH(NOW()), 1)

    // Handle blank dates:
    IF(ISBLANK(ORDERS."ShipDate"), 'Not Shipped', DATEDIF(ORDERS."OrderDate", ORDERS."ShipDate", 'D'))
    ```
  </Accordion>

  <Accordion title="Common Error: Text Function Confusion">
    **Problem:** FIND vs SEARCH, UPPER vs LOWER, concatenation issues.

    **Solutions:**

    * **FIND**: Case-sensitive search
    * **SEARCH**: Case-insensitive search
    * **CONCAT**: Joins multiple values
    * **Always use single quotes** for text literals

    **Example Fixes:**

    ```javascript theme={null}
    // Case-sensitive search:
    FIND('Manager', EMPLOYEES."Title")

    // Case-insensitive search:
    SEARCH('manager', EMPLOYEES."Title")

    // Proper concatenation:
    CONCAT(CUSTOMERS."FirstName", ' ', CUSTOMERS."LastName")

    // Wrong - don't use double quotes:
    CONCAT(CUSTOMERS."FirstName", " ", CUSTOMERS."LastName")
    ```
  </Accordion>

  <Accordion title="Common Error: Field Reference Issues">
    **Problem:** Field references not working, getting 'field not found' errors.

    **Solutions:**

    * **Check field names**: Must match exactly (case-sensitive)
    * **Use proper syntax**: HANDLE."FieldName" format
    * **Verify relationships**: Ensure fields are properly related

    **Example Fixes:**

    ```javascript theme={null}
    // Correct field reference:
    CUSTOMERS."FirstName"

    // Wrong - missing quotes:
    CUSTOMERS.FirstName

    // Wrong - incorrect case:
    CUSTOMERS."firstname"

    // For related fields:
    RELATED_CUSTOMERS."FirstName"
    ```
  </Accordion>

  <Accordion title="Performance Issues">
    **Problem:** Calculations running slowly or timing out.

    **Solutions:**

    * **Simplify complex calculations**: Break into smaller parts
    * **Avoid nested functions**: Use intermediate calculations
    * **Check data volumes**: Large datasets may need optimization

    **Example Optimization:**

    ```javascript theme={null}
    // Instead of nested complexity:
    IF(AND(CUSTOMERS."Status" = 'Active', DATEDIF(CUSTOMERS."LastPurchase", NOW(), 'D') < 30), 'Recent', 'Old')

    // Break it down:
    // Step 1: Days since purchase
    DATEDIF(CUSTOMERS."LastPurchase", NOW(), 'D')

    // Step 2: Use result in simpler IF
    IF(PREVIOUS_CALC < 30, 'Recent', 'Old')
    ```
  </Accordion>
</AccordionGroup>

## Best Practices

These guidelines help keep calculations reliable and easy to maintain.

<AccordionGroup>
  <Accordion title="Naming and Structure">
    * Use descriptive field names that clearly indicate purpose
    * Keep calculations simple and readable
    * Break complex logic into multiple steps
    * Document complex calculations with comments
  </Accordion>

  <Accordion title="Data Validation">
    * Always check for blank values using ISBLANK()
    * Validate data types before performing operations
    * Use IF statements to handle edge cases
    * Test calculations with various data scenarios
  </Accordion>

  <Accordion title="Performance Optimization">
    * Avoid deeply nested functions
    * Use intermediate calculations for complex logic
    * Consider data volume when designing calculations
    * Test performance with realistic data sets
  </Accordion>

  <Accordion title="Error Prevention">
    * Use proper field reference syntax: HANDLE."FieldName"
    * Always use single quotes for text literals, never double quotes
    * Verify field relationships before using aggregate functions
    * Test calculations thoroughly before deployment
  </Accordion>
</AccordionGroup>

## Related documentation

* **[Core concepts](/getting-started/fundamentals/core-concepts)** — Apps, Elements, fields, and how records connect
* **[Tables](/data/tables)** — Calculated columns and spreadsheet-style views of your data
* **[Showing relationships](/data/showing-relationships)** — Related records and how aggregates apply to related fields
* **[Data best practices](/data/data-best-practices)** — Structuring data so formulas and reports stay maintainable
* **[Automation system](/workflows/automation-system)** — Triggers and actions where calculations often appear

## Need More Help?

<CardGroup cols={2}>
  <Card title="Help & Resources" icon="book-open" href="/support/resources">
    Support options, self-help topics, and how to reach the team
  </Card>

  <Card title="Support Tickets" icon="life-ring" href="/support">
    Get direct help from our support team
  </Card>

  <Card title="Best Practices Guide" icon="lightbulb" href="/getting-started/best-practices">
    Learn advanced calculation techniques
  </Card>

  <Card title="Video Tutorials" icon="play" href="/support">
    Watch step-by-step calculation examples
  </Card>
</CardGroup>