Let's take an example. The Total Amount in an Order form is calculated based on the Unit Price and Quantity of the ordered items. Instead of writing workflows to achieve this, you'd save a lot of time and effort by using the formula field. In addition to such mathematical operations, your requirements may include text-based operations like concatenation of the data in two fields, or date-based operations like finding the number of days between two dates. It's for such requirements (and more) that Zoho Creator has in store the Formula field.

As the formula field's value is calculated, it is not displayed in the form by default. You can make it visible if required. Learn More

Features

• Making the formula field visible in the form
  
• Set the field ty​pe
• Defining the formula field's expression
• Data type of a formula field
• Calculation of the formula field's value
  
  • When field values are null
  • Recalculation of the formula field's value

• Add a formula field to your form
• Set display format for reports
• Customize the field name and field link name
• Set the field size

Making the formula field visible in the form

As the value of the formula field is calculated and not entered by your users, it is by default not displayed when your users access your form. You can make it visible if required. Learn How

Things to know:

• When you display the formula field in the form, it will appear disabled — indicating to your users that they cannot enter an input in it
• When your formula field refers to other fields in the form, it makes sense to let your users view the calculated value before they submit your form. 
  
  Tip: Try to place the formula field near the fields it refers. This will help your users understand that there's a calculation happening based on the input they enter; provides a better user-experience.

Set the field type 


Define your formula field's type — single line type or multi-line type, for string type of expressions. Single line formula field type supports only upto 255 characters and Multi-line formula field type supports (upto 65355 characters) multiple lines of formula but with some particular limitations (refer below table).




The following are the behaviors shown by single line formula field type and multi-line formula field types.


Module	Property	Supports New Single Line Formula	Supports New Multi-Line Formula
Form	Lookup Display Field	Yes	No
	Lookup Advanced search	Yes	No
	Subform Field Based Sort	Yes	No
Report	Criteria Builder across modules(set criteria)	Yes	No
	Grouping	Yes	No
	Sorting	Yes	No
	Search	Yes	No
Deluge	Criteria filter	Yes	No
	Aggregate records (Count ,Sum, Distinct Count, Maximum, Minimum)	Yes	No
Pages	Panel/Gauge - Distinct Count	Yes	No
	Chart - X, Y axis	Yes	No
API	Criteria Usages	Yes	No
Mobile	Search in reports	Yes	No
	Grouping in reports	Yes	No
	Sorting in reports	Yes	No
	Search	Yes	No




Note:

• When switching from multi-line field type to single line field type, the values of the multi-line formula field will be truncated to fit the maximum limit of 255 characters. 
  
• The existing formula fields will remain as a single line field without any limitations, until the field type property is changed. All the newly added formula fields will be single line by default with the option to change the field type.
  

Defining the formula field's expression

Defining a formula field's expression starts with defining what value is the field to display. Refer to this page to learn more about defining expressions for formula fields.

Though formula field may be used to simply display a string constant like abcd or a numeric constant like 123. However, in most cases you would want to perform calculations based on field values. Refer to the following examples:

Requirement	Expression	Explanation
Calculate the Amount based on the product's Unit Price and Quantity	Unit_Price * Quantity	The arithmetic operator (*) multiplies the values stored in the two fields.
Concatenate the Last Name and Email address of an employee	Name.last_name + Email_address	Name is a composite field. last_name is the link name of the Last Name field. The arithmetic operator (+) concatenates the string values stored in the two fields.
Calculate the Average Marks scored by a student based on their scores in Math, English, and Science	(Math + English + Science) / 3	Math, English, and Science are field link names. The arithmetic operators + (addition) and / (division) have been used. The parentheses () helps define the order of execution of the two operations.

Data type of a formula field

Each field in Zoho Creator is associated with a data type. The formula field is unique in this regard — its data type is decided upon the evaluation of its expression. For example:

Expression	Data type	Explanation
2 + 3	Bigint	When the arithmetic operator (+) operates on numbers, it adds them. The result (5) is a number.
"2" + "3"	String	Characters enclosed in double quotation marks are considered as a string. When the arithmetic operator (+) operates on strings, it concatenates them. The result (23) is a string.
"abcd".length()	Bigint	Characters enclosed in double quotation marks are considered as a string. length() is a built-in function that returns the number of characters present in a string. The result (4) in a number.
zoho.currentdate	Timestamp	zoho.currentdate is a Deluge system variable that returns the current date. The result is a date value.
2 > 3	Boolean	When the relational operator (>) operators on the numbers 2 and 3, it checks if the relation is true or not. As 2 is not greater than 3, the result is false — a boolean value.

Calculation of the formula field's value

The value displayed by a formula field is the evaluation of its expression. This value is calculated when your users submit your form.

When you make the formula field visible on the form, your users will be able to view the calculated value (before they submit the form) depending on whether or not the expression includes fields.

• When the formula field's expression does not include fields, the calculated value will be displayed when your users enter an input (while submitting an entry) or modify the data in any one of fields (while editing an entry) in that form. Even if users do not enter or modify any data, the formula field's value is calculated on form submission.
• When the formula field's expression includes fields, the value will be calculated only when none of the fields store null. 

When field values are null

Unless a field is made mandatory on the form, it can remain empty (i.e., without data) in many records. Zoho Creator assigns such empty fields with the value null. When a field in a record stores null, Zoho Creator interprets that there's no value in the field (in that record). When your formula field's expression includes fields which can be null, its expression may not be evaluated (as mentioned above).

The following type of fields can contain null: number, decimal, currency, percent, date, date-time. Text-based fields like single line, name, email, address, never contain null. When they are empty (or store no data), they are considered as strings containing zero characters. To ensure that your formula field always has a value for it, you must define a conditional expression.

Recalculation of the formula field's value

The formula field's value will be recalculated when:

• You modify the field's expression
  
• You switch the field types from single line to multi-line field type or vice versa.
  
• Your users edit records in bulk and one of fields being edited is part of that formula field's expression
Note: Modifying the formula field's expression will initiate a recalculation in all records that form contains. This recalculation can take some time when multiple records are involved.

Set display format for reports


When your formula field's data type is string, you can define the format in which its data is to be shown in reports. This is defined by its Display format in reports property. Learn How

Choosing to display formula field's data in reports as plain text may benefit the performance as the system gets to skip checking it for invalid HTML tags.