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

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! 






    • Recent Topics

    • Is there a way to prevent a user from removing a payment method on a live, recurring subscription?

      As it stands, if you allow users to remove payment methods, they can do so on live subscriptions. This leaves the sub without a payment method. The user should be able to REPLACE the card with a new one, but not remove all payment methods. We have had

    • Assistance Required: Report Export to Excel and WorkDrive Upload via Deluge

      I'm working on automating a scheduled workflow in Zoho Creator that needs to run weekly. The goal is to: Export a report from Zoho Creator in Excel (.xls or .xlsx) format, and Upload the exported file to a specific folder in Zoho WorkDrive using a Deluge

    • Kanban View for Projects.

      At our organization, we describe active projects with various statuses like "In Proofing" or "Printing" or "Mailing". In the Projects view, one can set these project statuses by selecting from the appropriate drop-down. While this works, it's difficult to view and comprehend the progress of all of your projects relative to each other in a table. Creating a Kanban view for projects where I can move them from one status to another allows me to see where each project is in the order of our workflow.

    • Useful enhancements to Mail Merge in Zoho CRM

      Dear Customers, We hope you're well! We're here with a set of highly anticipated enhancements to the Mail Merge feature in Zoho CRM. Let's go! Mail Merge in Zoho CRM integrates with Zoho Writer to simplify the process of customizing and sharing documents

    • How can I see the actual copy sent to the specific contact?

      I have setup a journey in Marketing Automation and created addition of contact in a specific list as the trigger, emails are sending to the contacts, but I can't see the actual copy sent to any specific contact in the list. Please guide me, where can

    • Client Script for Task Module

      When do we expect the client script for the tasks module?

    • Formula Fields inside of Blueprint Transitions

      We would like to have formula fields inside of blueprint transitions. We type in currency fields and would like to see the result inside of a formula field. Our use case: Send out a price for XY 1. Filling out cost fields 2. See gross profit

    • inventory based on bills and not physical stock

      Hello, I have noticed a very annoying issue with zoho books/inventory. I use composite items. If I have an sub assembly item on back order, I am unable to make up the composite item, even when I have received the goods and it is in my stock. I have to convert the PO into a BILL in order for the item to show as 'Accounting Stock'. The problem is that the supplier Invoice is not shipped with the goods, but can follow even a week later. So I have to make the bill have a 'dummy name and number' until

    • Using Zoho Inventory for managing Item compatibilities

      Use case: Using Zoho Inventory for managing (as an example) aftermarket car parts, where a single part can suit many makes, models and years. How do most businesses assign and manage compatibility for each part? Do ZI users typically do this using multi-select

    • Unable to enter manual journal entries in AR/AP.

      Hi there, i am facing some problem relating to AR/AP. I want to make some entries in AR/AP through manual journal. for example. some time I send cash 500 to Mr. A (Customer) for any purpose and then after 2 or 3 days Mr. A returned back 700 to me and

    • Questions regarding WorkDrive

      Here are my questions regarding WorkDrive: Does WorkDrive have a limit on the number of downloads? What will happen if a file is downloaded excessively? Does WorkDrive have any restrictions on download speeds? Are there any limitations on downloading

    • How to record tips from customers

      I run a cleaning service business and on occasion customers include a tip with their check payment. How can I record the amount paid for service and the tip separately? Any amount over the invoice is looked at as an overpayment and suggest a credit note

    • Multi-currencies in Zoho Books

      I'd like to request multiple currencies in Zoho Books. I have customers paying in USD, CAD, CNY. I need to bill customers in these currencies too. I can set up a base currency, and then set up a exchange-rate table to convert these currencies.

    • I want refund of Zoho standard subscription on WEB payment

      Hi, Kindly help to refund the WEB payment made on 13/07/2025 for Zoho standard annual subscription. I didn't find the software useful for my project. Regards

    • Is Conditional display of field(s) possible in a Canvas Customer Portal?

      Is Conditional display of field(s) possible in a Canvas Customer Portal? How?

    • Automate Backups

      This is a feature request. Consider adding an auto backup feature. Where when you turn it on, it will auto backup on the 15-day schedule. For additional consideration, allow for the export of module data via API calls. Thank you for your consideration.

    • View Linked Subscription on Invoice list

      When looking at the list of invoices in billing is it possible to see the subscription that an invoice is for. This would allow you to see if it's a subscription a customer is behind on, or they simply haven't paid a one time invoice.

    • Recording Credit Card Fees when Recording Payment for Bills

      It seems I am unable to record credit card fees when paying a bill. I pay close to 100% of my bills with a company credit card via online portals. I'm happy for the CC fess to be recorded as Bank Charges but it will not allow that field to be used if

    • Workerly/Zoho One Pricing

      Hi What is the pricing model that is suppose to be as a Zoho One user? If i use the services of a freelancers or temp workers (Sometimes for a few hours a week/month), I need to purchase a zoho one license for each of them as well as workerly licenc

    • DKIM

      I have my DKIM in Titan, which is another email service provider. I get this notification on my portal. What shall I do? ( Improve the deliverability of emails sent from Zoho Social by adding DKIM (DomainKeys Identified Mail) records to your DNS settings.

    • Show All Notes For A Single Record

      I would like to be able to view a list of all Notes for a particular record. We can add several (unlimited) Notes for the Leads, Contacts, Accounts and Potentials records. The subject line and some of the note content for up to 10 notes is visible on the first page. You can select the arrow to view additional notes in the list. I would like a button next to the New Note button that says View All. This would bring up a printable list of all notes listed in descending order by Modified Time (newest

    • Zoholics Europe 2025: Build Smarter with the Zoho Creator Low-code Workshop!

      Why should you attend? This year, Zoholics Europe 2025, Zoho’s official user conference, is your opportunity to connect directly with the teams behind the tools you use every day. Don’t miss one of the most requested sessions: dedicated, hands-on workshop

    • Assigning multiple roles to a user in Creator

      Hi I find we can assign only one role and permission to an user in creator. There is a requirement to assign multiple roles and corresponding permission to one user. Is there any solution or workaround for this? Refer the screenshot below

    • 【Zoho CRM】ウィザード機能のアップデート：セクション要素の追加

      ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中から、ウィザード機能のアップデートをご紹介します。 ウィザード機能に新たに「セクション要素」が追加されました。 各ウィザード画面内で項目、テキスト、サブフォーム、ウィジェット要素などをより柔軟に整理できるようになりました。 セクション要素とは？ ウィザードの中で、関連する項目や要素をひとつの「サブエリア」 としてまとめられる新しい構成要素です。 セクション要素活用メリット 入力内容やプロセスによっては、より細かな構造や階層化が求められる場合があります。

    • Edit PDFs from within your web app using Zoho Office Integrator

      Hi users, We are excited to introduce PDF editing capabilities in Zoho Office Integrator. You can now open, edit, and collaborate on PDFs from within your web app—in addition to documents, spreadsheets, and presentations—and save them in your configured

    • Zoho CRM Workflow and Function Backup Options

      Hi everyone! I have been able to make several backups of my CRM data and noticed that the Workflows and Functions are not included in these backups. To my knowledge, there is no backup feature for workflows and functions, which is problematic in of itself.

    • Zoho Recruit > Job Opening Module> Editing, Deleting, Adding (Fields)

      Hello All In the Job Opening Modules How do i go about deleting the Positing Title or changing to Job Title or can i hide it from all? I want to add a Look Up User Field, however when i select the look up field i do not see that Can i have the industry

    • Can you create a lead without assigning an owner?

      Here is the question I have:   Is there a way I can create a bunch of leads without assigning it an owner?   What I want is for my salespeople to pick and choose their own leads without me assigning them..   right now all leads are owned by me because

    • Bug - Incorrect example in the documentation

      I found an issue in the Zoho API documentation. The example "Sample Request" on this page is incorrect — it shows the example for "Get Email Attachment Info" instead of "Get Email Attachment Content". Wrong example: curl "https://mail.zoho.com/api/accounts/12345678/folders/9000000002014/messages/1710915488416100001/attachmentinfo"

    • Power of Automation :: Dynamically generate Tasklists based on values selected in Task Custom Fields

      Hello Everyone, A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate

    • WhatsApp Enhancements to Boost Customer Engagement

      We're excited to introduce a series of powerful enhancements to the WhatsApp integration in Zoho FSM, making customer interactions faster, smarter, and more actionable—right from your Zoho FSM interface. Here’s a quick rundown of what’s new: Try It Out

    • Store reviews module just got smarter in Zoho Apptics

      Hello, Apptics community! Every app deserves better visibility in the marketplace, and reviews play a crucial role in improving your app's App Store Optimization (ASO), rankings, and credibility across stores. That's why we've rolled out a set of enhancements

    • Try CRM for everyone button in the way of workflow

      Please consider using the bottom bar for offers. Using the top bar for offers like "Try CRM for everyone" really gets in the way of my day to day workflow.

    • Include EVERYTHING in Language Files

      Hey, we are building out a system, that needs to be translated. The language files lack a few things though. 1. Blueprint names 2. Canvas View elements like tab names Please include everything into these files, otherwise it's not really a multi language

    • A way to create kits (groups of items) that do not require bundling and are added to an order as the individual items

      We are evaluating moving off of Sage BusinessWorks (which handles accounting, order entry, inventory control, accounts receivable, etc) to Zoho Books/Inventory. One of the things we heavily use is a feature called Kits, which allows creating a group of

    • Allocating Salesman To Customer

      Hello, please add a feature to allocate a salesman to a particular business customer/account. Regards

    • Shipment

      I have 70 shipments Out of which 40 delivery date are updated( since it is live tracked) 30 numbers i need to update manually. How to do it in bulk?

    • arabic not printing

      Qatar has a legal requirement to have invoices in English and Arabic. When writing invoice fields in Arabic, they don't print. Why?

    • How to show Order Number/SO Ref on Invoice emails

      When creating a sales order or invoice we have an option to input an order number or ref# (see attached images). For the Sales Order email template we can add a placeholder for the SO_REF. However, for the Invoice email template there's no option to reference

    • Specify in-line image size in question

      I have an image inserted into a file upload type question. I can click and drag the corner of the image to make it larger or smaller, but I would like to manually input the dimensions I need. No matter what size I make the image in photoshop before uploading

    • Next Page