Kaizen #183 - COQL API's Enhancements in Zoho CRM API Version 7
Hello everybody!
Welcome back to another post in our Kaizen series.
COQL API
Query API lets you query for records based on queries using the CRM Object Query Language(COQL). COQL is based on the SQL query syntax, and supports the SELECT query to fetch records. Using this API, you can query for data across different modules that are linked using lookup fields.
1. Enhanced Field Limits in the SELECT Clause
You can now retrieve up to 500 fields in a single query. Previously, the limit was 50. This allows you to fetch more data in a single request, reducing the number of API calls and improving performance.
Sample query:
{ "select_query": "select Last_Name, Email, Phone, Industry, Revenue, ... (500 fields) from Leads where id is not null" } |
2. Enhanced Value Limits for IN and NOT IN Operators
The IN and NOT IN operators now support up to 100 values each, doubling the previous limit of 50. This enhancement allows for more comprehensive filtering in your queries. Users can now include a larger set of criteria in a single query, making it easier to retrieve your data without needing multiple queries.
Sample query for IN:
{ "select_query": "select Account_Name from Accounts where Industry in ('IT', 'Finance', 'Healthcare', ... (100 values))" } |
Sample query for NOT IN:
{ "select_query": "select Account_Name from Accounts where Industry not in ('IT', 'Finance', 'Healthcare', ... (100 values))" } |
3. Custom View support in COQL API
No more manual filtering! Simply use a custom view ID directly in the FROM clause. Retrieve custom view IDs using the Custom View API and include them in your queries to retrieve specific-custom view data.
Unsupported Custom Views
- Custom View in Other Modules : The following custom views are not supported in any module.
- Shared By Me
- Co-owner
- Shared To Me
Example : Shared By Me
Key and Value Structure | "system_name": "{module_name}SHAREDBYME" |
Example | "system_name": "LEADSSHAREDBYME" |
COQL supports CVID in the following format: FROM {module_API_name}#{CVID}.
See the sample query below for reference.
Sample query:
{ "select_query": "select Lead_Status from Leads#5725767000000087501 WHERE id is not null" } //CVID: 5725767000000087501 - This is the Custom View ID in the Leads module. |
4. Retrieve Corresponding Field Metadata via COQL
Need to retrieve field details? You can fetch both the required data and field metadata in a single query. Just add "include_meta" key to your query to retrieve field metadata. This key fetches metadata for the fields specified only in the SELECT column. Also, it eliminates making an additional GET - Fields Metadata API call to know the field metadata and save API credits.
Sample query:
{ "select_query": "select Lead_Status, Phone from Leads where id is not null", "include_meta": [ "fields" ] } |
Ensure you have the ZohoCRM.settings.fields.READ scope to avoid an OAUTH_SCOPE_MISMATCH error.
Sample Response:
{ "data": [ { "Lead_Status": "Attempted to Contact", "Phone": "555-555-5555", "id": "5725767000000420480" }, { "Lead_Status": "Contact in Future", "Phone": "555-555-5555", "id": "5725767000000420481" }, . . . ], "fields": { "Lead_Status": { "webhook": true, "operation_type": { "web_update": true, "api_create": true, "web_create": true, "api_update": true }, "colour_code_enabled_by_system": false, "field_label": "Lead Status", "tooltip": null, "type": "used", "table_name": "CrmLeadDetails", "field_read_only": false, "required": false, "display_label": "Lead Status", "read_only": false, "association_details": null, "multi_module_lookup": {}, "id": "5725767000000002611", "created_time": null, "filterable": true, "visible": true, "refer_from_field": null "auto_number": {} } }, "info": { "count": 200, "more_records": true } } |
5. Field Limit Restriction in the ORDER BY Clause
The fields limit has been restricted in the ORDER BY clause. You can specify up to 10 fields in the ORDER BY clause for more precise sorting. Previously, there was no limit. This restriction helps improve query performance.
sample query:
{ "select_query" : "select Last_Name from Leads where id is not null order by Lead_Source, Lead_Status, Email, ... (up to 10 fields)" } |
6. Duplicate fields restriction
Duplicate fields are restricted in ORDER BY, GROUP BY, and AGGREGATE clauses. That is, if the same field is specified multiple times within the same clause, leading to unnecessary repetition and potential errors. This ensures cleaner and more accurate query results. Previously, this restriction applied only to the SELECT column.
Bad query:
{ "select_query":"select id from Contacts where id is not null order by id,id" } |
Duplicate aggregate fields in a query will result in a "DUPLICATE_DATA" error in the response.
7. Retrieve territory information
You can now retrieve territory information from a module by including the territory field API name in the SELECT column. This is useful to understand where your data has been distributed in various geographical locations.
Sample query 1 :
{ "select_query": "select Account_Name, Territories from Accounts where id is not null" } |
Sample query 2 :
{ "select_query" : "select Territories, Account_Name.Territories from Contacts where id is not null" } |
Sample response:
{ "data": [ { "Account_Name.Territories": [ { "name": "USA", "id": "5725767000000454003" } ], "id": "5725767000000420261", "Territories": [ { "name": "China", "id": "5725767000000454981" } ] }, . . . ], "info": { "count": 174, "more_records": false } } |
Supported modules to retrieve territory details: Contacts, Accounts, Leads, and Deals.
Note:
The territory field API names differ by module:
- Accounts, Contacts, and Leads : Territories
- Deals : Territory
8. "Full_Name" retrieval based on user preferences
The full name can be retrieved based on the current user's display name pattern, as set in their preferences. This ensures that the full name format aligns with the user's settings.
The "Full_Name" field contains the concatenated values of the First_Name and Last_Name fields. This is a read-only field available only in the Leads and Contacts modules.
Sample query:
{ "select_query": "select Full_Name from Contacts where id is not null" } |
9. Support for Multiple Modules in "what_id"
A Multi-Module Lookup (MML) field is a special type of lookup field that allows you to establish a relationship between a record in one module and records from multiple other modules. Unlike a standard lookup field that references a single module, an MML field can point to multiple module types.
Currently, an MML field is available in the Appointments module. The Appointment_For field is an MML field that can reference multiple modules, such as Leads and Contacts.
Sample query:
{ "select_query": "select 'What_Id->Leads.Company','What_Id->Accounts.Account_Type' from Calls where id is not null" } |
9.1 Support to query Multi-module Lookup (MML)'s inner field
You can now retrieve inner fields of linked modules within the MML field. This provides deeper insights into related fields data in a module.
For example, you can now query specific fields from a related module that is linked through a Multi-Module Lookup, making your data retrieval more precise and informative.
{ "select_query": "select 'Appointment_For->Contacts.Lead_Source' from Appointments__s where id is not null" } |
9.2 Support for Querying Multi-Module Lookup Module Name
Retrieve the module name associated with each record in a Multi-Module Lookup (MML) field. This helps in identifying the source module of the linked records, providing a better context for your data.
{ "select_query": "select Appointment_For.module.api_name, Appointment_For from Appointments__s where id is not null" } |
10. Retrieving Consent-Related Information
You can now retrieve consent-related information using the Consents Lookup fields in a module. This is particularly useful for GDPR compliance, ensuring you have consent records.
For more details, refer to the Consent Management documentation. This is particularly useful for GDPR compliance, ensuring you have all necessary consent information.
Note:
- Once GDPR is enabled and configured for a module, a new Data Processing Basis Details field will be created in the respective module with the data type consent_lookup ("data_type": "consent_lookup").
- You can retrieve the Data Processing Basis Details field created in the module using the GET Fields Metadata API.
- To know all available fields in the Consents module, make another GET Fields Metadata API call.
Sample query:
{ "select_query": "select Data_Processing_Basis_Details.Contact_Through_Survey, Data_Processing_Basis_Details.Data_Processing_Basis from Leads where Data_Processing_Basis_Details is not null" } |
11. Enhanced Lookup Field Response
Previously, when a lookup field was included in the SELECT column JOIN, only the lookup field’s ID was displayed. From V7, the corresponding display field value is also provided. Now, when you specify a lookup field in the SELECT column, the response includes the id of the Lookup record ID and the display field value of the lookup record.
This eliminates the need for an additional SELECT column JOIN to fetch the display value (e.g., Owner.last_name).
Sample query:
{ "select_query" : "select Owner from Accounts where id is not null" } |
Sample response
V6 | V7 |
{ "data": [ { "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420169" }, { "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } | { "data": [ { "Owner": { "name": "Boyle", "id": "5725767000000411001" }, "id": "5725767000000420169" }, { "Owner": { "name": "Kate", "id": "5725767000000411482" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } |
Note:
- If you query the Users lookup field in the SELECT column, the corresponding record’s last name (display field) will be displayed in the name key of the corresponding lookup JSON (e.g. Owner) response, from V7.
- Display field value will not be available for Consent Lookup and Multi-Module Lookup (MML) fields.
12. Increasing "Profile Image" field comparators
The Profile Image field now supports additional comparators.
V6 | V7 |
is null, is not null | is null, is not null, =, !=, in, not in, like, not like |
You can use a record image's ID to fetch related data. For example, if you only have a record image's ID and want to retrieve the respective record's last name, you can include the record image ID in your query.
Refer to the COQL document for more details.
Sample query:
{ "select_query" : "select Record_Image from Leads where Record_Image = 'siwz79472749456ff433f8b88d0a795ae9855' " } |
13. Enhanced "Rollup Summary" field comparators
The Rollup Summary field now supports additional comparators, making it easier to filter and analyze your data with more precision.
| V6 | V7 |
is null, is not null | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
Sample query:
{ "select_query" : "select Last_Name from Leads where Total_Calls between 5 and 10" } |
13.1 Enhanced response for the Date and Datetime return types of the Rollup Summary
The Date and Datetime return types in the Rollup Summary field are now provided in ISO date and ISO datetime formats based on the user's time zone.
Sample query:
{ "select_query": "select Last_Note_Added from Leads where id is not null" } |
Response
| V6 | V7 |
{ "data": [ { "Last_Note_Added": "1721302770000", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } | { "data": [ { "Last_Note_Added": "2024-07-04T03:49:48-07:00", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } |
14. Enhanced LIMIT and OFFSET Value Limit
You can now retrieve up to 100,000 records using pagination with the LIMIT and OFFSET clauses. This significantly increases the amount of data you can handle in a single query, making it easier to work with large datasets. Previously, the maximum limit was 10,000. For the next set of records, add another criteria id > {1,00,000th record ID} with the "AND" operator, and go on to fetch the next 1,00,000 records.
Sample query:
{ "select_query" : "select Last_Name, Created_Time from Leads where (Last_Name is not null and id > 5725767000003160016)" } |
15. Enhanced JOIN Support Limit
Base Column JOIN
If a query contains any lookup_field.{any_field} in any clause other than the SELECT column such as ORDER BY, GROUP BY, or AGGREGATE columns, then it will be treated as Base JOIN. The Base JOIN support limit has been increased from two to five. This enhancement enables more complex queries across multiple modules, improving data retrieval flexibility.
Sample query:
{ "select_query" : "select id from Contacts where id is not null order by Owner.role, Created_By.profile" } |
Refer to the Base JOIN section in the COQL API document for more details.
15.1 Introducing the SELECT Column JOIN
A new SELECT column JOIN has been introduced, with a maximum limit of 15.
Advantages of SELECT Column JOIN
- If a lookup field is included in the SELECT column and another lookup field is used in the Base column JOIN, they will be treated as separate JOINs. This ensures that the SELECT column JOIN does not affect the Base JOIN.
- Previously, when two lookup fields in the SELECT pointed to the same module (e.g., Users), they were treated as separate JOINs. From V7, they are now considered a single JOIN, optimizing query performance and reducing redundancy.
Sample query:
{ "select_query" : "select Owner.last_name, Created_By.role from Contacts where id is not null" } |
Refer to the JOINs in COQL Queries for more details on both the JOINs.
16. Subquery in COQL
A subquery support has been given in COQL. A subquery is a child query that is nested or embedded within a parent query i.e., outer query. Subqueries can be used as an alternative to JOINs in a query. Use subqueries when a JOIN field is only needed in the criteria, i.e., in the WHERE clause. The primary reason for using subqueries is to improve performance by saving time and memory.
Sample query:
{ "select_query" : "select Lead_Source,Email from Leads where Contacts in (select id from Contacts where Email='patricia@mail.com')" } |
You can retrieve up to 100 records per subquery in a single query. A maximum of 5 subqueries can be queried in a parent query.
Refer to the Subquery documentation for more details. A detailed post on Subquery will be covered in an upcoming Kaizen post.
We trust that this post meets your needs and is helpful. Let us know your thoughts in the comment section or reach out to us at support@zohocrm.com.
Stay tuned for more insights in our upcoming Kaizen posts!
Topic Participants
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
Recent Topics
Unable to Filter Retail Sales Orders & Need Paid/Unpaid Filter – Zoho Books
Hi Zoho Team, Recently you introduced Retail – Standard and Retail – Premium templates for Sales Orders. However, in the Sales Order module we still cannot filter or segregate Retail Sales Orders separately from normal B2B sales orders. Because of this,Service op locatie organiseren met Zoho FSM: waar lopen organisaties tegenaan?
Bij organisaties met service teams op locatie merken we vaak dat de complexiteit niet zozeer in de planning zelf zit, maar in wat er rond die planning gebeurt. Denk aan opvolging na interventies, consistente servicerapporten, en het bijhouden van installatiesPossible to delete the "Big Deal Alert" in Zoho CRM?
Hi, Is it possible to delete the "Big Deal Alert" in Zoho CRM? My company has no need for it and I want to remove it to clean up my email templates list. Thank you. Moderation Update: Currently, the option to delete the "Big Deal Alert" template is inAllow selection of select inactive users in User data fields
Hello, We sometimes need to select a previous employee that has an inactive account in the User data field. For example, when doing database cleanup and indicating actions are done by a certain employee that weren't filled out when they were part of the[Webinar] Top 10 Most Used Zoho Analytics Features in 2025
Zoho Analytics has evolved significantly over the past year. Discover the most widely adopted features in Zoho Analytics in 2025, based on real customer usage patterns, best practices, and high-impact use cases. Learn how leading teams are turning dataNeed advice for product/item search functionality when adding invoices.
My client uses "Catalog or Vendor" name and Product code to search for his items. But Zoho only allow to search by product name and SKU when adding items to Invoices/Estimates. Clients product codes are not unique as they may overlap from different catalogs/vendors.Super Admin Logging in as another User
How can a Super Admin login as another user. For example, I have a sales rep that is having issues with their Accounts and I want to view their Zoho Account with out having to do a GTM and sharing screens. Moderation Update (8th Aug 2025): We are workingDo buttons and vba msgbox work on mobile, specifially the iPhone zoho sheets app?
In Zoho sheets on the web, I inserted a button and assigned a VBA macro to it. It pops up a msgbox with some text. When I go onto the iPhone mobile zoho sheets app, the button is there. When I click on that button, the spinning asterisk appears for a[Webinar] Solving business challenges- Handling the challenge of transitioning to Zoho Writer from legacy tools
Moving to Zoho Writer is a great way to consolidate your business tools and become more agile. With multiple accessibility modes, no-code automation, and extensive integration with business apps and content platforms, Zoho Writer helps solve your organization'sHow can I effectively manage a website with your help?
I’m wondering if it’s possible to develop a custom website with specific features using Zoho as an alternative platform. My goal is to create a website similar to https://tmsim.ph, with the same kind of functionality and user experience. I would trulyIntroducing the Yes/No field: Binary decisions made beautiful
Greetings, form architects! What would you do when you need a simple yes/no answer on your form? Normally, you add a Radio field. Type Yes. Type No. Until now. The new Yes/No field is purpose-built for binary decisions. It is preconfigured, visually consistent,Move email between inboxes?
Is it possible to move emails from one team inbox to another? We would like to be able to have a single "catch-all" inbox for incoming requests, and then move the email to the appropriate department inbox. I was hoping we would be able to accomplish thisThe power of workflows in Zoho Marketing Automation - Video Webinar
In this Zoho Marketing Automation video webinar, our experts walk you through: Why you may want to create marketing workflows How to create marketing workflows Use Zoho CRM data and apply workflows to automate your marketing strategy How workflows canZoho CRM's sales trend and sales follow-up trend dashboards are now customizable
Dear Customers, We're here with good news! Sales trend and sales follow-up trend are two system-defined dashboards that help you understand trends and anomalies in your sales outreach and conversion efforts. They use Zia's intelligence to identify patternsIntroducing Rule-Based AI Coding Assistants for Zoho Finance Widgets
Hello customers, We’ve introduced rule-based AI coding assistants to speed up Zoho Finance widget development. You can try them out in Cursor AI and GitHub Copilot. This helps you build widgets quickly using simple prompts, while ensuring the generatedMarketing Tip #21: Create an About Us page
People don’t just buy products, they buy from brands they trust. An About Us page helps customers understand who you are, what you stand for, and why your business exists. It adds a human touch to your store and reassures shoppers that there’s a realZobot to handle single choice menu when dynamic list content is more than 10 items
Whatsapp supports maximum of only 10 items for its single choice menu. When we need to show a dynamic list with content more than 10, this could be challenging. We essentially need to add a next and previous buton here in order to make it functional.Add 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 onlyColumn letter from number
Hello, I am trying to select a cell and i have the column number. How do i do this or is there a way of getting the letter from the number? Thank youZoho One Spaces UI
Zoho One も UI が新しくなっていくようです。 Switch Space Overview https://help.zoho.com/portal/en/kb/one/spaces/spaces-ui/articles/swi#Overview Your new space for personalized business productivity https://www.zoho.com/blog/one/your-new-space-for-personalized-business-productivity.htmlZoho CRM NextGen UI
Zoho CRM の Everyone 画面の UI がまた変わるのですね。NextGen UI 更新日時が1ヶ月前で、Zoho Japan でもアナウンスされていたようですが、気が付きませんでした。 【Zoho CRM】Zoho CRM for EveryoneのUIアップデートのお知らせ https://support.zoho.co.jp/portal/ja/kb/articles/zoho-crm-zoho-crm-for-everyone-s-nextgen-ui-gets-an-upgradeView Answer Bot conversations?
We are trialing Zia and are experimenting with Answer Bot on our knowledge base. So far so good! Management asks me if it is possible to view Answer Bot conversations, the purpose being to look over its shoulder and confirm that it is working as desTrain Zia answer bot on only part of Knowledge Base?
We are trialing Zia answer bot and hope to use it on the knowledge base to help our users find the information they are looking for. I have found how to train Zia on the entirety of our knowledge base. But is there a way to train it on only certain categoriesSmarter holiday planning with yearly-specific Holiday Lists
Hello everyone! Managing holidays and business hours is now easier and more efficient. Holiday Lists now support holidays that fall on different dates every year, while business hours now supports more than one holiday list. This helps businesses manageTemporary Outage in Zoho Cliq Affecting US Users – July 23, 2025
We experienced a service disruption in Zoho Cliq that impacted core functionality for users in the US region. The issue occurred between Jul 23, 2025, 06:54:00 PM IST and 07:13:13 PM IST, lasting approximately 19 minutes. To restore service stability,ERROR CODE :550 - 550 5.1.1 Invalid email recipients
This message was created automatically by mail delivery software. A message that you sent could not be delivered to one or more of its recipients. This is a permanent error. mustafa.ashraf@techlink-eg.net, ERROR CODE :550 - 550 5.1.1 Invalid email recipientsFunction #20: Custom calculation in item table of invoices
When you create an invoice in Zoho Books, the 'Amount' of a line item is typically calculated as the product of the "Quantity" and the "Rate" of the item. For instance, if an item has a sales rate of $50 and a quantity of 5 is sold, then the amount wouldImpossible to import Journal from Freshbooks
I have been trying to import journals from Freshbooks since August 30th. Every time I try to import, I get an error message. I have already made sure every row has a date. First it was saying the account and notes had unexpected input and that every debit/creditWie veröffentliche ich ein PDF Datei?
Hallo! Wie veröffentliche ich PDF Datein ich habe ein PDF Datei und den sieht ihr im Upload ich möchte ihn veröffentlichen wie? Mit Freundlichen Grüßen, Herr Bahaa Addin ChkiridZoho Campaigns: An Outstanding Email Marketing Tool
Introducing Zoho Campaigns! A product designed by Zoho, the Zoho Campaigns is made to create, deliver, and manage integrated email campaigns that can help in boosting the sales of a company and its customer base. Zoho Campaigns is actually an email marketingZoho Creator Developer Console | Improved Distribution and Lifecycle Management for apps
Hello everyone, We're excited to introduce new enhancements now in the Zoho Creator Developer Console. These updates strengthen private app distribution through licensing controls and extend environment support across all installed apps, helping teamsZoho 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). ImpactedSync images with Shopify/Cart
Hello, sync images with shopify or other cart, it cuts out the double work of having to upload to shopify/cart and zoho. ThanksIs it Possible to Modify Standard Report Urls
Is there a way to permanently modify standard report Urls? Use case: Suppose I have a Products report. Showing list as timeline, calendar, or kanban doesn't make sense. Want to hide that from users by adding #Report:Products?zc_ShowAs=false&zc_Print=falsePrice List
II want to restrict the items to display in sales, quote, etc for which custom rates are added in price list. How I can do the same in Zoho booksExternal User onboarding for zoho connect is not really intuitive.
So the external user is sent an invite, which has a button that directs them to login to zoho to view the invite, but if they don't have a zoho account, they cannot access that invite, which seems kinda silly, as there is not real way on for them to createHaving trouble fetching contents of Zoho Connect Feeds using the API, requesting alternative API documentation.
I'm trying to retrieve feed/post data from Zoho Connect using the API but facing challenges with the current documentation. What I've tried: OAuth authentication is working correctly (getting 200 OK responses) Tested multiple endpoints: /pulse/nativeapi/v2/feeds,How to upload file to Connect using API?
Hi there. I looked at the API documentation and nowhere did it mention how to use the API method to upload a file even though it is mentioned that it is possible to be done so. Please help.Select the task view on the board in the Zoho Connect iPhone app
Hello. How do I select the task view on the board in the Zoho Connect iPhone app? The Android app has this functionality.Auto tagging
Some of the articles I enter into Notebook get there when I enter them in Raindrop.io and IFTTT copies the articles in Notebook. When this happens the notes are tagged but instead of useful one word tags with topic the tag pertains to the specific articleNext Page