Auto-calculating values on a subform's field

Auto-calculating values on a subform's field


Perform statistical operations on the numerical fields in subforms for analysis.

Use Case 

In an order management application, there are two forms, Order and Product. The Product form lists all the products in the inventory and the Order form is used to place a new order. Th is Order form has a subform that will hold a list of products purchased by the customer. Upon adding a product, its corresponding price should be displayed for every row. Before the Order form is submitted, the total amount should be calculated. In addition, the average price of the products purchased should be shown.

Steps to follow  

1. Create the forms with the following details:
Form Name
Form Link Name
Field Type
Field Name
Field Link Name
Single Line
Product Name
Order s
  • Lookup (Product)
  • Number
  • Currency
  • Currency
Order Details
  • Products
  • Quantity
  • Rate
  • Sub-Total
  • Products
  • Quantity
  • Rate
  • Sub_Total
Total Amount
Number of Items

2. Create a workflow on the Orders form to disable the Average Amount, Total Amount and Number of Items in the main form, and the Sub-Total and Rate fields in the subform fields during the load of the form. We are disabling this because these are auto-calculated and the values cannot be manually entered.

3. Click Add New Action and add the below script to disable the fields:
  1. disable Total_Amount;
  2. disable Number_of_Items;
  3. disable Order_Details.Rate;
  4. disable Order_Details.Sub_Total;

4. Create a workflow on the Orders form, to be executed on the user input of the subform's Products field.

5. Click Add New Action and a dd the below code in the ensuing deluge editor.
  1. // Fetch the Rate of the selected Product 
  2. rate=Products[ID==row.Products].Rate; 
  3. // Assign the fetched Rate 
  4. row.Rate=rate; 
  5. if( row.Quantity != null)
  6. {
  7. row.Sub_Total = row.Rate * row.Quantity;
  8. }

6. Create another workflow on the Orders form, to change the subtotal for the given product based on the quantity purchased.

7. Click Add New action and click on Deluge Script. Add the below code in the ensuing deluge editor.
  1. //Product Total and Sub Total calculations here
  2. if(row.Quantity != null && row.Rate != null)
  3. {
  4.  row.Sub_Total=row.Quantity * row.Rate; //Calculate the total based on the quantity
  5. }
  6. else
  7. {
  8.  row.Sub_Total=0;
  9. }
  10. //Count the number of items ordered
  11. input.Number_of_Items = Order_Details.count();
  12. //Calculate sum of the prices of all the items
  13. input.Total_Amount = Order_Details.sum(Sub_Total);
Every time a product is added to the subform, the workflow triggers to find the corresponding product's rate. Every time a quantity is added, workflow to find the sub- total, total/average amounts and count the number of products are triggered.

See how it works  

Points to note  

  • The average, minimum, maximum, and median values can be found in the same way. The average amount calculated using deluge is shown in the video for reference.
  • It is possible to display the overall statistics of the subform values on the report. We can have corresponding numerical fields in the main form to find the statistical values of the subform numerical fields like our example here. In our above example, go to the All Orders report. Under Quick View click the first layout. Select the Total Amount field and toggle the Sum, Average, Min, and Max fields, as shown below: 
The report will appear as follows:

    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

                                                                                                        • Auto populate a field based on the selection from a lookup dropdown

                                                                                                          Requirement   When a value is chosen from a lookup dropdown, related values are populated in the other fields of the same form. Use Case   An order management app contains a form to store order details. When the admin chooses the customer name from ...
                                                                                                        • Display subform's report in parent form's report

                                                                                                          Requirement   When the user views a particular record in a report that has subform, the values in the corresponding records are also shown. Use Case   An order management application contains two forms: Customers and Orders. The Customer s form is ...
                                                                                                        • Add subform rows with dynamic data from another subform

                                                                                                          Requirement             Automatically add rows to a subform and populate it with dynamic data fetched from another subform. Use Case   A form named Orders Database contains customer details and their frequent orders in a subform. We have another ...
                                                                                                        • Understand Lookup Field

                                                                                                          Understand Lookup Field The lookup field enables you to create a relationship between forms. Using this relationship you will be able to lookup data from another form in the same application or from a form in a different application in your Zoho ...
                                                                                                        • Statistical values on a report based on numerical fields

                                                                                                          Requirement Display the common statistical results on the reports for the numerical fields in a form, to help in identifying trends and decision-making process. Use Case In any sales-based business, crunching numbers is a big task. An order ...
                                                                                                          Wherever you are is as good as
                                                                                                          your workplace



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


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


                                                                                                            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