Kaizen #81 - COQL API - Part II
Hello everyone!
Welcome to another week of Kaizen!
In continuation to last week's post on COQL API, we will discuss the rest of the field types and their supported operators with detailed examples in this post.
Supported field types and Operators
Please refer to the COQL API - Part I Kaizen post for the first three field types and their supported operators.
4) Boolean
The supported operator for Boolean field is =. You should provide the value as either true or false.
Sample Query
{ "select_query" : "select Product_Name, Product_Code from Products where Product_Active = 'false' limit 2" } |
This sample query selects the Product_Name and Product_Code fields from the Products module where Product_Active is false, and limits the result to only 2 records.
Sample Response:
{ "data": [ { "Product_Code": "SR-001", "Product_Name": "Sigma", "id": "4876876000001036109" }, { "Product_Code": "SR-003", "Product_Name": "Alpha", "id": "4876876000001039006" } ], "info": { "count": 2, "more_records": false } } |
what_Id support in COQL
=, !=, in, not in, is null, is not null are the supported operators. what_Id support is extended only to Tasks, Calls and Events. The what_Id field could be a lead or a contact.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
This sample query queries for the last name of leads associated with tasks where the lead ID is either '4876876000000900134' or '4876678000000900987', and the lead's industry is not 'ERP'.
Sample Response:
{ "data": [ { "What_Id->Leads.Last_Name": "Perry", "id": "4876876000001103044" } ], "info": { "count": 1, "more_records": false } } |
Roles and Profiles support in COQL
COQL supports roles and profiles lookup fields, allowing users to retrieve the owner's profile details such as ID, name etc, and the owner's role details using COQL queries. The supported comparators are =, !=, in, not in, is null, is not null.
Sample Query
{ "select_query": "select Owner.role.id,Owner.role.name,Owner.role.reporting_to, Owner.role.share_data_with_peers, Owner.role.description, Owner.profile.id from Leads where Last_Name is not null LIMIT 1" } |
This query fetches the Profile details and Role details of the owner of one record from the Leads module.
Sample Response:
{ "data": [ { "Owner.role.reporting_to": null, "Owner.profile.id": "4876876000000026011", "Owner.role.name": "CEO", "Owner.role.description": "crm.security.role.admin.desc", "Owner.role.share_data_with_peers": true, "Owner.role.id": "4876876000000026005", "id": "4876876000000333403" } ], "info": { "count": 1, "more_records": true } } |
Alias support
Supported operators : =, !=, in, not in, is null, is not null
Alias support in COQL allows users to simplify complex SELECT field names by giving them custom names or aliases. Using aliases in the SELECT column and ORDER BY class can make column names easier to understand and remember. For instance, What_Id->Leads.Last_Name from our previous example can be given an alias in the SELECT query as follows.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' AS 'Lead Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
Sample Response:
{ "data": [ { "id": "4876876000001103044", "Lead Name": "Perry" } ], "info": { "count": 1, "more_records": false } } |
If you compare this response with the sample response in the what_Id section above, you can see that the key 'What_Id->Leads.Last_Name' is replaced with the Alias 'Lead Name' in here.
Aggregate Function Support in COQL
Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions include SUM, MIN, MAX, AVG, and COUNT.
1. SUM
Use this function to sum up the values of an aggregate field in a module.
Sample Query 1
{ "select_query":"select SUM(Grand_Total) from Quotes WHERE Quote_Stage = 'Draft' " } |
This Query uses the SUM function to calculate the total value of the Grand_Total field for all quotes in the Draft stage in the Quotes module.
Sample Response:
{ "data": [ { "SUM(Grand_Total)": 1073660 } ], "info": { "count": 1, "more_records": false } } |
Sample Query 2
{ "select_query": "select SUM(Grand_Total), Account_Name.Account_Name AS 'Account Name', Billing_Country from Invoices where ((Status != 'Paid') and ((Billing_Country ='USA') OR (Billing_Country = 'Canada'))) GROUP BY Account_Name.Account_Name, Billing_Country ORDER BY 'Account Name' ASC" } |
This query retrieves the total SUM of the Grand_Total field, along with the Account_Name and Billing_Country from the Invoices module for those records whose Status field is not equal to 'Paid' and the Billing_Country field is either 'USA' or 'Canada'. The results are grouped by the Account_Name and Billing_Country fields and ordered in ascending order based on the 'Account Name' field.
Note that the 'Account Name' field is specified using the AS keyword to provide an alias for the Account_Name.Account_Name field. We have used the alias for the ORDER BY clause.
Sample Response:
{ "data": [ { "Account Name": "SR Systems", "Billing_Country": "USA", "SUM(Grand_Total)": 2430000 }, { "Account Name": "SR Systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 60000 }, { "Account Name": "Zylker systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 378000 } ], "info": { "count": 3, "more_records": false } } |
The response JSON contains the SUM of the Grand_Total fields, Account Name and Billing_Country for each unique combination of Account Name and Billing Country fields that satisfies the filter criteria specified in the query.
2. MAX
Use this aggregate function to find the largest value of a field in a module.
Sample Query
{ "select_query": "select MAX(Amount) AS 'Maximum Deal Amount', Account_Name.Account_Name from Deals where ((Stage = 'Closed Won') AND (Type = 'Existing Business')) Group by Account_Name.Account_Name" } |
This Query selects the maximum Deal Amount for each account from the Deals module where the stage is Closed Won and the type is Existing Business.
Sample Response:
{ "data": [ { "Maximum Deal Amount": 6750000, "Account_Name.Account_Name": "Benton" }, { "Maximum Deal Amount": null, "Account_Name.Account_Name": "King" }, { "Maximum Deal Amount": 945000, "Account_Name.Account_Name": "Morlong Associates" } ], "info": { "count": 3, "more_records": false } } |
3. MIN
Use this aggregate function to find the smallest value of a field in a module. For example, you can use this function to find the Lowest Annual Revenue in the Leads module.
Sample Query: 1:
{ "select_query": "select MIN(Annual_Revenue) from Leads where Annual_Revenue is not null" } |
Sample Response:
{ "data": [ { "MIN(Annual_Revenue)": 348.3 } ], "info": { "count": 1, "more_records": false } } |
4. AVG
Use this aggregate function to find the average value of a field in a module.
Sample Query
{ "select_query": "SELECT AVG(Amount) AS 'Average Deal Amount' FROM Deals WHERE Type = 'New Business'" } |
This query calculates the average deal amount for all new business deals in the "Deals" module.
Sample Response:
{ "data": [ { "Average Deal Amount": 877366.8359999999 } ], "info": { "count": 1, "more_records": false } } |
5. COUNT
Use this aggregate function to count the number of records in a module satisfying a certain criteria. You can use this aggregate function only for number, lookup, and picklist fields.
Sample Query:
{ "select_query": "SELECT COUNT(Product_Category) FROM Products WHERE Product_Active = 'false'" } |
This sample query queries for the total number of inactive products in the Product module. Please be aware that if there are any products with no value for the product category field, they will not be included in the count.
Important points to remember
- COQL keywords are not case-sensitive.
- To prevent ambiguity, the criteria listed in the WHERE clause must be properly enclosed in brackets. If WHERE has more than two criteria such as A, B, and C, use them as either of the following:
- (A and (B and C))
- ((A and B) and C)
- A and (B and C)
- (A and B) and C.
- COQL supports only SELECT queries with WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT().
- The default sort order is ascending based on record ID if ORDER BY is not specified.
- Use the following format for :
- Date : YYYY-MM-DD
- DateTime : YYYY-MM-DDTHH:mm:ss+/-HH:mm
- The default value for LIMIT is 200 and OFFSET is 0.
- A maximum of 10,000 records can be fetched using the API.
- Multi-line fields cannot be used in criteria.
- COQL doesn't support consent_lookup, subform, profileimage, multiselectlookup, fileupload datatypes.
- A maximum of 25 criteria can be included in the WHERE clause.
- A maximum of 50 field API names can be used in the SELECT column of the query.
Data Type | Supported Operators |
text | =, !=, like, not like, in, not in, is null, is not null |
picklist | =, !=, like, not like, in, not in, is null, is not null |
ownerlookup | =, !=, in, not in, is null, is not null |
lookup | =, !=, in, not in, is null, is not null |
date | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
datetime | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
currency | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
boolean | = |
email | =, !=, like, not like, in, not in, is null, is not null |
phone | =, !=, like, not like, in, not in, is null, is not null |
website | =, !=, like, not like, in, not in, is null, is not null |
double | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
bigint | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
integer | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
formula | If the return type is: Decimal/Currency/Date/Datetime : =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null String : =, !=, like, not like, in, not in, is null, is not null Boolean : = |
autonumber | =, !=, like, not like, in, not in, is null, is not null |
We hope you found this post useful. We will meet you next week with another interesting topic.
If you have any questions or feedback, please let us know in the comments below, or write to us at support@zohocrm.com. We would love to hear from you!
Additional Reading:
Access your files securely from anywhere
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
Arose Unity
Haiku Technical Support
Subramanian K
Sticky Posts
Kaizen #198: Using Client Script for Custom Validation in Blueprint
Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.Kaizen #226: Using ZRC in Client Script
Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In thisKaizen #222 - Client Script Support for Notes Related List
Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore howKaizen #217 - Actions APIs : Tasks
Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.Kaizen #216 - Actions APIs : Email Notifications
Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are
New to Zoho TeamInbox?
Zoho TeamInbox 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
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Release Notes | February 2026
We have rolled out another set of enhancements in Vertical Studio during February 2026, bringing improvements to Canvas customization, reporting capabilities, and data access controls. Here is a summary of what was released during February 2026: CanvasAgentic Engineering with Zoho: The Next Evolution of Intelligent Systems
The concept of Agentic Engineering is reshaping how modern systems are designed. It introduces a new layer to the way we think about artificial intelligence and system architecture. For years, most software systems have operated in a reactive way — respondingEngenharia Agêntica e as soluções da Zoho
O conceito de engenharia agêntica impacta diretamente como os sistemas são projetos. Este tema inseri mais uma camada a nossa idéia de inteligência artificial. Antes o desenvolvimento de sistemas operavam de forma reativa e dependente de comandos diretos,Beyond Task Lists #1 Effective project and task customization with CodeX
Dear users, Beyond Task Lists is a series of articles aimed at showcasing the various customization capabilities of Zoho Projects. We'll discuss real life project management scenarios, use cases, and requirements that needs combining multiple features.Is Zoho Sites still actively being developed?
Hello, Is Zoho Sites still actively being developed as part of the Zoho ecosystem? I noticed that the What's New page (https://www.zoho.com/sites/whats-new.html) does not show any updates since Q1 2025. We were considering migrating our website from SquarespaceSet expiration date on document and send reminder
We have many company documents( for example business registration), work VISA documents. It will be nice if we can set a expiry date and set reminders ( for example 90 days, 60 days, 30 days etc.,) Does Zoho workdrive provide that option?Depositing funds to account
Hello, I have been using Quickbooks for many years but am considering moving to Zoho Books so I am currently running through various workflows and am working on the Invoicing aspect. In QB, the process is to create an invoice, receive payment and thenDebit opening balances of vendors
Dear colleagues: I am looking at the trial balance as on 31st March 2024, and punching opening balances (1st April 2024) in Zoho Books. Vendors have credit balances, by its nature, but some of our vendors have debit balances as well (e.g., we have paidZoho Books emails suddenly going to Spam since 11 Nov 2025 (Gmail + now Outlook) — anyone else?
Hi everyone, We migrated to Zoho Books in July 2025 and everything worked fine until 11 Nov 2025. Since then, Zoho Books system emails are landing in customers’ Spam (first Gmail, and now we’re seeing Outlook/Office 365 also starting to spam them). ImpactedTasks in hours rather than days.
Hi there, I was wondering whether or not its possible to create a workflow task that has a timer based on hours rather than days? I'm looking to create a workflow task which, when a request field is changed, creates a task for the support desk to call the customer within an hour. However, the workflow task due dates only seem to work in days. Is this possible? ThanksClient Portal ZOHO ONE
Dear Zoho one is fantastic option for companies but it seems to me that it is still an aggregation of aps let me explain I have zoho books with client portal so client access their invoice then I have zoho project with client portal so they can access their project but not their invoice without another URL another LOGIN Are you planning in creating a beautiful UI portal for client so we can control access to client in one location to multiple aps at least unify project and invoice aps that wouldThe Social Wall: February 2026
Hello everyone, This month, we’re bringing you a mix of exciting toolkit enhancements and a few improvements across the web and iOS app, all designed to make your social media management simpler and smoother. File converter Images come in different formats,Zoho Mail and SalesInbox doesn't link to CRM record using Reply-To
Hi, I've just set up SalesInbox, with the intention of using it for sales enquiries (instead of Desk, which I have been using until now). I've noticed that, unlike Desk, SalesInbox only uses the 'From' email address to attempt to link to a CRM contactService line items
Hello Latha, Could you please let me know the maximum number of service line items that can be added to a single work order? Thanks, Chethiya.Subforms and automation
If a user updates a field how do we create an automation etc. We have a field for returned parts and i want to get an email when that field is ticked. How please as Zoho tells me no automation on subforms. The Reason- Why having waited for ever for FSMPoor Search Results on Zoho CRM
The search on Zoho CRM is quite poor. Salesforce has now published a new search, when will get this on Zoho? https://help.salesforce.com/s/articleView?id=data.c360_a_hybridsearch_index.htm&type=5How to use filters on all products page? Or even a category page?
Hello, I am trying to create some filters so users can use filters to find products they are looking for. So what i am trying is to create a filter according to price lets say. So if i define it this way i am expecting to see this filter option on categoryCapture Last check-in date & days since
I have two custom fields on my Account form, these are "Date of Last Check-In" and "Days Since Last Contact" Using a custom function how can I pull the date from the last check-in and display it in the field "Date of Last Check-In"? and then also display the number of days since last check-in in the "Days SInce Last Contact" field? I tried following a couple of examples but got myself into a bit of a muddle!Pasted Images not being embedded in custom mail
Hi, I'm making a custom report by email based on commentaries. I have the email ready, all working great except for images that are being pasted in the commentaries. Zoho deals with them as temp images and so it requires authentication to view them, somethingAI Bot and Advanced Automation for WhatsApp
Most small businesses "live" on WhatsApp, and while Bigin’s current integration is helpful, users need more automation to keep up with volume. We are requesting features based on our customer Feedbacks AI Bot: For auto-replying to FAQs. Keyword Triggers:Bigin, more powerful than ever on iOS 26, iPadOS 26, macOS Tahoe, and watchOS 26.
Hot on the heels of Apple’s latest OS updates, we’ve rolled out several enhancements and features designed to help you get the most from your Apple devices. Enjoy a refined user experience with smoother navigation and a more content-focused Liquid GlassSelect CRM Custom Module in Zoho Creator
I have a custom module added in Zoho CRM that I would like to link in Zoho creator. When I add the Zoho CRM field it does not show the new module. Is this possible? Do i need to change something in CRM to make it accesible in Creator?Zoho CRM Quotes – Subform and PDF/Writer Limitations
Hello, I am encountering the following limitations in Zoho CRM Quotes: Custom product images cannot be uploaded in the subform – the image upload field cannot be added; only the file upload field is available. File upload placeholders cannot be used inIntroducing Workqueue: your all-in-one view to manage daily work
Hello all, We’re excited to introduce a major productivity boost to your CRM experience: Workqueue, a dynamic, all-in-one workspace that brings every important sales activity, approval, and follow-up right to your fingertips. What is Workqueue? SalesArchiving Contacts
How do I archive a list of contacts, or individual contacts?Every time an event is updated, all participants receive an update email. How can I deactivate this?
Every time an event is updated in Zoho CRM (e.g. change description, link to Lead) every participant of this meeting gets an update email. Another customer noticed this problem years ago in the Japanese community: https://help.zoho.com/portal/ja/community/topic/any-time-an-event-is-updated-on-zohocrm-calendar-it-sends-multiple-invites-to-the-participants-how-do-i-stop-that-from-happening3/18 オンライン勉強会のお知らせ Zoho ワークアウト （無料）
ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 3月開催のZoho ワークアウトの開催が決定しましたのでご案内します。 今回はZoomにて、オンライン開催します。 ▶︎参加登録はこちら（無料） https://us02web.zoom.us/meeting/register/BoNTN7zYR8OvOPGShqBY0A ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目指すイベントです。Zoho Sheet for Desktop
Does Zoho plans to develop a Desktop version of Sheet that installs on the computer like was done with Writer?Conversion Rate – Won Deals over Assigned Prospects
Hello, I would like assistance configuring a KPI in Zoho Analytics titled: Objective of the calculation: Number of Won Deals divided by Total number of assigned prospects (not only converted prospects). Important clarification: The denominator must includePerfomance Management - Zoho People
Hi team, I am looking for performance management data such as KRA, goals, feedback, appraisals, etc., in Zoho Analytics. However, I am unable to find these metrics while editing the setup. Could you please confirm whether these fields are available inFeature Request – Conditional Visitor Information Request in Zoho SalesIQ
We would like to request the ability to conditionally ask for visitor details based on the communication channel used in Zoho SalesIQ. Specifically: When a visitor initiates a conversation through the live chat widget on the website, we want to continueApple Messages for Business in Omnichannel communications?
Hello, Apple launched "Apple Messages for Business" but Zoho CRM or Zoho Desk don't appear in the list of possible integrators. Zoho already promotes https://www.zoho.com/crm/omnichannel.html Omni Channel integration, but Apple Messages does not yet appear.Admin asked me for Backend Details when I wanted to verify my ZeptoMail Account
Please provide the backend details where you will be adding the SMTP/API information of ZeptoMail Who knows what this means?Cliq iOS can't see shared screen
Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? RegardsZOHO.CRM.UI.Record.open not working properly
I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",【Zoho CRM】通貨機能のアップデート：為替レートの自動更新やデータ更新オプションなど
ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から通貨機能のアップデートをご紹介します。 CRMの複数通貨機能を使うと、地域ごとに通貨を管理し、顧客の現地通貨で記録したデータを自国通貨に自動換算して分析やレポートに活用できます。 従来は、為替レートを管理者が手動で定期更新する必要があり、各データには作成時点のレートが固定されるため、その後の市場変動は反映されませんでした。 本記事では、この問題を解消する「為替レートの自動更新」をはじめ、さまざまな新機能をご紹介します。Can we add zoho sign to a custom module?
i understand out of the box it works with 8 modules. is it possible to add it to a custom module?This domain is not allowed to add in Zoho. Please contact support-as@zohocorp.com for further details
Selected date present employees roster
=CHOOSECOLS(FILTER(A2:E5;(INDEX(C2:E5;0;MATCH(VALUE(B8);INDEX(VALUE(C1:E1));0))<>"OFF")*(INDEX(C2:E5;0;MATCH(VALUE(B8);INDEX(VALUE(C1:E1));0))<>"EL"));1;2;2+MATCH(VALUE(B8);INDEX(VALUE(C1:E1));0)) This way sheet looks lCredit Card Terminal for Zoho Books
Hello, Instead of punching the credit card number manually for customer payment, do you have a third-party hardware credit card reader that works with Braintree? Thank YouNext Page