United Planet GmbH

Calculating with Intrexx 7

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.

  • OperatorDescription
    +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:

FormulaDesciption
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:

FormulaDescription
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:

FormulaDescription
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:

FormulaDescription
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:

FormulaDescription
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 non-numerical 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 user-defined 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):

FunctionDescription
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:

FunctionDescription
max/subTotalMax/totalMaxMaximum value
min/subTotalMin/totalMinMinimum value
count/subTotalCount/totalCountAmount
avg/subTotalAvg/totalAvgAverage 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 time-zone can be entered as a second parameter):

//Number of hours of the current date that have passed in the time-zone Europe/Berlin.
hour(currentDateTime, "Europe/Berlin")

//Day of the month from a date value of a data field without a time-zone entry. 
//This will take the time-zone of the currently logged-in user into account.
dayOfMonth(dataField("2DA5429B4BE959EBE28B18E0BAB8C4705C789D1A", -1))
FunctionDescription
currentDateTimeThe current date
currentRequestDateTimeThe 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)
FunctionDescription
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.

FunctionDescription
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