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.
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.
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.
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.
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.
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.
Learn to create a customized inventory report with Zoho Analytics. This tutorial contains a link to our GitHub page for SQL code that will help with your table creations....
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code....