Understand Formula Field's Expression | Zoho Creator Help

Understand expression

Understand formula field's expression

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:

Unit_Price * Ordered_Quantity

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:

Building blocks of expressions

A formula field's expression can be a combination of the following:

FieldsThe expression can include fields in same form and fields in related forms (related via lookup and subform fields)
ConstantsThey 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 variablesIncluding Deluge system variables can come in handy. For example, zoho.currentdate is a variable that returns the current date. Learn more
OperatorsThese 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 functionsThey perform specific operations on one or more values. For example, the length() function returns the number of characters present in a string. Learn more

Guidelines for defining expressions

The sample expressions section illustrates these guidelines.

  • With regard to including fields in expressions:
    • Fields are to be referred by their field link names. Expressions can include all types of field except add notes and section. A formula field can be referred in the expression of another formula field.
  • To include Deluge system variables, you must type them.
  • With regard to including constants in expressions:
    • String constants are to be enclosed within double quotation marks. Numeric constants are to be added as such. Date and date-time constants are to be enclosed within single quotation marks.
  • Parentheses are to be added to define the order of execution (while evaluating the expression)
    • Example 1: The result of the expression 2 + 3 * 5 will be 17. The multiplication operator (*) has precedence of execution over the addition operator (+). So 3 and 5 will be multiplied first, and then their result (15) will be added with 2.
    • Example 2: The result of the expression (2 + 3) * 5 will be 25. The parentheses ensures that the operation within it is executed first. So first 2 and 3 will be added, and then their result (5) will be multiplied with 5.
  • You can define conditional expressions. Learn more

Defining conditional expressions

A conditional expression is one that returns one of two values subject to the result of a boolean expression. It's defined as follows:
if ( <condition>, <value if the condition is true>, <value if the condition is false> )

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

  • The condition here is if the quantity that's requested in an order is greater than 15 or not
  • If the ordered quantity is greater than 15, the expression is to return a discouted value
  • If the ordered quantity is 15 or less, the expression is to return the amount as such
This expression will be as follows:
if ( Ordered_Quantity > 15, Amount - 100, Amount )

You can nest conditional expressions — use one conditional expression inside another. Refer to this section to see a sample expression.

Sample expressions

Expressions including constants and Deluge system variables

Purpose of Formula fieldIts ExpressionExplanation
Display a string
"abcd"
String values must be enclosed within double quotation marks
Display a string that contains double quotation marks, say
"\"abcd\""
Backslash character (\) must precede the double quotation marks you want to display
Display a number
105
Numbers can be inserted as such
Display a decimal value
105.23
Decimal values can be inserted as such
Display a date value
'10-Aug-2018'
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
zoho.currentdate
or
today
zoho.currentdate and today are system variables that return the current date
Display tomorrow's date
tomorrow
or
today.addDay(1)
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
yesterday is a system variable that returns the date of the day before current day
Display a date-time value
'10-Aug-2018 14:30:00'
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
zoho.currenttime
or
now
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
10.5356.round(2)
or
100.2345.round(2)
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
"This is a string".length()
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.daysBetween(Delivery_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.

Expressions including fields

Purpose of Formula fieldIts ExpressionExplanation
Concatenate the data in two fields, say Product Name and Product Type fields in a Product form.
Product_Type + Product_Name
or
Product_Type + " - " + Product_Name
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.length()
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
<field_link_name>.round(1)
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.daysBetween(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.latitude + " , " + Address.longitude

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.

Expressions including lookup and subform fields

Purpose of Formula fieldIts ExpressionExplanation
Display the Email Alias of the Department in which the Employee works
Department.Email_Alias
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
Product.Price
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 * Quantity
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.count()
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.sum(Line_Total)
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.

Conditional expressions

Refer to this section to learn what a conditional expression is.

Purpose of Formula fieldConditionValue if condition holds trueValue if condition doesn't hold trueFinal expressionExplanation
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 * Quantity0
if(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"))
if(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 123, and 4 as its choices. The expression progressively checks from 1 to 4, and returns the corresponding word. 

    Access your files securely from anywhere

      Zoho CRM Training Programs

      Learn how to use the best tools for sales force automation and better customer engagement from Zoho's implementation specialists.

      Zoho CRM Training
        Redefine the way you work
        with Zoho Workplace

          Zoho DataPrep Personalized Demo

          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.

          Zoho CRM Training

            Create, share, and deliver

            beautiful slides from anywhere.

            Get Started Now


              Zoho Sign now offers specialized one-on-one training for both administrators and developers.

              BOOK A SESSION









                                            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.




                                                Manage your brands on social media

                                                  Zoho Desk Resources

                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                    Zoho Marketing Automation

                                                      Zoho Sheet Resources

                                                       

                                                          Zoho Forms Resources


                                                            Secure your business
                                                            communication with Zoho Mail


                                                            Mail on the move with
                                                            Zoho Mail mobile application

                                                              Stay on top of your schedule
                                                              at all times


                                                              Carry your calendar with you
                                                              Anytime, anywhere




                                                                    Zoho Sign Resources

                                                                      Sign, Paperless!

                                                                      Sign and send business documents on the go!

                                                                      Get Started Now




                                                                              Zoho TeamInbox Resources



                                                                                      Zoho DataPrep Resources



                                                                                        Zoho DataPrep Demo

                                                                                        Get a personalized demo or POC

                                                                                        REGISTER NOW


                                                                                          Design. Discuss. Deliver.

                                                                                          Create visually engaging stories with Zoho Show.

                                                                                          Get Started Now









                                                                                                              • Related Articles

                                                                                                              • Understand Formula Field

                                                                                                                The Formula field enables you to automatically generate a value for each record in your form, based on a predefined expression. Let's take an example. The Total Amount in an Order form is calculated based on the Unit Price and Quantity of the ordered ...
                                                                                                              • Set expression

                                                                                                                Set expression for formula field Refer to this page to learn about the Formula field's Expression Open the form builder. Select the required formula field. Navigate to the Field Properties pane on the right. Click the input box below Expression . The ...
                                                                                                              • Understand portal

                                                                                                                This help page is for users in Creator 6. If you are in the older version (Creator 5), click here. Know your Creator version. A Portal is a great way for an organization to allow its external users to log in to their dedicated internal system and ...
                                                                                                              • Understand microservices

                                                                                                                Microservices are small, independent services or components of app that can easily be deployed to work in tandem with your application. You can build a robust application for your business. However, as your business grows, you will find that the ...
                                                                                                              • Understand authentication

                                                                                                                This help page is for users in Creator 6. If you are in the older version (Creator 5), click here. Know your Creator version. Authentication is the process or action of verifying the identity of a user of the portal. Zoho Creator allows you to choose ...
                                                                                                                Wherever you are is as good as
                                                                                                                your workplace

                                                                                                                  Resources

                                                                                                                  Videos

                                                                                                                  Watch comprehensive videos on features and other important topics that will help you master Zoho CRM.



                                                                                                                  eBooks

                                                                                                                  Download free eBooks and access a range of topics to get deeper insight on successfully using Zoho CRM.



                                                                                                                  Webinars

                                                                                                                  Sign up for our webinars and learn the Zoho CRM basics, from customization to sales force automation and more.



                                                                                                                  CRM Tips

                                                                                                                  Make the most of Zoho CRM with these useful tips.



                                                                                                                    Zoho Show Resources