Data Transformation – Available Functions

The Formula Editor includes built-in functions grouped into categories:

• Text
• Number
• Date
• Table

Below is an overview of all available functions grouped by category.

Text Functions

Number Functions

Date Functions

Table Functions

TextFormat

What does this function do?

TextFormat changes the letter case of a text value.

It can convert text to:

• Uppercase
• Lowercase
• Title case (first letter of each word capitalized)

Syntax

TextFormat({{variable}}, "upper" | "lower" | "title")

• {{variable}} – The text you want to format
• "upper" – ALL LETTERS
• "lower" – all letters
• "title" – First Letter Of Each Word

Examples

Uppercase

TextFormat({{firstName}}, "upper")

If firstName = "anna" → ANNA

Lowercase

TextFormat({{email}}, "lower")

If email = "INFO@COMPANY.COM" → info@company.com

Title case

TextFormat({{fullName}}, "title")

If fullName = "anna svensson" → Anna Svensson

Important

• Only works with text values.
• Returns Text.
• Placeholder variable must exist.

TextTrim

What does this function do?

TextTrim removes spaces at the beginning and end of a text value.

It does not remove spaces inside the text — only before and after.

Syntax

TextTrim({{variable}})

• {{variable}} – The text you want to clean

Example

TextTrim({{customerName}})

If: customerName = " Anna Svensson "

Result: Anna Svensson

Important

• Only removes leading and trailing spaces.
• Does not affect spaces between words.
• Returns Text.
• The variable must contain text.

TextLen

What does this function do?

TextLen returns the number of characters in a text value.

It counts all characters, including letters, numbers, and spaces.

Syntax

TextLen({{variable}})

• {{variable}} – The text you want to measure

Example

TextLen({{customerName}})

If: customerName = "Anna Svensson"

Result: 14

(Spaces are included in the count.)

Important

• Counts all characters, including spaces.
• Returns a Number.

TextReplace

What does this function do?

TextReplace replaces part of a text value with another value.

It searches for a specific text and replaces all matching occurrences.

Syntax

TextReplace({{variable}}, "search", "replace")

• {{variable}} – The text to modify
• "search" – The text you want to find
• "replace" – The text that will replace it

Example

TextReplace({{phoneNumber}}, "-", "")

If: phoneNumber = "070-123-45-67"

Result: 0701234567

Important

• Replaces all matching occurrences.
• Search is case-sensitive.
• Returns Text.
• The variable must contain text.

TextJoin

What does this function do?

TextJoin combines multiple values into one single text string.

It joins values in the order you write them.

Syntax

TextJoin(value1, value2, value3, ...)

• value1, value2, ... – Text values, variables, or static text
• Values are joined exactly in the order provided

Example

TextJoin({{firstName}}, " ", {{lastName}})

If:

• firstName = "Anna"
• lastName = "Svensson"

Result: Anna Svensson

Example – Add Static Text

TextJoin("Order: ", {{orderId}})

If:

• orderId = 12345

Result: Order: 12345

Important

• Values are joined exactly as written.
• You must manually add spaces or separators (for example " " or ", ").
• Returns Text.

TextSubstring

What does this function do?

TextSubstring extracts part of a text value based on position.

It returns a selected portion of the text.

Syntax

TextSubstring({{variable}}, startIndex, length)

• {{variable}} – The text to extract from
• startIndex – Position where extraction starts
• length – Number of characters to return

Index starts at 0 (first character).

Example

TextSubstring({{ssn}}, 0, 6)

If: ssn = "19900101-1234"

Result: 199001

(Starts at position 0 and returns 6 characters.)

Example – Extract Last 4 Characters

TextSubstring({{ssn}}, 9, 4)

If: ssn = "19900101-1234"

Result: 1234

Important

• Index starts at 0.
• Length controls how many characters are returned.
• If the range exceeds the text length, it may return fewer characters.
• Returns Text.

NumberFormat

What does this function do?

NumberFormat formats a number into a readable text format.

It allows you to control decimals and thousand separators.

Syntax

NumberFormat({{variable}}, "format")

• {{variable}} – The number you want to format
• "format" – Defines how the number should be displayed

The format string controls:

• Thousand separator
• Decimal separator
• Number of decimals

Example

NumberFormat({{amount}}, "1 000,00")

If: amount = 1234.5

Result: 1 234,50

Example – No Decimals

NumberFormat({{amount}}, "1 000")

If: amount = 1234.5

Result: 1 235

Important

• The function returns Text (not Number).
• The format must match your regional number style.
• Only works with numeric values.

DateFormat

What does this function do?

DateFormat formats a date into a specific text format.

You can control both the date pattern and the language/culture.

Syntax

DateFormat({{variable}}, "format", "culture")

• {{variable}} – The date value to format
• "format" – Defines how the date should be displayed
• "culture" – Language/culture code (for example "nb")

Example

DateFormat({{invoiceDate}}, "dd.MM.yyyy", "nb")

If: invoiceDate = 2025-06-15

Result: 15.06.2025

Example – With Month Name

DateFormat({{invoiceDate}}, "dd MMMM yyyy", "nb")

If: invoiceDate = 2025-06-15

Result: 15 juni 2025

