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

    • CV-Library: The Newest Source Booster in Zoho Recruit!

      We’ve expanded your sourcing toolkit — CV-Library, one of the UK’s largest and most trusted online job boards, is now available as a Source Booster in Zoho Recruit. This gives recruiters instant access to millions of UK-based candidate profiles, all without

    • Zoho AI Translate Task as Rest API

      I cant find any docs on how to use Zoho AI Translate Task from a rest api call https://www.zoho.com/deluge/help/ai-tasks/translate.html I am working on a custom Widget and I dont think I can execute zoho deluge ai translate task from a custom widget.

    • Is there the possibility to book less than 250 customer portal users?

      If you use the Creator, which is included in Zoho ONe, you can create a customer portal and give access to a maximum of 3 external people / customers, right? On the Creator website I saw that you can add 250 users for 100€/month. However, we don't need

    • Display Parts custom fields in the Work Order

      Hello, I see that is it possible to add custom fields in the Parts module. Would it be possible to also add those custom fields in the Work Order Module under Parts? This would be very useful for customer who want to showcase more information about parts.

    • Unable to edit Saved Estimates

      We are facing issue with editing the price or discount for a saved estimate for nearly 1 week.

    • Translation Blueprint & Picklists with Custom Values

      Hello everyone, I downloaded the translation file and I'm not finding the blueprint transitions. Does this means that the transitions cannot be translated? Also, the picklist values to be translated in the document are not the same that are actually used the in module. For exemple, for my lead module, the picklist values for Lead Status are : Not-contacted, contacted, junk and lost lead. In the translation document (English to French), this is what I see: PicklistValues.Leads.Lead_Status.Contact

    • How to create a custom sales signal?

      I want to create a custom sales signal for a custom module. is it possible to create one? If anyone know please let me know

    • Only show products in offers based on lead source

      We work with several lead sources, each with their own products and different pricing. For example, one product might have two or three different prices. Sometimes, when creating a quote, the wrong product is accidentally selected. Is there a way to only

    • Narrative 7 - The importance of data sharing

      Behind the scenes of a successful ticketing system - BTS Series Narrative 7 - The importance of data sharing Definition Data sharing requires a commitment to preserving the integrity and dependability of shared data throughout its entire lifecycle. This

    • TrueSync regularly filling up my local disk

      Seems that WorkDrive's TrueSync randomly starts filling up my local hard drive space. None of the folders have been set as "Make Offline" but still it seems to randomly start making file offline. The settings of the app is so minimal and is of no real

    • Check out in Meetings

      Why there is no check out in Meetings of Zoho CRM, very difficult to track

    • Issue with Zoho Projet

      Zoho Project on all the cellphones of my customer is crashing. He has mixed brands (Samsung and Pixel). Everything is fine on website and mobile website. Could not reproduce the issue in workshop using Samsung/Pixel/iPhone Uninstallation of Zoho Project

    • SecurePass email English language issue -- please hire someone to correct the English language issues sprinkled throughout your interface

      I wrote previously about some incorrect English in your SecurePass offering. After about six months it was corrected. I have never revoked permission before today and just discovered another language mistake that was not corrected. When you revoke a SecurePass

    • Published Components

      @zoho team, Checking if we can add a password for the public links as we have non-licensed users

    • Zoho Creator customer portal users

      Hi, I'm in a Zoho One subscription with our company. I'm running a project now that involves creating a Zoho Creater application and using the Zoho Creator Customer Portal.  At most we need 25 customer portal users. In our Zoho One plan we only get 3

    • Please Enable Snippets for Agents Adding Comments

      Snippets and templates are currently enabled for agents when they use the reply functionality. There is currently no way to add a template or snippets when an agent comments. This is really weird. Our agents don't use the reply functionality, only the

    • Tip of the Week - Extracting Data from JSON Columns

      Let's say you are importing a CSV or Excel file into Zoho Analytics. What if the file contains a few columns in JSON format? This is where Zoho DataPrep, embedded right within Zoho Analytics, comes in handy. This week's tip explains how to extract data

    • Deluge UI Updates

      Are there any updates planned for the Deluge UI in Zoho Desk? It feels quite dated and lack-lustre after you've spent some time in the CRM Deluge UI.

    • Zia Agents - Follow Ups

      It would be nice to have Zia Agents do follow ups. A lot of times agents are trying to schedule things with users and they say "can we set your printer up tomorrow?" and the user never says anything. A way to have a personalized reminder to the user automatically

    • Hide Agents name in Ticket Responses in "My Area"

      We were able to hide the agents name in the emails, however, the customer is able to see which agent has responded to a ticket in the thread of emails by going into their Help Center thread. Is there any way we can hide that information?

    • Option to Automatically Update Extensions

      Hello Zoho Desk Team, We recently received an email notifying us about an extension update that must be done manually. We’d like to request a feature that allows extensions to be updated automatically without requiring manual intervention. This would

    • Embed Report with Auto Height

      Hello Developers, When we embed report within Page, we are getting one awesome feature to adjust the report height. "Auto" and "Custom' height. This "Auto" option works well when we have no records. But I want to embed report based on some conditions

    • Zoho Payroll: Product Updates - August 2025

      This month’s updates to Zoho Payroll bring enhanced efficiency and smarter features, simplifying payroll management for users in India, UAE, KSA, and US. Read on to explore how these updates can streamline your workflow. Filter Pay Runs by Type (India,

    • Numerical Rating for Interviewer Assessments

      Introducing Numerical Rating, a powerful new addition to interviewer assessments that brings precision and flexibility to candidate evaluations. ✅ What is Numerical Rating? Numerical Rating lets interviewers assign a score for each question in the assessment,

    • User Tips: Adding Multiple Products (Package) to a Quote v2.0 (with Client Script)

      This solution is an improvement on the original idea which used deluge. My solution was posted in the comments are: https://help.zoho.com/portal/en/community/topic/adding-multiple-products-package-to-a-quote The updated version uses client script instead

    • Are downloadable product available in Zoho Commerce

      Hi all. We're considering switching to Zoho Commerce for our shop, but we sell software and remote services. Is there a features for downloadable products? I can't find any information about this. Thank you very much Alice

    • Need Faster Help? Try Live Chat Support

      Hello there, We understand that sometimes, whether you’re facing an issue, exploring a feature, or need quick clarification, sending an email and waiting for a response just doesn’t cut it. You need answers, and you need them now. That’s exactly why we

    • Custom image for each contact using merge tag

      Hi, I'm wondering if it's possible to set up an email campaign to display a different image for each contact using a custom field for the image url. I tried inserting custom html: <img src='$[UD:APP_IMAGE_URL||]$'/> but the editor seemed to reject this and did not actually add anything to the email template. Has anyone got any ideas? cheers, Jeremy

    • Comment Reactions like a Thumbs Up

      Can we please have the ability to emoji react on comments or at the very least, thumbs a comment up? Literally every other project management system out there can support this and it is very much needed to just acknowledge a comment instead of completely

    • Zoho Mail Search Engine Sucks

      Hello There! I really like all the features of Zoho Mail and I believe is way better than Gmail except for the Search Function. I believe Zoho Mail has a very poor search function, if you misspelled a word in the search box, Zoho Mail won't display any

    • Allow Subtasks and Task Lists to have their own Layout

      I'd like to have the ability for subtasks and task lists to have their own layout.  A subtask for us doesn't require all the fields that a parent task has.  The subtask seems cluttered with all the extra fields. Within a project Task List had different purposes.  I'd like the task layout to be different for some task lists.

    • How to update Customers in Zoho Books through Zoho Flow?

      Updating customers in Zoho Flow using a "update customer" action is deleting contact persons in Zoho Books.

    • Conect chat of salesiq with zoho cliq

      Is there any way to answer from zoho cliq  the chat of salesiq initiated by customers?

    • How to transfer credit between customers?

      Hi All, We have a slightly odd situation. It happens that we have two separate customers that are related to each other personally. One customer has an amount of credit, and would like to use that credit to pay the *other* customers invoice. How would

    • Zoho Books Reports: Basic report with total items sold and Cost of goods sold

      How can books show a report with Item Name || Qty Sold || Total Revenue || Total Cost of Goods Sold || Margin I need to factor in also invoices that have been credited where customers returned good. This seems like such a basic report, I'm sure I'm missing

    • The amount entered is more than the balance due for the selected bills."

      Hi Team i am trying to record payments for vendors using API and getting this error in response as { "code": 28011, "message": "The amount entered is more than the balance due for the selected bills." } JSON is prepared according to Documentation and

    • VBA handling named ranges

      In VBA I'm able to work with a single cell named range. E.g. Set Jr = Range ("Year").Value. Also other options are working fine, like Range("Year").Address shows $L$3. However a named range refering to multiple cells is not working for me. Eg. tbl_Test

    • Kaizen #176 - Optimizing the Use of Record ID Variables in Zoho CRM Queries

      Hello everyone! Welcome to another week of Kaizen! Since the release of Queries, we have covered Handling Query Variables in Zoho CRM, Serialization and Schema Management, and Leveraging the 'crmAPIResponse' object in Queries in previous posts. Today,

    • Unbilled Items Report?

      Hello! Is there any way to display a list of items that remain unbilled, without creating an invoice for each customer to see if the unbilled items box is displayed? ;-) Ben

    • Consolidation of Multi currency

      Would like to discuss on the possibility of consolidating the ZOHO companies

    • Next Page