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.

See how it works

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: