Kaizen #171: FAQs on COQL API

Kaizen #171: FAQs on COQL API

Hello all!!
Welcome back to another post in the Kaizen series!

In this post, we will address some of the most frequently asked questions about Zoho CRM's COQL API from the Zoho CRM Developer Community Forum.




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. Why did I get a SYNTAX error for the following query?


{
    "select_query":"select External from Contacts where id is not null"
}


Answer: 
The error occurs because the query uses an SQL reserved keyword, "External", as a column name without enclosing it in quotes. When using SQL reserved keywords like "External", you must enclose them in single or double quotes.

The above-query can be written as:

{
    "select_query":"select 'External' from Contacts where id is not null"
}



------------------------------------------------------------------------------------------------------------------------------------

2. Why does the following query throw an error?


{
    "select_query":"select id from Contacts where id is not null and Account_Name is not null and Vendor_Name is not null"
}


Answer: 
When the query involves more than two criteria, ensure that you enclose your criteria properly by grouping them in pairs, such as ((A and B) and C).

The above-query can be written as:

{
    "select_query": "select id from Contacts where ((id is not null and Account_Name is not null) and Vendor_Name is not null)"
}


------------------------------------------------------------------------------------------------------------------------------------

3. How to escape single quotes within single quotes and double quotes within double quotes?

Answer: 
For single quote:

If you want to retrieve a value that contains a single quote, for example, 1' 2 in the Designation field (a single-line field), you need to escape the single quote in the value by adding another single quote.

Example:
1'' 2

Sample Input and Response:



For double quotes:

If you want to retrieve a value which contains double quotes, for example, 1' ' 2 in the Designation field (any single-line field), then you have to escape each double quotes with backslash (\) in the value.



------------------------------------------------------------------------------------------------------------------------------------

4. Why does the following query throw an error?


{
    "select_query":"select What_Id->Leads.Last_Name from Tasks where id is not null"
}


Answer: 
Special characters such as "-",  ">"  "*", and "!" in a query must be enclosed within quotes, either in single or double quotes. 
Your query can be written as:


{
    "select_query":"select 'What_Id->Leads.Last_Name' from Tasks where id is not null"
}



------------------------------------------------------------------------------------------------------------------------------------

5. How can I retrieve records from a subform module using COQL?

Answer: 
To retrieve subform data within a module, specify the respective subform module's API name in your query instead of querying the parent module. For more details, refer to Kaizen #124 on managing Subforms using Zoho CRM APIs.

------------------------------------------------------------------------------------------------------------------------------------

6. How to retrieve Multi Select lookup field data using COQL?

Answer: 
To query Multi-Select Lookup fields (MxN fields), you need to retrieve the data through the corresponding linking module instead of querying them directly from the parent module. For more details, refer to Kaizen #125 on manipulating Multi-Select Lookup fields using Zoho CRM APIs.

------------------------------------------------------------------------------------------------------------------------------------

7. Can I query more than 50,000 records?

Answer: 
Up to V6, you can retrieve up to 10,000 records without changing the criteria by using the LIMIT and OFFSET in your query. When retrieving the initial 10,000 records, make an API call with Created_Time, Modified_Time, or ID in the ORDER BY clause. To fetch more than 10,000 records, apply a condition based on the fields used in the initial ORDER BY clause in the query.
Refer to the Pagination section for more details. 
From V7 onwards, we support retrieving up to 1,00,000 records without changing the criteria by using LIMIT and OFFSET in your query. To fetch more than 1,00,000 records, please refer to the Pagination section for more details.

------------------------------------------------------------------------------------------------------------------------------------

8. Is territory field supported in COQL?

Answer: 
Yes,  you can use the territory field in the COQL from V7. Refer to the Territories section in COQL document for sample.
------------------------------------------------------------------------------------------------------------------------------------

9. Is CVID support provided in COQL?

Answer: 
Yes, from V7, you can use the CVID in your query. Refer to the CVID section in the COQL document for details.
------------------------------------------------------------------------------------------------------------------------------------

10. What are the supported aggregate functions, and why do they not work with values like Avg or avg?

Answer:
The aggregate functions are case-sensitive and only work when specified in all capital letters.  Using values like Avg or avg will result in an error.
The supported aggregate functions are MIN, MAX, AVG, SUM, and COUNT. 

