In this post, you’ll learn everything about the Workflow Action “Calculated Fields” and how to use it effectively to perform calculations, identify text, and format data.
What are Calculated Fields?
This function is based on a defined set of formulas that allow you to perform complex transformations. It consists of three main components: Parameters, Relation Parameters, and Formulas.
1. Parameters
Parameters are the fields of the workflow module that you can use for your calculations. These parameters are included in formulas using the format `{P0}`. When you add a new parameter, the interface will display which parameter the respective field has. This way, you always have full control over how the data is used in your calculations.
2. Relation Parameters
Relation Parameters give you access to all one-to-many relationships of the module and their fields. These are included in formulas using the format `{R0}`. This allows you to evaluate and consider data from related records. It gives you the ability to perform more complex data analyses and transformations.
3. Formulas
Under Formulas, you define the specific formula that should be applied to a particular field. First, select the corresponding field and then enter the formula in the text line. You can nest multiple formulas within each other or chain them together. To do this, simply add another formula in place of a parameter part in another formula, or append a new formula to your previous one. It’s important to ensure that the notation is correct, as otherwise the evaluation could fail.
Supported Formula Categories
- Logical Functions: These yield values of 0 or 1 and are ideal for comparisons like “equal” or “greater than or equal to.”
- Text Functions: Use these to transform text in different forms.
- Mathematical Functions: Utilize all common mathematical operations to transform numbers.
- Date Functions: These are useful for transforming date values or setting them based on certain conditions.
- Control Functions: With the ifThenElse function, you can perform complex queries and conditional checks. Nestings are possible here to consider multiple conditions.
- Counter Functions: These functions count records within the system and add counters to them.
- JSON Functions: JSON Arrays can be transformed according to specific rules, and specific values can be extracted.
- PHP Functions: These functions can be used to transform PHP arrays.
With this overview, you are well-equipped to optimally use Calculated Fields in your workflows.
Logical Functions
Logical functions are returning true or false in the form of 1 and 0 so checkboxes typed fields can be filled with these functions. They can be also used as the logical condition for the ifThenElse function.
equal
| Formula | {equal(parameter1;parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 equals with parameter2 |
| Returns | 1 if the two parameters are equal or 0 if not |
| Example call | {equal(1; 2)} returns 0 |
notEqual
| Formula | {notEqual(parameter1; parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 not equals with parameter2 |
| Returns | 0 if the two parameters are equal or 1 if not |
| Example call | {notEqual(1; 2)} returns 1 |
greaterThan
| Formula | {greaterThan(parameter1; parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 greater than parameter2 |
| Returns | 1 if parameter1 greater than parameter2, 0 if not |
| Example call | {greaterThan(3; 3)} returns 0 |
greaterThanOrEqual
| Formula | {greaterThanOrEqual(parameter1; parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 greater than or equal parameter2 |
| Returns | 1 if parameter1 greater than or equal parameter2, 0 if not |
| Example call | {greaterThanOrEqual(3; 3)} returns 1 |
lessThan
| Formula | {lessThan(parameter1; parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 less than parameter2 |
| Returns | 1 if parameter1 less than parameter2, 0 if not |
| Example call | {lessThan(3; 3)} returns 0 |
lessThanOrEqual
| Formula | {lessThanOrEqual(parameter1; parameter2)} |
| Parameters | parameter1: can be any value of any type |
| parameter2: can be any value of any type | |
| Description | Determines if parameter1 less than or equal parameter2 |
| Returns | 1 if parameter1 less than or equal parameter2, 0 if not |
| Example call | {lessThanOrEqual(3; 3)} returns 1 |
empty
| Formula | {empty(parameter)} |
| Parameters | parameter: text value |
| Description | Determines if parameter is empty |
| Returns | 1 if parameter is empty, 0 if not |
| Example call | {empty(any text)} returns 0 |
notEmpty
| Formula | {notEmpty(parameter)} |
| Parameters | parameter: text value |
| Description | Determines if parameter is not empty |
| Returns | 1 if parameter is not empty, 0 if empty |
| Example call | {notEmpty(any text)} returns 1 |
not
| Formula | {not(parameter)} |
| Parameters | parameter: logical value |
| Description | Negates the logical value of the parameter |
| Returns | 1 if parameter is 0, 0 if parameter is 1 |
| Example call | {not(0)} returns 1 |
and
| Formula | {and(parameter1; parameter2)} |
| Parameters | parameter1: logical value |
| parameter2: logical value | |
| Description | Applies the AND logical operator to two logical values |
| Returns | 1 if parameter1 and parameter2 is 1, 0 if any parameters are 0 |
| Example call | {and(1; 0)} returns 0 |
or
| Formula | {or(parameter1; parameter2)} |
| Parameters | parameter1: logical value |
| parameter2: logical value | |
| Description | Applies the OR logical operator to two logical values |
| Returns | 1 if parameter1 or parameter2 is 1, 0 if both parameters are 0 |
| Example call | {or(1; 0)} returns 1 |
Text Functions
Text functions are used to manipulate text in various ways. All the functions listed here are fully supports UTF-8 texts, so special characters should not raise any problems.
substring
| Formula | {substring(text; start; length)} |
| Parameters | text: text value |
| start: decimal value | |
| length (optional): decimal value | |
| Description | Cuts the substring of a text field from start. If the length optional parameter is not set, then it cuts all characters until the end of the string, otherwise cuts the provided length. Indexing of a text’s characters starting from 0. |
| Returns | Substring of the given text |
| Example call | {substring(This is my text; 5)} returns is my text |
| {substring(This is my text; 5; 5)} returns is my |
length
| Formula | {length(parameter)} |
| Parameters | parameter: text value |
| Description | Count the characters in a text. |
| Returns | The count of the characters in a text. |
| Example call | {length(sample text)} returns 11 |
replace
| Formula | {replace(search; replace; subject)} |
| Parameters | search: text value |
| replace: text value | |
| subject: text value | |
| Description | Replace all occurrences of search to replace in the text subject. |
| Returns | subject with replaced values. |
| Example call | {replace(apple; orange; This is an apple tree)} returns This is an orange tree |
position
| Formula | {position(subject; search)} |
| Parameters | subject: text value |
| search: text value | |
| Description | Find position of first occurrence of search in a subject |
| Returns | Numeric position of search in subject or -1 if search not present in subject |
| Example call | {position(Where is my text?; text)} returns 12 |
lowercase
| Formula | {lowercase(parameter)} |
| Parameters | parameter: text value |
| Description | Make text lowercase |
| Returns | The lowercased text. |
| Example call | {lowercase(ThIs iS a sAmPlE tExT)} returns this is a sample text |
uppercase
| Formula | {uppercase(parameter)} |
| Parameters | parameter: text value |
| Description | Make text uppercase |
| Returns | The uppercased text. |
| Example call | {uppercase(ThIs iS a sAmPlE tExT)} returns THIS IS A SAMPLE TEXT |
uppercaseword
New in DataEngine 24.11.1
| Formula | {uppercaseword(parameter)} |
| Parameters | parameter: String or text. Can also be a variable: {P0} |
| Description | Converts the first letter of all words in a string to upper case. Converts all other letters to lower case. |
| Returns | The character string with the first letter of a word as a capital letter. |
| Example call | {uppercaseword(THIS IS A UPPERCASE TEXT)} -> This Is A Uppercase Text |
Base64Encode
| Formula | {Base64Encode(parameter1)} |
| Parameters | parameter1: The string to encode |
| Description | Encodes a string to base64. |
| Returns | The base64 encoded string. |
| Example call | {Base64Encode(abc)} – Encode the string abc to base64. |
Base64Decode
| Formula | {Base64Decode(parameter1)} |
| Parameters | parameter1: The base64 encoded string to decode |
| Description | Decodes a base64 encoded string. |
| Returns | The decoded string. |
| Example call | {Base64Decode(“YWJj”)} – Decodes the base64 encoded string YWJj to abc. |
Mathematical Functions
Mathematical functions are used to manipulate numbers in various ways. Several mathematical operators are implemented as functions in Calculate Fields.
add
| Formula | {add(parameter1; parameter2)} |
| Parameters | parameter1: number value |
| parameter2: number value | |
| Description | Adds parameter1 and parameter2 |
| Returns | The sum of parameter1 and parameter2 |
| Example call | {add(3.12; 4.83)} returns 7.95 |
subtract
| Formula | {subtract(parameter1; parameter2)} |
| Parameters | parameter1: number value |
| parameter2: number value | |
| Description | Subtracts parameter2 from parameter1 |
| Returns | The distinction of parameter2 and parameter1 |
| Example call | {subtract(8; 3)} returns 5 |
multiply
| Formula | {multiply(parameter1; parameter2)} |
| Parameters | parameter1: number value |
| parameter2: number value | |
| Description | Multiplies parameter1 and parameter2 |
| Returns | The product of parameter1 and parameter2 |
| Example call | {multiply(2; 4)} returns 8 |
divide
| Formula | {divide(parameter1; parameter2)} |
| Parameters | parameter1: number value |
| parameter2: number value | |
| Description | Divides parameter2 with parameter1 |
| Returns | The division of parameter2 and parameter1 |
| Example call | {divide(8; 2)} returns 4 |
power
| Formula | {power(parameter1; parameter2)} |
| Parameters | parameter1: number value |
| parameter2: number value | |
| Description | Raises parameter1 to the power of parameter2 |
| Returns | parameter1 raised to the power of parameter2 |
| Example call | {power(2; 7)} returns 128 |
squareRoot
| Formula | {squareRoot(parameter)} |
| Parameters | parameter: number value |
| Description | Calculates the square root of parameter |
| Returns | The square root of parameter |
| Example call | {squareRoot(4)} returns 2 |
absolute
| Formula | {absolute(parameter)} |
| Parameters | parameter: number value |
| Description | Calculates the absolute value of parameter |
| Returns | The absolute value of parameter |
| Example call | {absolute(-4)} returns 4 |
sum
New in DataEngine 25.01.1
| Formula | {sum(BeanID;RelatedModule;Field;FilterField;FilterOperator;FilterValue)} |
| Parameters | BeanID: ID of the Bean from which to start. |
| RelatedModule: Name of the relationship as defined in the Studio. Multiple relationships can be linked using :: (From Contact to Company, From Company to Invoice). It can be empty to sum all entries in the module table. | |
| Field: Name of the field to be summed from the last mentioned “RelatedModule.” | |
| FilterField(Optional): Any field from the last mentioned “RelatedModule” that is used for filtering. If the parameter is a date/time field, the specified value is automatically converted to a date. | |
| FilterOperator (Optional): An operator for filtering. Possible operators are lt, lte, gt, gte, eq, neq. | |
| FilterValue (Optional): A value against which the FilterField is compared with the corresponding FilterOperator. | |
| Description | Sums the values of entries related to the specified Bean. It is possible to filter the summation based on a filter field in the associated module, such as for “Only invoices from the last 2 years.” |
| Returns | Sum of all values. |
| Example call | {sum({P0};core_accounts_core_contracts;contract_volume;last_invoice;gt;{subtractYears(Y-m-d;{now(Y-m-d)};2)})} – Sums the contract volume of the contracts of the current account that have received an invoice in the last two years. |
count
New in DataEngine 25.01.1
| Formula | {count(BeanID;RelatedModule;Field;FilterField;FilterOperator;FilterValue)} |
| Parameters | BeanID: ID of the Bean from which to start. |
| RelatedModule: Name of the relationship as defined in the Studio. Multiple relationships can be linked using :: (From Contact to Company, From Company to Invoice). It can be empty to count all entries in the module table. | |
| Field: Name of the field to be counted, e.g., id. If a field other than “id” is specified here, it is checked that this field is not empty. This allows for a different result than counting over “id”. The field must come from the last mentioned “RelatedModule.” | |
| FilterField(Optional): Any field from the last mentioned “RelatedModule” that is used for filtering. If the parameter is a date/time field, the specified value is automatically converted to a date. | |
| FilterOperator (Optional): An operator for date filtering. Possible operators are lt, lte, gt, gte, eq, neq. | |
| FilterValue (Optional): A value against which the FilterField is compared with the corresponding FilterOperator. | |
| Description | Counts the entries related to the specified Bean. It is possible to filter the count based on a filter field in the associated module, such as for “Only invoices from the last 2 years.” If a field is specified, it must not be empty. |
| Returns | Number of results. |
| Example call | {count({P0};core_accounts_core_contracts;contract_volume;last_invoice;gt;{subtractYears(Y-m-d;{now(Y-m-d)};2)})} – Counts the contracts of the current account with non-empty volume that have received an invoice in the last two years. |
floor
New in DataEngine 25.01.1-beta1
| Formula | {floor(parameter)} |
| Parameters | parameter: number value |
| Description | Rounds the current number to the next smaller whole number. |
| Returns | The rounded number of the parameter. |
| Example call | {floor(3.1415)} returns 3 |
ceil
New in DataEngine 25.01.1-beta1
| Formula | {ceil(parameter)} |
| Parameters | parameter: number value |
| Description | Rounds the current number to the next bigger whole number. |
| Returns | The rounded number of the parameter. |
| Example call | {floor(3.1415)} returns 4 |
round
New in DataEngine 25.01.1-beta1
| Formula | {round(parameter1; parameter2; parameter3)} |
| Parameters | parameter1: number value |
| parameter2 (optional): number of decimal places | |
| parameter3 (optional): rounding rule (what happens with .5) with the following options: down = strictly down; up = strictly up; even= to the next even whole number; odd = to the next uneven whole number | |
| Description | Rounds the current number to the next value based on the entered rules in parameter 2 and 3. If no parameter 2 and 3 are set, the number will be rounded down with two decimal places. |
| Returns | The rounded number of parameter 1. |
| Example call | {round(3.1415; 3; up)} returns 3,142 |
Date Functions
There are several date functions implemented in Calculate Fields, so the user can manipulate dates in many ways. Most of the functions uses a format parameter, which is used to set the result of the functions formatted as the user wants to. The options for these formats are equivalent with the PHP format parameters:
For day
| Format character | Description | Example returned values |
| d | Day of the month, 2 digits with leading zeros | 01 to 31 |
| D | A textual representation of a day, three letters | Mon through Sun |
| j | Day of the month without leading zeros | 1 to 31 |
| l | A full textual representation of the day of the week | Sunday through Saturday |
| N | ISO-8601 numeric representation of the day of the week | 1 (for Monday) through 7 (for Sunday) |
| S | English ordinal suffix for the day of the month, 2 characters | st, nd, rd or th. Works well with j |
| w | Numeric representation of the day of the week | 0 (for Sunday) through 6 (for Saturday) |
| z | The day of the year (starting from 0) | 0 through 365 |
For week
| Format character | Description | Example returned values |
| W | ISO-8601 week number of year, weeks starting on Monday | 42 (the 42nd week in the year) |
For month
| Format character | Description | Example returned values |
| F | A full textual representation of a month, such as January or March | January through December |
| m | Numeric representation of a month, with leading zeros | 01 through 12 |
| M | A short textual representation of a month, three letters | Jan through Dec |
| n | Numeric representation of a month, without leading zeros | 1 through 12 |
| t | Number of days in the given month | 28 through 31 |
For year
| Format character | Description | Example returned values |
| L | Whether it’s a leap year | 1 if it is a leap year, 0 otherwise |
| o | ISO-8601 year number. This has the same value as Y, except that if the ISO week number (W) belongs to the previous or next year, that year is used instead | 1999 or 2003 |
| Y | A full numeric representation of a year, 4 digits | 1999 or 2003 |
| y | A two digit representation of a year | 99 or 03 |
For time
| Format character | Description | Example returned values |
| a | Lowercase Ante meridiem and Post meridiem | am or pm |
| A | Uppercase Ante meridiem and Post meridiem | AM or PM |
| B | Swatch Internet time | 000 through 999 |
| g | 12-hour format of an hour without leading zeros | 1 through 12 |
| G | 24-hour format of an hour without leading zeros | 0 through 23 |
| h | 12-hour format of an hour with leading zeros | 01 through 12 |
| H | 24-hour format of an hour with leading zeros | 00 through 23 |
| i | Minutes with leading zeros | 00 to 59 |
| s | Seconds, with leading zeros | 00 through 59 |
For timezone
| Format character | Description | Example returned values |
| e | Timezone identifier | UTC, GMT, Atlantic/Azores |
| l | Whether or not the date is in daylight saving time | 1 if Daylight Saving Time, 0 otherwise |
| O | Difference to Greenwich time (GMT) in hours | 200 |
| P | Difference to Greenwich time (GMT) with colon between hours and minutes | +02:00 |
| T | Timezone abbreviation | EST, MDT |
| Z | Timezone offset in seconds. The offset for timezones west of UTC is always negative, and for those east of UTC is always positive. | -43200 through 50400 |
For full date/time
| Format character | Description | Example returned values |
| c | ISO 8601 date | 2004-02-12T15:19:21+00:00 |
| r | RFC 2822 formatted date | Thu, 21 Dec 2000 16:01:07 +0200 |
| U | Seconds since the Unix Epoch (January 1 1970 00:00:00 GMT) | |
The following date functions can be used with calculated fields:
date
| Formula | {date(format; timestamp)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| Description | Creates a date in the given format |
| Returns | timestamp in the given format |
| Example call | {date(ymd; 2016-02-11)} returns 160211 |
now
| Formula | {now(format)} |
| Parameters | format: format text |
| Description | Creates the actual date/time in the given format |
| Returns | Current date/time in the given format |
| Example call | {now(Y-m-d H:i:s)} returns 2016-04-29 15:08:03 |
yesterday
| Formula | {yesterday(format)} |
| Parameters | format: format text |
| Description | Creates yesterday’s date/time in the given format |
| Returns | Yesterday’s date/time in the given format |
| Example call | {yesterday(Y-m-d H:i:s)} returns 2016-04-28 15:08:03 |
tomorrow
| Formula | {tomorrow(format)} |
| Parameters | format: format text |
| Description | Creates tomorrow’s date/time in the given format |
| Returns | Tomorrow’s date/time in the given format |
| Example call | {tomorrow(Y-m-d H:i:s)} returns 2016-04-30 15:08:03 |
datediff
| Formula | {datediff(timestamp1; timestamp2; unit)} |
| Parameters | timestamp1: date/time value |
| timestamp2: date/time value | |
| unit: years/months/days/hours/minutes/seconds; default: days | |
| Description | Subtracts timestamp2 from timestamp1 |
| Returns | The difference between the two dates returned in unit |
| Example call | {datediff(2016-02-01; 2016-04-22; days)} returns 81 |
addYears
| Formula | {addYears(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount years to timestamp |
| Returns | Incremented date in format |
| Example call | {addYears(Ymd; 2016-04-22; 1)} returns 20170422 |
addMonths
| Formula | {addMonths(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount months to timestamp |
| Returns | Incremented date in format |
| Example call | {addMonths(Ymd; 2016-04-22; 1)} returns 20160522 |
addDays
| Formula | {addDays(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount days to timestamp |
| Returns | Incremented date in format |
| Example call | {addDays(Ymd; 2016-04-22; 1)} returns 20160423 |
addHours
| Formula | {addHours(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount hours to timestamp |
| Returns | Incremented date in format |
| Example call | {addHours(Ymd H:i:s; 2016-04-22 23:30; 5)} returns 20160423 04:30:00 |
addMinutes
| Formula | {addMinutes(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount minutes to timestamp |
| Returns | Incremented date in format |
| Example call | {addMinutes(Ymd H:i:s; 2016-04-22 22:58; 5)} returns 20160422 23:03:00 |
addSeconds
| Formula | {addSeconds(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Adds amount seconds to timestamp |
| Returns | Incremented date in format |
| Example call | {addSeconds(Ymd H:i:s; 2016-04-22 22:58; 5)} returns 20160422 22:58:05 |
subtractYears
| Formula | {subtractYears(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts amount years from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractYears(Ymd; 2016-04-22; 5)} returns 20110422 |
subtractMonths
| Formula | {subtractMonths(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts amount months from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractMonths(Ymd; 2016-04-22; 5)} returns 20151122 |
subtractDays
| Formula | {subtractDays(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts amount days from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractDays(Ymd; 2016-04-22; 5)} returns 20160417 |
subtractHours
| Formula | {subtractHours(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts mount hours from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractHours(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 07:37:00 |
subtractMinutes
| Formula | {subtractMinutes(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts amount minutes from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractMinutes(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 12:32:00 |
subtractSeconds
| Formula | {subtractSeconds(format; timestamp; amount)} |
| Parameters | format: format text |
| timestamp: date/time value | |
| amount: decimal number | |
| Description | Subtracts amount minutes from timestamp |
| Returns | Decremented date in format |
| Example call | {subtractSeconds(Ymd H:i:s; 2016-04-22 12:37; 5)} returns 20160422 12:36:55 |
Control Functions
There is only one control function implemented in Calculate Fields so far, but this function ensures that the user can write very complex formulas with conditions. Since the functions can be embedded in each other, the user can write junctions with many branches.
ifThenElse
| Formula | {ifThenElse(condition; trueBranch; falseBranch)} |
| Parameters | condition: logical value |
| trueBranch: any expression | |
| falseBranch: any expression | |
| Description | Selects one of the two branches depending on condition |
| Returns | trueBranch if condition is true, falseBranch otherwise |
| Example call | {ifThenElse(\{equal(1; 1)}; 1 equals 1; 1 not equals 1)} returns 1 equals 1 |
Counter Functions
There are several counters implemented in Calculate Fields which can be used in various scenarios.
The counters sorted into two groups:
- Global counters: Counters which are incremented every time an affected formula is evaluated
- Daily counters: Counters which resets every day. (Starting from 1)
You will find more details on each counter on this page: Global Counter Manager
In this chapter we assume that the counters current value is 4, so the incremented value will be 5 with the given format.
GlobalCounter
| Formula | {GlobalCounter(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name with length numberLength |
| Returns | Counter with length numberLength |
| Example call | {GlobalCounter(myName; 4)} returns 0005 |
GlobalCounterPerUser
| Formula | {GlobalCounterPerUser(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the user who creates the entity with length numberLength |
| Returns | Counter with length numberLength |
| Example call | {GlobalCounterPerUser(myName; 3)} returns 005 |
GlobalCounterPerModule
| Formula | {GlobalCounterPerModule(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the module of the entity with length numberLength |
| Returns | Counter with length numberLength |
| Example call | {GlobalCounterPerModule(myName; 2)} returns 05 |
GlobalCounterPerUserPerModule
| Formula | {GlobalCounterPerUserPerModule(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the user who creates the entity and for the module of the entity with length numberLength |
| Returns | Counter with length numberLength |
| Example call | {GlobalCounterPerUserPerModule(myName; 1)} returns 5 |
DailyCounter
| Formula | {DailyCounter(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name with length numberLength |
| Returns | Counter with length numberLength, or if the counter is not incremented this day then 1 with length numberLength |
| Example call | {DailyCounter(myName; 1)} returns 5 |
DailyCounterPerUser
| Formula | {DailyCounterPerUser(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the user who creates the entity with length numberLength |
| Returns | Counter with length numberLength, or if the counter is not incremented this day for this user then 1 with length numberLength |
| Example call | {DailyCounterPerUser(myName; 3)} returns 005 |
DailyCounterPerModule
| Formula | {DailyCounterPerModule(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the module of the entity with length numberLength |
| Returns | Counter with length numberLength, or if the counter is not incremented this day for this module then 1 with length numberLength |
| Example call | {DailyCounterPerModule(myName; 1)} returns 5 |
DailyCounterPerUserPerModule
| Formula | {DailyCounterPerUserPerModule(name; numberLength)} |
| Parameters | name: any text |
| numberLength: decimal number | |
| Description | Increments and returns the counter for name for the user who creates the entity and for the module of the entity with length numberLength |
| Returns | Counter with length numberLength, or if the counter is not incremented this day for the user who creates the entity and for this module then 1 with length numberLength |
| Example call | {DailyCounterPerUserPerModule(myName; 1)} returns 5 |
JSON Functions
With the various JSON functions within the Calculated Fields, you can process JSON arrays and, for example, extract specific values from arrays or combine values into new JSON arrays.
CreateJSON
| Formula | {CreateJSON(parameter1;parameter2;parameter3;…)} |
| Parameters | parameterX: The key and value to add to the json object. The key and value are seperated by a . |
| Description | Creates a json object from the given parameters. |
| Returns | The json object string. |
| Example call | {CreateJSON(abc.def;ghi.jkl)} – Creates a json object {“abc”:”def”,”ghi”:”jkl”} |
JsonExtract
| Formula | {JsonExtract(parameter1;parameter2;parameter3)} |
| Parameters | parameter1: The json object string |
| parameter2: The key to fetch | |
| parameter3: The default value if the key does not exist | |
| Description | Get a value from a json object by key. |
| Returns | The value for the key or the default value if the key does not exist. |
| Example call | {JsonExtract({P0};abc;default)} – Get the value for the key abc from the json object from parameter 0. For {“abc”:”def”,”ghi”:”jkl”} would return def |
JsonExplode
| Formula | {JsonExplode(parameter1;parameter2;parameter3)} |
| Parameters | parameter1: A string value seperated by the parameter 2 |
| parameter2: The separator | |
| parameter3: Default value. Caution this returns the value directly not an json array! | |
| Description | Explode a string value to an json array. |
| Returns | The json array string. |
| Example call | {JsonExplode(abc;,;ghi)} – Explode the string abc to an json array [“abc”] |
JsonImplode
| Formula | {JsonImplode(parameter1;parameter2;parameter3)} |
| Parameters | parameter1: A json array string |
| parameter2: The separator | |
| parameter3: Default value. This is only used if the json is invalid! | |
| Description | Implode a json array to a string value. |
| Returns | The string value. |
| Example call | {JsonImplode({P0};,;)} – Implodes the json array from parameter 0 to a string value. For [“abc”,”def”,”ghi”] the result would be abc,def,ghi |
PHP Functions
With the PHP functions, serialized PHP arrays can be transformed.
GetValueForKey
| Formula | {GetValueForKey(parameter1;parameter2)} |
| Parameters | parameter1: A base64 encoded serialized php array |
| parameter2: The key to fetch. Use . to access nested key | |
| Description | Get a value from a serialized php array by key. |
| Returns | The value for the key or null if the key does not exist. |
| Example call | {GetValueForKey({P0};abc)} – Get the value for the key abc from the serialized php array in parameter 0. |