How to write formulas that reference variables and perform calculations
Formula | Description |
---|---|
ISPRESENT(extraction1.address) | Verify that an address has been extracted |
extraction1.amount = extraction1.price * extraction1.quantity + extraction1.tax | Verify that the total amount is calculated correctly |
IF(classification1 = "invoice", extraction1.invoice_date, extraction2.due_date) | If the document is an invoice, then get the invoice date, otherwise get the due date |
classification1
.
The type of the variable will be text
and its value will be the file’s classification type.
extraction1.address
or extraction2.line_items.quantity
(for nested object or array fields).
The type of the variable will match the type of the extraction field.
For subfields of an array, the type will be an array of the subfield’s type.
The value may be NULL
if the field could not be extracted.
external1.path.to.field
.
Type | Description |
---|---|
boolean | A TRUE or FALSE value |
number | A 64bit floating number |
text | A string of arbitrary length |
date | A date without a time of day or time zone |
currency | A monetary value containing a number amount and a ISO 4217 currency code |
array<T> | An array of the non-array type T . Mixed type arrays are not permitted. |
currency
and array
types represent collections over one of the other primitive types and can be used wherever their underlying primitive type can be used.
For example, the currency
type’s primitive type is number
, so it can be used in numerical operations like addition or multiplication with other number
or currency
values.
So can an array of numbers, but not an array of texts.
Note that when an operation or a function uses both collection and primitive types, then the output will usually, but not always, belong to the collection type.
For example, adding a currency
and a number
produces a currency
, but comparing them will produce a boolean
instead.
NULL
which represents the absence of a value.
Generally, null values will be treated as zero values of their type (e.g. 0
or FALSE
or ""
) when used in an operation or function.
Use the ISPRESENT
, ISBLANK
, or IFNULL
functions to explicitly check for and handle nulls.
Operation | Type | Description |
---|---|---|
a = b | boolean | a is equal to b |
a <> b | boolean | a is not equal to b |
a < b | boolean | a is less than b |
a <= b | boolean | a is less or equal to b |
a > b | boolean | a is greater than b |
a >= b | boolean | a is greater than or equal to b |
a + b | number | a plus b |
a - b | number | a subtract b |
s & t | text | s concatenate t |
a * b | number | a multiplied by b |
a / b | number | a divided by b |
a ^ b | number | a raised to the power of b |
-a | number | Negative a |
( ... ) | T | Parentheses |
FUNC( ... ) | T | Function call |
Formula | Type | Description |
---|---|---|
ISPRESENT(arg: T) | boolean | Returns whether or not the argument is not NULL . |
ISBLANK(arg: T) | boolean | Returns whether or not the argument is NULL . |
IF(condition: boolean, ifTrue: T, ifFalse: T) | T | Returns the value of ifTrue if condition is TRUE , returns the value of ifFalse otherwise. |
IFNULL(arg: T, ifNull: T) | T | Returns the value of arg if it is not NULL , returns the value of ifNull otherwise. |
AND(arg: boolean, ...) | boolean | Returns TRUE if all the arguments are TRUE , returns FALSE otherwise. |
OR(arg: boolean, ...) | boolean | Returns TRUE if any of the arguments are TRUE , returns FALSE otherwise. |
NOT(arg: boolean) | boolean | Returns TRUE if the argument is falsey (e.g. FALSE , 0 , "" , NULL ), returns FALSE otherwise. |
DATE(arg: text) | date | Converts the given text into a date value. |
SUM(arg: array<T>) | T | Returns the sum of all the elements in the array. |
0
, 12.50
, -100
), texts (e.g. "hello!"
, ""
), or booleans (TRUE
or FALSE
) only.
To declare a constant date, use the DATE
function (e.g. DATE("2024-01-01")
).
To include double quotation marks in a text constant, escape it with a backslash \"
, e.g. "She said \"Hi!\""
.
SUM
function returns the sum of all subfield elements in an array field. For example, if you are extracting data from an invoice and have an array field line_items
with a subfield cost
, you can use the SUM
function to get the total cost of all the line items. You could then compare that to the total amount due and validate that the data was correctly extracted.