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

    • Emails Failing with “Relaying Issues – Mail Sending Blocked” in ZeptoMail

      Hello ZeptoMail Support Team, We are facing an email delivery issue in our ZeptoMail account where emails are failing with the status “Process failed” and the reason “Relaying issues – Mail sending blocked.” Issue Details Agent Name: mail_agent_iwwa From

    • How does Zoho handle support operations for our EU users?

      Dear Zoho users, We have some news and reminders about how we handle support operations for Zoho users in the EU Data Center. This is part of our broader commitment to ensuring GDPR compliance and providing secure data handling for all users. We'd like

    • Layout Adjustment.

      Hello Latha, Hope you are doing well. I wanted to check with you about adjusting the layout columns in the Service Report from the backend, as currently there is no capability for us to customize the layout from our end. Below is the output that I am

    • Anyone in Australia using Zoho Books AND has their account with NAB?

      Hi I have an account with both NAB and Suncorp. Suncorp transaction come in the next day however NAB transactions take 4-5 business days to appear. eg: A deposit made today in my Suncorp will be imported into Zoho tomorrow. A deposit made today to the NAB account will be imported maybe Saturday (Friday overnight). I have contacted both Zoho and NAB but noone seems to know why. I was just wondering if anyone else in Australia uses NAB and has this issue (or doesn't) maybe we could compare notes and

    • Zoho Often Lag

      Desk Team, We've noticed that Desk, Creator & other apps seems to lag more these days. We often refresh multiple times.

    • Importing into the 'file upload' field

      Can you import attachments into the file upload field. I would expect it to work the same way as attachments do, But can't seem to get it to work. Moderation Update: The option to import into File Upload and Image Upload fields has been supported. Read

    • Layout Rules Don't Apply To Blueprints

      Hi Zoho the conditional layout rules for fields and making fields required don't work well with with Blueprints if those same fields are called DURING a Blueprint. Example. I have field A that is used in layout rule. If value of field A is "1" it is supposed to show and make required field B. If the value to field A is "2" it is supposed to show and make required field C. Now I have a Blueprint that says when last stage moves to "Closed," during the transition, the agent must fill out field A. Now

    • Why is the ability Customize Calls module so limited?

      Why can't I add additional sections? why can't I add other field types than the very limited subset that zoho allows? Why can I only add fields to the outbound/inbound call sections and not to the Call Information section?

    • Zoho Desk blank screen

      opened a ticket from my email, zoho desk comes up blank, nothing loads. our receptionist also gets the same thing under her login on her computer. our sales rep also gets same thing on zoho desk at his home on a different computer. I tried clearing cache/history/cookies,

    • Optimum CRM setup for new B2B business

      Can some advise the most common way to setup Zoho CRM to handle sales for a B2B company? Specifically in how to handle inbound/outbound emails. I have spent hours researching online and can't seem to find an accepted approach, or even a tutorial. I have

    • filter on sheets mobile (iOS, iPadOS) does not work

      I have this issue on every spreadsheet, whether imported or created natively in Zoho Sheets. I can use on desktop without issue: desktop screenshot on iOS the filter dropdowns are not there and there is no way to add them mobile iPadOS looks the same

    • Canva Integration

      Hello! As many marketing departments are streamlining their teams, many have begun utilizing Canva for all design mockups and approvals prior to its integration into Marketing automation software. While Zoho Social has this integration already accomplished,

    • Can't edit Segments

      Happening with 2 different Zoho One environments, in different browsers. Please fix.

    • Error 554 Email Outgoing Error

      Our email address (info@tvbhouston) was recently migrated to a new domain host on WordPress, and our SSL certificate is still pending. We would like to continue using your service, but we are currently unable to send emails. Could you please re-enable

    • Transfer ownership of files and folders in My Folders

      People work together as a team to achieve organizational goals and objectives. In an organization, there may be situations when someone leaves unexpectedly or is no longer available. This can put their team in a difficult position, especially if there

    • Consumer Financing

      Does Zoho currently have a payment gateway (such as Stripe, Square, etc) which offers financing for customers? So, let's say the estimate we give the customer is greater than what they can afford at the time, but we can sell the service now, letting them

    • Subform edits don't appear in parent record timeline?

      Is it possible to have subform edits (like add row/delete row) appear in the Timeline for parent records? A user can edit a record, only edit the subform, and it doesn't appear in the timeline. Is there a workaround or way that we can show when a user

    • Zoho CRM - Option to create Follow-Up Task

      When completing a Zoho CRM Task, it would be very helpful if there was an option to "Complete and Create Follow-Up Task" in the pop-up which appears. It could clone the task you are closing and then show it on the screen in edit mode, all the user would

    • Deluge function to copy parent record file upload field to child record file upload field

      I'm stuck trying to write a deluge function that is triggered via automation in child record "Appointments," confirms if a file is in file upload "Report" field of parent "Contacts" record via Contacts lookup field "Contact_Name". If no file is in parent

    • Make panel configuration interface wider

      Hi there, The same way you changed the custom function editor's interface wider, it would be nice to be able to edit panels in pages using the full width of the screen rather than the currently max-width: 1368px. Is there a reason for having the configuration panel not taking the full width? Its impossible at this width to edit panels that have a lot of elements. Please change it to 100% so we can better edit the layouts. Thanks! B.

    • Image Compression Options

      Much better if we have level of options to compress the image [20%, 40%...] We are dealing with service reports daily that has before and after photos (image field)- the file size too large and one thing, the current limit is 10mb or 15mb for report

    • How to hide or archive a blog post temporarily in Zoho commerce website builder?

      I would like to temporarily hide or archive a blog post in zoho commerce website builder so that it doesnt appear on my website till I enable it again. I tried to look for this option but could not find it.  It only allows me to permanently delete a blog

    • How to mix different types of inputs (such as dropdown list and textbox)

      Hi, I'm creating a form called "Room Reservations" for a company. I created a "table" using "Matrix Choice". I created "Room 1", "Room 2" and "Room 3" with the "Questions". I would then like to create two columns with the "Answers", one called "Department"

    • How to Convert NSF to PST Format Effortlessly? - SYSessential

      It is highly recommended to obtain the error-free solution of the SYSessential NSF to PST converter to convert NSF files from Lotus Notes. Using this professional software, it becomes easier to convert all NSF database items, including emails, journals,

    • Zoho Commerce - Poor Features Set for Blogging

      Hi Zoho Commerce team, I'm sure you will have noticed that I have been asking many questions about the Blogs feature in Commerce. I thought that it would be useful if I share my feedback in a constructive way, to highlight the areas which I feel need

    • Pass shipping info to payment gateway Zoho Books to Authorize.net

      For some reason the integration from Zoho books to Authorize.net does not pass the shipping address. Authorize.net is ready to receive it, but zoho books does not send it

    • Massive Zoho Books failure

      We have not received any communication or notification from Zoho, but we have detected that Zoho Books is not working for all our users. We cannot access or use Zoho Books. This is critical. We are trying to contact Zoho on the Spain telephone number,

    • Does the Customer “Company Name” field appear anywhere in the Zoho Books UI outside of PDFs?

      Hi everyone, I’m trying to understand how the Company Name field is actually used in Zoho Books. There is a Company Name field on the customer record, but when viewing transactions like a Sales Order in the normal UI (non-PDF view), that field doesn’t

    • Email outbox is now available in the sandbox

      Hello all! Testing emails without visibility has always been a blind spot in the sandbox. With the new Outbox, that gap is closed. You can now view and verify every email triggered from your sandbox, whether it’s through workflows, approvals, or mass

    • Looking For Recruit Developer

      Hi everyone, I am looking for a Zoho Certified Developer to assist with a development project for MetalXpert. We are building a software system designed to bridge the gap between a candidate mobile app and an employer web portal using Zoho Recruit as

    • sales IQ issue on website

      i integrated the zoho sales IQ code on the website but it is comming in distroted form i am sharing the screenshot below the website is bulit in wix platform

    • I need to know the IP address of ZOHO CRM.

      The link below is the IP address for Analytics, do you have CRM's? IP address for Analytics I would like to know the IP address of ZOHO CRM to allow communication as the API server I am developing is also run from CRM. Moderation Update: The post below

    • Important Update: Google Ads & YouTube Ads API Migration

      To maintain platform performance and align with Google's newest requirements, we are updating the Google Ads and YouTube Ads integrations by migrating from API v19 to the newer v22, before the official deprecation of v19 on February 11, 2026. Reference:

    • Blocklist candidates in Zoho Recruit

      We’re introducing Block Candidate, which helps recruiters to permanently restrict a candidate from applying to current/future job openings. Once the candidate is blocked, they will no longer be able to participate in the recruitment process. This will

    • Admin asked me for Backend Details when I wanted to verify my ZeptoMail Account

      Please provide the backend details where you will be adding the SMTP/API information of ZeptoMail Who knows what this means?

    • Zoho Desk - Upsert Ticket

      Hi Desk Team, It is common to request more information from end-users. Using forms is a great way to ensure all the required information is collected. It would be great if there were an "upsert" option on the Zoho Form -> Zoho Desk integration which would

    • Client Side Scripts for Meetings Module

      Will zoho please add client side scripting support to the meetings module? Our workflow requires most meeting details have a specific format to work with other software we have. So we rely on a custom function to auto fill certain things. We currently

    • Introducing Multiple Sandbox Types and Support for Module's Data Population

      Register here for the upcoming Focus Group webinar on Multiple Sandbox | Help documentation to learn more about the new enhancements Hello everyone,  Sandbox in CRM is a testing environment for users to create and test new configurations like workflow

    • Creator Offline

      We had online access setup and working on our iphones. We have just set it up on an 'Android Tablet' and it is not downloading all the images? We use it to show customers our catalogue. Any ideas. Offline components all setup on both devices

    • Enable or disable any Field Rule!

      Hello Zoho Forms Community, We are excited to announce a powerful new enhancement to Field Rules that gives you greater control and flexibility in managing your form logic! Previously, if you wanted to temporarily deactivate a field rule, you had two

    • Next Page