Configuring a Formula field

Table of Contents  [ Hide]

Overview
Steps in adding a formula field
Sample Formula Expressions

Overview

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. 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.

Steps in adding a formula field

1. Drag-n-drop the formula field type to the form editor area.
   
   
2. In the Adding '<Formula field name>' dialog, specify the label name of the formula field and the expression based on which the value for this field is calculated. For example, in the Sales form, we will add a formula field named Amount to calculate the Sales Amount based on the field values Units_Sold and Rate. So, in the expression area, we will specify the expression Units_Sold * Rate, where, Units_Sold and Rate are the field names. All the field names in the form are displayed below the expression area, for reference.
   
   



3. Click Done to save the formula expression.
4. The formula field named "Amount" is added to the form, as shown below.  To edit the formula expression at any point of time, move mouse over the Amount field and click on the Edit this field option.
   
   

5. Since formula fields are calculated, they do not get displayed in the form when it is rendered. The Sales form given below does not display the Amount formula field, we created above.
   



6. The formula field named Amount is displayed in the Sales View, as shown in the screen-shots below:

Notes: The fields that participates in a formula expression has a data type associated with it.  The field in which you place the result of your formula also has a data type. All these data types and the Operators you use on them must be compatible. If you combine field types incorrectly, your formula won't work and Zoho Creator displays an "Error message" describing the data type mismatch. Deluge built-in functions and Deluge System Variables can be used in the formula expression. One formula field cannot be used in another formula field. Since formula fields are calculated, they do not get displayed in the form when it is rendered. User can choose to view these fields in the form view. All types of form fields except multiple select fields can be used in formulas. The formulas will get recalculated when,  the formula is modified the values of the fields participating in the formula gets modified (when a record is updated).

Sample Formula Expressions

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.