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.
Click here to copy the script. For more Zoho-wizardry, check out our GitHub page.
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.
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.
How nice would it be if you could, at the press of a button, send clients an email w/a Zoho Books invoice? Replete with “Pay Now” buttons that link to PayPal/Stripe/other payment gateways?
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.
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.
Convert fields, related activities, attachments, notes and more from one record to another across modules via custom function.
Responses