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:

Number: Any number from 0-9.
ex. 123
String: Characters enclosed in double quotes.
ex. "Hello, World!"
Attribute Name: Attribute name of the data definition enclosed in curly brackets.
ex. {attributeName}
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"
isblank(string)	Returns true if the string is null or empty	isblank(" ") true
concat(params: string[])	Concatenates a series of strings into one.	concatenate("hello", " ", "world") "hello world"

Mathematical Function Call Functions

Format	Purpose	Sample
todecimal	Rounds the number of decimal places, the third parameter can be one of the following rounding strategies: round_up Always rounds away from zero. round_down Always rounds toward zero. round_ceil Rounds towards positive infinity. round_floor Rounds towards negative infinity. round_half_up Rounds towards the nearest neighbor, rounding up on a tie (default). round_half_down Rounds towards the nearest neighbor, rounding down on a tie. round_half_even Rounds towards the nearest neighbor, rounding to even on a tie.	todecimal(123.4567, 3, "round_half_even") 123.457
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"
len(string) or len([])	Returns the length of the string (if one arg) or the length of the array if more than one)	len("hello") 5
split (string, delimiter)	Split a string into two different parts.	split("Sample text", " ") "Sample" "text"
regex(string, regex)	Returns true or false is string matches regex	regex("hello", "[0-9]+") false
strlen(string)	Return the length of a string	strlen("hello") 5
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.

Special Notes: dateMath

The dateMath function is a powerful tool designed to convert human-readable date expressions into JavaScript Date objects. This function allows users to work with natural language inputs for date calculations, making it easier to compute relative dates and times without complex manual conversions.

It has two modes of operation, the first is where you provide a date as the first parameter, then you would provide the temporal unit and then the number of that unit, for example:

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

The second is where you only provide a single parameter, and this is used to parse out a date.

For example:

datemath("today + 1 day")

Key Features

Flexible Starting Points

• “now”:

Uses the current date and time as the base for any further calculations.

• “today”:

Sets the base date to the start of the current day, ignoring the current time.

• Relative Days:

Supports phrases such as “last Monday” or “next Tuesday” to determine a starting point based on a specified day of the week. A helper function calculates the correct date by considering the current day and the target day.

Time Adjustments

• Dynamic Modifications:

After establishing the base date, the function looks for additional adjustments in the format of - <number> <unit>. For example, inputs like “- 3 hours” or “- 2 days” subtract the specified time from the base date.

• Supported Units:

The function handles various time units including days, hours, minutes, seconds, weeks, months, and years, offering comprehensive support for various date arithmetic needs.

Introducing Data Definitions

Using Structured Filters

Working with External Data

Using Validations

Using Expressions