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 #197: Frequently Asked Questions on GraphQL APIs

  🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Kaizen #198: Using Client Script for Custom Validation in Blueprint

  Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

  Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

• Kaizen #193: Creating different fields in Zoho CRM through API

  🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

• Client Script | Update - Introducing Commands in Client Script!

  Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands

• Recent Topics

• How To Insert Data into Zoho CRM Organization

  Hi Team I have this organization - https://crm.zoho.com/crm/org83259xxxx/tab/Leads I want to insert data into this Leads module, what is the correct endpoint for doing so ? Also I have using ZohoCRM.modules.ALL scope and generated necessary tokens.

• Where can I get Equation Editor por Zoho Writer?

  I need to use Math Formulas in my document. Thank you.

• How can I get base64 string from filecontent in widget

  Hi, I have a react js widget which has the signature pad. Now, I am saving the signature in signature field in zoho creator form. If I open the edit report record in widget then I want to display the Signature back in signature field. I am using readFile

• Creator roadmap for the rest of 2022

  Hi everyone, Hope you're all good! Thanks for continuing to make this community engaging and informative. Today we'd like to share with you our plans for the near future of Creator. We always strive to strike a good balance of features and enhancements

• Filtering repport for portal users

  Salut, I have a weird problem that I just cannot figure out : When I enter information as administrator on behalf of a "supplier" portal user (in his "inventory" in a shared inventory system), I can see it, "customer" portal users can see it, but the

• Zoho Inventory. Preventing Negative Stock in Sales Orders – Best Practices?

  Dear Zoho Inventory Community, We’re a small business using Zoho Inventory with a team of sales managers. Unfortunately, some employees occasionally overlook stock levels during order processing, leading to negative inventory issues. Is there a way to

• Integración Books para cumplir la ley Crea y Crece y Ley Antifraude (VeriFactu)

  Hola: En principio, en julio de 2025, entra en vigor la ley Crea y Crece y Ley Antifraude (VeriFactu). ¿Sabéis si Zoho va a cumplir con la ley para cumplir con la facturación electrónica conectada a Hacienda? Gracias

• Zoho One - Syncing Merchants and Vendors Between Zoho Expense and Zoho Books

  Hi, I'm exploring the features of Zoho One under the trial subscription and have encountered an issue with syncing Merchant information between Zoho Expense and Zoho Books. While utilizing Zoho Expense to capture receipts, I noticed that when I submit

• Is Zoho Sheet available for Linux ?

  Is Zoho Sheet available for Linux ?

• Zoho Sheet for Desktop

  Does Zoho plans to develop a Desktop version of Sheet that installs on the computer like was done with Writer?

• Create static subforms in Zoho CRM: streamline data entry with pre-defined values

  Last modified on (9 July, 2025): This feature was available in early access and is currently being rolled out to customers in phases. Currently available for users in the the AU, CA, and SA DCs. It will be enabled for the remaining DCs in the next couple

• BUTTONS SHOWN AS AN ICON ON A REPORT

  Hi Is there any way to create an action button but show it as an icon on a report please? As per the attached example? So if the user clicks the icon, it triggers an action?

• Dropshipping Address - Does Not Show on Invoice Correctly

  When a dropshipping address is used for a customer, the correct ship-to address does not seem to show on the Invoice. It shows correctly on the Sales Order, Shipment Order, and Package, just not the Invoice. This is a problem, because the company being

• RFQ MODEL

  A Request for quotation model is used for Purchase Inquiries to multiple vendors. The Item is Created and then selected to send it to various vendors , once the Prices are received , a comparative chart is made for the user. this will help Zoho books

• Will zoho thrive be integrated with Zoho Books?

  title

• Product Updates in Zoho Workplace applications | August 2025

  Hello Workplace Community, Let’s take a look at the new features and enhancements that went live across all Workplace applications this August. Zoho Mail Delegate Email Alias Now you can let other users send emails on your behalf—not just from your primary

• Notebook audio recordings disappearing

  I have recently been experiencing issues where some of my attached audio recordings are disappearing. I am referring specifically to ones made within a Note card in Notebook on mobile, made by pressing the "+" button and choosing "Record audio" (or similar),

• Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked

  Hi, I sent few emails and got this: Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked And now I have few days since I cant send any email. Is there something wrong I did? Also can someone fix this please

• Want to use Zoho Books in Switzerland. CHF support planned?

  Hi, We're a Swiss company using other Zoho suite software and I discovered Zoho Books and other accounting SaaS when looking for an accounting tool. Do you intend to cover Switzerland and CHF based accounting anytime soon? Roy

• Zoho sheet desktop version

  Hi Zoho team Where can I access desktop version of zoho sheets? It is important as web version is slow and requires one to be online all the time to do even basic work. If it is available, please guide me to the same.

• Weekly Tips : Teamwork made easy with Multiple Assignees

  Let's say you are working on a big project where different parts of a single task need attention from several people at the same time—like reviewing a proposal that requires input from sales, legal, and finance teams. Instead of sending separate reminders

