Konect Calculated Field Docs
This documentation describes konect calculated fields and their syntax.
The calculated field
A konect calculated field is simply a user defined expression that evaluates to a final value that is then stored in the field.
Triggers
Konect calculated fields can be triggered to evaluate when the record is created, when a field used in the calculation changes or when the record is saved. A calculated field can use 1 or more of these triggers.
Parsing
A konect calculated field is created by taking user specified text and trying to parse it as an expression using the syntax rules defined in this documentation. If the specified text can be parsed and validated the calculated field is ready to be used in konect forms. The user specified text will be rewritten after validation with extra parenthesis and white space to make it clear where one operand finishes and another begins.
Samples
An Area calculated field could be created using the fields length and width:
length * width
A TreeCanopyArea calculated field using the diameter field and the pi and pow functions:
pi() * pow((diameter / 2), 2)
An InspectionDate calculated field could automatically capture todays date using the now function:
now()
A DueDate calculated field could calculate a due date in 2 weeks from the InspectionDate field using the date_add function:
date_add(InspectedDate, 14, 'day')
Manipulating or creating text data is very useful. In these samples we will work with BOM (Bureau of Meteorology) AMOC Area Codes (AAC) for forecast districts. These codes are based on the STATE_CODE and DIST_NO (district number) padded to 3 places separated with the text '_PW'. E.G. QLD_PW001.
To create an AAC from STATE_CODE and DIST_NO fields, we use the concatenate operator & to concatenate the fields and text together, formatting the number to 3 places using the format_number function:
STATE_CODE & ('_PW' & format_number(DIST_NO, '000'))
To extract the state code from an AAC we use the index_of and substring functions:
substring(AAC, 0, index_of(AAC, '_'))
To extract the the distict number from an AAC we also use the parse_number function:
parse_number(substring(AAC, index_of(AAC, 'PW') + 2))
Advanced Sample 1
In this sample the InspectionDate will only be calculated if the form variant 'Inspection' is being used.
if_else(
(info('variant') == 'Inspection'),
today(),
InspectionDate )
The if_else function takes 3 arguments, a condition that evaluates to true or false the result if the condition is true and the result if the condition was false.
The condition argument uses the info function and the equality operator == to determine if we are using the form variant 'Inspection'
(info('variant') == 'Inspection')
The true result will return todays date using the today function
today()
The false result will return the value already stored in the InspectionDate field
InspectionDate
Please note in this example the InspectionDate will update to todays date everytime the form is saved while using the Inspection variant. This calculated field will only evaluate using the on save trigger as it references itself.
Advanced Sample 2
Calculate a due date using the Priority field (a list with values High, Medium and Low) - add 2 days for high, 1 week for medium or 1 month for low priorities
case(
(Priority == 'High'),
date_add(InspectionDate, 2, 'day'),
(Priority == 'Medium'),
date_add(InspectionDate, 7, 'day'),
(Priority == 'Low'),
date_add(InspectionDate, 1, 'month') )
The case function takes alternating arguments of conditions and results.
case(condition1, result1, ... conditionN, resultN).
A condition is an expression that evaluates to true or false. When a condition evaluates to true the next result argument in the case function is evaluated and returned. If the condition evaluates to false, the case function moves to the next condition argument.
Advanced Sample 3
In this sample the CompletedBy field will be set to the current logged in user when the Completed field a list with values Yes or No (this could also be a yes no field) has been changed to the value 'Yes'. This is done using an if_else and username function calls.
The rest of the calculation deals with ensuring the field is only set if it hasn't been set before. Here is the sample:
if_else(
(Completed == 'Yes'),
coalesce(
nullif(CompletedBy, ''), username()),
CompletedBy )
The if_else function takes 3 arguments, a condition that evaluates to true or false the result if the condition is true and the result if the condition was false.
The condition argument determines if the Completed field equals 'Yes'
(Completed == 'Yes')
The true result will return the username if the CompletedBy field doesn't already contain a value
coalesce(
nullif(CompletedBy, ''),
username() )
The true result uses some additional functions to make sure we don't overwrite the value of CompletedBy once it has been set the first time. The coalesce function will return the first non null argument.
Argument 1 will return the value in CompletedBy. The nullif function is used to return a null if the value in CompletedBy is empty text.
nullif(CompletedBy, ''),
Argument 2 will return the logged in user, which we get using the username function.
username()
The false result will return the value already stored in the CompletedBy field
CompletedBy
This calculated field will only evaluate using the on save trigger as it references itself.
Calculated Field Syntax
Konect calculated field syntax is the set of rules on how calculated fields are constructed:
Values
The konect calculated field syntax defines two types of values: Field operands and literal value operands. Field values operands are looked up from the state of the record the calculated field is being applied to. Literal operand values are numbers, strings or booleans.
Fields Operands
Field operands hold the current value of a field from the record. To identify a field from the record you must use the field export name.
Using field operands is much like algebra. In programming, just like in algebra, we use field operands (like price1) to hold values. In programming, just like in algebra, we use field operands in expressions (price1 + price2). In this example, the fields price1 is 5 and price2 is 6, they will calculate the value 11:
price1 + price2
Your calculation can self reference the field you are creating, but these calculations will only evaluate using the on save trigger. For example a field called SaveCount could have this calculation which will increment every time the form is saved.
coalesce(SaveCount, 0) + 1
Cyclic references of fields are not allowed and will fail validation. For example: a field volume may contain the calculation
area * height
And the field height may contain the calculation
volume / area
When validating the height field it will fail due to the cyclic reference.
Literal Operand Data Types
Literal value operand date types are strings, numbers and booleans. The DateTime data type does exist, but they can only be created using DateTime functions.
Data Types
Numbers
A konect number can be written with or without decimals.
10.50
1001
Numbers are always double precision floating point number. Integers (numbers without a period) are accurate up to 15 digits. The maximum number of decimals is 17, but floating point arithmetic is not always 100% accurate. In this example, the return value will be 0.30000000000000004
0.2 + 0.1
To solve the problem above, it helps to multiply and divide:
((0.2 * 10) + (0.1 * 10)) / 10
Strings
A konect string is zero or more characters written inside single quotes.
'John Doe'
Konect strings do not understand formatting characters such as new line, carriage returns or tabs. This would not work.
'John
Doe'
Because strings must be written within quotes, konect will misunderstand strings that contain single quotes:
'We are the so-called 'Vikings' from the north.'
The solution is to use 2 single quotes in the string where you want 1. If you require 2 single quotes in a row, your string will need 4 single quotes.
'We are the so-called ''Vikings'' from the north.'
'Konect literal strings need 2 ''''single quotes'''' '
Booleans
A konect boolean represents one of two values: true or false.
DateTimes
All dates in konect are datetimes storing a time and time zone. DatesTimes can only be created using the now or today functions, the date function passing in the numerical values of each date part or with the parse_date function to convert a string representation of the datetime to a datetime.
now()
today()
date(2020, 3, 24, 17, 15, 20)
parse_date('2020-03-24 17:24 +10:00', 'yyyy-MM-DD HH:mm Z')
Operators
Operators in konect calculated fields are used between operands to calculate new values. Operands can be literal values, fields, functions or another expression contained within parentheses.
number_field + 1
user_field != username()
(number_field + 10) / 2
Although not necessary for arithmetic or simple comparison operators it is recommended to use spaces between values and operators.
If parentheses are not used operator precedence will follow fairly standard precedence order. During validation parentheses will be inserted back into the text to provide clarity on the precedence.
Operators in this document have been grouped into arithmetic, comparison, logical, sql comparison and string operators.
Functions
Functions in konect calculated fields allow more complex tasks to be performed. They are invoked by using the function name then enclosing any arguments in parentheses () with commas between each argument. Arguments can be literal values, fields, functions or another expression contained within parentheses.
round(2.3)
round(number_field)
round(pi())
round((number_field + 5))
Konect function names are case sensitive. All konect functions return a value.
Functions in this document have bene grouped into choose, date, formatting-and-parsing, lookup, maths, text and trigonometry functions.