FAQs on COQL API

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 #197: Frequently Asked Questions on GraphQL APIs

      🎊 Nearing 200th Kaizen Post – We want to hear from you! 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 #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.

    • Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

      Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

    • Kaizen #193: Creating different fields in Zoho CRM through API

      🎊 Nearing 200th Kaizen Post – We want to hear from you! 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.

    • Client Script | Update - Introducing Commands in Client Script!

      Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands

    • Recent Topics

    • FONT Sizing in Notebook

      Hi Kishore - What is the status of adding font sizing to the application? I have several things that I have pasted directly into Notebook and the fonts are HUGE! I would like the ability to highlight them and reduce the font to a legible size. Nothing

    • Can managers Upload documents to their direct rapports?

      Admin employees have the ability to upload documents to employees' files; however, managers do not have add/manage button - is it possible for managers to upload their direct reports' documents, such as absence documents or 121 documents. Is there something

    • Leave balance display for next year

      Is there a way to not have a rollover or not limit the leave balance depending on the date. For example an employee has 10 days leave balance and wants to apply for January leave in December. They cant because the rollover doesnt show the leave balance

    • Please add an “Auto-Apply Unused Credits” toggle

      Hello — please add a simple org-level option to automatically apply unused credits (credit notes, excess payments, retainers) to new invoices and/or bills. An ON/OFF toggle with choices “invoices”, “bills”, or “both” would save lots of manual work for

    • Zoho Books not working/loading

      Hi! I haven't been able to access/load Zoho Books for the past hours. I get a time out (and it is not due to my internet connection). Could you please check this asap? Thank you!

    • Custom Fields with Data Types for Expense and Payments Received in Zoho Books

      Hi all, We are glad to present to you, the option to create Custom Fields for the Expense and Payments received modules in Zoho Books. This also comes with an icing on top of it - Yes, the custom fields can now be created with different data types. Types like Text, Number, Decimal, Amount, Auto Number and Check Box are supported as of now. Rush to the gear icon at the top right corner, select 'More Settings', choose 'Preferences' in the left pane. Click the Expense/Payment preferences where you can

    • [Webinar] Automate sales and presales workflows with Writer

      Sales involves sharing a wide range of documents with customers across the presales, sales, and post-sales stages: NDAs, quotes, invoices, sales orders, and delivery paperwork. Generating and managing these documents manually slows down the overall sales

    • Zoho Cliq - Incident alert (Server outage - IN DC) | August 28

      We've received server down alerts and are currently investigating the issue (IN DC) to find the root cause. Our team is actively working to restore normal operations at the earliest. Status: Under investigation Start time: 09:44:21 AM IST Affected location:

    • Claude + MCP Server + Zoho CRM Integration – AI-Powered Sales Automation

      Hello Zoho Community 👋 I’m excited to share a recent integration we’ve worked on at OfficehubTech: ✅ Claude + MCP Server + Zoho CRM This integration connects Zoho CRM with Claude AI through our custom MCP Server, enabling intelligent AI-driven responses

    • How can I see content of system generated mails from zBooks?

      System generated mails for offers or invices appear in the mail tab of the designated customer. How can I view the content? It also doesn't appear in zMail sent folder.

    • Limitations on editing a message in Cliq

      Hi I've checked the documentations and there's no mention of how many times a message can be edited. When trying with code, I get various numbers such as ~1000 edits or so. Please mention if there's a limit on how many times one can change a message via

    • Problem with reports due to "Connected" items change - Yes this IS a problem

      Now that the change has been made to use "connected" items I can no longer run the reporting I need in CRM. I should be able to start with Deals as the parent, connect down to the Account (Account_Name) on the deal as the child, then to any child items

    • CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive

      Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files

    • Narrative 10: Assignment Rules - Streamlining Ticket Management

      Behind the scenes of a successful ticketing system: BTS Series Narrative 10: Assignment Rules - Streamlining Ticket Management In the complex world of customer support, a flood of incoming tickets can hit the help desk in seconds. Businesses must do more

    • Free webinar! Digitize recruitment and onboarding with Zoho Sign and Zoho Recruit

      Hello, Tired of being buried in onboarding paperwork? With the integration between Zoho Sign and Zoho Recruit, a powerful applicant tracking system, you can digitize and streamline the entire recruitment and onboarding process, all from one platform.

    • Custom form - Duplicate Value Present

      I am new to Zoho People. I create a new form on Zoho People quite simple. A date (by default the current date) and a radio button with 3 options (Yes, No, Not applicable) I defined the date as ‘No duplicate’ as only one entry by date is allowed. I added:

    • Zoho API connection issues

      Hi, Today at around 1930 GMT our application started experiencing intermittent timeouts from the Zoho API. These intermittent timeouts are not enabling our app to work properly. The API connection was working just fine before. HTTPSConnectionPool(host='www.zohoapis.com',

    • Query Regarding our Partnership between AIC-JKLU and Zoho

      Dear Zoho Team, I am writing to raise a concern on behalf of AIC-JKLU, one of Zoho’s incubator partners. Recently, our startups have been facing difficulties while trying to get themselves onboarded on Zoho through our dedicated partner link. Unfortunately,

    • Disputed Purchase Invoices

      We have recently moved to Zoho Books from Sage. In Sage we were able to post a purchase/vendor invoice but mark it as on dispute. This would usually be a pricing query or if something was damaged. It would show in their ledger, so we could agree their

    • Getting events in the future

      Hi I am trying to get events in the future by calling this API Endpoint https://www.zohoapis.eu/crm/v8/Events?fields=Event_Title,Created_By,Created_Time,Start_DateTime But that gives me all events in the database. How do I make a query that returns all

    • Created Date/Invalid Fields

      Since Saturday we have suddenly had issues with our webhooks and data retrieval from CRM. Specifically how Created Date is handled. It appears there was some sort of change within CRM that broke a lot of our code that has been in place for several years.

    • Zoho Inventory. Preventing Negative Stock in Sales Orders – Best Practices?

      Dear Zoho Inventory Community, We’re a small business using Zoho Inventory with a team of sales managers. Unfortunately, some employees occasionally overlook stock levels during order processing, leading to negative inventory issues. Is there a way to

    • Problem for EU users connecting Zoho CRM through Google Ads for Enhanced conversions

      Has anyone else experienced this problem when trying to connect Zoho CRM through Google Ads interface to setup enhanced conversions? Did you guys get it fixed somehow? The Problem: The current Google Ads integration is hardcoded to use Zoho's US authentication

    • integration zoho form - drive

      I integrated my form with Google Drive. The report of user submissions from the Google Form becomes a Google Sheets table. When I used Google Forms for the same task, the summary sheet adapted to the form. For example, if I added a new field to the form,

    • Revenue Management: #9 Revenue Recognition in Media & Publishing

      Media & Publishing industry has evolved in recent times. It offers subscriptions, bundles digital and print access, runs sponsored content, and sometimes even sells ad spaces. If you run a media or publishing business, you will always get into a situation

    • Zoho CRM Community Digest - July 2025 | Part 2:

      Hello, Everyone! We’re closing out July with a can’t-miss highlight: Zoholics Europe 2025! Happening from September to October, it’s your chance to level up your CRM skills, covering everything from automation and CPQ to dashboards and advanced workflows.

    • How can I trigger a flow action only once while updating contact?

      Hi, we have a trigger to merge&mail file when the field YYY is filled out. For this acion I used "Create or update module entry". But unfortunately we get tens of email on a day with this merged file, because the contact is being regularly updated. The

    • Copy a Record Template from one Form to another

      I have a Creator application with several forms.  I developed a record template for one of the reports/forms but want to use most of it for another of the form/report combinations in the application. Is there a way to copy the template (code or otherwise) to another form?

    • Tip of the Week #70 – Create common team signatures for your shared inboxes

      Did you know that a small detail, such as an email signature, can make a big difference in how your brand is perceived? One simple yet smart way to enhance your team’s communication is by creating common team signatures for your shared inboxes. Instead

    • Enhanced data export features: XLSX format, custom character encoding, and selective record export

      Greetings all, Here are a few enhancements related to exporting CRM data, including the ability to export data in XLSX file format now. The Export feature under Data Administration now offers new options that expand its flexibility and enable users to

    • Invalid value passed for file_name

      System generated file name does not send file anymore - what is the problem?

    • Tip #42 – How to manage data security with Privacy Settings – 'Insider Insights'

      Data privacy is a cornerstone of trust in remote support. Through Privacy Settings in Zoho Assist, you can set up how data is gathered, stored, and handled in your organization. These settings ensure compliance, data protection for sensitive details,

    • Zoho DataPrep and File Pattern configuration

      I'm using Zoho data prep to ingest data from One Drive into Zoho Analytics... The pipeline is super simple but I can't any way to get all the files that I need. Basically I need to bring all the files with a certain pattern and for that I'm using a regex

    • Custom Function for Estimates

      Hey everyone, I was wondering if there was a way to automate the Subject of an estimate whenever one is created or edited: * the green box using following infos: * Customer Name and Estimate Date. My Goal is to change the Subject to have this format "<MyFirm>-Estimate

    • Introducing Dark Mode / Light Mode : A New Look For Your CRM

      Hello Users, We are excited to announce a highly anticipated feature - the launch of Day, Night and Auto Mode implementation in Zoho CRM's NextGen user interface! This feature is designed to provide a visually appealing and comfortable experience for

    • Quick Item Search & Auto-suggestion for Invoices

      Hi Team, I am facing an issue while creating invoices in Zoho Books. Currently, I have to type the full item name in the correct sequence and spelling for it to appear. For example, my item name is: "Distemper Acri Silk Special White 10kg" If I type something

    • Ticketbai! en el Pais Vasco

      Hola a todos, En enero de 2.022 se va a implantar en el país vasco un nuevo sistema de facturación, denominado ticketbai!, ¿hay alguna previsión de realizar las adaptaciones en zoho books o zoho invoices? Ignoro la cantidad de clientes que tienen estas

    • Zoho CRM mobile app feature update: home page widgets, field tooltips and user image upload

      Hello everyone! Your business doesn't pause when you're on the move, and neither should your CRM. That's why in our latest update, we've introduced a few new features to make your mobile CRM experience smoother and more efficient. Let's take a quick look

    • Zoho CRM Plain Text Template: Line Breaks and Formatting Issue

      Hello, I'm following the instructions to create email templates in Zoho CRM, but I'm having a problem with the plain text version. https://help.zoho.com/portal/en/kb/zoho-sign/integrations/zoho-apps/zoho-crm/articles/zoho-crm-email-templates#Steps_to_create_a_custom_email_template

    • Optimizing Task Handling: Auto-Remove Recurrence for cancelled Tasks.

      Hello Everyone, A Custom function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:

    • Next Page