Auto-calculating values on a subform's field

Auto-calculating values on a subform's field

Requirement

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
Products
Products
Single Line
Product Name
Product_Name
Currency
Rate
Rate
Order s
Orders
 
Subform
  • Lookup (Product)
  • Number
  • Currency
  • Currency
Order Details
  • Products
  • Quantity
  • Rate
  • Sub-Total
Order_Details
  • Products
  • Quantity
  • Rate
  • Sub_Total
Currency
Total Amount
Total_Amount
Number
Number of Items
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

                                                                                                            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