Contents
Introduction
For the first time, Intrexx 7‘s formula editor enables you to create computational calculations using the Portal Manager. As well as view elements for calculations, results such as sum totals or cumulative values can be displayed beneath the tables. Furthermore, formulae can also be defined within rows and columns.
Operators
The foundation is made up of mathematical operators that allow you to perform standard calculations such as addition and subtraction. Logical operators are also added to this. It should be noted that the logical operators deliver double values instead of Boolean values as a result. In this case, 1.00 equals true and 0.00 false.

Operator Description + Addition  Subtraction / Division * Multiplication < Less than <= Less than or equal to > Greater than >= Greater than or equal to == Equal to != Unequal to && Logical And  Logical Or ! Logical Not
Reading values
In formulae, values can be read from diverse sources such as controls or data groups on entry and view pages.
//Read a value from a control with the type Integer or Double with a FallbackValue of 0. //The GUID here corresponds to the GUID of the read control. control("3A8788BDE1561CAF449890BAF01D6306B4F8E6AC", 0)
The following methods are available here:
Formula Desciption
control(CONTROL_GUID, FALLBACK) Read a value from a control with the type Integer or Double with a FallbackValue of 0.
controlAsNumber(CONTROL_GUID, FALLBACK) Read a numerical value from a control with the type String with a FallbackValue of 0.
formula(CONTROL_GUID, FALLBACK) Read the content of another calculation control. In doing so, you should make sure that you don’t define any circular referrals between calculation controls!
dataField(DATAFIELD_GUID, FALLBACK) Read a value based on the GUID of the desired data field.
dataFieldAsNumber(DATAFIELD_GUID, FALLBACK) Read a numerical value with the type String based on the GUID of the desired data field.
If the current data group has a child data group, you can also access the child datasets of the current datasets and carry out the various aggregate functions on them.
//Calculate the sum total of a data field from the child data group. childSum(dataGroup("669F2C0747A5FFBB4FB2C7829945071E1ED5B0CB"), dataField("9AA6E0484980D633B43DB53D9B11AF22962667B4", 0))
The following methods are available here:
Formula Description
childSum(datagroup(GUID), dataField(GUID), FALLBACK) Sum total of a data field across all child datasets of the current parent dataset from the child data group.
childMin(datagroup(GUID), dataField(GUID), FALLBACK) Minimum value of a data field from all parent datasets from the child data group.
childMax(datagroup(GUID), dataField(GUID), FALLBACK) Maximum value of a data field from all parent datasets from the child data group.
childCount(datagroup(GUID), dataField(GUID), FALLBACK) Calculate the number of child datasets from the current parent dataset.
childAvg(datagroup(GUID), dataField(GUID), FALLBACK) Calculate the average value of a data field across all child datasets from the current parent dataset.
Table functions
Calculations in view tables
Calculation controls can be defined as a new column within a view table. With that, the inserted formula can be carried out in every row of the table.
//Calculation of the product of two column values (e.g. number of items * individual price) //The GUIDs of the desired table columns are to be entered here. row("FDBD14318DD7A434AD5131D5C26D630E8F292530", 0) * row("C6CA5368F24171BD6EDBF63C3B83F49FF1813C11", 0)
The following methods are available here:
Formula Description
row(COLUMN_GUID, FALLBACK) Obtain a column value with a FallbackValue.
rowAsNumber(COLUMN_GUID, FALLBACK) Obtain a column value with the type String with a FallbackValue.
rowFormula(CONTROL_GUID, FALLBACK) Obtain a column formula with a FallbackValue.
rowDataField(DATAFIELD_GUID, FALLBACK) Obtain a column value based on a data field GUID with a FallbackValue.
rowDataFieldAsNumber(DATAFIELD_GUID, FALLBACK) Obtain a column value with a FallbackValue.
If the current data group has a child data group, you can also access the child datasets of the current datasets and carry out the various aggregate functions on them.
//Calculate the total sum of a data field from the child data group. //The GUIDs from the child data group and the data field from the child data group are to be entered here. childSum(dataGroup("669F2C0747A5FFBB4FB2C7829945071E1ED5B0CB"), rowDataField("9AA6E0484980D633B43DB53D9B11AF22962667B4", 0))
The following methods are available here:
Formula Description
childSum(datagroup(GUID), dataField(GUID), FALLBACK) Sum total of a data field across all child datasets of the current parent dataset from the child data group.
childMin(datagroup(GUID), dataField(GUID), FALLBACK) Minimum value of a data field from all parent datasets from the child data group.
childMax(datagroup(GUID), dataField(GUID), FALLBACK) Maximum value of a data field from all parent datasets from the child data group.
childCount(datagroup(GUID), dataField(GUID), FALLBACK) Calculate the number of child datasets from the current parent dataset.
childAvg(datagroup(GUID), dataField(GUID), FALLBACK) Calculate the average value of a data field across all child datasets from the current parent dataset.
System values
As well as values from datasets, various system values, such as data from sessions or system data groups, can be accessed within formulas.
//Read a value from a system data group. //The data field name is to be entered here. systemField(("L_VERSION"), 0)
The following methods are available here:
Formula Description
systemField(COLUMN_NAME, FALLBACK) Read a value from a system data group.
systemFieldAsNumber(COLUMN_NAME, FALLBACK) Read a value with the type String from a system data group.
requestAsNumber(VARIABLE_NAME, FALLBACK) Read a value from the current request.
sharedState(VARIABLE_NAME, FALLBACK) Read a value from the current processing context.
sharedStateAsNumber(VARIABLE_NAME, FALLBACK) Read a value with the type String from the current processing context.
session(VARIABLE_NAME, FALLBACK) Read a value from the user session.
sessionAsNumber(VARIABLE_NAME, FALLBACK) Read a value with the type String from the user session.
Functions
Error handling
The fallback values shown in the previous examples are effective in case the requested value, desired control or the like are undefined (null). Server errors, which occur during the calculation, are however not handled. As an example, this is the case if a value from a string data field should be used in a formula but this contained value can't be parsed to a number. This is the case with any nonnumerical character. Merely the phrase "An error has occurred" will appear in the calculation control instead of a value in this situation. This can be desirable in some cases to directly detect flawed data. If this is not desired however, such server errors can be caught again separately.
//Read a string value and return a 0 in the case of error. zeroOnError(rowAsNumber("117B9589365400BBF5B72540565F75D42496BE73")) //Read a string value and return a 1 in the case of error. oneOnError(rowAsNumber("117B9589365400BBF5B72540565F75D42496BE73")) //Read a string value and return a userdefined value in the case of error. fallbackOnError(rowAsNumber("117B9589365400BBF5B72540565F75D42496BE73"), 1)
Rounding functions
There are a number of methods available for rounding inserted or calculated numerical values according to specific criteria or mathematical procedures. The individual procedures are described here with examples.
Function
roundLong(VALUE)
roundLongHalfAwayFromZero(VALUE)
roundLongHalfEven(VALUE)
roundLongHalfUp(VALUE)
roundLongHalfDown(VALUE)
round(VALUE)
roundHalfAwayFromZero(VALUE)
roundHalfEven(VALUE)
roundHalfUp(VALUE)
roundHalfDown(VALUE)
Distinction of cases
So that even complex calculations or formulas are only carried out under certain conditions, you have the ability to define case distinctions.
//Foundational structure case(BOOLEAN_EXPRESSION, TRUE_EXPRESSION, FALSE_EXPRESSION) caseNot(BOOLEAN_EXPRESSION, TRUE_EXPRESSION, FALSE_EXPRESSION) //If the total sum is greater than 1000, the result of a formula will be used. If not, the value 1000 will be used. case(totalSum(table("A2391263105FD17F556A68A10AECC63115AEF217"), row("E605C89A571DDD8112A252A48256A92555A5E794")) > 1000, formula("26D6E15533B8AD9AFCE9BB36672F2F9912C5CB73", 0), 1000)
As well as being able to compare numerical values, strings can also be verified in a Boolean expression. Doing so, a calculation can only be carried out if, for example, the corresponding order has a specific status.
//Read an order's status case(equalsIgnoreCase(rowDataField("062FA7D7B2F1651C5037307B0E3E1FB2B107969F", ""), "Ordered"), true, false)
Function
equals(STRING_1, STRING_2)
equalsIgnoreCase(STRING_1, STRING_2)
startsWith(STRING, PREFIX)
startsWithIgnoreCase(STRING, PREFIX)
endsWith(STRING, SUFFIX)
endsWithIgnoreCase(STRING, SUFFIX)
contains(STRING, SEQUENCE)
containsIgnoreCase(STRING, SEQUENCE)
isEmpty(STRING)
isNullOrEmpty(STRING)
isNull(STRING)
Aggregate functions for column values
In many cases, a determination of values is required beneath a table, this is then displayed in the table. This applies to totals, subtotals and average values. There are three different variants to cover different UseCases (this is demonstrated with the example of a total sum determination):
Function Description
sum(table("TABLE_GUID"), row("CONTROL_GUID")) Determination of the sum of all values that are currently displayed.
subTotalSum(table("TABLE_GUID"), row("CONTROL_GUID")) Determination of the sum of all data from page 1 of the table
up until the displayed page. This only differentiates itself from the function sum if a table navigation is present and the user isn't on the first page of the table.
totalSum(table("TABLE_GUID"), row("CONTROL_GUID")) Sum total of all datasets that are displayed in the table, the current position of the navigation element is irrelevant here.
Analog methods are available for:
Function Description
max/subTotalMax/totalMax Maximum value
min/subTotalMin/totalMin Minimum value
count/subTotalCount/totalCount Amount
avg/subTotalAvg/totalAvg Average value
Please note that the total methods are "expensive", meaning computationally intensive, operations. Corresponding delays can occur with very large amounts of data.
Date functions
It's possible to work with date and time values to determine deadlines, due dates and similar circumstances. Included in this is system data such as creation date as well as values from date and time data fields or the current time.
Functions for individual date values
If a value is to be obtained from an individual date value, the following methods can be implemented, as shown in the example (with every method, the desired timezone can be entered as a second parameter):
//Number of hours of the current date that have passed in the timezone Europe/Berlin. hour(currentDateTime, "Europe/Berlin") //Day of the month from a date value of a data field without a timezone entry. //This will take the timezone of the currently loggedin user into account. dayOfMonth(dataField("2DA5429B4BE959EBE28B18E0BAB8C4705C789D1A", 1))
Function Description
currentDateTime The current date
currentRequestDateTime The date from the last request
dateTime("DATE_STRING", DATE_FORMAT") Date value from a string
dayOfYear("DATE_EXPRESSION") Day of the year
dayOfMonth("DATE_EXPRESSION") Day of the month
dayOfWeek("DATE_EXPRESSION") Day of the week with Monday as the first day of the week
weekOfYear("DATE_EXPRESSION") Week of the year with Monday as the first day of the week
weekOfYearSunday("DATE_EXPRESSION") Week of the year with Sunday as the first day of the week
year("DATE_EXPRESSION") The date's year
month("DATE_EXPRESSION") The date's month (January = 1, February = 2, ...)
hour("DATE_EXPRESSION") The number of hours of the date
minute("DATE_EXPRESSION") The number of minutes of the date
second("DATE_EXPRESSION") The number of seconds of the date
millisecond("DATE_EXPRESSION") The number of milliseconds of the date
Difference between two date values
In order to ascertain the difference between two date values, the following methods can be used:
//Difference in days between a date value and the current date diffInDay(dataField("C2FA3A0DD01E7E6612972A7A97D8DDCA1709CF3E", 1), currentDateTime)
Function Description
diffInDay("DATE_1", "DATE_2") Difference between two date values in days
diffInHour("DATE_1", "DATE_2") Difference between two date values in hours
diffInMinute("DATE_1", "DATE_2") Difference between two date values in minutes
diffInSecond("DATE_1", "DATE_2") Difference between two date values in seconds
diffInMillisecond("DATE_1", "DATE_2") Difference between two date values in milliseconds.
Date expressions from datasets or system values
As well as reading numerical values, date values can also be read from data fields, rows in tables etc.
Function Description
controlAsDateTime("CONTROL_GUID", "FALLBACK") Date value from a control
dataFieldAsDateTime("DATAFIELD_GUID", "FALLBACK") Date value from a data field
rowAsDateTime("CONTROL_GUID", "FALLBACK") Date value from the current row (in a view table)
rowDataFieldAsDateTime("DATAFIELD_GUID", "FALLBACK") Date value from the current row based on a GUID (in a view table)
sessionAsDateTime("VARIABLE", "FALLBACK") Date value from a session variable
sharedStateAsDateTime("VARIABLE", "FALLBACK") Date value from a variable from the current processing context
requestAsDateTime("VARIABLE", "FALLBACK") Date value from a request variable
systemFieldAsDateTime("COLUMN_NAME", "FALLBACK") Date value from the value of a system data field