Write expressions using Konect smart fields

This article provides an overview of smart expressions which can be added to konect dataset fields. Konect uses SQL language as the basis for defining smart expressions. Using smart expressions will defining add significant additional value to a Konect dataset. 

Please refer to our technical documentation page for further details regarding an overview of individual functions for form expressions

https://api.konectech.com/docs/integration/calculated-fields/index.html

When creating a dataset in Konect, the following calculated field types can be used to create smart fields. Before creating a smart field in Konect, decide whether the following is required

·       Auto Calculated Text

·       Auto Calculated Numbers

·       Auto Calculated Date and Time

Open

Expression syntax for Konect Smart Fields

Below is a description of how the following could be used to enhance the value of using konect for smart, data driven automated workflows

Auto Calculated Text

Text may be auto calculated when another field in Konect needs to be auto populated based on a selection in another field. For example, if an inspection has been completed flag it as being ready for supervisor sign off. If the inspection is not applicable to sign off, this value will also be auto populate into the destination field. The Supervisor_Sign_Off ready field is greyed out because it is auto populated by the smart expression when the source field is populated.

Open

The calculated field expression is entered in the expression dialogue within Konect Manager itself.

Once the expression is entered, it can be validated and tested within Konect Manager.

The below syntax was used to auto generate a YES value in this dialogue.

Open

The syntax below includes the following elements;

if_else((Inspection_Completed == 'NO'), coalesce(nullif(Inspection_Completed, ''), username()), Inspection_Completed)

If_else – choose a defined value based on a condition. In other words, if an option in the form is select that is not ‘NO’ populate the target field

Coalesce – return the first non – null value

Username() – reference the username of the konect user

Auto Calculated Number

Konect can use smart expressions to auto calculate work hours for a staff member or contractor. A smart expression in this case would read the entered start and finish time on a specific day and auto populate the calculated hours field.

Open

floor((date_diff(parse_date(format('2000-01-01 {0}', FridayEndTime),'yyyy-MM-dd HH:mm:ss'), parse_date(format('2000-01-01 {0}', FridayStartTime),'yyyy-MM-dd HH:mm:ss'), 'second')/3600)*100 + 0.5)/100

Auto Calculated Date & Time

To autocalculate dates, an expression could be written to calculate every subsequent day of the week up to seven days from a date entered.

For example, should September 29 2025 be entered konect fields will be auto populate with the next 7 days concluding at the end of the week – Sunday October 5 2025

The following calculate date time field is used

date_add(Week_Start_Date, 6, 'day')

Open