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


            Get started with Zoho Sign

            in a few quick steps!

            Download Help Guide





                        Still can't find what you're looking for?

                        Write to us:  support@zohoforms.com


                              




                            

                          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 SalesIQ Resources



                                                    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 ...
                                                                                      • Create Serial Number for subform rows

                                                                                        Requirement Create serial numbers or auto-numbers for subform rows when a new row is added. Use Case In an employee management system, an employee can add up to five dependents. It will be easy for the employees if the dependents added in the subform ...
                                                                                      • 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