Formulas
Formulas let you write validation rules that check extracted data, perform calculations, and make decisions based on document content. Think of them like Excel formulas, but for validating your workflow data.
Quick Examples
Here are some common validation scenarios:
Writing Your First Formula
Formulas are made up of three parts:
- Variables - Reference data from your workflow (e.g.,
extraction1.total_amount) - Operations - Compare or calculate values (e.g.,
=,+,*,>) - Functions - Built-in tools for common tasks (e.g.,
ISPRESENT(),SUM(),IF())
Example: Validating an invoice total
Let’s say you extracted these fields from an invoice:
extraction1.subtotal= 100extraction1.tax= 10extraction1.total= 110
You can validate the math with:
This formula returns TRUE if the total equals subtotal plus tax, and FALSE otherwise.
How to Reference Variables
Variables let you access data from different workflow steps. The format depends on the step type:
Classification Steps
Format: Just the step name
Example: classification1
This returns the document type as text (e.g., "invoice", "receipt", "contract").
Extraction Steps
Format: stepName.fieldName
Example: extraction1.invoice_number
This returns the value of the extracted field. The value will be NULL if the field wasn’t found.
For nested fields or arrays:
When referencing a subfield of an array (like line_items.quantity), you get an array of all the quantity values.
External Data Validation Steps
Format: stepName.path.to.field
Example: external1.customer.credit_limit
This returns data from your external system based on the JSON path in the response.
Make sure your external API returns data in the right format. For array operations, return an object containing arrays, not an array of objects.
Good: {"prices": [10, 20, 30]}
Bad: [{"price": 10}, {"price": 20}]
Metadata
Format: metadata.fieldName
Example: metadata.document_id
This returns metadata values attached to your workflow run.
Understanding Data Types
Every value in a formula has a type. This determines what operations you can perform on it.
Working with Currency
Currency values behave like numbers in math operations:
This works even if subtotal is currency and tax is a plain number. The result will be currency.
Note: Mixing different currency codes in the same operation will cause an error. For example, $100 USD + $100 AUD is not allowed.
Working with Arrays
When you reference a field inside an array (like line_items.price), you get an array of all those values:
This adds up all the prices from all line items.
Handling Missing Data (NULL values)
If a field wasn’t extracted, its value is NULL. The behavior depends on how you use it:
- In comparisons (
<,>,<=,>=):NULLacts like0 - In equality checks (
=,<>):NULLonly equalsNULL - In math operations:
NULLacts like0 - As text:
NULLacts like"" - As boolean:
NULLacts likeFALSE
Check if a field has a value:
Provide a default value if missing:
This returns the discount if it exists, otherwise returns 0.
Using Constants
You can use fixed values directly in your formulas:
Numbers: 100, 3.14, -50
Text: Use double quotes "text here"
To include quotes in text, use \":
Booleans: TRUE or FALSE
Dates: Use the DATE() function
Operations
Operations let you compare values, do math, and combine text. They follow standard order of operations (like math class): parentheses first, then multiplication/division, then addition/subtraction, then comparisons.
Comparison Operations
Compare two values and get TRUE or FALSE. All text comparisons are case-insensitive.
Math Operations
Perform calculations on numbers:
Text Operation
Order of Operations
Operations are evaluated in this order (like math):
- Parentheses:
( ... ) - Functions:
FUNC( ... ) - Power:
^ - Multiply/Divide:
*/ - Add/Subtract/Concatenate:
+-& - Comparisons:
=<><><=>=~=
Example:
This multiplies price by quantity first, then adds tax, then checks if it equals total.
Built-in Functions
Functions help you handle common validation tasks. Here’s what’s available:
Checking for Values
ISPRESENT(field) - Returns TRUE if field has a value (not NULL)
Use this to ensure required fields were extracted.
ISBLANK(field) - Returns TRUE if field is empty (NULL)
Opposite of ISPRESENT.
ISEMPTY(array) - Returns TRUE if array field has no items
Use this to check if an array field is empty.
Conditional Logic
IF(condition, valueIfTrue, valueIfFalse) - Choose between two values based on a condition
If amount is over 1000, returns “high”, otherwise “normal”.
Real-world example:
This says: “If it’s an invoice, it must have an invoice number. If it’s not an invoice, that’s fine.”
IFNULL(field, defaultValue) - Provide a fallback value if field is empty
Uses the discount if extracted, otherwise uses 0.
Logic Operations
AND(condition1, condition2, ...) - All conditions must be TRUE
Amount must be between 0 and 10,000 AND date must be present.
OR(condition1, condition2, ...) - At least one condition must be TRUE
Status must be either “paid” or “completed”.
NOT(condition) - Flip TRUE to FALSE and vice versa
Same as ISPRESENT(extraction1.address).
Working with Dates
DATE(text) - Convert text to a date
Check if invoice date is after January 1, 2024.
Dates should be in format: "YYYY-MM-DD"
Array Functions
SUM(values...) - Add up numbers
This validates that the line item costs add up to the total. Perfect for invoice validation! You can also pass multiple values: SUM(a, b, c).
CONTAINS(array, value) - Check if value is in the array
Returns TRUE if “urgent” is one of the categories. Text matching is case-insensitive.
CONTAINSALL(array, [value1, value2]) - Check if all values are in the array
Returns TRUE only if both “approved” AND “verified” are in the tags array.
Common Validation Patterns
Here are some real-world examples combining multiple concepts:
Invoice Total Validation
Required Field for Document Type
Date Range Validation
Amount Within Credit Limit
Status Must Be Valid
Need a function that’s not listed here? Reach out to us on Slack - we’re happy to add more!
