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.123String: 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. |
|
- | Subtracts one number from another or from a series of numbers. |
|
* | Multiplies two or more numbers. |
|
/ | Divides one number by another |
|
^ | Raises a number to the power of another number. |
|
Comparison Operations
You can use comparison operators to compare two values. These operators return a boolean value (true or false):
Format | Purpose | Sample |
< | Returns |
|
<= | Returns |
|
= | Returns |
|
> | Returns |
|
>= | Returns |
|
!= | Returns |
|
lt, lte, eq, gte, gt | Compare two values and return "1" if the comparison is true, and "0" if false. | lt([1, 2]) |
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. |
|
OR | Either of the statement should be correct in order to return true. |
|
NOT | Opposite of the current statement. |
|
Grouping Functions
Operator | Purpose |
& | Concatenates two or more strings. |
() | Group expressions and control the order of operations. Example: |
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"]) |
lowercase(params: string[]) | Converts all input strings to lowercase. | lowercase(["HELLO", "WORLD"]) |
concat(params: string[]) | Concatenates a series of strings into one. | concatenate(["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"]) |
ceil(params: number[]) | Rounds a number up to the nearest integer or specified number of decimal places. | ceil(["2.556", "2"]) |
floor(params: number[]) | Rounds a number down to the nearest integer or specified number of decimal places. | floor(["2.556", "1"]) |
abs(params:number[]) | Returns absolute value of numbers enclosed. | abs(["-2"]) |
max(params: number[]) | Returns the maximum number in the numbers enclosed. | max([2,3,4]) |
min(params: number[]) | Returns the minimum number in the numbers enclosed. | min([2,3,4]) |
Aggregate Functions
Format | Purpose | Sample |
sum(params: number[]) | Calculates the sum of given numbers. | sum([1, 2, 3]) |
multiply(params: number[]) | Calculates the product of given numbers. | multiply([2, 3, 4]) |
average(params: number[]) | Calculates the average of given numbers. | average ([2,3,4]) |
count(params:any[]) | Counts the number of enclosed string or numbers. | count([1,2,3,4]) |
sumifs (params:number[], criteria) | Adds the numbers if the criteria is met. | sumifs(["2","3","4"], "<=4") |
countifs(params:any[], criteria) | Counts the enclosed string or characters if the criteria is met. | countifs(["2","3","4"], "<=4") |
stddeviation(params: any[]) | Calculates the standard deviation of the provided numbers. | stddeviation(1, 2, 3, 4, 5) |
Special Functions
Format | Purpose | Sample |
if(params: any[], true, false) | Executes a conditional logic based on the provided condition. | if(["1", "yes", "no"]) |
ifnull(params: any[], true, false) | Check if the provided attribute is null. | ReadQuantity = null |
count(params: any[]) | Counts the number of entries in the field. | count([1,2,3,4]) |
contains(params:any[], true, false) | Checks whether enclosed string contains sequence of characters. | contains("12345","12","true",false") |
startswith(params:any[], true, false) | Checks whether the string starts with the character indicated. | startswith("Apple","A", "true",' false") |
endswith(params:any[], true, false) | Checks whether the string ends with the character indicated. | endswith("Apple","e", "true",' false") |
replace(original_string, new_string, start, end) | Replaces part of the string with a different string. | replace("Sample", "x", 3, 4) |
split (string, delimiter) | Split a string into two different parts. | split("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]) |
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.