------------------------------------------------------------------------------------------------------------------------------------

11. Are other SQL-related functions like CONCAT or DATE() supported in COQL?

Answer:
No, COQL in Zoho CRM API Version 7 currently supports only aggregate functions and does not include other SQL-related functions like CONCAT or DATE().
------------------------------------------------------------------------------------------------------------------------------------
12. Is it possible to select more than 50 fields in the SELECT column?

Answer:
Yes, from Zoho CRM API Version 7, the limit has been increased from 50 to 500 fields in the SELECT column.
------------------------------------------------------------------------------------------------------------------------------------

13. Can Multi-Module Lookup inner fields be queried?

Answer:

Yes, you can query fields from the associated module in a Multi-Module Lookup (e.g., Appointments module). 

Use the following format:

select 'What_Id->{associated_module_API_name}.{field_API_name_from_associated_module}'

Example:

{
  "select_query": "select 'What_Id->Leads.Last_Name','What_Id->Accounts.Account_Type' from Events where id is not null"
}



Note:
From Zoho CRM API Version 7, you can also query the "Appointment_For" field.

------------------------------------------------------------------------------------------------------------------------------------

14. Is Display Name available for all types of lookup fields?

Answer:
From V7, the display name is available for Lookup and User Lookup fields when you specify them in the SELECT column, but it is not available for Consent Lookup and Multi-Module Lookup (MML) fields.
Please note, for the Users module, only the "last_name" is shown as the display field for the Users module is last_name.

Example:

{
    "select_query": "select Account_Name, Owner, Data_Processing_Basis_Details from Contacts where Data_Processing_Basis_Details is not null"
}


Response:

