You can create fields that are calculated instead of being entered by the user. These fields are called the formula fields. When you create a formula field, you have to specify the formula expression based on which the value for this field is calculated. You can also display the formula value in the Form / Report or only in the Report, based on your requirement. By default, the formula value will be displayed only in the report.
Depending on the output of the formula, the value held by a formula field could be numeric values, text values, dates, and durations. The formula evaluation is nothing but expression evaluation and is explained in detail in Expressions.
Drag-n-drop the formula field type to the form editor area.The formula field named "Amount" is added to the form, as shown below
In the Field Properties, specify the Field name that will be displayed for this field and the expression based on which the value for this field is calculated. For example, in the "Bill Payment" form, we will add a formula field named Amount to calculate the Sales Amount based on the field values Quantity and Price. So, in the expression area, we will specify the expression Quantity * Price, where, Quantity and Rate are the field names. All the field names in the form are displayed below the expression area, for reference.
By default, the formula field value will be displayed when you access the report. Select the check box option Make the field visible in the Form to display the formula value in the Form and the View.
To edit the formula expression at any point of time, select the field and in the field properties manke the necessary changes. The changes you make will be auto saved. If the option "Make the field visible in the Form" is selected,the Bill Payment Form will display the Amount field with value, when you access the form.
The formula field named Amount is displayed in the Product View, as shown in the screen-shots below:
1. To calculate the number of days between any two date fields, ServiceStartDate and ServiceEndDate
((ServiceEndDate - ServiceStartDate) / (1000 * 60 * 60 * 24))
2. To calculate the average marks scored in three subjects, where, English, Maths and Science are the numeric field names.
((English + Maths + Science) / 3)
3. To calculate the sum of all scores and display it as a percentage of total score, where score1, score2 and score3 are the numeric field names and round is a Deluge built-in function.
((score1 + score2 + score3) / 3 * 100).round(2)
In the following expression, when the boolean expression is true then variable is assigned with the value of expression1 else value of expression2 will be assigned
<variable> = IF(<Boolean expression>, expression1, expression2);
The following formula expression can be used to check for null values in numeric fields. If the value of a numeric field is null, the value 0 is assigned, else the value of the number field is assigned.
The following formula expression can be used to update the value of a field based on the value specified in another field. If the value of sales is greater than cost, the value "Profit" is assigned, else the value "Loss" is assigned.
IF(input.sales > input.cost, "Profit","Loss");
The following formula expression can be used in the send mail task to send only those field values that are not null. This expression is used in the "Message" content of the send mail task. Here, Name and Email_1 are field names in the form.
if((input.Name != ""),"\nName : " + input.Name,"") + "<br />\n" + if((input.Email_1 != null),"\nEmail : " + input.Email_1,"") + "<br />"
For complex conditions,if statement can be used in the on add -> on success block of Form actions.
You can use the Deluge round() function to round the result of your formula expression to two decimal places.To perform this action,
The syntax is given below:
Example: (Decimal_field + Number_field).round(2)
In the above example the summation of decimal and number field is rounded off to two digits and the result is displayed.