Skip to main content

Using Formula

Learn how you can build formula in your data definitions

Updated today

Overview

Formulas in Kodexa allow you to calculate values for data elements based on other data elements in your Data Definition. Instead of extracting a value from the document, you can derive it through mathematical operations, text manipulation, logical conditions, or date calculations.

What is a Formula?

A formula is an expression that calculates a value using:

  • Data elements - Reference other elements in your Data Definition

  • Operators - Arithmetic (+, -, *, /), comparison (<, >, =), logical (AND, OR, NOT)

  • Functions - Built-in functions for text, math, aggregation, and special operations

  • Constants - Numbers, strings, or arrays

Example: Calculate a line total from quantity and price: {Quantity} * {Price}

Creating a Formula

To use a formula for a data element:

  1. Open your Data Definition

  2. Select or create a data element

  3. Set the Data Source to "Formula"

  4. Navigate to the Semantics tab

  5. Write your formula in the formula editor

Formula Editor Features

Code Editor

The formula editor provides:

  • Syntax highlighting - Visual differentiation of formula components

  • Real-time validation - Immediate feedback on syntax errors

  • Error messages - Precise indication of problems with suggestions

  • Multi-line support - Write complex formulas across multiple lines

Available Elements Dropdown

Below the editor, a dropdown shows all data elements available for use in your formula:

  • Hierarchical tree view of your Data Definition

  • Filterable list to find elements quickly

  • Click an element to insert it into your formula as {ElementName}

  • Shows only elements accessible from the current element's scope

Validation and Error Help

When your formula has errors, the editor displays:

  • Error location - Shows the position of the problem in your formula

  • Offending symbol - Highlights what caused the error

  • Function suggestions - If you mistype a function name, suggests the closest match

  • Clear error messages - Explains what went wrong and why

Formula Format

Data Types

Formulas support these data types:

  • Number: 123, 45.67

  • String: "Hello, World!" (use double quotes)

  • Attribute Reference: {ElementName} (reference data elements)

  • Array: [1, 2, 3] or ["apple", "banana"]

Operators

Arithmetic Operators

Operator

Purpose

Example

+

Addition

5 + 3 = 8

-

Subtraction

10 - 4 = 6

*

Multiplication

7 * 2 = 14

/

Division

20 / 4 = 5

^

Exponentiation

2 ^ 3 = 8

Comparison Operators

Operator

Purpose

Example

<

Less than

3 < 5 = true

<=

Less than or equal

5 <= 5 = true

=

Equal to

5 = 5 = true

>

Greater than

10 > 7 = true

>=

Greater than or equal

7 >= 7 = true

!=

Not equal

4 != 3 = true

Logical Operators

Operator

Purpose

Example

&&

AND (both must be true)

true && false = false

||

OR (either can be true)

true || false = true

!

NOT (opposite)

!true = false

Grouping

  • ( ) - Group expressions and control order: (3 + 2) * 4 = 20

  • & - Concatenate strings: "Hello" & " " & "World"

Available Functions

Text Functions

Function

Purpose

Example

uppercase()

Convert to uppercase

uppercase("hello") = "HELLO"

lowercase()

Convert to lowercase

lowercase("HELLO") = "hello"

concat()

Concatenate strings

concat("hello", " ", "world") = "hello world"

isblank()

Check if empty

isblank(" ") = true

len()

Get string length

len("hello") = 5

split()

Split string

split("A,B,C", ",") = ["A", "B", "C"]

replace()

Replace part of string

replace("Sample", "x", 3, 4) = "Saxxle"

contains()

Check if contains text

contains("12345", "23") = true

startswith()

Check if starts with text

startswith("Apple", "A") = true

endswith()

Check if ends with text

endswith("Apple", "e") = true

regex()

Test regex pattern

regex("hello", "[0-9]+") = false

Mathematical Functions

Function

Purpose

Example

round()

Round to decimal places

round(2.556, 2) = 2.56

ceil()

Round up

ceil(2.556, 2) = 2.56

floor()

Round down

floor(2.556, 1) = 2.5

abs()

Absolute value

abs(-2) = 2

max()

Maximum value

max(2, 3, 4) = 4

min()

Minimum value

