Custom Function: Searching Events in the current month - with record count

Custom Function: Searching Events in the current month - with record count

I hate to be a bother to the fine folks here, but I'm stuck in another function.  GOAL:  write a custom function to count the number of Meetings each person in Contacts has for the current month, then push that count to a field in the contact record.  

Spent about 6 hours trying different versions of the function, none work. Original code below. I think I might have to use CoQL query for the function, but was hoping to avoid it. Never used APIs.

Attempts so far:  Tried to iterate in various forms & found out Deluge doesn't support iterative loops with increments. Found out CRM deluge doesn't support count() for a collection, it's only applicable to Creator according to help docs. And the integration task "zoho.crm.searchrecords" only supports equals and starts with as criteria, so I can't search "in" the current month.

Then I thought about using intersect(). But now currently thinking,  in natural language: "for each name in Contacts, select query search Meetings this month where Event_Title contains name" and return count of record, I assume using size(). 

So, maybe I need to use a "for each" loop on Contacts with a CoQL select query for meetings in the current month where the Event_Title field contains the contact's name. 

Really frustrating as a non-dev. I'm sure there's multiple ways to do it but I can't figure it out due to search criteria limitations. Makes me feel stupid, and it feels like there's information missing from the help documents.

Original Code (trying to iterate, mistakes and all):
  1. Startdate = zoho.currentdate.toStartOfMonth();
  2. Enddate = Startdate.addMonth(1);
  3. eventlist = zoho.crm.searchRecords("Events","Start_DateTime : in : this month");
  4. myList = eventlist.toList();

  5. for each  rec in clients
  6.       {
  7. name = rec.getJSON("Last_Name");
  8. ident = rec.getJSON("id");
  9. sessioncount = 0;
  10. for each  meeting in myList
  11. {
  12. title = meeting.getJSON("Event_Title"); 
  13. if(title.contains(name) == true && title.contains("training") == true)  //this line would NOT work if "eventlist" was still a collection variable, so I had to convert it to List variable
  14. {
  15. sessioncount +=1;
  16. }
  17. }
  18. mp = Map();
  19. mp.put("Scheduled Sessions",sessioncount);
  20. info name;
  21. info sessioncount;
  22. info mp;
  23. //update = zoho.crm.updateRecord("Contacts",ident,mp);
  24. //info update;
  25. }

Obviously problems there. Now trying to use the CoQL api, but I've never done APIs before. 
Relevant code below. You can see several versions of the searchRecords task based on different community/help docs, but none seem to work.

Code below just tries to return ALL the Meetings in the current month to get proof of concept. Returns Invalid Query. Sessioncount should be 116, but returns 4... I have no idea where that's from.
  1. //eventlist = zoho.crm.searchRecords("Events","(Start_DateTime:equals:"+Startdate.toString("yyyy-MM-dd")+")");
  2. eventlist = zoho.crm.searchRecords("Events",{"select_query":"SELECT Event_Title FROM Events where Start_DateTime in'"+Startdate+"'"});

  3. //eventlist = zoho.crm.getRecords("Events",1,400,query_map);
  4. //myList = eventlist.toList();


  5. queryMap = Map();
  6. queryMap.put("select_query", "select Event_Title, Start_DateTime from Events where Start_DateTime in '"+Startdate+"'");
  7. response = invokeurl
  8. [
  9. url :"https://www.zohoapis.com/crm/v3/coql"
  10. type :POST
  11. parameters: queryMap.toString()
  12. connection:"crmconnection_all"
  13. ];
  14. info response;
  15. sessioncount = eventlist.size();
  16. info sessioncount;

