Kaizen #183 - COQL API's Enhancements in Zoho CRM API Version 7

Hello everybody!

Welcome back to another post in our Kaizen series.

In this post, we will discuss the latest COQL API enhancements introduced in Zoho CRM Version 7.

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

In the Custom View API response for a module (e.g., Leads), search for the key "system_name". If the value matches any of the unsupported custom views (Shared By Me, Co-owner, Shared To Me), that view is not supported.

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" }

This query retrieves the Lead Source field from the Contacts module within the Appointment_For MML field. 

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.

Sample query:

{ "select_query": "select Appointment_For.module.api_name, Appointment_For from Appointments__s where id is not null" }

The Appointment_For.module.api_name returns the module name (e.g., Contacts or Accounts) for each record linked in the Appointment_For MML field.

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! 

Happy querying!

Previous Kaizen: Kaizen #182 - Queries in Canvas List View | Kaizen Directory

• 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 this

• Kaizen #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 how

• Kaizen #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

• What's New - January 2026 | Zoho Backstage

  Welcome to 2026! If you thought last year was big, buckle up. We’re stepping into this year with a singular focus: unimaginable growth, elite scalability, and performance benchmarks that leave the old ways of event management in the rearview mirror. As

• Cliq on your mobile phone

  Some of the employees in my company get their cliq on their mobile phones but it will not let them answer the call. The employee has to go to their laptop to access it. 

• Bulk Delete Messages in Cliq

  Isn't it possible to bulk delete messages in Cliq? I have them delivered to me as notifications from another service but most of them need to be deleted as not of value. I get dozens of them per day, and deleting them is already difficult is the "Delete"

• stock movement report by each transaction for each item

  Dear Sir we are using zoho inventory for foodstuff and we lack the transaction report which clearly shows movement of an item from Opening balance of the month to Goods received in, Supplier return out, Invoice out, Sales return In, Transfer out and Transfer

• Unable to change Company Logo

  Hi Guys, I have an issue where I've uploaded a Company image in Gif format and the logo has now got a black background whereas the logo is supposed to be black not the background. I didn't think it would be an issue so I created a PNG version to upload

• Metadata API Access to Functions

  I think it would be incredibly helpful to have api access to every function's code. Our team primarily uses deluge functions to update fields across modules according to business logic. I would like to create a visual dependancy model for our CRM, but

