Quantitate Subform Table Values in Zoho CRM

Difficulty: Intermediate
Estimated reading time: 5 mins
Estimated implementation time: 30 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.

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. 

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.

Want to learn even more? Sign up for one of our Zoho courses!

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.

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.

More Free Resources

Deluge Tips – Null Check

When scripting, ask yourself this question – will this value ever be null? If it’s a yes, that’s a place for a null check! Here are 3 tips and best practices to help you kickstart the habit.

Read More »

Responses

Are we through here? Ok, go check out our other guides!