Does anybody have an approach they could share?  I've pulled my hair out!

    Access your files securely from anywhere


              Zoho Developer Community




                                      • Desk Community Learning Series


                                      • Digest


                                      • Functions


                                      • Meetups


                                      • Kbase


                                      • Resources


                                      • Glossary


                                      • Desk Marketplace


                                      • MVP Corner


                                      • Word of the Day


                                      • Ask the Experts



                                          Zoho Marketing Automation
                                                  • Sticky Posts

                                                  • User Tips: Auto-Create Opportunity/Deal upon Quote Save (PART 1)

                                                    Problem: We use quotes which convert to sales orders but Users / Sales Reps do not create opportunities / deals and go straight to creating a quote. This leads to poor reporting. Implementing this solution improves reporting and makes it easier for users.
                                                  • Zoho CRM Functions 53: Automatically name your Deals during lead conversion.

                                                    Welcome back everyone! Last week's function was about automatically updating the recent Event date in the Accounts module. This week, it's going to be about automatically giving a custom Deal name whenever a lead is converted. Business scenario Deals are the most important records in CRM. After successful prospecting, the sales cycle is followed by deal creation, follow-up, and its subsequent closure. Being a critical function of your sales cycle, it's good to follow certain best practices. One such
                                                  • Custom Function : Automatically send the Quote to the related contact

                                                    Scenario: Automatically send the Quote to the related contact.  We create Quotes for customers regularly and when we want to send the quote to the customer, we have to send it manually. We can automate this, using Custom Functions. Based on a criteria, you can trigger a workflow rule and the custom function associated to the rule and automatically send the quote to customer through an email. Please note that the quote will be sent as an inline email content and not as a PDF attachment. Please follow
                                                  • Function #50: Schedule Calls to records

                                                    Welcome back everyone! Last week's function was about changing ownership of multiple records concurrently. This week, it's going to be about scheduling calls for records in various modules. Business scenario Calls are an integral part of most sales routines.. Sales, Management, Support, all the branches of the business structure would work in cohesion only through calls. You could say they are akin to engine oil, which is required by the engine to make all of it's components function perfectly. CRM
                                                  • Function #37: Create a Purchase Order from a Quote

                                                    Welcome back everyone! Last week, we learnt how to calculate the total number of activities for a lead and further take note of the activity count for particular dates. For instance, from the period of Demo to Negotiation. This week, let's look at a function that lets you create a Purchase Order instantly from a Quote. Business scenario: In any form of business, one of the most important things to do is to document the transactions. Naturally, negotiation, signing an agreement, placing an order,


                                                  Manage your brands on social media



                                                        Zoho TeamInbox Resources

                                                          Zoho DataPrep Resources



                                                            Zoho CRM Plus Resources

                                                              Zoho Books Resources


                                                                Zoho Subscriptions Resources

                                                                  Zoho Projects Resources


                                                                    Zoho Sprints Resources


                                                                      Qntrl Resources


                                                                        Zoho Creator Resources



                                                                            Zoho CRM Resources

                                                                            • CRM Community Learning Series

                                                                              CRM Community Learning Series


                                                                            • Kaizen

                                                                              Kaizen

                                                                            • Functions

                                                                              Functions

                                                                            • Meetups

                                                                              Meetups

                                                                            • Kbase

                                                                              Kbase

                                                                            • Resources

                                                                              Resources

                                                                            • Digest

                                                                              Digest

                                                                            • CRM Marketplace

                                                                              CRM Marketplace

                                                                            • MVP Corner

                                                                              MVP Corner





                                                                                Design. Discuss. Deliver.

                                                                                Create visually engaging stories with Zoho Show.

                                                                                Get Started Now


                                                                                  Zoho Show Resources


                                                                                    Zoho Writer Writer

                                                                                    Get Started. Write Away!

                                                                                    Writer is a powerful online word processor, designed for collaborative work.

                                                                                      Zoho CRM コンテンツ








                                                                                        Nederlandse Hulpbronnen


                                                                                            ご検討中の方




                                                                                                  • Recent Topics

                                                                                                  • Track Items - Sprints History

                                                                                                    I want to track when items move from "Active Sprint" to "Upcoming Sprint" or "Backlog" in Zoho Sprints. I need to display these items in reports or graphs in Zoho Analytics or Zoho Sprints. How can I do this? We have Deal History in Zoho CRM, but I couldn't
                                                                                                  • Zoho AI Translate – No Support for Hebrew?

                                                                                                    I want to use Zoho AI Translate, but I see that it does not support Hebrew. Since Zoho supports multiple languages, I was hoping this function would also work with Hebrew. Is there any plan to add Hebrew support in the near future? Or is there an alternative
                                                                                                  • Deletion of CRM rows giving error

                                                                                                    Hi, I am exporting data from a module into excel workbook. THis works fine... until...I export the same data 10 minutes later and I get duplicates in my sheet. There is code to "delete" the previous sheet but it is giving errors. The DELETION of records
                                                                                                  • Rename Record Summary PDF in SendMail task

                                                                                                    So I've been tasked with renaming a record summary PDF to be sent as part of a sendmail task. Normally I would offer the manual solution, a user exports the PDF and uploads it to a file upload field, however this is not acceptable to the client in this
                                                                                                  • Creating Layout Rule With Formula Field

                                                                                                    By The Grace Of G-D. Hi, I see that i cannot use Layout Rules to show/hide Formula Fields. Is that something you plan on adding sometime soon?
                                                                                                  • Weekly Tips: Get Work Done Faster with Zia AI

                                                                                                    Every day brings a fresh batch of messages — meeting invites to accept, client feedback to review, colleagues asking for updates, and the constant ping of new notifications. It's exhausting trying to clear your inbox while wondering how you will catch
                                                                                                  • Add clients without needing a client company

                                                                                                    I, like many people, deal directly with individual clients rather than companies. It's really obnoxious to have to add a client company and treat my client, who is an individual person, as a company. Zoho - can you please build in a way to bypass the need for the client to be part of a company? This is a problem in cliq as well - I want to invite my clients to channels in Cliq but needing to create a company is confusing for them and makes them feel as if they don't belong on the app. But that's
                                                                                                  • Setting default From address when replying to request

                                                                                                    At the moment, if I want to reply to a request, the From field has three options, company@zohosupport.com, support@company.zohosupport.com, and support@company.com.  The first two are really internal address that should never be seen by the customer and
                                                                                                  • Can't create task programmatically using deluge

                                                                                                    I have a Date/Time field like this when it is edited, I want to create a task using deluge function. I use the code below to create a task. but unfortunately, the task is never created even I run it using 'execute' button on function editor. what went
                                                                                                  • Ability to add VAT to Retainer Invoices

                                                                                                    Hello, I've had a telephone conversation a month ago with Dinesh on this topic and my request to allow for the addition of VAT on Retainer Invoices.  It's currently not possible to add VAT to Retainer Invoices and it was mutually agreed that there is absolutely no reason why there shouldn't be, especially as TAX LAW makes VAT mandatory on each invoice in Europe!   So basically, what i'm saying is that if you don't allow us to add VAT to Retainer Invoices, than the whole Retainer Invoices becomes
                                                                                                  • Python Code: Calling "Deals" API results in 404 Error.

                                                                                                    Good evening, I wrote a small python code to scrape the data from a selected record and then copy the information I need into another website. We have renamed the "Deals" module to "Opportunities", however the API name remains as "Deals" which is fine.
                                                                                                  • setVisibility() not working for Subform Fields with Client Script

                                                                                                    I am currently working on a Zoho CRM Client Script involving a subform and encountered a limitation that hinders my ability to fully control the visibility of specific fields within subform rows. The setVisibility() method, which works perfectly fine
                                                                                                  • UpdateRecord for contacts doesnt work from rule from another custom module

                                                                                                    I have a function that is triggered by an automatic rule when a record is created or edited in a custom module. The function searches for a record in the Contacts module and saves it in a lookup field of the newly created or updated record. It also updates
                                                                                                  • Office 365 and Zoho CRM

                                                                                                    Does anyone have this synching properly. I configured it and the Zoho Calendar shows up in 365 but nothing comes from 365 into zoho crm. I have enabled the two way. No errors are being generated. If anyone has some insights I would really appreciate
                                                                                                  • How to export Subform Data from a record?

                                                                                                    How can we export the subform data from a record? Currently we can import data, but I found no way to export data from one record I need a simple button to export the current subform data to an excel file, why can we import subform data but cannot export
                                                                                                  • Trident: please make right widget column re-sizable

                                                                                                    I'm really enjoying using Trident and think Zoho is doing a great job developing it and adding features without cluttering the interface. However, the one thing that keeps irritating me in daily use is that the right side widget column is too narrow to
                                                                                                  • Does Client Script work on Zoho CRM Portal?

                                                                                                    Hi ! I create a new module to use at customer portal. But Client Script looks not work. Please help me clarify, thank you!
                                                                                                  • Analytics - Opened versus Clicked?

                                                                                                    Good day, we have just sent out an email campaign vis the mass email option to our contacts lists and i have noticed that more recipients clicked on the email than opened the email. Can that be and can you explain the difference between the 2 statuses
                                                                                                  • Unattended Sessions - Groups and Access

                                                                                                    I would like to see Groups to organize Unattended Computers (what is currently labeled "My Computers") and a way to restrict access to individual users to specific groups.
                                                                                                  • Need help in search with formula culums in reports

                                                                                                    Hello, I do not know where to start with this one : I have a customer report with all that was bought and paid by my customers. It can be many records for one specific customer. Ex. Customer A has 1 record for a purchase of 1000 $, and 2 records of payments
                                                                                                  • 【参加無料】3/19 東京ユーザ交流会 参加登録 受付開始!(新橋開催)

                                                                                                    ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 3月19日(水)に東京、新橋でユーザー交流会を開催します! 今年最初のユーザー交流会 東京・新橋では、Zoho Champion の松井さん、鈴田さんが登壇します。 Zoho CRM活用事例のセッション、参加者同士で自社の活用・運用について情報交換ができるグループワークや、CRM / Marketing Automationに関するパネルディスカッションも予定しています。 ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
                                                                                                  • Genie baked into Truesync

                                                                                                    Shouldn't the functionality of genie be baked into truesync? This is how google drive's desktop app works. It's minor but annoying to have 2 things loaded when it seems it should all just be one app. Just an idea.
                                                                                                  • File attachments adding date and time to name when uploading?

                                                                                                    I have set up a function that uses the mail merger api to get a lead and fill in the details, it is basic but was just using to test will fill in all the details later, It works perfectly fine the only issue i have is that when it uploads the file into
                                                                                                  • Cannot Access Admin Console

                                                                                                    Hello, After I upgrade from free mail to Lite Mail I can't access Admin Console. Always stuck like that. Please help, thank you. Regards, Arman
                                                                                                  • Live webinar: Mastering financial presentations with Zoho Show

                                                                                                    Hey there finance professionals! We know many of you are currently knee-deep in report creation mode to wrap up the fiscal year for your organization. Creating a presentation to communicate essential financial data isn’t simple, with all the calculations,
                                                                                                  • Rejected Accounts still listed for Deal

                                                                                                    Hello guys I have a approval process thats validate the Accounts in CRM The issue is even when a account is rejected you can open a deal with it It's not supposed to appear in the lookup field or the approval process become pointless Can you guys help
                                                                                                  • Optimizing Zoho CRM & Xero Workflow – Advice Neede

                                                                                                    Hi everyone, We’ve been using Zoho for all CRM functions and Xero for all accounting, but the workflow between the two has become difficult to manage. The business does not want to move from Xero, as it's the accountants’ preferred system, but we need
                                                                                                  • Open word documents directly within zoho writer without converting

                                                                                                    There was an article in the help page that said that there would be a feature where zoho writer can directly open MS Word files without the need to convert those files to zoho format. It was said that it would be available by Q1 2023. Here is the article
                                                                                                  • Not receiving email at Outlook

                                                                                                    Hi, I've set up the account and can do everything except receiving email at Outlook. Please find print screen below parameters configured and advise what could went wrong. I can send email from both Webmail and Outlook. I could, however, receive email
                                                                                                  • To unblock , ensure atleast 5% free space of your total mailbox size exists after email deletion.

                                                                                                    Bom dia, Já deletei todos os e-mails , mas ainda não consigo desbloquear o e-mail comercial@hidrotecnicauberlandia.com.br. Já faz dias que deletei, mas ainda não desbloqueia quando sigo os passos de UNLOCKED ME. Por favor me ajudem.
                                                                                                  • Kiosk Page Refresh

                                                                                                    We have a Kiosk running from a button in contacts to update values and also add related lists, which works great, but when the kiosk is finished the page does not refresh to show the changes. Is there a way to force the contact to refresh/update when
                                                                                                  • Zoho does not send invitation email if meeting is created in calendar

                                                                                                    Problem: I create a meeting in calendar and add attendees. After "Save" button it asks me if I want to send email to all ettendees, I click Send. But none emails are sent. I did try that over outlook (connected thru active sync) or in zoho web mail. Nothing
                                                                                                  • Change Background in PDF Template

                                                                                                    Background PDF Hi, I want to ask a question. I want to create a background template with my own image. Above is an example that I made, why does the result not fit the A4 format? Like cut off. I used an A4 Portrait image, is there a size error? can you
                                                                                                  • Spotlight #7 - Automatic Transitions in Blueprint

                                                                                                    Previously, records could not be moved from one state to another without user intervention. With automatic transitions, move records from one state to another automatically, when it elapses its pre-defined time. This Spotlight discusses how automatic
                                                                                                  • Accepted meeting invites not showing on calendar

                                                                                                    I can receive a calendar invite to a meeting, I confirm I can attend and hit accept. I then check my calendar and it doesn't show. 30 seconds later, it does show up but it is delayed leaving the feeling something didn't work correctly. Can this be improved,
                                                                                                  • Cross module filtering is now supported in CRM

                                                                                                    Editions: All DCs: All Release plan: This enhancement is being released in phases. It is now available in AU, JP, and CN DCs. Help resource: Advanced filters The Cross-module filtering enhancement is now available to all CRM accounts in the following
                                                                                                  • No fue posible enviar el mensaje;Motivo:553 Relaying disallowed. Invalid Domain - admin@laboratoriosantarosa.org

                                                                                                    Hola Renovamos después del tiempo el dominio, y luego de eso se cayó el servicio de correo. Seguimos las indicaciones que se indican en este articulo, sin embargo, hasta el momento solo podemos recibir correos pero no enviar. Hemos actualizado los registros
                                                                                                  • Zoho Error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details

                                                                                                    Hello There, l tried to verify my domain (florindagoreti.com.br) and its shows this error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details. Screenshot Given Below -  please check what went wrong. Thanks
                                                                                                  • Error AS101 when adding new email alias

                                                                                                    Hi, I am trying to add apple@(mydomain).com The error AS101 is shown while I try to add the alias.
                                                                                                  • My IMAP mail suddenly stopped working

                                                                                                    On my iPhone and iPad, IMAP stopped working for my Zoho account with the error "User name or password incorrect" and "Invalid credentials failure" however I was able to access via web with the same credentials. Also stopped working on Apple Mail client.
                                                                                                  • Next Page