What is a Calculated Field
Codeless ONE provides support for specific object properties (fields) that automatically generate values whenever a new record is created or an existing record is updated. These auto-generated values are dynamically calculated based on predefined formulas applied to the fields. The formulas can be tailored to fit various use cases, allowing users to automate calculations, generate unique identifiers, timestamps, or even populate fields based on complex business logic.
For example, users can set up formulas to automatically calculate totals based on other fields, generate sequential numbers for unique records, or apply date and time stamps upon record creation. This automation saves time and reduces manual input errors, ensuring consistent and accurate data across the system. The flexibility of this feature enables users to configure formulas according to their specific business needs, making it a powerful tool for streamlining workflows, enhancing data accuracy, and improving operational efficiency.
This article deals with the concepts related to the Calculated Field option. The actual process to devise formulas for calculated fields are discussed in separate articles.
Data types supported for Calculated Field are:
- Number (integer/decimal),
- Text
- DateTime (Date/DateTime)
Edit property section shows Calculate Formula toggle button, when a supported Data type is selected.
When the Required checkbox is selected, the toggle button becomes disabled.
If the toggle button is enabled, a textbox appears below it, for the formula. This textbox, however is not directly editable. Users have to enter the desired formula using the Configure Formula option below the textbox.
When a user clicks the Configure Formula option, the Configure Formula dialogue box is displayed, where the user can configure the desired formula.
Dialogue box UIs
For each supported data type, there is a unique dialogue box. The following image shows the Dialogue box for the DateTime type.
Should the formula be calculated at the time of creation of the record?
Should the formula be calculated at the time of updating the record?
The formula
The property that is used to calculate the formula
Operator applied on the property
To decrease the value by a day
To directly enter the number of days used in the formula
To increase the value by a day
Dialogue box for the Text/String type
Should the formula be calculated at the time of creation of the record?
Should the formula be calculated at the time of updating the record?
The formula
The property that is used to calculate the formula
Operator applied on the property
Link to add another group (Property & operator) to calculate the formula
Dialogue box for the Number type
Should the formula be calculated at the time of creation of the record?
Should the formula be calculated at the time of updating the record.
The formula
Operator 1 applied on the property
The property that is used to calculate the formula
Operator 2 applied on the property
Link to add another group (Property & operator) to calculate the formula
On Create and On Update
Initially, Both the On Create and On Update checkboxes are enabled but uneditable. As soon as a formula is devised, both these options become editable.
The On Update option is dependant on the On Create option. If On Create option is enabled, only then the On Update option can be enabled or disabled.
If a user un-checks both the On Create and On Update options (meaning both parameters have values FALSE), then the formula is not calculated and the default value is taken instead.
Enabling On Create option means that whenever the record is created, the value would be calculated as per the formula and saved in the field.
Enabling On Create and On Update options means that whenever the record is created or updated, the value would be calculated as per the formula and saved in the field.
As mentioned earlier, a user can’t disable the On Create and enable the On Update option, because On Update is dependant on On Create option.
Property and Operators
In the Form Builder, not all properties are available for use in formula calculations—only relevant ones can be selected. For instance, if a formula is being applied to a DateTime property, only other DateTime properties can be used in that formula.
Properties used in the formula could be either regular properties, or linked properties. While link properties are not supported in Text formula, the Number and DateTime support both the regular and linked properties.
If there is reference present from one property, then the other property should not make reference with first property, else an error message would be displayed
When a Calculated field is being formulated, the same property can not be used/called in the formula. This limitation is to avoid the self-referencing loop. In the following example, Employee ID can not be used as a property in the Formula Builder (in any group).
For DateTime type, the available operators are Plus and Minus.
For Text(String) type, the available operators are Capitalize, Lower case and First letter.
For the Number type, there are two kinds of operators. Operator 1 is optional. Operators available for Operator 1 control are:
- Exponent (power)
- Absolute
- Round
- Ceil
- Floor
- Count
- Min
- Max
- Avg
- Sum
For Operator 2 control, the operators available are
- Divide
- Multiply
- Subtract
- Add
- Modular
Users can create multiple rows in the form builder by clicking the +Add group link.
The right sequence in the formula builder (for both DateTime and Text) is that first a user selects the property then the operator. It can’t be the other way round because operator remains disabled until a property has been selected. However, Operator 1 in Number type is an exception to this rule.