Important

• The function returns Text.
• The format must follow standard date format patterns.
• The culture code controls month and weekday language.
• The variable must contain a valid date.

DateAddDays / DateAddMonths / DateAddYears

What do these functions do?

These functions add or subtract time from a date.

• DateAddDays – Adds days
• DateAddMonths – Adds months
• DateAddYears – Adds years

Use positive numbers to add time.
Use negative numbers to subtract time.

Syntax

DateAddDays({{date}}, number)
DateAddMonths({{date}}, number)
DateAddYears({{date}}, number)

• {{date}} – The original date
• number – Amount of time to add (can be negative)

Example – Add 14 Days

DateAddDays({{startDate}}, 14)

If: startDate = 2025-06-01

Result: 2025-06-15

Example – Add 2 Months

DateAddMonths({{startDate}}, 2)

If: startDate = 2025-01-10

Result: 2025-03-10

Example – Add 1 Year

DateAddYears({{startDate}}, 1)

If: startDate = 2025-06-01

Result: 2026-06-01

Important

• All functions return a Date.
• Use DateFormat() if you need formatted text output.
• If a month has fewer days (for example February), the date adjusts automatically.

DateDiff

What does this function do?

DateDiff calculates the difference between two dates.

You can choose whether the result should be returned in days, months, or years.

Syntax

DateDiff({{startDate}}, {{endDate}}, "unit")

• {{startDate}} – The first date
• {{endDate}} – The second date
• "unit" – The unit of time:
  • "days"
  • "months"
  • "years"

Example – Difference in Days

DateDiff({{startDate}}, {{endDate}}, "days")

If:

• startDate = 2025-06-01
• endDate = 2025-06-15

Result: 14

Example – Calculate Age in Years

DateDiff({{birthDate}}, {{today}}, "years")

If:

• birthDate = 2000-05-10
• today = 2025-05-10

Result: 25

Important

• Returns a Number.
• The result is based on the selected unit.
• Both variables must contain valid dates.

DateStartOfMonth / DateEndOfMonth

What do these functions do?

These functions return the first or last day of the month for a given date.

• DateStartOfMonth → First day of the month
• DateEndOfMonth → Last day of the month

Syntax

DateStartOfMonth({{date}})
DateEndOfMonth({{date}})

• {{date}} – The original date

Example

If: invoiceDate = 2025-06-15

Start of month

DateStartOfMonth({{invoiceDate}})

Result: 2025-06-01

End of month

DateEndOfMonth({{invoiceDate}})

Result: 2025-06-30

Important

• Both functions return a Date.
• The original day value is ignored.
• Use DateFormat() if you need formatted text output.

DateToday

What does this function do?

DateToday returns today’s date.

Syntax

DateToday("format")

Example – Formatted Output

DateToday("dd.MM.yyyy")

If today is 24 February 2026

Result: 24.02.2026

Important

• Format must follow standard date format patterns.

TableCount

What does this function do?

TableCount returns the number of rows in a table variable.

Use it to check how many records exist in a table.

Syntax

TableCount({{tableName}})

• {{tableName}} – The table variable

Example – Count Rows

TableCount({{employees}})

If the table employees contains 5 rows,

Result: 5

Example – Use in a Condition

You want to check if the table contains any rows.

TableCount({{employees}})

If the result is greater than 0, the table contains data.

Important

• Returns a Number.
• Only works with table variables.
• If the table is empty, the result is 0.

TableMerge

What does this function do?

TableMerge combines values from a specific column in a table into a single text string.

You can define:

• A separator between values
• A different separator before the last value

Syntax

TableMerge({{tableName}}, "fieldName", "separator", "lastSeparator")

• {{tableName}} – The table variable
• "fieldName" – The column to extract values from
• "separator" – Separator between values (for example ", ")
• "lastSeparator" – Separator before the last value (for example " and ")

Example

TableMerge({{products}}, "name", ", ", " and ")

If the table products contains:

Result:

Apple, Banana and Orange

Important

• Returns Text.
• Only works with table variables.
• If the table contains one row, no separators are added.
• If the table is empty, the result is empty.

TableMap

What does this function do?

TableMap builds a readable text string from a table.

It applies a template to each row and combines the results into one sentence.

Syntax

TableMap({{tableName}}, "template", "separator", "lastSeparator")

• {{tableName}} – The table variable
• "template" – Text applied to each row (use {{columnName}} to insert column values)
• "separator" – Separator between rows (for example ", ")
• "lastSeparator" – Separator before the last row (for example " and ")

Example – Insurance Policies

TableMap({{InsurancePolicies}}, "{{policyType}}, {{coverageAmount}} kr", ", ", " and ")

If the table contains:

[
  { "policyType": "Home Insurance", "coverageAmount": 2000000 },
  { "policyType": "Car Insurance", "coverageAmount": 500000 },
  { "policyType": "Travel Insurance", "coverageAmount": 100000 }
]

Result:

Home Insurance, 2000000 kr, Car Insurance, 500000 kr and Travel Insurance, 100000 kr

Important

• Returns Text.
• {{columnName}} must match the table column exactly.
• The function automatically loops through all rows.
• If the table is empty, the result is empty.