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. |
|
- | 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") |
isblank(string) | Returns true if the string is null or empty | isblank(" ")
|
concat(params: string[]) | Concatenates a series of strings into one. | concatenate("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")
|
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) |
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")
|
split (string, delimiter) | Split a string into two different parts. | split("Sample text", " ") |
regex(string, regex) | Returns true or false is string matches regex | regex("hello", "[0-9]+")
|
strlen(string) | Return the length of a string | strlen("hello")
|
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.
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.