• Historical Sales Info - Blend with Finance Invoice Line Items, Access in CRM and Desk

  My company has been using Zoho One since 2021, with sales data going back through 2020. However, we have been in business much longer, and we have historical sales information that we want to have at our fingertips when talking with customers (usually

• Introducing Record Category in CRM: Group options to see record status at a glance.

  Release update: Currently available for CN, JP, and AU, CA, Sa, and UAE DCs (all editions); EU, US, and IN (Standard and Professional editions). It will be made available to other DCs by mid-March. Hello everyone, We are pleased to introduce Record Category

• Service disruption in Zoho Cliq (EU Region) – February 9, 2026

  Hello everyone, We’d like to inform you about a brief service disruption that affected Zoho Cliq users in the EU region earlier today. The issue has been resolved, and all services are now operating normally. Summary On February 9, 2026, Zoho Cliq experienced

• Spotlight series #26 - Build slides with visual clarity using Diagram

  Hello all! The feature in the spotlight this month is Diagram. Let's say you are a part of a project kickoff meeting and your teammate starts explaining the project life cycle like this: "First we ideate, then we create an initial prototype and share

• Client scripts for Zoho Books ?

  Good day everyone, I am looking for a way to be able to interact with the Quotes and Invoices as they are being created. Think of it like Zoho client script in Zoho CRM. But for the life of me I dont see a way to do this. The issue with having function

• Uplifted homepage experience

  Editions: All editions. Release Note: Available in CA and SA DCs (Standard, Professional). Availability update (17th February 2026): The feature has been enabled for the following DCs: All editions in the CA and SA DC | Free, Standard and Professional

• Ability to move emails between different Shared Mailboxes

  Hi Zoho Team and Community, I would like to suggest a feature that would greatly improve the workflow for teams managing multiple Shared Mailboxes. Currently, we often receive emails in the wrong shared mailbox. While we can forward them to the correct

• Crossbeam

  Does anyone use Crossbeam with their Zoho CRM? I'm looking for a way to import Crossbeam partner leads into Zoho CRM. If so: - What's your experience been like? - Are you able to automatically import Crossbeam leads > Zoho CRM? How? - What doesn't work

• Access forms within a form

  I have multiple forms that I would like to create access to for users within one form. Example: I have forms A, B, C, D. I would like to create a form with the links to forms A, B, C, & D. That way we can send one link and then the user can select the

• WorkDrive issues with Windows Explorer Not Responding

  We are using WorkDrive to collaborate on editing video content. We have a lot of files and quite a few are a few gigs. Recently anytime I try and work with the files Explorer freezes for a couple minutes whether it's dragging the files into Premiere or

• Issues with Actions By Zoho Flow

  Hi, I have a workflow that fires when a deal reaches a stage. This then sends out a contract for the client to sign. I have connected this up through Actions by Zoho Flow. Unfortunately this fails to send out. I have tracked it down to the date fields.

• Fill Mail Merge document up with subform fields of an Inventory module record being in the Related List

  Hi, I try to insert subform fields from an inventory module record being on the Related List of another inventory module record into a Mail Merge template without success. For example: we use ratecards in licensing and this ratecard items are available

• 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 currency

• Better use of contacts

  Zoho inventory has the ability to add multiple contacts to customers. However Zoho inventory doesn't currently provide a way to link a contact to objects like sales orders. This means that while you can tell what company has placed a sales order you can't

• How can we add products using a Wizard?

  We want to create a Wizard to add products. Why is there no possibility to use the products module when creating a wizard?

• Orphan email alias blocking user creation – backend cleanup required

  Hello Zoho Mail Support, I´m unable to assign or create the address xx@iezzimatica.ar in my organization. Current situation: Alias cannot be assigned to any user (system says it is already in use) New user with this address cannot be created Address does

• Ensure Consistent Service Delivery with Comprehensive Job Sheets

  We are elated to announce that one of the most requested features is now live: Job Sheets. They are customizable, reusable forms that serve as a checklist for the services that technicians need to carry out and as a tool for data collection. While on

• Something wrong with client script??

  Someone have the same feeling? Client script behavior become very strange..

• API 500 Error

  Hello amazing ZOHO Projects Community, I get this message. How can we solve this? { "error": { "status_code": "500", "method": "GET", "instance": "/api/v3/portal/2010147XXXX/projects/2679160000003XXXX/timesheet", "title": "INTERNAL_SERVER_ERROR", "error_type":

• Polish signer experience to compete with docusign

  I would like to suggest that someone spend the little bit of time to polish the signer experience, and the email templates to more of a modern professional feel. They are currently very early 2000s and with some simple changes could vastly improve the

• how to change the page signers see after signing a document in zoho sign

  Hello, How can I please change the page a signer sees after signing a document in Zoho Sign? I cannot seem to find it. As it is now, it shows a default landing page "return to Zoho Sign Home". Thanks!

• Digest Janvier - Un résumé de ce qui s'est passé le mois dernier sur Community

  Bonjour chers utilisateurs, Le premier mois de l’année est déjà derrière nous ! Découvrons ensemble comment s'est passé janvier pour Zoho Community France. Nous avons démarré le mois avec une nouvelle intégration entre Zoho Desk et Zoho Contracts. Cette

• contact support

  I have 2 request for a phone call and not received one call as promise!

• Can we fetch Deal owner and Deal owner email with COQL?

  While fetching deal deatils with coql api and while fetching deal owner name and email it is just giving owner id not ginving name and email https://www.zohoapis.in/crm/v2/coql

• Insane mail security

  I cannot access my email... anywhere. For some reason the password for the Mail app on my Mac is being rejected, it worked yesterday but now it doesn't? Ok let's try the web interface. I can access my general Zoho login with the password but if I want

• Zoho Social - Cliq Integration / Bot

  Dear community / zoho, I am looking for a way to create a bot within Zoho Cliq to update my colleagues about our Zoho Social activities. For example, if a new post is published, it would be great if this post automatically would be shared in our social

• Comment to DM Automation

  Comment to DM automation feature in Zoho Marketing Automation, similar to what tools like ManyChat offer. Use case: When a user comments on a social media post (Instagram / Facebook), the system should automatically: Send a private DM to the user Capture

• Send / Send & Close keyboard shortcuts

  Hello! My team is so close to using Zoho Desk with just the keyboard. Keyboard shortcuts really help us to be more efficient -- saving a second or two over thousands of tickets adds up quickly. It seems like the keyboard shortcuts in Desk are only for

• Handling Agent Transfer from Marketing Automation Journey to SalesIQ WhatsApp

  We are currently using Marketing Automation for WhatsApp marketing, and the features are great so far We have a scenario where, during a campaign or journey, we give customers an option to chat with our sales team. For this, we are using SalesIQ WhatsApp

• Webinar - Getting Started with Zoho LandingPage

  Want to launch landing pages that support real marketing goals? This webinar is designed to help you understand the role landing pages play in your overall campaigns and how they contribute to lead generation and conversions. During this session, you’ll

• Increase Round Robin Scheduler Frequency in Zoho Desk

  Dear Zoho Desk Team, We hope this message finds you well. We would like to request an enhancement to the Round Robin Scheduler in Zoho Desk to better address ticket assignment efficiency. Current Behavior At present, the Round Robin Scheduler operates

• Zoho mail to contacts and leads, but not to accounts?

  We use the accounts tab a lot for our business because they can be linked to the sales orders. Now we want to use the mail add on to link communication of our emails to our accounts. However this is only possible for contacts and leads? It would be convenient

• Marketing Tip #19: Upsell on product page to increase order value

  Upselling doesn’t have to feel pushy. It can actually feel helpful when done right. Think "people also bought", upgrades, add-ons, or complementary items. A smart upsell increases your average order value and improves the customer experience by making

• CRM limit reached: only 2 subforms can be created

  we recently stumbled upon a limit of 2 subforms per module. while we found a workaround on this occasion, only 2 subforms can be quite limiting in an enterprise setting. @Anonymous User I've read about imminent increase of other components (e.

• Next Page