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

    • How to refresh a ticket view ?

      I am doing a widget where I send a rest api call to make a new draft to the ticket I am viewing. The issue is sometimes it refresh a ticket view and I can see inserted draft right away, but sometimes I do not see it even if it is inserted correctly and

    • Ugh! - Text Box (Single Line) Not Enough - Text Box (Multi-line) Unavailable in PDF!

      I provide services, I do not sell items. In each estimate I send I provide a customized job description. A two or three sentence summary of the job to be performed. I need to be able to include this job description on each estimate I send as it's a critical

    • Automatic Project Owner change

      Is there a way to change Project Owner automatically once a specific Milestone in a project is marked as completed. Different Teams are working on projects in our Org, they have their own Milestones to complete and so we transfer the project from team

    • customer data security

      We are exploring ways to enhance our within Zoho CRM. Our Goal: We want to fully integrate RingCentral with Zoho CRM to enable click-to-call functionality for our sales team. However, to comply with data privacy regulations and protect customer contact

    • Supervisor Rules - Zoho Desk

      Hi, I have set up a Supervisor Rule in Zoho Desk to send an email alert when a ticket has been on hold for 48 hours. Is there a way to change it so that the alert only sends once and not on an hourly basis? Thank you Laura

    • ResponseCode 421, 4.7.0 [TSS04] Messages from 136.143.188.51 temporarily deferred due to user complaints

      Had email bounce. Let me know if you can fix this. Thanks. Michael

    • Sync CRM inventory data with Zoho Books

      I just switched everything over to ZoHo books, but I am trying to find out why the CRM Estimates, Invoices, and Sales Orders created in ZoHo CRM are not then duplicated in ZoHo Books? I had Quickbooks before, and had to do everything twice, I thought

    • Track Zoho Campaign and Workflow sales impact

      I am attempting to measure the performance of our marketing workflows and campaigns by comparing the date each campaign was sent to a contact with the purchase date of the contact. For example, if Contact A was sent Email A on 9/1 and made a purchase

    • Automation #15: Automatically Adding Static Secondary Contacts

      Rockel is a top-tier client of Zylker traders. Marcus handles communications with Rockel and would like to add Terence, the CTO of Zylker traders to the email conversations. In this case, the emails coming from user address rockel.com should have Terence

    • Fuel up your sales with the Zoho SalesIQ + Bigin integration

      Hi everyone! We’re happy to bring you the all-new Zoho SalesIQ + Bigin integration. With this, every prospect from your website instantly becomes a contact in Bigin, complete with transcripts and follow-up tasks, so you never lose a lead again. Let's

    • Default Sorting on Related Lists

      Is it possible to set the default sorting options on the related lists. For example on the Contact Details view I have related lists for activities, emails, products cases, notes etc... currently: Activities 'created date' newest first Emails - 'created

    • New Zoho triggers Google Dangerous flag due toabnormal charcters

      Just signed up and doing my first email test. I sent it to my google email account but it got flagged as Dangerous" due abnormal characters. My DNS setup looks ok. Page snips attached Help Please Thanks, Rick DC PowerWorld

    • Top Bar Shifting issue still not fixed yet

      I mentioned in a previous ticket that on Android, the top bar shifts up when you view collections or when you're in the settings. That issue still hasn't been fixed yet. I don't wanna have to reinstall the app as I've noticed for some reason, reinstalling

    • Introducing Connected Records to bring business context to every aspect of your work in Zoho CRM for Everyone

      Hello Everyone, We are excited to unveil phase one of a powerful enhancement to CRM for Everyone - Connected Records, available only in CRM's Nextgen UI. With CRM for Everyone, businesses can onboard all customer-facing teams onto the CRM platform to

    • Triggering Zoho Flow on Workdrive File Label

      Right now Im trying to have a zoho flow trigger on the labeling/classification of a file in a folder. Looking at the trigger options they arent great for something like this. File event occurred is probably the most applicable, but the events it has arent

    • Is there a way to set Document Owner/Sender via the API

      When sending requests for zoho sign, it would seem zoho uses the id of the person that created the zoho api cred to determine the owner_id, is there a way to set a default for this?

    • SendMail to multiple recipients

      Hi, I'm trying to send an email to a list of recipients.  Right now the "to" field is directed to a string variable. (List variables won't work here). In the string variable, how can I make it work? trying "user@app.com;user2@app.com" or "user@app.com; user2@app.com" just failed to send the emails. Ravid

    • Populate drop down field from another form's subform

      Hello, I found how to do that, but not in case of a subform. I have a Product form that has a subform for unit and prices. A product might have more than one unit. For example, the product "Brocoli" can be sold in unit at 3$ or in box of 10 at 25 $. Both

    • Usar o Inventory ou módulo customizado no CRM para Gestão de Estoque ?

      Minha maior dor hoje em usar o zoho é a gestão do meu estoque. Sou uma empresa de varejo e essa gestão é fundamental pra mim. Obviamente preciso que esse estoque seja visível no CRM, Inicialmente fiz através de módulos personalizados no próprio Zoho CRM,

    • Signup forms behaviour : Same email & multiple submissions

      My use case is that I have a signup form (FormA) that I use in several places on my website, with a hidden field so I can see where the contact has been made from. I also have a couple of other signup forms (FormB and FormC) that slight differences. All

    • getting error in project users api

      Hello, I'm getting a "Given URL is wrong" error when trying to use the Zoho Projects V3 API endpoint for adding users to a project. The URL I'm using is https://projectsapi.zoho.com/api/v3/portal/{portalid}/projects/{projectid}/projectusers/ and it's

    • Email Reminders on Shared Calendars

      How do we turn off the setting that emails reminders to everyone who has accepted or declined a calendar invite? If 8 of us have been invited to the same meeting, we receive 8 notifications for every step of the process, from invitation to decision.

    • Change total display format in weekly time logs

      Hi! Would it be possible to display the total of the value entered in the weekly time log in the same format that the user input? This could be an option in the general settings -> display daily timesheet total in XX.XX format or XX:XX.

    • Problem with Submit Button Design

      I have made a template to apply to my forms and under the button controls, I have it set to "standard" and yet it's still filling the container. This is super frustrating and looks weird. Why do we not have full control over button size? How can I fix

    • In the Zoho Creator Customer Payment form i Have customer field on select of the field Data want to fetch from the invoice from based on the customer name In the Customer Payment form i Have subf

      In the Zoho Creator Customer Payment form i Have customer field on select of the field Data want to fetch from the invoice from based on the customer name In the Customer Payment form i Have subform update Invoice , there i have date field,Invoice number

    • Different Company Name for billing & shipping address

      We are using Zoho Books & Inventory for our Logistics and started to realize soon, that Zoho is not offering a dedicated field for a shipping address company name .. when we are creating carrier shipping labels, the Billing Address company name gets always

    • mask Customer phone number and agents cant see customer phone number

      Is there any way we can integrate Zoom Phone with Zoho CRM while ensuring that customer phone numbers remain masked? We need a solution where agents can make outbound calls but cannot see customer phone numbers. Please let us know if there is any solution

    • How to display historical ticket information of the total time spent in each status

      Hi All, Hoping someone can help me, as I am new to Zoho Analytics, and I am a little stuck. I am looking to create a bar chart that looks back over tickets raised in the previous month and displays how much time was spent in each status (With Customer,

    • How can I track which zoho users are actively using Zoho CRM

      I have several licenses of Zoho CRM. We now need to add a new user. I could purchase a new license, but before I do, I would like to see if any of our existing users are not actively using the license assigned to them. How can I determine the activity

    • Zoho Projects iOS app update: Global Web Tabs support

      Hello everyone! In the latest version(v3.10.10) of the Zoho Projects app update, we have brought in support for Global Web Tabs. You can now access the web tabs across all the projects from the Home module of the app. Please update the app to the latest

    • Zoho Community Weekend Maintenance: 13–15 Sep 2025

      Hi everyone, We wanted to give you a heads-up that Zoho Community will undergo scheduled maintenance this weekend. During this period, some community features will be temporarily unavailable, while others will be in read-only mode. Maintenance Window:

    • Agent Performance Report

      From data to decisions: A deep dive into ticketing system reports An agent performance report in a ticketing system provides a comprehensive view of how support agents manage customer tickets. It measures efficiency and quality by tracking key performance

    • Show both Vendor and Customers in contact statement

      Dear Sir, some companies like us working with companies as Vendor and Customers too !!! it mean we send invoice and also receive bill from them , so we need our all amount in one place , but in contact statement , is separate it as Vendor and Customer, 

    • Pourquoi dans zohobooks version gratuite on ne peut ajouter notre stock d'ouverture??

      Pourquoi dans zohobooks version gratuite on ne peut ajouter notre stock d'ouverture ??

    • How can I adjust column width in Zoho Books?

      One issue I keep running into is as I show or hide columns in reports, the column widths get weird. Some columns have text cut off while others can take a fourth of the page for just a few characters. I checked report layout guides and my settings, but

    • Invalid value passed for file_name

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

    • 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

    • This domain is not allowed to add. Please contact support-as@zohocorp.com for further details

      I am trying to setup the free version of Zoho Mail. When I tried to add my domain, theselfreunion.com I got the error message that is the subject of this Topic. I've read your other community forum topics, and this is NOT a free domain. So what is the

    • Search in module lists has detiorated

      Every module has a problem with the search function :-/

    • YouTube Live #1: AI-powered agreement management with Zia and Zoho Sign

      Hi there! We're excited to announce Zoho Sign’s first YouTube live series, where you can catch the latest updates and interact with our Zoho Sign experts, pose questions, and discover lesser-known features. We're starting off by riding the AI wave in

    • Next Page