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
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 #197: Frequently Asked Questions on GraphQL APIs
🎊 Nearing 200th Kaizen Post – We want to hear from you! 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 #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.Celebrating 200 posts of Kaizen! Share your ideas for the milestone post
Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQsKaizen #193: Creating different fields in Zoho CRM through API
🎊 Nearing 200th Kaizen Post – We want to hear from you! 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.Client Script | Update - Introducing Commands in Client Script!
Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands
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
Zoho Projects - Q2 Updates | 2025
Hello Users, With this year's second quarter behind us, Zoho Projects is marching towards expanding its usability with a user-centered, more collaborative, customizable, and automated attribute. But before we chart out plans for what’s next, it’s worthFile attachments not working - web version
Since Notebook announced better file attachments, I have experienced file attachments failing. When uploaded from my pc to the web version, notebook just spins. Acting like the attachment is very large. When it's 30k. If I cancel out of this, the notecardAPI - Barebones Data for Send Doc For Signiture
I am learning how to use with Zoho Sign API. I am wondering if someone can give me a bare bones JSON data sample for "Send Document For Signature". Below is the blank data that is pre-populated in postman. Seems like there is more here than the bare minimumRevenue Management: #2 How to compute revenue?
In our previous post, we discussed the revenue recognition standards under IFRS 15 and ASC 606 and the five-step model. Now, let's examine three distinct business scenarios to see how the standards and model work in practice. This will help you understandExternal lookup in CRM (as in Books)
Hello Context: We have a CRM module, similar to Deals, but for Purchasing. Once the PO is created, a link to this PO should be updated in that "deal". In Books, you can add a custom 'external' field which can look up into CRM modules. I'm asked to have"Undo Send" Feature
Would love it if TeamInbox had an "Undo Send" feature, that gives you 10 seconds or so to "undo" the sending of an email. Many other email clients already have this feature, and my clients really miss it, as it has saved them many times in the past whenBetter Notes Commenting
Hi, I'd like to suggest better collaboration tools for NOTES. The current notes section for Accounts, Contacts and Deals is not ideally suitable for any degree of communication or collaboration. When responding to a note, there is no ability to leaveZoho Sites
Does anyone have experience building Zoho sites or know how I can find someone who does?Zoho Crm Lagging
Hi Zoho Support Team, Starting from today, my Zoho CRM has been extremely slow and laggy when accessing any pages or modules. This is affecting my work and overall productivity. Could you please help to check if there are any ongoing issues or if there’sIs there a way to reference/attach mails to deals/contacts when the mails haven't come through their contacts normal email and the mail comes through software / app who use their mail system
There are often system mails that come through systems or other software which use their email addresses since they use their own mail servers to mail. This causes an issue as it does not record the mail in the history of the CRM since the email is notRecommendation
I give up on Zoho. It's never going to be an all in one solution, their own apps don't even connect. Can any one recommend an alternative at least for the crm / people.CRM - Site/Location
Hi Zoho, One massive oversight of the Zoho CRM is the inability to add multiple sites or locations to one account. Many organisations have several (in some cases, hundreds) of sites across the country or even the globe. The workaround to this, of course,An unknown error occurred. Please contact support
Whenever I visit this page I see this. I changed browser and still the same. Can someone from Zoho help me please?How to Streamline Pick & Ship
Is there a way to streamline the built-in pick and ship process? I guess mostly on the ship side. Our current process allows us to choose shipping super fast. It's an EasyPost plugin for WooCommerce. You have to populate the boxes / weights / shippingHow to add, delete or rename the "payment method" drop down?
When we recorded payment in invoice, there's a "payment method" drop down with choices like cash, bank transfer... We want to add and delete or rename some new selection. How to do such adjustment? Please advice.Smarter data gathering with Query component in Wizards
Dear All, Introducing the Query Component in Wizards for CRM! A smart search field that saves you time and effort, and helps you manage and gather data more efficiently than ever before. Long and complex record entries can be overwhelming and prone toIntroducing teamspaces and team modules in Zoho CRM mobile app
Hello everyone, We have an exciting update to share in the Zoho CRM mobile app. As part of CRM For Everyone—a new set of features that reflect our vision to democratize CRM and make it accessible for all—teamspaces and team modules are now available onClient Script - change page and/or section background colours
Hello, Would anyone be willing to help me with a bit of Client Scripting code? I have had a go myself but only been able to alter the form field styles, not the overall page/section/heading styles. I want to set a different page background colour forClient Script | Update - Introducing Subform Events and Actions
Are you making the most of your subforms in Zoho CRM? Do you wish you could automate subform interactions and enhance user experience effortlessly? What if you had Client APIs and events specifically designed for subforms? We are thrilled to introduceZoho SalesIQのチャットボット、ブロックのコピー機能
Zoho SalesIQのチャットボットの構築でドラッグアンドドロップで作成を行っているます。 内容は同じブロックのコピーペースト機能がないみたいなのですが、同一のブロック、同一の複数のブロックをいくつも作成する場合は、皆様はどのように行われていますか？ 例えば添付の４つのブロックをまとめてコピーして、別のフローの先につなげる場合です。 教えていただけますと幸いです。よろしくお願いいたします。Questions About Zoho Commerce Member Portal
Hello, A couple questions about the Zoho Commerce Member Portal: 1. Can I add only specific pages be added to the Members Portal, and not the entire website? 2. When a customer signs up on my Zoho Commerce site, is there a setting that gives me a chanceCancel and refund
Hi, Yesterday I paid for the Zoho email subscription. Within a few minutes, I realized that the subscription counts one email ID as one user. To make another email ID for the same domain name, I'd have to pay for another user. I emailed right away toOnline Payment Fees
We don't take many online credit card payments so the merchant service provider (PayPal) charges us the 2.9% fee for processing the amount. I would like the ability for the fee to be automatically added to the total amount for "ease of payment". We'dError occured. please try again!!!
I created a new list. Added two new contact's email addresses. I try to EDIT either one of them to add their first name, last name and phone number. Whatever I try to edit and update, I get the error message Error occured. please try again!!! And it doesn't update any of their info. I tried just editing first and last name. Still get the error. No matter what I try to update, I get the error. I tried creating a new list, and adding just one email address and then edit it and also get the error."Send email as" not work
Hi team, I currently use ZOHO MAIL services on the MAKE platform (formerly INTEGROMAT). In MAKE I entered my ZOHO MAIL credentials but the problem is that when the emails are sent from the automation in MAKE, the emails do not come in with the name set*UPDATE: ISSUE RESOLVED, SEE HOW FOR HELP* Issue imbedding Youtube Videos
**UPDATE: RESOLVED** For anyone that is having a similar issue, try this workaround before attempting to have the Zoho techs fix it. I have been in communication with them for weeks about this and they can't seem to fix the issue. Thinking it may have just been my computer or my browser, I tried two different computers on two different networks, each running different versions of Windows, and tried it on Chrome, Firefox and IE; nothing worked. So, needless to say, it's an issue with Zoho somewhere.Backorder For Composite Items
Hello If i released SO for composite item and use backorder feature of zoho inventory then it should backorder child item items of composite and not composite item.This is basic of backordering.I conveyed this to zoho call center but no solution yet.Paid Support
We are in the process of creating tiers for our support offerings. There will be three tiers of support available - 2 paid and 1 free. The paid tiers will be purchased in allotted hours. I need to figure out how to a) work in the out of the box SLA, ContractsWhere is the desktop app for Zoho Projects???
As a project manager, I need a desktop app for the projects I manage. Yes, there's the web app, which is AWESOME for cross browser and platform compatibility... but I need a real desktop app for Projects that allow me to enter offline information whereAutomatically CC supervisor
Hello, in our organization agents are replying to tickets and they are required to CC their supervisor on all emails to and from customers. is there a way to have an email address automatically populate in the CC field when reply to a ticket? thaZoho One - White Label
Releasing a white-label feature for Zoho One, or any software or service, can offer several advantages and benefits for both the company providing the software (Zoho in this case) and its users. Here are some key reasons for releasing a white-label featureAre this Features included in the Road Map
1. Sync between description/short description in Zoho Books/Inventory and Zoho Commerce: I am unsure how it works now. I belive the description is not pulled. 2. Pick Up: This feature is great. It is added on Zoho Commerce but not on Books/Inventory whichReconciliation - Show Transactions After End Date
I did a quick search, and I've only seen this brought up indirectly. In the reconciliation interface, transactions dated up to a week after the end date should be available for being reconciled. The use case is pretty simple... many times, transactionsAuto populate email field in zoho form
Hi, Is there a way the email field in zoho forms can be auto populated based on the name selected from a drop down list?Option to accept and pay for estimate from Public Estimate link?
Is there any way that our customers can get the Estimate in their email, click on "View Estimate" and from the public link, click Accept and Pay? When they click that, in the background Zoho would create the invoice record in the so that the payment wouldKaizen #125 Manipulating Multi-Select Lookup fields (MxN) using Zoho CRM APIs
Hello everyone! Welcome back to another week of Kaizen. In last week's post in the Kaizen series, we discussed how subforms work in Zoho CRM and how to manipulate subform data using Zoho CRM APIs. In this post, we will discuss how to manipulate a multi-selectEasily track referrals with Zoho Forms
Referral tracking can be a powerful way for businesses to understand where their leads are coming from. With Zoho Forms, tracking the referral sources of your leads is an easy and straightforward process. Here are some tips to help you make the most ofAdd an email to an existing ticket is not working
I noticed that in Zoho Desk the funcionality to add an email to an existing ticket is not working using the syntax [##12345##], has the method changed? In red is the syntax we use to add email to an existing ticket As you can see, he did not add the emailNew CRM to Campaigns Sync Doesn't Continue Making Updates
Changes made in CRM are not appearing in mapped fields in matching Campaign records after migrating to the new sync process. The only way we've found to get records to update is to call into Support and point out the problem. After convincing SupportZoho Integration with UPS
I have 2 questions: Firstly, is there a way to notify UPS that we have a package to collect once we have done the shipping label? Secondly, how do I get the tracking number and shipment method onto the Invoice and Package Slip for the customer? ThanNext Page