min(2, 3, 4) = 2

todecimal()

Round with strategy

todecimal(123.4567, 3, "round_half_even") = 123.457

Rounding strategies for todecimal():

  • round_up - Always away from zero

  • round_down - Always toward zero

  • round_ceil - Toward positive infinity

  • round_floor - Toward negative infinity

  • round_half_up - Nearest neighbor, up on tie (default)

  • round_half_down - Nearest neighbor, down on tie

  • round_half_even - Nearest neighbor, even on tie

Aggregate Functions

Function

Purpose

Example

sum()

Sum of numbers

sum(1, 2, 3) = 6

multiply()

Product of numbers

multiply(2, 3, 4) = 24

average()

Average of numbers

average(2, 3, 4) = 3

count()

Count items

count(1, 2, 3, 4) = 4

sumifs()

Sum with condition

sumifs([2, 3, 4], "<=4") = 9

countifs()

Count with condition

countifs([2, 3, 4], "<=4") = 3

stddeviation()

Standard deviation

stddeviation(1, 2, 3, 4, 5) = 1.5811

Conditional Functions

Function

Purpose

Example

if()

Conditional logic

if({age} >= 18, "Adult", "Minor")

ifnull()

Check if null

ifnull({Quantity}, 0, {Quantity})

Date Functions

Function

Purpose

Example

isdate()

Check if valid date

isdate("2023-12-25") = true

isafterdate()

Compare dates

isafterdate("2023-12-26", "2023-12-25") = true

isbeforedate()

Compare dates

isbeforedate("2023-12-24", "2023-12-25") = true

datemath()

Date arithmetic

datemath("2023-04-01", "days", 5) = "2023-04-06"

Date Math Function

The datemath() function has two modes:

Mode 1: Date Arithmetic

Calculate dates by adding/subtracting time units:

datemath(date, unit, offset)

Supported units: days, weeks, months, years

Examples:

  • datemath("2023-04-01", "days", 5) = "2023-04-06"

  • datemath("2023-04-01", "weeks", 2) = "2023-04-15"

  • datemath("2023-04-01", "months", -2) = "2023-02-01"

  • datemath("2023-04-01", "years", 2) = "2025-04-01"

Mode 2: Natural Language Parsing

Parse human-readable date expressions:

datemath(expression)

Starting points:

  • "now" - Current date and time

  • "today" - Start of current day

  • "last Monday", "next Tuesday" - Relative days

Time adjustments:

  • "today + 1 day" - Tomorrow

  • "now - 3 hours" - Three hours ago

  • "last Monday - 2 days" - Two days before last Monday

Supported units: days, hours, minutes, seconds, weeks, months, years

Common Formula Patterns

Calculate Line Total

{Quantity} * {UnitPrice}

Calculate Tax

{Subtotal} * 0.08

Calculate Total with Tax

{Subtotal} + ({Subtotal} * {TaxRate})

Conditional Discount

if({Quantity} >= 10, {Price} * 0.9, {Price})

Full Name from Parts

{FirstName} & " " & {LastName}

Days Until Due

datemath({DueDate}, "days", 0) - datemath("today", "days", 0)

Handle Missing Values

ifnull({Discount}, 0, {Discount})

Age Calculation

if({Age} >= 18, "Adult", "Minor")

Best Practices

  • Use clear element names - {UnitPrice} is clearer than {UP}

  • Test incrementally - Build complex formulas step by step

  • Use parentheses - Make order of operations explicit

  • Handle nulls - Use ifnull() to prevent calculation errors

  • Format numbers - Use round() or todecimal() for consistent decimal places

  • Validate with test data - Check formula results with sample documents

  • Comment complex logic - Use descriptive element names as documentation

Tips

  • Use the Available Elements dropdown to avoid typos in element names

  • The editor validates formulas in real-time - fix errors before saving

  • Function suggestions help when you mistype a function name

  • Formulas calculate after all referenced elements are extracted

  • Complex formulas can reference other formula-based elements

  • Use arrays with aggregate functions for calculations across repeating groups

  • The formula evaluates each time the document is processed

Related Articles
Introducing Data Definitions
Defining your Data
Working with External Data
Using Validations
Using Expressions
Did this answer your question?