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

    • PAN - Aadhar Link Status

      Can Zohobooks also get latest PAN-Aadhar Linking Status from Income Tax Portal ?

    • Canvas translation

      We want to offer our CRM system to our users in English and Dutch. However, it seems that text in our deal Canvas isn't available for translation through the translation file. The same applies to the field tooltips. They don't appear in the translation

    • Are Cadences visible to anyone with Cadence permission?

      When setting up a new Cadence is it possible to restrict its use to a specific user? How can I prevent users from making modifications to existing Cadences?

    • Super Admin Logging in as another User

      How can a Super Admin login as another user. For example, I have a sales rep that is having issues with their Accounts and I want to view their Zoho Account with out having to do a GTM and sharing screens. Moderation Update (8th Aug 2025): We are working

    • Show my cost or profit while creating estimate

      Hi, While creating estimate it becomes very important to know exact profit or purchased price of the products at one side just for our reference so we can decide whether we can offer better disc or not .

    • Issue on Upload API and href image URL

      Here is my Full API Code , URL : URL: https://desk.zoho.com/api/v1/uploads/659563000000193003/content Headers* Authorization: 'Zoho-oauthtoken 1000.ed5ce2836bf5ba9b946f5ec9************88e73ff4883a3e9c58ffeb7870' orgId: 7586***** RESPONSE{ "errorCode":

    • Issue when downloading a Mail Merged Zoho Writer Document as .docx

      Hi, We are using within Zoho CRM mailmerge to create documents. This results in a Zoho Writer document. When we try to download as a Microsoft Docx file we get following error: "Word experienced an error trying to open the file. Try these suggestions.

    • 【Zoho CRM】ケイデンス機能のアップデート

      ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 今回は「Zoho CRM アップデート情報」の中から、ケイデンス機能のアップデートをご紹介します。 ケイデンス機能の2つの強化されたことで、適用と解除のタイミングをより柔軟に管理できるようになり、 よりタイムリーで的確なコミュニケーションが実現できるようになりました。 目次： 1. ケイデンスの再開／最初からのやり直し 2. ケイデンスからのデータ解除タイミングの設定 1. ケイデンスの再開／最初からのやり直し 手動削除、完了、または適用解除条件が満たされた場合など、以前に適用解除されたデータをケイデンスに再適用できるようになりました。

    • Rescheduled US meetups: Zoho Desk user meetups are coming to seven U.S. cities in October and November, 2025

      Hello to our Zoho Desk users in the United States, We're excited to share the revised dates for the upcoming Zoho User Groups happening across the US this summer. Our product experts are heading to seven cities throughout the country, and for the first

    • Anyone get the OpenAI API to work in Zoho Meeting?

      Has anyone been able to get the OpenAI API to work in generating meeting summaries? I have been trying, but I get an error that says "OpenAI key notes request rate exceeded. Please try again later or upgrade your open AI account." I contacted Zoho support

    • Push Notifications Customization

      There is no way to customize the notifications we get. I would like to be able to get notifications based on if they are assigned directly to me, my team, my department, or perhaps tickets that match a specific criteria (a contact or account is a VIP

    • Announcing Early Access to the next generation of Zoho Desk UI

      Customer service is one of the categories where efficiency and quality of service have to run in parallel, and your team's experience with their helpdesk goes a long way ensuring these aspects are uncompromised. Introducing DOT Design for Zoho Desk -

    • Editing the record in report

      I have a use-case as below- User creates a assessment record by filling some fields. User assigns that record to portal user by using Assigned To dropdown (Assigned To is Users field in form with choices as customers). I have set the record owner of form

    • Unified WhatsApp Number Management in Zoho Desk and SalesIQ

      Dear Zoho Desk Support Team, We are currently utilizing both Zoho Desk and Zoho SalesIQ for our customer support operations. While both platforms offer WhatsApp integration, we are facing challenges due to the requirement of separate WhatsApp numbers

    • Can we have Bills of Material Module ?

      Can we have Bills of Material Module ?

    • Main Ticket Page Customization

      We do not love the ticket list page (right after clicking Tickets menu item) would like options to customize it.

    • Communicating with emojis

      On July 17, we celebrate World Emoji Day! We're a bit late 😐 sharing insights about this day. But we just couldn't let it pass without a mention 😊 because emojis have a meaningful connection with customer service 💬 🤝. We do not want to miss out on

    • Agent Collision Missing from Mobile App

      Please add Agent Collision capabilities to the mobile app.

    • Zia Sentiment and Functionality on Mobile

      Please add Zia sentiment and generative responses to the mobile app. It would be nice to see the ticket sentiment and generate a response back to a user using Zia on my iPhone

    • View Account Attachments on iOS

      Please allow us to view account attachments on the mobile iOS app!

    • How do I run a PnL by Sales Person?

      I am trying to run a PnL by sales person but am not seeing the option do so. All I need to know (per salesperson) is revenue, cost of goods, gross profit.

    • View Contracts and Support Plans on Mobile

      We would like to be able to see contracts and support plans from the mobile app on iOS!

    • Why is Zoho Meeting quality so poor?

      I've just moved from Office 365 to Zoho Workplace and have been generally really positive about the new platform -- nicely integrated, nice GUI, good and easy-to-understand control and customisation, and at a reasonable price. However, what is going on

    • App like Miro

      Hi all, is there a way to have a interactive whiteboard like in Miro? We want to visualize our processes and workflows in an easy way.

    • Loan repayment Entry

      While receiving loan, i does following steps in My Zoho books. 1. Create "Loan & Advance " Account as Parent Account under Long Term Liabilities. 2. Create another account For Example "Mr. ABC's Loan as Child account under the parent account. Now: In

    • Quotes module send email reverted back into 2022??

      Our Zoho CRM PLUS quotes, sales orders, invoice modules is showing us an email composer from 2022. We cannot send emails and its been a real pain. I tried clicking the new version over there but it doesnt seem to do anything. Any help is welcome. th

    • Workflow Condition - how do check that a date / time value is in the past?

      Hello, I'm got a workflow that runs a function when records reach their 'Effective Date / Time', but sometimes records are created after the 'Effective Date / Time' so I have another workflow that checks for records which needs to be processed immediately.

    • Pre filling SignForm field values by URL field alias's in Zoho Sign

      Hi, Does anyone know if it's possible to pre fill the field values of the SignForm by using field alias's like you can in Zoho forms? To be more specific, I want to be able to change the SignForm URL to include some information like this: Before : https://sign.zoho.eu/signform?form_link=234b4d535f495623920c288fc8538cb9e6db03bbfd44499b63f3e5c48daf78f44bc47f333e2f5072cc1ee74b7332fe18b25c93fab10cb6243278d49c67eacbf30bbe5b6e1cc8c6b2#/

    • How to Split Payout in Zoho Books (Without Using Journal?)

      Hi, I'm trying to properly record payouts in Zoho Books. The issue is that each payout is a combination of sales and expenses (fees). When I try to categorise the payout transaction from the Banking tab, I can only split the transaction using income-type

    • Payment Schedule

      Please add the ability to create a payment schedule. The other options, like retainer invoices or two invoices, do not work for the customer.  We invoice a client and need to be able to show them everything they owe in one invoice, and when each payment

    • Which pricing system do you think would work best for us?

      Imagine we’re selling strictly wholesale. We’d rather not publish unit prices; instead, we quote customers case-by-case. To spur larger orders, we’re considering a transparent discount ladder—say: $0 – $999: 0 % $1,000 – $1,999: 5 % $2,000 – $4,999: 10

    • Can't Remove Payment Gateway

      I am getting the error "Settings cannot be cleared as some of the transactions are still in progress." when trying to remove the PayGate payment gateway which I was unable to get working. I am now using paystack and I want to remove Paygate.

    • Sync specific Zoho Inventory Warehouses to Zoho Commerce

      As said in the title, we would want to only sync stock from one warehouse of Zoho Inventory to the Zoho Commerce item stock. We have a 2 warehouses in different countries and the way that Zoho Commerce works (It takes stock from ALL WAREHOUSES EVERYWHERE

    • Weekly Tips : Automatically clean clutter with Junk cleanup interval

      If you regularly receive many unwanted or spam emails, your Spam folder can quickly fill up and start taking up valuable storage space in your Zoho Mail account. Instead of manually clearing it every few days, you might find it helpful to enable automatic

    • Any solution for getting portal users list in deluge or in widget

      Hi Team, Has anyone able to find the solution to get portal users list in deluge or in zoho creator widgets? Thanks, Payal

    • The Grid is here!

      Hey Zoho Forms Community! 👋 We’re thrilled to announce the launch of a feature that’s been on your wishlist for a while: Grids What is Grids? Grids let you place form fields side by side in multiple columns to create a more concise and organized form

    • Steuerberater der Zoho benutzt in Deutschland

      I write in English because the issue is related to German regulations. Wir sind ein Unternehmen, welches aktuell keine Pflicht zur doppelten Buchführung hat. Aktuell bucht unser Steuerberater jeden Beleg, auch unsere Auslagen. Wir würden dies gerne selbst

    • GraphQL in new Send Webhooks feature

      Hello, is it possible to use GraphQL apis in the new Send Webhooks feature?

    • # 2 Why do we need a billing system when accounting covers billing?

      In today's evolving financial tech stack, businesses use a mix of tools to manage their day-to-day operations, from invoicing to full-fledged accounting. While accounting platforms typically come with built-in invoicing features, specialized billing systems

    • How to insert an Excel/Zoho Sheet table in a chat?

      Hello, is there a way to paste an excel/zoho sheet table to a conversation without loosing table lines. I tried to paste a piece of a table and all the columns and rows were gone. How to easily paste a table without a need of sending a file? Katarzy

    • Next Page