Formula Fields

Overview

Formula Fields are computed fields that automatically calculate values from other document data. Unlike AI-extracted fields, formulas run after extraction as a post-processing step, allowing you to validate, derive, enrich, and customize your document data.

When to Use Formula Fields

Formula Types

Sum Fields

Sum fields automatically aggregate numeric values from line items. They are auto-generated for every numeric field in your line item schemas.

Example: Automatically sum all item amounts to calculate a total:

{
  name: "calculatedTotal",
  type: "sum",
  displayName: "Calculated Total",
  formula: {
    type: "sum",
    fieldPath: "items.amount",
    fieldType: "currency",
    currencyFieldPath: "header.currency"
  }
}

Features:

• Auto-generated for numeric and currency fields
• Hidden by default (can be enabled in schema settings)
• Supports currency fields with automatic currency handling

Code Fields

Code fields allow you to write custom JavaScript to transform or enrich your data. They execute after sum fields and can access document data, file metadata, and datasets.

Example: Calculate profit margin:

{
  name: "margin",
  type: "code",
  displayName: "Profit Margin",
  formula: {
    type: "code",
    fieldType: "number",
    inputFields: ["header.total", "header.cost"],
    code: `
      const revenue = header.total || 0;
      const cost = header.cost || 0;
      return {result: revenue > 0 ? ((revenue - cost) / revenue) * 100 : 0};
    `
  }
}

Features:

• Full JavaScript support
• Can access header data, line items, and file metadata
• Supports async operations (e.g., await fetch())
• Can generate notifications for validation issues

Using Datasets

Formula fields can reference datasets (XLSX or CSV files marked as datasets) to enrich your documents with external data.

What are Datasets?

Datasets are special documents (XLSX or CSV files) that are marked as Datasets in the system. They contain reference data that can be used by formula fields for lookups and enrichment.

Example: SKU Lookup

Upload a dataset file (e.g., product-catalog.xlsx) with columns like SKU, Product Name, Price. Then use it in a formula field:

{
  name: "productName",
  type: "code",
  displayName: "Product Name",
  formula: {
    type: "code",
    fieldType: "text",
    datasets: ["productCatalog"], // Reference your dataset
    inputFields: ["sku"],
    code: `
      const lineItem = tx.getCurrentLineItem();
      const sku = lineItem?.sku;
      
      if (!sku) return;
      
      // Lookup product name from dataset
      const match = tx.dataset.lookup('productCatalog', 'SKU', sku);
      
      if (match) {
        tx.setResult(match['Product Name']);
      }
    `
  }
}

Dataset Features

• Case-insensitive matching: "productCatalog" matches "PRODUCT_CATALOG"
• Field aliasing: Access fields by name or display name
• Latest version: System automatically uses the latest version of each dataset
• Multiple datasets: Reference multiple datasets in a single formula

Execution Order

Formulas execute in a specific order to ensure dependencies are resolved:

1. Sum Fields - Simple aggregations run first
2. Code Fields - Custom JavaScript runs after sum fields
3. AI Fields - (Future phase)

This ensures that code fields can reference sum field results.

Formula Location

Formulas can be defined in two places:

Creating Formula Fields

In Document Type Settings

1. Navigate to Settings → Document Types
2. Select the document type you want to customize
3. Click Edit on a field or Add Field
4. Choose Sum or Code as the field type
5. Configure the formula:
   • Sum: Select the field path to aggregate
   • Code: Write your JavaScript code
6. For code fields using datasets, specify the dataset names in the datasets array

Testing Formulas

When testing a formula in the UI:

• Input fields are displayed so you can verify the computation
• Results are shown immediately
• Any notifications or errors are displayed

Best Practices

Sum Fields

• Use sum fields for simple aggregations
• They're automatically generated, so enable them when needed
• Perfect for validating totals match line item sums

Code Fields

• Keep code simple and readable
• Use inputFields to document which fields your formula uses
• Return results using tx.setResult(value)
• Use tx.addMessage() for validation warnings or errors
• Reference datasets by their document type name

Datasets

• Upload datasets as XLSX or CSV files
• Mark them as Datasets when uploading
• Use clear, consistent column names
• Keep datasets up to date for accurate lookups

Examples

Example 1: Validate Invoice Total

{
  name: "totalValidation",
  type: "code",
  displayName: "Total Validation",
  formula: {
    type: "code",
    fieldType: "boolean",
    inputFields: ["header.total", "items_amount_sum"],
    code: `
      const headerTotal = header.total || 0;
      const calculatedTotal = header.items_amount_sum || 0;
      const difference = Math.abs(headerTotal - calculatedTotal);
      
      if (difference > 0.01) {
        tx.addMessage(\`Total mismatch: header shows \${headerTotal} but sum is \${calculatedTotal}\`, {
          level: 'warning',
          field: 'total'
        });
        return {result: false};
      }
      
      return {result: true};
    `
  }
}

Example 2: Enrich with External Data

{
  name: "vendorCategory",
  type: "code",
  displayName: "Vendor Category",
  formula: {
    type: "code",
    fieldType: "text",
    datasets: ["vendorCatalog"],
    inputFields: ["vendorName"],
    code: `
      const vendorName = header.vendorName;
      if (!vendorName) return;
      
      const match = tx.dataset.lookup('vendorCatalog', 'Vendor Name', vendorName);
      if (match) {
        tx.setResult(match['Category']);
      }
    `
  }
}

Next Steps

• Learn about Document Types
• Explore Uploading Documents
• Check out advanced formula examples in the API documentation