The formula field's Expression is what defines the value it will store and display. For example, the Amount field in an Order form is usually a result of multiplying the values in Unit Price and Ordered Quantity fields. The expression of this Amount field will be:
Here Unit_Price and Ordered_Quantity are field link names and the asterisk (*) represents the arithmetic operator that multiplies the values in the two fields. Refer to the following sections to learn about defining a formula field's expression:
A formula field's expression can be a combination of the following:
Fields | The expression can include fields in same form and fields in related forms (related via lookup and subform fields) |
Constants | They refer to static values. A constant can be some text, number, special character, or a combination of these. For example, abcd is a string constant, 123.02 is a numeric constant, abcd - 123 is a string constant (even though it contains the number 123). A constant's data type is determined by the manner in which it's included in the expression. Refer to the guidelines |
Deluge system variables | Including Deluge system variables can come in handy. For example, zoho.currentdate is a variable that returns the current date. Learn more |
Operators | These are symbols that perform specific computations on one or more values (referred to as 'operands'). For example, the + symbol is an arithmetic operator that performs the addition of two numbers or concatenation of two strings. Learn more |
Built-in functions | They perform specific operations on one or more values. For example, the length() function returns the number of characters present in a string. Learn more |
The sample expressions section illustrates these guidelines.
Let's take an example. Imagine that you want to discount the Amount in an Orders form by $10 if the ordered quantity exceeds 15. (To keep it simple, it is assumed here that an order can include only one product. This is mostly not the case in the real world.)
You can nest conditional expressions — use one conditional expression inside another. Refer to this section to see a sample expression.
Purpose of Formula field | Its Expression | Explanation |
Display a string | String values must be enclosed within double quotation marks | |
Display a string that contains double quotation marks, say | Backslash character (\) must precede the double quotation marks you want to display | |
Display a number | Numbers can be inserted as such | |
Display a decimal value | Decimal values can be inserted as such | |
Display a date value | Date values are to be enclosed with single quotation marks. The value must follow the date format set in that app's Settings. (This example considers the format to be dd-MMM-yyyy) | |
Display the current date | or | zoho.currentdate and today are system variables that return the current date |
Display tomorrow's date | or | tomorrow is a system variable that returns the date of the day after the current day. addDay() is a built-in date function. |
Display yesterday's date | yesterday is a system variable that returns the date of the day before current day | |
Display a date-time value | Date-time values are to be enclosed with single quotation marks. The value must follow the date format set in that app's Settings. This example considers the format to be dd-MMM-yyyy. | |
Display the current date and time | or | zoho.currenttime and now are system variabes that return the current date and time |
Round off a decimal value to nearest hundredth, i.e., 2 digits after the decimal point | or | round() is a built-in function. It requires you to tell what value it is to operate on, and the number of decimal places it is to round the decimal value to. |
Find the number of characters in a string | length() is a built-in function. It returns the number of characters present in a string, including whitespaces. | |
Find the number of days between the entered date and current date | zoho.currentdate is the system variable that returns the current date. Delivery_date is a field link name. daysBetween() is a built-in function that returns the number of days between two date or date-time values. |
Purpose of Formula field | Its Expression | Explanation |
Concatenate the data in two fields, say Product Name and Product Type fields in a Product form. | or | Product_Type and Product_Name are field link names. + is the arithmetic operator that concatenates its operands when either one of them is a string. |
Find the number of characters entered in a field, say Comments | Comments is the field's link name. length() is a built-in function that returns the number of characters present in a string | |
Round off a decimal value stored in a field to nearest tenth, i.e., 1 digit after the decimal point | round() is a built-in function. It requires you to tell the decimal value it is to operate on, and the number of decimal places it is to round to. The field containing a decimal value is to be referred to by its field link name. | |
Find the number of days between two date or date-time fields, say Start Date and End Date | Start_Date and End_Date are field link names. daysBetween() is a built-in function. It returns the number of days between two date or date-time values. | |
Display the latitude and longitute of the address selected by the user | Address is a composite field. The expression will concatenate the two values, with a comma between them. Note: Latitude and longitude are captured only when a user selects an address from the map. |
Purpose of Formula field | Its Expression | Explanation |
Display the Email Alias of the Department in which the Employee works | Usually an employee would be assigned to only one department at a time. This relationship is established using the lookup field. Department data is looked-up from the Employees form. Department and Email_Alias are the field link names. | |
Display the Price of a Product when placing orders | Usually product details are stored in a separete form, and are looked-up in the Order form. Product is the lookup field added to the Order form, through which the selected product's Price is fetched. | |
Display the Line Total of each ordered product in an Orders form | Price and Quantity are field link names. Price can be a formula field that displays the selected product's price from the Product form. (see the example above) | |
Display the number of records added in the subform | Line_Items is the field link name of the subform. count() is a built-in aggregation function that returns the number of records in a form. | |
Display in the main form the sum of numeric values present in the subform | Line_Items is the field link name of the subform. Line_Total is the link name of the field that stores the line total (see the example above) of each ordered product. sum() is a built-in aggregation function. |
Refer to this section to learn what a conditional expression is.
Purpose of Formula field | Condition | Value if condition holds true | Value if condition doesn't hold true | Final expression | Explanation |
When user has selected a product and entered a quantity in an Orders form, set its Line Total with the product (multiplication) of its Price and Quantity, else set it as zero (0). | Product.Price != null && Quantity != null | Product.Price * Quantity | 0 | Price and Quantity are field link names. Price can be a formula field that displays the selected product's price from the Product form. | |
Convert the numerical Rating your users selects in your Webinar Feedback form, into a word: 1 - Poor, 2 - Average, 3 - Good, 4 - Excellent. These numerical values would be choices displayed by a radio field. | Rating == "1" | "Poor" | if(Rating == "2", "Average", if(Rating == "3", "Good", "Excellent")) | This is an example of nesting conditional expressions. Rating is the link name of the choice field, with 1, 2, 3, and 4 as its choices. The expression progressively checks from 1 to 4, and returns the corresponding word. |
Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.
If you'd like a personalized walk-through of our data preparation tool, please request a demo and we'll be happy to show you how to get the best out of Zoho DataPrep.
You are currently viewing the help pages of Qntrl’s earlier version. Click here to view our latest version—Qntrl 3.0's help articles.