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
|
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
|
Order Details
|
Order_Details
|
|
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:
- disable Total_Amount;
- disable Number_of_Items;
- disable Order_Details.Rate;
- 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.
- // Fetch the Rate of the selected Product
- rate=Products[ID==row.Products].Rate;
- // Assign the fetched Rate
- row.Rate=rate;
- if( row.Quantity != null)
- {
- row.Sub_Total = row.Rate * row.Quantity;
- }
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.
- //Product Total and Sub Total calculations here
- if(row.Quantity != null && row.Rate != null)
- {
- row.Sub_Total=row.Quantity * row.Rate; //Calculate the total based on the quantity
- }
- else
- {
- row.Sub_Total=0;
- }
- //Count the number of items ordered
- input.Number_of_Items = Order_Details.count();
- //Calculate sum of the prices of all the items
- 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:
Related Links
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 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 Workerly Home
- Forums
- Connect With Us:
- Zoho Recruit Home
- Forums
- Connect With Us:
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.
New to Zoho Survey?
Zoho Sheet Resources
Zoho Forms Resources
New to Zoho Sign?
Zoho Sign Resources
Sign, Paperless!
New to Zoho TeamInbox?
Zoho TeamInbox Resources
New to Zoho ZeptoMail?
Zoho DataPrep Resources
Design. Discuss. Deliver.
New to Zoho Workerly?
New to Zoho Recruit?
New to Zoho CRM?
New to Zoho Projects?
New to Zoho Sprints?
New to Zoho Assist?
New to Bigin?
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 ...
New to Zoho LandingPage?
Zoho LandingPage Resources