Skip to main content
Using Formula

Learn how you can build formula in your data definitions

Updated over 3 months ago

You can use a formula to calculate a value for a data element, once you choose the data source to be Formula you can go to the Formula tab and select the data elements that are to be used for this formula.

Here you can type your formula, you can see the available elements, and can click to add them. Then you can use the available operators and functions to build a formula. The Extracted Data in workspace updates realtime depending on the formula.

Format

Kodexa platform supports the following formats:

  1. Number: Any number from 0-9.
    ex. 123

  2. String: Characters enclosed in double quotes.
    ex. "Hello, World!"

  3. Attribute Name: Attribute name of the data definition enclosed in curly brackets.
    ex. {attributeName}

  4. Arrays: Can hold multiple values of any types that are enclosed in square brackets.
    ex. [1, 2, 3] or ["apple", "banana", "cherry"]

Available Operators and Their Use

In the formula input field, you can use the following operators to perform basic mathematical and logical operations:

Arithmetic Functions

The platform provides support to basic arithmetic operations such as addition, subtraction, multiplication, and division. Here are some examples:

Format

Purpose

Sample

+

Adds two or more numbers.

5 + 3 results in 8

-

Subtracts one number from another or from a series of numbers.

10 - 4 results in 6

*

Multiplies two or more numbers.

7 * 2 results in 14

/

Divides one number by another

20 / 4 results in 5

^

Raises a number to the power of another number.

2 ^ 3 results in 8

Comparison Operations

You can use comparison operators to compare two values. These operators return a boolean value (true or false):

Format

Purpose

Sample

<

Returns true if the first number is less than the second number; otherwise returns false.

3 < 5 results in true

<=

Returns true if the first number is less than or equal to the second number; otherwise returns false.

5 <= 5 results in true

=

Returns true if the two numbers or strings are equal; otherwise returns false.

5 = 5 results in true

>

Returns true if the first number is greater than the second number; otherwise returns false.

10 > 7 results in true

>=

Returns true if the first number is greater than or equal to the second number; otherwise returns false.

7 >= 7 results in true

!=

Returns true if the first number is not equal to the second number; otherwise returns false.

4 != 3 results in true

lt, lte, eq, gte, gt

Compare two values and return "1" if the comparison is true, and "0" if false.

lt([1, 2])
"1"

Logical Operators

Logical operators allow you to combine multiple boolean expressions:

Format

Purpose

Sample

AND &&

Both statement should be correct in order to return true.

true && false results in false

OR ||

Either of the statement should be correct in order to return true.

true || false results in true

NOT !

Opposite of the current statement.

!true results in false

Grouping Functions

Operator

Purpose

&

Concatenates two or more strings.

()

Group expressions and control the order of operations.

Example: (3 + 2) * 4 = 20

Supported Functions

You can also use the following functions within the formula field. Each function performs specific operations as described below:

Text Functions

Format

Purpose

Sample

uppercase(params: string[])

Converts all input strings to uppercase.

uppercase(["hello", "world"])
"HELLOWORLD"

lowercase(params: string[])

Converts all input strings to lowercase.

lowercase(["HELLO", "WORLD"])
"helloworld"

concat(params: string[])

Concatenates a series of strings into one.

concatenate(["hello", " ", "world"])
"hello world"

Mathematical Function Call Functions

Format

Purpose

Sample

round(params: number[])

Rounds a number to a specified number of decimal places.

round(["2.556", "2"])
"2.56"

ceil(params: number[])

Rounds a number up to the nearest integer or specified number of decimal places.

ceil(["2.556", "2"])
"2.56"

floor(params: number[])

Rounds a number down to the nearest integer or specified number of decimal places.

floor(["2.556", "1"])
"2.5"

abs(params:number[])

Returns absolute value of numbers enclosed.

abs(["-2"])
"2"

max(params: number[])

Returns the maximum number in the numbers enclosed.

max([2,3,4])
"4"

min(params: number[])

Returns the minimum number in the numbers enclosed.

min([2,3,4])
"2"

Aggregate Functions

Format

Purpose

Sample

sum(params: number[])

Calculates the sum of given numbers.

sum([1, 2, 3])
"6"

multiply(params: number[])

Calculates the product of given numbers.

multiply([2, 3, 4])
"24"

average(params: number[])

Calculates the average of given numbers.

average ([2,3,4])
"3"

count(params:any[])

Counts the number of enclosed string or numbers.

count([1,2,3,4])
"4"

sumifs (params:number[], criteria)

Adds the numbers if the criteria is met.

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

countifs(params:any[], criteria)

Counts the enclosed string or characters if the criteria is met.

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

stddeviation(params: any[])

Calculates the standard deviation of the provided numbers.

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

Special Functions

Format

Purpose

Sample

if(params: any[], true, false)

Executes a conditional logic based on the provided condition.

if(["1", "yes", "no"])
"yes"

ifnull(params: any[], true, false)

Check if the provided attribute is null.

ReadQuantity = null

ifnull({ReadQuantity}) will return true

count(params: any[])

Counts the number of entries in the field.

count([1,2,3,4])
"4"

contains(params:any[], true, false)

Checks whether enclosed string contains sequence of characters.

contains("12345","12","true",false")
"true"

startswith(params:any[], true, false)

Checks whether the string starts with the character indicated.

startswith("Apple","A", "true",' false")
"true"

endswith(params:any[], true, false)

Checks whether the string ends with the character indicated.

endswith("Apple","e", "true",' false")
"true"

replace(original_string, new_string, start, end)

Replaces part of the string with a different string.

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

split (string, delimiter)

Split a string into two different parts.

split("Sample text", " ")
"Sample" "text"

datemath(params: [string | Date, string, number])

Performs date arithmetic based on the given date, temporal unit (days, weeks, months, years), and offset.

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"

Putting It All Together

Here's a complex example that combines many of the elements described above:

IF({age} >= 18 && {hasID} = true, "Eligible", "Not Eligible")

This expression checks if a person is 18 or older and has an ID. If both conditions are true, it returns "Eligible"; otherwise, it returns "Not Eligible".

When using these operators and functions, ensure you provide the correct number and type of parameters as required by each function. Incorrect parameters may lead to unexpected results or errors.

Did this answer your question?