Deluge script that calculates the duration of Deals in their respective current Deal Stages.
Zoho Reports has a limitation when it comes to getting the Deal Stage Duration – the system only captures stage duration when a Deal has exited a stage, which means, you can only get the duration of a Deal in its past stages. To get the current Deal stage duration, some Deluge scripting would be necessary. The idea is to get the timestamp of a Deal when it entered its current stage, calculate the duration between the aforementioned timestamp vs today (whenever the function is run), and update the value in a custom field on the Deal record.
Before you can use this script with Zoho CRM, you must configure the following:
To get all Deal records, a Zoho CRM API call is used. Most companies have over 200 Deals in their CRM, but Zoho CRM API has a limit of 200. In order to overcome this, we would simulate a while
loop via pagination. This allows you to get every single Deal record in a list (read more about API pagination here.
zohoCrmModule = "Deals"; perPageLimit = 200; pageIterationList = {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25}; // This accounts for 5000 Deals (25 x 200). Increase the page number if needed. allRecords = List(); iterationComplete = false; for each page in pageIterationList { if(iterationComplete == false) { response = invokeurl [ url :"https://www.zohoapis.com/crm/v2/" + zohoCrmModule + "?page=" + page + "&per_page=" + perPageLimit type :GET connection:"zohocrm" // Change this to your Connection Name ]; records = response.get("data"); allRecords.addAll(records); if(records.size() < perPageLimit) { iterationComplete = true; } } } // Check for correctness info "Number of Records: " + allRecords.size(); { update2 = zoho.crm.updateRecord("Deals",id,{"Company_Overview":company_overview}); info update2; } }
For
loop is used here to iterate through every single Deal record in the list..get(0)
) – by default, the latest (current) stage is the first index in the list..get("Last_Modified_Time")
will get the time stamp of when the Deal enters its current stage.today
with the days360
funciton and voila, you get the duration (in days) of the current Deal Stage. for each a in allRecords { //Get the Deal Stages response2 = invokeurl [ url :"https://www.zohoapis.com/crm/v2/Deals/" + a.get("id") + "/Stage_History" type :GET connection:"zohocrm" ]; //Calculate the Duration of the Current Deal Stage duration = days360(response2.get("data").get(0).get("Last_Modified_Time"),today); //Update field here update = zoho.crm.updateRecord("Deals",a.get("id"),{"Current_Stage_Duration_Days":duration}); // Change this to your custom field API name if needed. info update; }
Now, you can use the “Current Stage Duration (Days)” custom field in Zoho Reports for analysis purposes. A good usage of this is for sales people to review dormant Deals.
Click here to copy these scripts. 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....