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

    • Why can't we choose Fixed Asset account for Purchased Items? (eTims issue?)

      Hello, When the company purchase items not for sale and not supposed to be in the inventory stock, like equipment for operational use, there is no way to access the Fixed Asset accounts in the drop down list. Is that an eTims limitation again? Or something

    • "Track Inventory for this item" is forced checked by default for goods items (eTims issue?)

      Hello, Since connecting our Zoho books to eTims (Kenya) the "Track Inventory for this item" is forced checked by default (eTims issue?) in the Item creation page for any type of goods. So when purchasing anything that the company does not intend to sale,

    • Zoho CRM Functions: Redesigned Interface, Rich Analytics, and Multi-Language Support

      Hello everyone! We have given Functions in Zoho CRM a major overhaul with a new interface that makes it easier to build, organize, monitor, and troubleshoot your functions throughout their lifecycle. As part of this revamp, we have also introduced a unified

    • Can you import projects into Zoho Projects yet?

      I see some very old posts asking about importing project records into Zoho Projects. But I can't find anything up to date about the topic. Has this functionality been added? Importing tasks is helpful. But we do have a project where importing projects

    • reset password

      Hi I need to reset my password and need help

    • Is it possible to set a value to a field based on certain conditions

      Greetings Say i have a field that i want to set its value based on the value of another field.. for example if "number" field has value greater than 10, i want to set my field's value to X. Or if radio group had option 1 selected, then i want to set my

    • Native SMS and MMS Channel Support

      89% of US consumers say they prefer to communicate with businesses via SMS / MMS (aka "texting"). (link) Unfortunately, Zoho Desk does not currently support the most popular channel for consumer communications in the United States. For those of us in

    • Zoho CRM Layout Rules: Nine New Actions, Profile-Based Execution, and Interactive Preview

      Hello everyone, Availability: This feature is now available for customers in the JP and SA DCs. It is planned to be released for other customers in soon. We’re excited to announce powerful new enhancements to Layout Rules in Zoho CRM - a feature built

    • Global Sets for Multi-Select pick lists

      When is this feature coming to Zoho CRM? It would be very useful now we have got used to having it for the normal pick lists.

    • How to add line breaks in formula with Concat?

      I am trying to combine different fields with a formula field, but want to add line breaks.  I.e. the current code looks like:  Concat(${Contacts.Company_Name}, ' ' ,${Contacts.First_Name}, ' ' ,${Contacts.Last_Name}) And the result should be:  John Doe

    • Introducing spam detection for webforms: An additional layer of protection to keep your Zoho CRM clean and secure

      Greetings all, One of the most highly anticipated feature launches—Spam Detection in webforms—has finally arrived! Webforms are a vital tool for record generation, but they're also vulnerable to submissions from unauthenticated or malicious sources, which

    • Is Zoho Assist Secure Connect even practical for 1 person shop?

      I was gun ho with Zoho Assist to replace my existing Remote Access product I have been using but I need to implement MFA on each unattended connection. Right now each of these connections in my other product has a rotating token that I have enter off

    • Join the Zoho Desk Virtual Classroom Training (VCRT)

      Hello everyone, Have you heard about Zoho's Virtual Classroom Training (VCRT)? Zoho Desk's Virtual Classroom Training offers end-to-end training for Zoho Desk users. Join the VCRT and get hands-on guidance for setting up Zoho Desk and getting your business

    • Zoho CRM's revamped timeline view now extends to 3 years and support notes, filters, and email statuses

      Editions: All DCs: All Release plan: These enhancements are being rolled out to customers in a phased manner. They will be available to most organizations by the end of May. For organizations with huge volumes of data, they will be available by the end

    • Zoho Mail App Unable to connect to server

      Hi all, For the last two weeks, my Zoho Mail App on my iPhone which has worked fine up to this point has been unable to connect to the mail server. I can't access my emails even though I will get notification banners on the screen saying that they have been received. I can open Safari on my phone and then log in that way. This issue happens when I'm connected to wifi and on my phone data.  I've tried deleting the app several times and reinstalling, and have updated to the latest version. But it still

    • Specific ListView Canvas on Canvas Home Page Always Loads Most Recent ListView, Not the One Specified

      I had mentioned this to ZOHO, but I mainly wanted to see if others in the Community are also facing this problem. I created a Canvas ListView for a Custom Module, and then created a Canvas Home page (technically on a tab item, but I'm not sure if that

    • Add the Zoom Option to the Camera

      Hi, I use the ZOHO Forms Camera so I can manage metadata, compression, etc. but this doesnt have the zoom parameter activated, so when we have photos in a tight space and want to use 0.5 for example, we can't, can this be enabled please. Thanks Dan

    • Manage booking pages from your Android phone

      Hello everyone! We are happy to announce that users can now add and manage booking pages from their Bigin Android app. It is a multi-step form that users can create for purposes like booking appointments, scheduling calls or meetings, conducting webinars,

    • New UI - Color Preference Not Saving Permanently

      Small, not very urgent problem I'd like to share regarding the new UI theme color. I've changed my theme preference to the default blue color around 20 times by now. It always reverts to a green color theme. I've followed the instructions of changing

    • Automating Employee Birthday Notifications in Zoho Cliq

      Have you ever missed a birthday and felt like the office Grinch? Fear not, the Cliq Developer Platform has got your back! With Zoho Cliq's Schedulers, you can be the office party-cipant who never forgets a single cake, balloon, or awkward rendition of

    • Unbundle feature for composite items

      We receive composite items from our vendors and sell them either individually or create other composite items out of them. So, there is a lot of bundling and unbundling involved with our composite items. Previously, this feature was supported in form

    • Inventory Barcode Creation - Add Picture of Item

      Hi I am trying to set up bar code labels and include a picture of the item on the label - any idea on how to add that field to the barcode generator?

    • Gemini Action - Add "inineData" Support

      Thank you guys for adding a Gemini action. I like Gemini models for the "grunt work" of AI, as they have the cheapest tokens of the trustworthy providers One thing that's missing in the action, though, is the ability to pass file data. If you pass a base64-encoded

    • Protect backed up and exported data using password encryption

      Hello everyone, Protecting live data is crucial for any organization or product, as it contains sensitive customer information. However, protecting data does not end within the platform. Backup and export files often contain sensitive information and

    • Smarter holiday planning with yearly-specific Holiday Lists

      Hello everyone! Managing holidays and business hours is now easier and more efficient. Holiday Lists now support holidays that fall on different dates every year, while business hours now supports more than one holiday list. This helps businesses manage

    • Zoho Projects - Cloning a task does not trigger task workflow when created

      Hello! I have a Project where my team uses a set of tasks from a tasklist as templates, so we could simply clone it and drag it to another list in kanban view to avoid creating a new one from scratch. The process works well, but after cloning it the new

    • Best way to setup Inventory bin tracking for products with multiple boxes/crates

      Hi - we need some advice from the community on setting up Items in the Inventory for products with multiple crates. We have large products in our warehouse where the product is delivered as two large (double pallet) crates. We've setup the Items for these

    • Cliq iOS can't see shared screen

      Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? Regards

    • not able to access Zoho from home WIFI

      for some reasone i am not able to access Zoho on my laptop or my iphone while i am connected to my home Wifi, i am able to access these sites both on laptop as well as Iphone and associated apps on any other Wifi as well as when I am on my 4G connection

    • Five Guided Conversations Updates in Zoho Desk

      Hello everyone, Guided Conversations is getting updates that make it easier to create more inclusive customer experiences, retain important conversation context, customize your widget, keep track of changes, and connect with external services. Here are

    • zoho desk

      Hello, Did Zoho Desk have any issues today? Are tickets coming in late? I have an email account linked, and messages seem to be arriving with a delay—some email threads aren't coming through completely, and so on.

    • An important update for Zoho Cliq desktop users

      The latest version of the Zoho Cliq desktop app (v1.8.0) will no longer be supported on macOS 10.15 Catalina and earlier versions. This is because the framework we use (Electron) no longer supports some older macOS versions. If you’re using macOS 10.15

    • Simplify eSignatures: Automate document workflows with Zoho WorkDrive

      Hello everyone, We’re excited to invite you to our upcoming live webinar, where Zoho WorkDrive and Zoho Sign will come together to show you how to simplify and automate document workflows. From creating and collaborating on documents to approvals, e-signatures,

    • Function #7: Fetch a value from Deal in Zoho CRM to the related Estimate in Zoho Books

      We're here with another function for those using the integration with Zoho CRM. You know that potentials can be linked to transactions in Zoho Books. But if you're including additional details about a Deal (potential) in Zoho CRM and you want to inherit

    • Turn off workflow Applied pop-up

      hi We are new to Desk. I have a rule to set to "waiting for customer" when agent sends reply. This comes up every time. How to i turn off? Or am i seeing as admin.

    • Workflows being applied and the Large unwanted popup

      When a workflow is being applied do to an action, then the Agent is left with a large Window asking if they would like the see the changes this workflow did. Is there any way to disable this prompt from appearing?

    • Maintenance notice for Zoho CommunitySpaces US data centers on August 23, 2026

      Dear customers, We have scheduled a maintenance activity for Zoho CommunitySpaces in our United States (US) data centers on August 23, 2026, from 1:30 a.m. to 2:00 a.m. During this 30-minute maintenance window, Zoho CommunitySpaces will be temporarily

    • Knowledge base: The nitty-gritty of SEO tags

      A well-optimized knowledge base with great SEO can benefit your company by allowing customers to find help articles and support resources using search engines. This enables customers to quickly and efficiently find the information they need without direct

    • zoho desk: Can tickets be created from Microsoft Teams messages?

      I am trying to see if there is a way to integrate Microsoft teams with zohodesk. we have a few customers who prefer to communicate requests through Teams, and it would be great to create tickets when they do.

    • ´´Send Email Reply´´ action does not work when the message body contains placeholders.

      Hi Community, I am working on a workflow where the Customer contact is notified via email when their ticket status goes On Hold. To do that, I chose the Send Email Reply action, and the minute I add a placeholder, the workflow does not work and the ticket´s

    • Next Page