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
I would like to request a new feature or setting for SalesIQ.
Hello Zoho Team, I would like to request a new feature or setting for SalesIQ. Currently, when a user opens our contact widget and clicks on the "Chat with us" option, it opens a standard chat window that remains empty until either the user types a messageAsk the Experts 27: Onboarding and managing support reps
Hello everyone, We are back with our Ask the Experts (ATE) series for 2026. This year, we bring experts to help you address customer support challenges using Zoho Desk. For our first ATE, we are getting into the human side of customer support. "EverySincronizar eventos de Bigin en Zoho Calendar (Zoho Mail)
Hola Me gustaría poder sincronizar mi Calendario de Zoho (Mail) con los eventos de Bigin. No veo la opción disponible.Additional Address - Company Name
It would be very helpful to have a "Company Name" field in the Additional Address shipping section. In a situation where you drop-ship orders to a different address or different company entirely, it may be necessary to list the company name of the receiver. I understand that the Attention field can be used for that purpose, but that's not really the intended purpose of that field.All 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. 24Re: Application Architecture in Zoho Creator — A Platform-Specific Deep Dive
A recent community post on application architecture made some excellent points about planning architecture early in Zoho Creator projects. The core message is right — Creator applications have a habit of growing organically into maintenance nightmares,Remove my video
Hi, How can I remove my video so that I don't have to see myself. It's weird so I always remove my own video from what I see but cannot find this feature here. Thanks!In Zoho inventory Converting sales return to cerdit note from using Api from Creator Error details: {"code":-1,"message":"Invalid Sales Return ID."}
In Zoho inventory Converting sales return to cerdit note from using Api from Creator Error details: {"code":-1,"message":"Invalid Sales Return ID."} this is button Function used in the Creator map Inventory.Create_Credit_note(int CRE_ID) { return_valueRealtime Translations of Video via OnAir
It would be an amazing feature to have a drop down selector in the On Air video area for attendees to select a language to convert the original language to.Zia should track how customer relationships evolve over time
Here's a feature idea that I've been thinking about The Problem Zia is great at analyzing individual interactions email sentiment, call transcription, best time to contact. But here's what it can't do: tell you how a relationship has evolved over time.First Name in Mail
While sending a mail/message to the user, I want only the first name to be displayed—for example: “Hi John” instead of the full name using "Hi ${Name_Field}"Assign emails to lead
I have some contacts who refers leads but there is no away to manually assign emails to lead.Built-in Date Functions in Zoho Analytics Query Tables
I have a doubt about whether Zoho Analytics Query Tables provide built-in functions for start date, end date, and the current monthFSM integration with Books
Hi, I have spent a few months working with FSM and have come across a critical gap in the functionality, which I find almost shocking....either that, or I am an idiot. The lack of bi-directional sync between Books and FSM on Sales Orders/ Work OrdersHow to Track Inventory Usage from Zoho FSM to Zoho Inventory?
Hi everyone, We’re currently working on integrating Zoho FSM with Zoho Inventory, and we’ve encountered a challenge we’re hoping the community can help us understand better. Here’s the context: When we create a Work Order in Zoho FSM that involves partsSet Field Mandatory by Client Script ZOHO CRM
#Tips of the day We can set the field as mandatory by the client script var field_obj = ZDK.Page.getField('Custom_Field1'); field_obj.setMandatory(true); Custom_Field1 = Field API Name Apart from is if you have required any kind of Zoho work please doDiscrepancy in Contracts with Fields list/Layout
The Support Plan field on the layout isn't in the fields list. What am I missing?Enhancements for Currencies in Zoho CRM: Automatic exchange rate updates, options to update record exchange rates, and more
The multi-currency feature helps you track currencies region-wise. This can apply to Sales, CTC, or any other currency-related data. You can record amounts in a customer’s local currency, while the CRM automatically converts them to your home currencyassociating products with accounts
I have all my accounts and products in zoho now. I need to generate a sales order for an account. The product lookup feature on the sales order shows no products...how can I get them to appear.Can we rely on order of returned ids when inserting multiple records?
Hello! API https://www.zoho.com/crm/developer/docs/api/v8/insert-records.html does not mention that the response array will match the input array*, keeping this important information implicit and someone might have doubts to rely on it. (*the responseNew UI for Writer - Disappointed
I've been enjoying Zoho Writer as a new user for about 6 months, and I really like it. One of my favorite things about it is the menu bar, which you can hide or leave out while still seeing most of your page because it is off to the left. I think thisCRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive
Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage filesZoho CRM Community Digest - February 2026 | Part 2
In this edition, we’re highlighting a few more product enhancements, along with some helpful community discussions that explore everyday CRM scenarios from automating reminders and managing lead outreach to making the most of APIs and client scripts.Dont want to list inactive items.
If an item is made inactive, there is no point in showing it in the item list. Please provide an option to hide all inactive items in 'Preferences'.Client Script event on any field of a Detail page
Hi everyone! I'd like to trigger a Client Script when a user modifies a field - any field - from the Account Details page, how can I do this? I don't want to trigger it on a specific field, but on all of them. Thanks in advance!Tip #66 - Exploring Technician Console: Network Statistics - 'Insider Insights'
Hello Zoho Assist Community! Have you ever wondered why a remote session feels sluggish, or wanted to check whether connectivity issues are on your end or the customer's? That's exactly where the Network Statistics feature in Zoho Assist comes to theSyncing zoho books into zoho crm
I was wondering how I can use zoho books in crm as I have been using them separately and would like to sync the two. Is this possible and if so, how? ThanksIntroducing a smarter, faster, and more flexible charting experience
Hello Zoho Sheet users, We're delighted to share the latest news about a major update to charts in Zoho Sheet! The new version supports dynamic data ranges, granular styling options, faster loading, and other interesting enhancements that allow you toREFUND REQUEST FOR PLAN SUBSCRIPTION CANCELLED
I have cancelled my Zoho Books subscription and would like to request a full refund for the subscription. Kindly process the refund at your earliest convenience.Connecting Airwallex in Zoho Books
I have a question. I’m trying to connect our Airwallex account to Zoho Books, but the integration does not seem to work. Could you please guide me on how to properly connect Airwallex? I need to see the expenses also, since it only shows the profits nowpurchasing a pagkage
goodmorning i want to pay for my monthly package but your app is sayinng i have insufficient funds yet i loaded money on my account yesterday though in our currency of ugxEdit Receipt, how?
Do you notice the big blank space in Receipt? I want to remove that. I know there is a template but that blank space, I can't seem to remove it. Please help. Thank you!Can I export a TXF (TurboTax exchange format) file out of Zoho Books?
Hello, I'm trying to do my US Small Business Taxes using Intuit's Turbo Tax. Can I export my expenses from Zoho Books to a TXF format file, so that it can then be imported into Turbo Tax? Thanks, RajneshTax inclusive and exclusive for Sales invoices
Hello, where can I set my tax to be inclusive or exclusive for invoices in Zoho Books? There used to be a button for me to do that right above the products table. Can someone help me please? Thank you!Unable to produce monthly P&L reports for previous years
My company just migrated to Books this year. We have 5+ years financial data and need to generate a monthly P&L for 2019 and a monthly P&L YTD for 2020. The latter is easy, but I'm VERY surprised to learn that default reports in Zoho Books cannot createZOHO Reports are taking longer time to get refresh
Hi Team, Since last few days, I'm facing issues in getting updated reports. For eg: right after making an expense entry or even posting a journal, it is taking longer then expected for the updated reports. Refer below: "You are viewing the report thataudio and video comments in tasks in zoho projects
Hi, is it planned to have the ability to post audio and video comments? the whatsapp wechat format of communicating is a reality... tools like zoho project would facilitate a lot teamwork by facilitating the way in which we can give feedback in audio and video rather than only text. thx!Questions Regarding Helpdesk & SalesIQ Customization and Email Setup
Hello, I hope you’re doing well. I have a few questions regarding Helpdesk and SalesIQ: Can the emails sent to customers via helpdesk tickets be fully customized — including signature, subject line, and other elements? Also, is it possible to send theseLooking for Zoho + Twilio Automation Specialist (Duda Website Integration)
Hi everyone, I’m looking for an experienced Zoho specialist to help build a mostly automated CRM and booking system for my service business. I already have a live website built on Duda.co and need help integrating my existing forms into Zoho CRM (no rebuildSalesforceに添付ファイルを格納したい
お世話になっております。 Salesforceに添付ファイルを格納したく、カスタムオブジェクトに連携し、 「ファイルのアップロード」項目を設けました。 実際、エラーもなく送信出来たのですが、実際生成されたカスタムオブジェクトのレコードを見ると、どこにも添付ファイルがありません。仕様として、この添付ファイルはSalesforceのどこに格納されるのでしょうか？ 今回作りたいフォームは、複数の書類を添付するため、Zohoformのファイルアップロード項目「本人確認書類」「源泉徴収票」などの項目を、Salesforce側にも設けた「本人確認書類」「源泉徴収票」という各項目にURLリンクとして紐づけたいと思っておりました。Next Page