Quantitate Subform Table Values in Zoho CRM
- Jay
- Difficulty: Intermediate
- Estimated reading time: 5 mins
Eureka!
We’ve developed a formula in our Zoho laboratory that sums up values of subform tables in Zoho CRM and we’re glad to share it with you at the cost of… 5 minutes of your time.
We’ve developed a formula in our Zoho laboratory that sums up values of subform tables in Zoho CRM and we’re glad to share it with you at the cost of… 5 minutes of your time.
Cool Ideas to use the Code
Suppose you have a subform in Deals that stores information (product, quantity, subtotal) of every related sale. In order to find out how well each product is selling, you need get the sum of the quantity/subtotal of each product sold in the subform across every Deal. This script does just that! Here’s how.
1. Create a Product Map
When a subform is created, information of the form is stored in a hidden “module” with the subform API name where you can simply execute a “zoho.crm.getRecords“ function to access it.
- To get the subform API name, you can either execute a “zoho.crm.getRecordsbyID” function or run a “Get List of Modules” API call.
Once you have the API name, iterate through each row in the subform to get the key that’s the Product, and assign the default value of 0 for both Quantity and Subtotal in a map. At this point, you will have a map of all the products with 0 Quantity and Subtotal.
- The “zoho.crm.getRecords” function has a default limit of 200 records per page. To account for more than 200 records, pagination is required.
producttable = zoho.crm.getRecords("INSERT SUBFORM API NAME HERE");
productMap = Map();
for each p in producttable
{
mp = Map();
mp.put("Quantity",0);
mp.put("Subtotal",0);
productMap.put(p.get("Product_Name").get("name"),mp); //Replace the API name accordingly
}
2. Sum ’em up!
Here comes the fun part. Create another loop on the subform to get the sum of the quantity/subtotal for each product by adding them up at every iteration while remapping variable productMap.
This works because when the specified key is already present in the map-variable, the key’s associated value is replaced with the new given value.
Once the iteration is complete, variable productMap will contain a map of the total quantity subtotal of the each product in the subform.
for each p in producttable
{
mp = Map();
mp.put("Quantity",productMap.get(p.get("Product_Name").get("name")).get("Quantity").toLong() + p.get("Quantity"));
mp.put("Subtotal",productMap.get(p.get("Product_Name").get("name")).get("Subtotal").toLong() + p.get("Subtotal"));
productMap.put(p.get("Product_Name").get("name"),mp);
}
//Replace the following API name accordingly - "Quantity", "Subtotal", "Product_Name", "name"
DISCLAIMER!
If you have null values in any of the subform columns, you’ll need to add null checks to prevent the script from running into an error.
Click here to copy the script. For more Zoho-wizardry, check out our GitHub page.
Contact Us!
Book a free 30-minutes consultation with a Zoho expert or send us an email