{
    "data": [
        {
            "Owner": { //user lookup field
                "name": "Boyle",
                "id": "5725767000000411001"
            },
            "Account_Name": { //lookup field
                "name": "Zoho",
                "id": "5725767000003464060"
            },
            "Data_Processing_Basis_Details": { //consent lookup field
                "id": "5725767000005083039"
            },
            "id": "5725767000005091053"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}


------------------------------------------------------------------------------------------------------------------------------------

15. Is it possible to get a Full Name based on User Preference?

Answer:
The Full Name field is supported in the Leads, Contacts, and Users modules.

Leads and Contacts modules:
  • Below V7: You must query the first_name and last_name fields separately and concatenate them to form the Full Name.
  • From V7: You can directly query the full_name field in the SELECT column, and it will return data based on the User Preference.
Users module:
In all versions, the full_name field is not directly supported. You need to query the first_name and last_name fields separately and concatenate them to construct the Full Name.

------------------------------------------------------------------------------------------------------------------------------------

16. Is it possible to use Profile Image and Rollup Summary fields in criteria?

Answer:
From V7, you can use Profile Image and Rollup Summary fields in criteria.

------------------------------------------------------------------------------------------------------------------------------------

17. Which fields are restricted in all clauses?

Answer:
The following fields are restricted from being included in all columns:
  • Multi-select lookup fields: These cannot be queried directly from the parent module. Instead, query them from their respective linking modules.
  • Line items: Pricing_Details and Product_Details
    • Pricing_Details: This field cannot be queried as it is not a subform but a separate section.
    • Product_Details: This is a subform. You cannot query data from the parent module. Instead, query it directly from the respective subform module.
  • Participants in the Events module.
  • File/Image upload fields
  • Availability Information fields in the Services module
    • Choose Date(s)
    • Choose Day(s)
Check the following image for reference.



------------------------------------------------------------------------------------------------------------------------------------

18. Which fields are restricted only in criteria?

Answer:
The following fields have restrictions when used in criteria:
  • Multiline fields: Cannot be used in criteria.
  • Encrypted numeric fields: Support limited comparators (is null, is not null, =, != ).
  • Encrypted non-numeric fields: Support only is null and is not null comparators.
------------------------------------------------------------------------------------------------------------------------------------

19. Which fields are restricted only in the Group By clause?

Answer:
Encrypted fields are restricted from being used in the Group By clause.

------------------------------------------------------------------------------------------------------------------------------------

20. Which fields are restricted only in the ORDER BY clause?

Answer:
Encrypted fields are restricted from being used in the ORDER BY clause.

------------------------------------------------------------------------------------------------------------------------------------

21. Is External ID supported in COQL?

Answer:
Yes, External ID has been supported from Zoho CRM API's Version 4. Please note that operators such as "is null" and "is not null" are not supported in the criteria when using External ID.

Supported operators are "=", "!=", "in" and "not in".

------------------------------------------------------------------------------------------------------------------------------------

22. How to resolve a 408 error (Request Timeout)?

Answer:
If your query takes more than a second to parse, a request timeout error will occur.

To avoid this, ensure the query is not overly complex and that all parentheses are properly balanced. Simplifying the query or fixing any mismatched parentheses can help resolve the issue.

------------------------------------------------------------------------------------------------------------------------------------

23. Is it necessary to query the records based only on the User Time Zone?

Answer: 
No, users can query records based on any time zone. However, the response will always be returned in the time zone of the user who made the query.

------------------------------------------------------------------------------------------------------------------------------------

24. Which fields are restricted when using aggregate functions, for example, SUM(field_API_name)?

Answer: 
Encrypted fields are restricted and cannot be used in the aggregate functions like SUM, AVG, MIX, or MAX.

------------------------------------------------------------------------------------------------------------------------------------

25. Where can we use Alias?

Answer:
Alias can be used in the SELECT clause and in the ORDER BY clause. You can assign an alias in the SELECT clause, and the assigned alias can be referenced in the ORDER BY clause. But you cannot assign an alias directly in the ORDER BY clause. 

Note: Alias cannot be used in the other clauses, such as WHERE and GROUP BY.

------------------------------------------------------------------------------------------------------------------------------------
Info
More enhancements in the COQL API are now live in Zoho CRM API Version 7. Check out the V7 Changelog for detailed information on these updates.

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

    • Sticky Posts

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

    • Kaizen #226: Using ZRC in Client Script

      Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this

    • Kaizen #222 - Client Script Support for Notes Related List

      Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore how

    • Kaizen #217 - Actions APIs : Tasks

      Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.

    • Kaizen #216 - Actions APIs : Email Notifications

      Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are

    • Recent Topics

    • Automated Task reminder

      First question: If a task does not have a reminder set, will it still send an email notification that the task is due today? If not, how can I set up an automated reminder to send the task owner an email that it is due on a certain date?

    • Zoho Support - contract notifications

      Hi, I have a few questions about using Zoho support. Is there a way to add custom contract notifications like (90 days before expiry send notification e-mail to agent and customer, then another 60 days before expiry and another 30 days.). And is it possible

    • Kaizen #230 - Smart Discount-Based Quote Approvals Using CRM Functions and Approval Process

      Hello everyone! Welcome back to the Kaizen series! Discount approvals are a standard part of sales governance. Most organizations need something like this: Discount % Required Action < 10% Auto-approve 10–19.99% Sales Manager approval ≥ 20% VP Sales approval

    • How to create a new Batch and update Stock via Inventory?

      Hi everyone, We are building an automation where a user enters batch details (Batch Number, Mfg Date, Expiry, and Quantity) into a Custom Module. I need this to trigger an API call to Zoho Inventory to: Create the new batch for the item. Increase the

    • OAuth2 Scope Error - Incorrectly defaulting to CRM instead of Analytics.

      Hello Zoho Team, I am trying to connect n8n to Zoho Analytics API V2 for a simple automation project. Despite using the correct Analytics-specific scopes, my OAuth handshake is failing with a CRM-related error. The Problem: The authorization screen shows:

    • Archive Option in Conversation View

      Hello, I have a suggestion\request to add an "Archive Thread" button in conversation view of Zoho Mail. The best suggestion I have is to put an "Archive Thread" button next to the "Label Entire Thread" button in conversation view. Most users don't just

    • Is it possible to create a meeting in Zoho Crm which automatically creates a Google Meet link?

      We are using Google's own "Zoho CRM for Google" integration and also Zoho's "Google Apps Sync" tools, but none of them provide us with the ability to create a meeting in Zoho CRM that then adds a Google Meet link into the meeting. Is this something that

    • Trigger a Workflow Function if an Attachment (Related List) has been added

      Hello, I have a Case Module with a related list which is Attachment. I want to trigger a workflow if I added an attachment. I've seen some topics about this in zoho community that was posted few months ago and based on the answers, there is no trigger

    • How can I link Products in a Deal Subform to the Products Module

      Hello, I have a pricing subform on our Deals page and use a lookup field to associate a product with each line. I want to be able to look at a product page within the Products module and see a list of the deals connected to that product. I have this working

    • Email Field Validation Incorrectly Rejects RFC-Compliant Addresses (Forward Slashes)

      I've encountered a validation issue with Zoho Creator's Email field that rejects RFC-compliant email addresses containing forward slashes, and I'm hoping the Zoho team can address this in a future update. The Issue When entering an email address containing

    • Call result pop up on call when call ends

      I’d like to be able to create a pop up that appears after a call has finished that allows me to select the Call Result. I'm using RingCentral. I have seen from a previous, now locked, thread on Zoho Cares that this capability has been implemented, but

    • ZOHO.CRM.UI.Record.open not working properly

      I have a Zoho CRM Widget and in it I have a block where it will open the blocks Meeting like below block.addEventListener("click", () => { ZOHO.CRM.UI.Record.open({ Entity: "Events", RecordID: meeting.id }).catch(err => { console.error("Open record failed:",

    • Payment system for donations management

      I manage an organization where we receive donations from payers. Hence, there is no need to first create invoices and then create payments received against the invoices. What are the recommended best practices to do this in ZohoBooks?

    • Recording the deducted TDS on advance received from Customer (Zoho Books India)

      Hi, How can we record the tds that has been deducted by my customer for the advance that he has paid to me. 1) My customer has paid Rs 10000 to me as advance (Rs 9800 as cash and deducted Rs 200 as TDS). I am not able to record the tds that has been deducted

    • Changing Account Type in Chart of Accounts

      Does anyone know how to change/edit the account type for an Account name in Chart of Accounts. Zoho will not let me do this for some reason

    • Bulk bank rule creatioin

      Hi team, I am exploring Option to create a multiple bank rule. Could please suggest the option to implement this?

    • Zoho books aide

      Bonjour, je rencontre un problème avec Zoho Books. J’ai effectué une demande de support via l’interface prévue à cet effet, mais je n’ai jamais de retour. Je ne reçois ni email de confirmation, ni information concernant la prise en charge de ma demande,

    • Smart Data, Smarter Contracts — Ensuring Consistency Between Metadata and Documents

      In contract management, data accuracy is not just a nice-to-have—it is essential. A single mismatch between what your system shows and what is written in the contract can ripple into approval delays, compliance risks, and broken trust in your data. Imagine

    • Join Zoho Meeting only via Web browser and not with Zoho Meeting App

      Dear Zoho team, according to the documentation [1], Zoho Meeting only offers web view for Chrome and Firefox on a desktop. For other browsers and devices, participants can only join a Zoho Meaning with the Zoho Meeting App installed. This is a big hurdle

    • CRM gets location smart with the all new Map View: visualize records, locate records within any radius, and more

      Hello all, We've introduced a new way to work with location data in Zoho CRM: the Map View. Instead of scrolling through endless lists, your records now appear as pins on a map. Built on top of the all-new address field and powered by Mappls (MapMyIndia),

    • Manage control over Microsoft Office 365 integrations with profile-based sync permissions

      Greetings all, Previously, all users in Zoho CRM had access to enable Microsoft integrations (Calendar, Contacts, and Tasks) in their accounts, regardless of their profile type. Users with administrator profiles can now manage profile-based permissions

    • inability to use different primary address on invoice per location

      my company operates in two different locations with different email address. The problems then is the inability to edit the primary to suite the invoice for the second location.

    • AI Search and Record Retrieval Inside Zoho Creator – Is This Possible?

      Is it possible to integrate an AI assistant into Zoho Creator that can intelligently search, retrieve, and analyze records within the application’s forms and reports? Can AI access and query existing Creator data securely using Deluge or APIs to provide

    • I have a requirement to integrate Zoho Books with Zoho Projects at both project and task levels.

      Currently, when i create transactions in Zoho Books (Expenses, Invoices, Bills), we can only map them at the project level. However, our requirement is to: Map records at both project and task levels Sync these transactions back to Zoho Projects under

    • Scheduled AU Data Center Database Version Upgrade for Zoho Forms

      Dear Zoho Forms' users, We would like to update you on a scheduled AU Data Center database version upgrade for Zoho Forms. Find the schedule below: Migration window: Sunday, 22nd February 2026 12.00 AM to 12.30 AM AEDT This migration is a part of our

    • Cannot get code to work with v2.mergeAndStore!

      Please can someone help me pass subform items into a repeating mail merge table row using v2.mergeAndStore? I have a mail merge template created in Writer and stored in Workdrive. This template is referenced by a custom CRM function which merges all of

    • Hotmail is blocking the zoho mail IP

      Greetings, Since last Tuesday (5 days ago today) I wrote to Zoho support and I still haven't received a single response (Ticket ID: 2056917). Is this how you treat people who pay for your email service? I am making this public so that those who want to

    • Zoho Bookings and Survey Integration through Flow

      I am trying to set up flows where once an appointment is marked as completed in Zoho Bookings, the applicable survey form would be sent to the customer. Problem is, I cannot customise flows wherein if Consultation A is completed, Survey Form A would be

    • Zoho CRM Community Digest - December 2025 | Part 2

      Hello Everyone! During the final weeks of December, Zoho CRM introduced updates that not only enhanced product capabilities but also offered deeper guidance through Kaizen posts. This section highlights what was released and shared in the last two weeks

    • CRUD actions for Resources via API

      Hello, is it possible to perform CRUD actions through the API for Resources? We want to create a sync from Zoho CRM Car record to Bookings resources to create availabilities for Car bookings. For Test drives, not only the sales person needs to be available,

    • Kaizen #186 : Client Script Support for Subforms

      Hello everyone! Welcome back to another exciting Kaizen post on Client Script! In this edition, we’re taking a closer look at Client Script Support for Subforms with the help of the following scenario. " Zylker, a manufacturing company, uses the "Orders"

    • オンライン勉強会のお知らせ Zoho ワークアウト （2/19 参加無料）

      ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 2月開催のZoho ワークアウトについてお知らせします。 今回はZoomにて、オンライン開催します。 ▶︎参加登録はこちら（無料） https://us02web.zoom.us/meeting/register/6AyVUxp6QDmMQiDGXGkxPA ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目指すイベント「Zoho

    • doubts about customer happiness in zoho desk

      Good afternoon, Desk community. The reason for my message is that I have a question regarding the customer satisfaction surveys we can ask our clients to rate our service. I know that in Desk, you can activate Customer Happiness to send a survey to the

    • COQL API in JS Widget only pulling 200 records

      Hello! We've been building a custom homepage widget using the Zoho JS SDK, and it seems that this https://help.zwidgets.com/help/latest/ZOHO.CRM.API.html#.coql only allows 200 records. I thought the limit was 2000 for COQL queries, but am I mistaken?

    • Standard Description Field - Can I change label or add dd tooltip

      Is there a way fo you guys to allow the customer to change the label name for the description field in the customer portal when submitting tickets. Or at least allow us to add a tooltip to clarify what description we need from them. I know I can create my own separate multi line description field but if I do that, it doesn't have the nice toolbar with Bold, Italic, Underline, color, font, indent, etc. Can you please allow us to add a tooltip to the zoho standard description field?

    • Introducing parent-child ticketing in Zoho Desk [Early access]

      Hello Zoho Desk users! We have introduced the parent-child ticketing system to help customer service teams ensure efficient resolution of issues involving multiple, related tickets. You can now combine repetitive and interconnected tickets into parent-child

    • Compensation | Salary Packages - Hourly Wage Needed

      The US Bureau of Labor Statistics says 55.7% of all workers in the US are paid by the hour. I don't know how that compares to the rest of the world, but I would think that this alone would justify the need for having an hourly-based salary package option.

    • Customizing Helpcenter texts

      I’m customizing the Zoho Desk Help Center and I’d like to change the wording of the standard widgets – for example, the text in the “Submit Ticket” banner that appears in the footer, or other built-in widget labels and messages. So far, I haven’t found

    • Ability to Edit Ticket Subject when Splitting a Ticket

      Often someone will make an additional or new request within an existing ticket that requires we split the ticket. The annoying part is that the new ticket maintains the subject of the original ticket after the split so when the new ticket email notification

    • Automatically Update Form Attachment Service with Newly added Fields

      Hi, When I have a Form Setup and connected to a 3rd Party Service such as OneDrive for Form Attachments, when I later add a new Upload Field I have to remove and redo the entire 3rd Party Setup from scratch. This needs to be improved, such as when new

    • Next Page