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
¿Puedo migrar mi sitio desde WordPress a Zoho? ¿Zoho admite herramientas con código personalizado?
¡Hola comunidad! Estoy evaluando la posibilidad de migrar mi sitio web https://calculadoradenotas.cl/ desde WordPress a una solución Zoho, y tengo algunas dudas técnicas que espero puedan aclararme. Mi sitio no es solo informativo: es una herramientaAdd a Parameter to the ZOHO CRM Deluge sendmail to denote priority (defaulting to normal if unspecified)
Hello! There is a similar thread for Creator out there that is 2 years old, but I'd like to raise it in the ZOHO CRM category as well. Would you kindly update the existing Deluge sendmail function to allow a priority parameter? The parameter should allowSet Custom Icon for Custom Modules in new Zoho CRM UI
Email outbox is now available in the sandbox
Hello all! Testing emails without visibility has always been a blind spot in the sandbox. With the new Outbox, that gap is closed. You can now view and verify every email triggered from your sandbox, whether it’s through workflows, approvals, or massTransform your line of items into line items: ICR can now record your table values as subform values
Enhancement in Zoho CRM Dear Customers, We hope you're well! Zia Vision’s ICR capability can now recognize, extract, and store tabulated values in your subforms. An ideal example is a university application form. It has printed fields and handwrittenis it possible to add more than one Whatsapp Phone Number to be integrated to Zoho CRM?
so I have successfully added one Whatsapp number like this from this User Interface it seems I can't add a new Whatsapp Number. I need to add a new Whatsapp Number so I can control the lead assignment if a chat sent to Whatsapp Phone Number 1 then assignAdd the ability to Hide Pages in Page Rules
Hi, We have Field Rules to show and hide fields and we have page Rules, but we can't hide a page in Page Rules so it isn't completed before the previous page (And then have the Deny Rules to prevent submitting without both pages completed), we can onlyData Export is forcing Creation/Modified date just like reports
The data export now forces you to select a created or modified date. There is not an All-Time option. This severely cripples the whole function of data export for audit purposes. If the data export (or reports for that matter), requires a date, thereCalendar Year View?
Is there a way I can view the calendar in year view? Maybe create a page with a view like this?Automation Series: Auto-update Project End Date When a Task is Rescheduled
When a project is planned, tasks are scheduled within the projects timeline. Any delay in the predecessor tasks can delay dependent tasks, which can in turn impact the project timeline. Unless the project is manually rescheduled, the end date might notWhat is a realistic turnaround time for account review for ZeptoMail?
On signing up it said 2-3 business days. I am on business-day 6 and have had zero contact of any kind. No follow-up questions, no approval or decline. Attempts to "leave a message" or use the "Contact Us" form have just vanished without a trace. It stillZoho Social | Instagram Integration in Zoho Desk
Good day Zoho team! I have a question. If we link the Instagram business account to our Facebook page, and we integrate the Facebook to Social in Zoho Desk, the "Messages" from Facebook will be converted into Ticket right? Does it also convert the InstagramCliq 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 ? RegardsRich-text fields in Zoho CRM
Moderation Update: During the initial release of Rich Text fields, it was supported only in the Enterprise and Ultimate editions. We have gradually extended Rich Text fields to all the paid editions of Zoho CRM. Hello everyone, We're thrilled to announceBounce rates
Where can I see my bounce rates for: 1. Each email I send out, and 2. in total?Tip #52: Practical uses of Zia Search in Zoho Sprints
If your agile team uses multiple Zoho apps, your team members might often find themselves switching between those apps looking for related information. When the data you're looking for is spread across different workspaces within Zoho environment, relyNew field in Zoho Forms: Validate structured data with the Regex field
Have you ever received form submissions where the information looked almost correct but wasn't quite in the format you expected? Maybe an employee ID was missing a character. A PAN number was entered in lowercase. A license number had an extra space.Need to show discount before total after subtotal
Need to show discount before total after subtotal on my estimate template (see attachment)Import Excel from email into CRM
I receive a daily Excel file by email and I would like to have it automatically imported into a custom Zoho CRM module. Do you think there's anything that can be done? My first idea was searching for a native option in CRM, such as "send an email to aApprove records efficiently: Useful enhancements to My Jobs module and Approval process in Zoho CRM
Dear Customers, As you might know, approval process is a process automation tool that allows you to automate approvals in your organization and My Jobs is where you approve requests from a single point of view. Here's how you'd go about it: You’d addZoho Connect is now even smarter and more personalized
Hello everyone, We’re excited to introduce new features and enhancements to help you personalize the employee experience, connect workplace knowledge with AI, and create more engaging dashboards. Deliver relevant dashboards with Audience With Audience,Stop Zoho Projects From Automatically Adding Client Users
I have a custom function that creates a Zoho Project for every Quote attached to a Deal when it moves to Closed Won. Everything works fine, except for the fact that connecting a Zoho Project to the CRM automatically takes the Contact associated with theGlobal "Search" in FSM
I'm missing the global Search function like in CRM. This should save us a lot of time. We don't now upfront if the customer is a Contact or a Company. So now we have to guess in what module to start a search/filter. Also looking up an address, phone number,Canvas not working
I have a canvas running. I selected it as the default for all options, but mainly want it for Portals. It isn't working. I selected Assign Canvas in both the Dtailed View and in the module itself. Nothing has happened. Any help?LinkedIn RSC is now live in Zoho Recruit
LinkedIn Recruiter System Connect (RSC) is here. Your Zoho Recruit data (candidates, jobs, notes, stage updates, resume attachments, and more) now syncs with LinkedIn in real time. Note: LinkedIn RSC is included with your LinkedIn Recruiter CorporateAdd the Zoom Option to the Camera
Hi, I use the ZOHO Forms Camera so I can manage metadata, compression, etc. but this doesnt have the zoom parameter activated, so when we have photos in a tight space and want to use 0.5 for example, we can't, can this be enabled please. Thanks DanThe Social Wall: July 2026
Hello everyone! We're halfway through the year, and we bring to you three new updates designed to help you experiment with your content, discover your top-performing posts, and automate your engagement workflow. Instagram Trial Reels Instagram trial reelsWhat's New in Zoho Invoice | April – June 2026
Hello everyone! We're back with the latest updates and enhancements we've rolled out in Zoho Invoice from April to June 2026. Here's what's new this quarter: Connect Zoho Invoice to AI Using Zoho MCP Customize Accessibility Preferences Attach AnnexureHelp with SEO
Hi There, I have recently published a site and added some Keywords in the SEO settings. Searching Google I currently don't find my site though. When do these settings take effect? In the SEO settings there is also a section "Sitemap" I can change settings for "frequency" and "Priority" What do these settings do? Kind regardsAll new Address Field in Zoho CRM: maintain structured and accurate address inputs
Availability Update: 29 September 2025: It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition exclusively for IN DC users. 2 March 2026: Available to users in all DCs except US and EU DC. 24HR Helpdesk Cases
We have Zoho One Enterprise. I'm trying to find HR Helpdesk Cases, but my UI does not match the documentation. I'm not sure how to move forward.Zoho Books Placeholder: Inventory Counts
I was hoping to figure out how to find the placeholders for inventory counts by item. We use Location based inventory tracking, so I dont know if that affects things. I want my PDF and Printed PICK LISTS to show the Quantity Available to Pick. I haveUndocumented Books API error message - 1000 - The requested action could not be completed. Please try again. | Unexpected error
This code sometimes throws this error 1000 - The requested action could not be completed. Please try again. | Unexpected error What does it mean? result = zoho.books.updateRecord("salesorders",organization.get("organization_id"),salesorder_id,sales_Reporting Tags and COGS
Is there any way to get COGS recorded against reporting tags? The use cases seems very straightforward to me. If I'm running a P/L report against a specific reporting tag (I use mine for customer type, but it could be used for regions, etc.), the revenuePlease critique my CRM design
I run a disability healthcare business with 2 business units. We run on Zoho One and are re-designing our CRM. We have developed the design concept ourselves, and I am hoping to get some critical feedback: 1) Noting that this is an early design of theCRM x WorkDrive: We're rolling out the WorkDrive-powered file storage experience for existing users
Release plan: Gradual rollout to customers without file storage add-ons, in this order: 1. Standalone CRM 2. CRM Plus and Zoho One DCs: All | Editions: All Available now for: - Standalone CRM accounts in Free and Standard editions without file storageError in Batch Details Stock Reportt
I am a new user of zoho inventory. When extracting batch details stock report as of dec 2025, the report takes into account transactions that happen this year. Thus causing discrepancies when comparing with Stock Report. Is this a global error? Can zohoInvoice template, how to change the text under "Notes" and "Terms and Conditions"
In "Invoice templates", there are two text/info sections at the bottom:"Notes" and "Terms and Conditions". It is possible to change the names of these two headings, but how is it possible to change/alter the text under it. As a standard it says "Thank you for your business" under Notes - I need to change it into something different- How? Thank you.Zoho Commerce B2B
Hello, I have signed up for a Zoho Commerce B2B product demo but it's not clear to me how the B2B experience would look for my customers, in a couple of ways. 1) Some of my customers are on terms and some pay upfront with credit card. How do I hide/showNeed complete Zoho Commerce theme ZIP example and safe staging workflow
I want to build a complete custom Zoho Commerce storefront through Edit Code, including: • Global header and footer • Responsive homepage • Category, search and filter pages • Product cards and product-detail pages • Cart and checkout wrapper • MobileNext Page