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

• Google Ads Conversions Not Being Tracked in Zoho CRM

  We have 3 different conversions created in our Google Ads Account. Only one of the 3 conversion types is tracking in Zoho CRM. Our forms are Elementor Forms that are mapped into Zoho CRM. It apprears to me that all leads are showing up in Zoho CRM, but

• Enable Locations for Expense

  Hi, please enable Locations (ex Branches) for Zoho Expense so that there is consistency between this app and Zoho Books. Thanks in advance.

• Currency abbreviations

  Hello, Im stuck, and need help. I need the currency fields for example, opportunity value, or total revenue, to be abbreviated, lets say for 1,000 - 1K, 1,000,000 - 1M, and so on, how should I do this?

• in the Zoho Creator i have File Upload field get the file on submission of the form Get the File and upload to Zoho Books

  in the Zoho Creator i have File Upload field get the file on submission of the form Get the File and upload to Zoho Books . how I get the file From zoho creator and upload to Zoho Books . using Api response = invokeUrl [ url: "https://www.zohoapis.com/creator/v2.1/data/hh/l130/report/All_Customer_Payments/"+input.ID

• Generate a link for Zoho Sign we can copy and use in a separate email

  Please consider adding functionality that would all a user to copy a reminder link so that we can include it in a personalized email instead of sending a Zoho reminder. Or, allow us to customize the reminder email. Use Case: We have clients we need to

• Syntax for URLs in HTML Snippets

  What are some best practices for inserting a URL in an HTML snippet? I've looked at Zoho Help articles on navigation-based and functional-based URLs, but I'm still unclear on how to incorporate them in an HTML snippet. For example, 1. How do I link to

• Rate Limiting in Zoho Flow (OpenAI API)

  Hi Everyone, We are facing some issues when using Zoho Flow as we have a deluge script running which is making external calls to OpenAI endpoint. Sometimes the response takes more than 30 seconds meaning the script will timeout. We want to implement a

• Placing a condition before converting the LEAD

  Hi,  I need some assistance with Lead conversion. I need to place certain conditions before allowing the user to convert the lead.  For example: up until the certain status's doesn't equal "green" don't allow to convert lead.  I tried creating this using

• it is possible to open a widget via deluge script function

  I have one function that is workflow action I call my fucntion I need to call the internal widget it is possible to open or it have to please tell me the solution

• Creator - Portal Custom Domain

  I will pay $100 in crypto to anyone who can actually get my Creator Custom Domain to function (actually tell me how you got yours to).  Domain verifies, Nothing. I've been fighting it a week, multiple chats to customer service. Clearly I'm doing something wrong.  Some datapoints Domain name itself unimportant, can be a string of numbers.  I need to know what registrars are working for you because GoDaddy does NOT.  Do I need hosting? I've tried both ways and nothing works.  I pushed through Cloudflare

• steps and options to change Domain DNS/Nameservers settings

  Please share the options or steps to change  Domain DNS/Nameservers settings 

• Employees in Leave Policy exceptions

  In the Leave Policies we should be able to add specific employees to the exception list So it will be like All Employees except A,B,C in the exception list, currently we can only add departments etc

• Searching customer field

  Hello, When entering a receipt, we select customer information. The customer information is synced with Zoho CRM. However, we can't find the customer information because it searches for words that begin with the entered value. It needs to search for words

• How I set default email addresses for Sales Orders and Invoices

  I have customers that have different departments that handle Sales Orders and Invoices. How can i set a default email for Sales Orders that's different than the default email for Invoices? Is there a way I can automate this using the Contact Persons Departments

• Modular Permission Levels

  We need more modular Permissions per module in Books we have 2 use cases that are creating problems We need per module export permission we have a use case where users should be able to view the sales orders but not export it, but they can export other

• Kaizen #157: Flyouts in Client Script

  Hello everyone! Welcome back to another exciting edition of our Kaizen series, where we explore fresh insights and innovative ideas to help you discover more and expand your knowledge!In this post, we'll walk through how to display Flyouts in Client Script

• How get stock name from other column ?

  How get stock name from other column ? e.g. =STOCK(C12;"price") where C12 is the code of the stock

• Adding a developer for editing the client application with a single user license

  Hi, I want to know that I as a developer I developed one application and handed over to the customer who is using the application on a single user license. Now after6 months customer came back to me and needs some changes in the application. Can a customer

• Download an email template in html code

  Hello everyone, I have created an email template and I want to download it as html. How can i do that? I know you can do it via the campaigns-first create a campaign add the template and download it as html from there. But what if i don't want to create

• Attachment is not included in e-mails sent through Wordpress

  I have a Wordpress site with Zeptomail Wordpress plugin installed and configured. E-mails are sent ok through Zeptomail but without the included attachment (.pdf file) Zeptomail is used to send tickets to customers through Zeptomail. E-Mails are generated

• Upcoming Changes to the Timesheet Module

  The Timesheet module will undergo a significant change in the upcoming weeks. To start with, we will be renaming Timesheet module to Time Logs. This update will go live early next week. Significance of this change This change will facilitate our next

• Best way to schedule bill payments to vendors

  I've integrated Forte so that I can convert POs to bills and make payments to my vendors all through Books. Is there a way to schedule the bill payments as some of my vendors are net 30, net 60 and even net 90 days. If I can't get this to work, I'll have

• Cant update image field after uploading image to ZFS

  Hello i recently made an application in zoho creator for customer service where customers could upload their complaints every field has been mapped from creator into crm and works fine except for the image upload field i have tried every method to make

• Billing Management: #4 Negate Risk Free with Advances

  In the last post, we explored how unbilled charges accumulate before being invoiced. But what happens when businesses need money before service begins? Picture this: A construction company takes on a $500,000 commercial building project expected to last

• Is there an equivalent to the radius search in RECRUIT available in the CRM

  We have a need to find all Leads and/or Contacts within a given radius of a given location (most likely postcode) but also possibly an address. I was wondering whether anyone has found a way to achieve this in the CRM much as the radius search in RECRUIT

• Zoho CRM Inventory Management

  What’s the difference between Zoho CRM’s inventory management features and Zoho Inventory? When is it better to use each one?

• Cannot Enable Picklist Field Dependency in Products or Custom Modules – Real Estate Setup

  Hello Zoho Support, I am configuring Zoho CRM for real estate property management and need picklist field dependency: What I’ve tried: I started by customizing the Products module (Setup > Modules & Fields) to create “Property Type” (Housing, Land, Commercial)

• Get Workflow Metadata via API

  Is there a way to get metadata on workflows and/or custom functions via API? I would like to automatically pull this information. I couldn't find it in the documentations, but I'm curious if there is an undocumented endpoint that could do this. Moderation

• Zoho Projects - Q2 Updates | 2025

  Hello Users, With this year's second quarter behind us, Zoho Projects is marching towards expanding its usability with a user-centered, more collaborative, customizable, and automated attribute. But before we chart out plans for what’s next, it’s worth

• FSM setup

  So we have been tinkering with FSM to see if it is going to be for us. Now is the time to bite the bullet and link it to our zoho books and zoho crm. The help guides are good but it would really help if they were a bit more in depth on the intergrations.

• Upcoming Updates to the Employees Module in Zoho Payroll (US)

  We've made a couple of updates to the Employees module in Zoho Payroll (latest version of the US edition). These changes will go live today. While creating an employee Currently, the Compensation Details section is part of the Basic Details step, where

• Possible to Turn Off Automatic Notifications for Approvals?

  Hello, This is another question regarding the approval process. First a bit of background: Each of our accounts is assigned a rank based on potential sales. In Zoho, the account rank field is a drop-down with the 5 rank levels and is located on the account

• ZOHO Creator subform link

  Dear Community Support, I am looking for some guidance on how to add a clickable link within a Zoho Creator subform. The goal is for this link to redirect users to another Creator form where they can edit the data related to the specific row they clicked

• Allow Resource to Accept or Reject an Appointment

  I have heard that this can be done, is there any documentation on how?

• Create new Account with contact

  Hi I can create a new Account and, as part of that process, add a primary contact (First name, last name) and Email. But THIS contact does NOT appear in Contacts. How can I make sure the Contact added when creating an Account is also listed as a Contact?

• Custom Fonts in Zoho CRM Template Builder

  Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities if

• Python - code studio

  Hi, I see the code studio is "coming soon". We have some files that will require some more complex transformation, is this feature far off? It appears to have been released in Zoho Analytics already

• Sync desktop folders instantly with WorkDrive TrueSync (Beta)

  Keeping your important files backed up and accessible has never been easier! With WorkDrive desktop app (TrueSync), you can now automatically sync specific desktop folders to WorkDrive Web, ensuring seamless, real-time updates across devices. Important:

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

• Next Page