• Best way to share/download presentation files in Zoho without losing formatting?

  Hello Zoho Community, I often work with PPT/PDF files in Zoho Docs and share them with colleagues. While PDFs usually give a direct download option, I’ve noticed that PPT/PPTX files sometimes only open in the viewer without a clear download link. Is there

• Celebrating Connections with Zoho Desk

  September 27 is a special day marking two great occasions: World Tourism Day and Google’s birthday. What do these two events have in common (besides the date)? It's something that Zoho Desk celebrates, too: making connections. The connect through tourism

• What is Resolution Time in Business Hours

  HI, What is the formula used to find the total time spent by an agent on a particular ticket? How is Resolution Time in Business Hours calculated in Zohodesk? As we need to find out the time spent on the ticket's solution by an agent we seek your assistance

• How use

  Good morning sir I tried Zoho Mail

• Adding Overlays to Live Stream

  Hello folks, The company I work for will host an online event through Zoho Webinar. I want to add an overlay (an image) at the bottom of the screen with all the sponsors' logos. Is it possible to add an image as an overlay during the live stream? If so,

• Email Sending Failed - SMTP Error: data not accepted. - WHMCS Not sending emails due to this error

  I have been trying to figure out a fix for about a week now and I haven't found one on my own so I am going to ask for help on here.  After checking all the settings and even resetting my password for the email used for WHMCS it still says: Email Sending Failed - SMTP Error: data not accepted.  I have no clue how to fix it at this point. Any insight would be lovely. 

• Does Zoho Learn integrate with Zoho Connect,People,Workdrive,Project,Desk?

  Can we propose Zoho LEarn as a centralised Knowledge Portal tool that can get synched with the other Zoho products and serve as a central Knowledge repository?

• Zoho Flow - Update record in Trackvia

  Hello, I have a Flow that executes correctly but I only want it to execute once when a particular field on a record is updated in TrackVia. I have the trigger filters setup correctly and I want to add an "update record" action at the end of the flow to

• Add Comprehensive Accessibility Features to Zoho Desk Help Center for End Users

  Hello Zoho Desk Team, We hope you're doing well. We’d like to submit a feature request to enhance the client-facing Help Center in Zoho Desk with comprehensive accessibility features, similar to those already available on the agent interface. 🎯 Current

• Rename Record Summary PDF in SendMail task

  So I've been tasked with renaming a record summary PDF to be sent as part of a sendmail task. Normally I would offer the manual solution, a user exports the PDF and uploads it to a file upload field, however this is not acceptable to the client in this

• in zoho creator Sales Returns form has sub form Line Items return quantity when i upate the or enter any values in the sub form that want to reflect in the Sales Order form item deail sub form field Q

  in zoho creator Sales Returns form has sub form Line Items return quantity when i upate the or enter any values in the sub form that want to reflect in the Sales Order form item deail sub form field Quantity Returned\ pls check the recording fetch_salesorder

• Estimates with options and sub-totals

  Hi It seems it would be great to be able to show multiple options in an estimate. For instance I have a core product to which I can add options, and maybe sub-options... It would be great to have subtotals and isolate the core from the not compulsory items. Thanks

• Optional Items Estimate

  How do you handle optional items within an estimate? In our case we have only options to choose with. (Like your software pricing, ...standard, professional, enterprise) How can we disable the total price? Working with Qty = 0 is unprofessional....

• Important Update : Zendesk Sell announced End of Life

  Hello Zendesk users, Zendesk has officially announced that Zendesk Sell will reach its End of Life (EOL) on August 31, 2027 (Learn more). In line with this deprecation, Zoho Analytics will retire its native Zendesk Sell connector effective October 1,

• Zoho Sheets

  Hi, I am trying to transition into Zoho sheets, I have attached the issues encountered. Server issues, file trying to upload for more than 30 mins, even once uploaded my data aren't loaded. Simple calculations are not working I have attached the sample.

• Zoho CRM + Zoho FSM : alignez vos équipes commerciales et techniques

  La vente est finalisée, mais le parcours client ne fait que commencer ! Dans les entreprises orientées service, conclure une vente représente seulement la première étape. Ce qui suit — installation, réparation ou maintenance régulière — influence grandement

• Top Bar Shifting issue still not fixed yet

  I mentioned in a previous ticket that on Android, the top bar shifts up when you view collections or when you're in the settings. That issue still hasn't been fixed yet. I don't wanna have to reinstall the app as I've noticed for some reason, reinstalling

• Power of Automation:: Automate the process of updating project status based on a specific task status.

  Hello Everyone, Today, I am pleased to showcase the capabilities of a custom function that is available in our Gallery. To explore the custom functions within the Gallery, please follow the steps below. Click Setup in the top right corner > Developer

• ZOHO SHEETS

  Where can I access desktop version of zoho sheets? It is important to do basic work If it is available, please guide me to the same

• Next Page