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 #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

    • DKIM cannot be enabled for the domain as no verified default selector present

      Hi Support Team, For Domain DKIM record trying to enable status. but showing error "DKIM cannot be enabled for the domain as no verified default selector present" So, please resolve the issue. Thank you.

    • Issue Connecting My Domain to Zoho Sites Despite Purchasing It from Zoho

      Hello, I am facing an issue connecting my domain to my website on Zoho Sites. Details of the issue: I purchased the domain directly from Zoho. I am already using the same domain successfully with Zoho Mail. However, when I try to assign this domain to

    • Insert auto number from main form into subform rows

      Hello. I'm trying to take from my main form "order number" which i have setup as an auto generated number into every line created in my subform. So when a row is created in my subform i want the "order number " from the main form to be inserted automatically.

    • Dark Mode - Font Colors Don't Work

      When editing a document in Dark Mode and selecting font colors, they don't show up on screen.  Viewing/editing the same document in Light Mode shows them just fine.

    • Integrate Bunq with ZOHO Bookes

      We are new users of ZOHO Books, and our bank (BUNQ, in the Netherlands) isn't listed on the bank integrations. Is there a way to handle this?

    • Access Denied

      I am iOS Developer and updating our clients project and shifted ZohoDeskPortalCore SDKs from cocoapods to SPM and changed few lines of code but now i am get access denied, the help center app is unavailable. please contact administrator.

    • Using Zoho Desk to support ISMS process

      Hi, I am evaluating using Zoho Desk for security incident management. This seems to be aligned with Zoho Desk purpose as its just another type of incident. However in security incident management, ideally I can link incidents (tickets) with a risk from

    • Bin Locations

      Dear all, I am wondering if someone has the ability to develop the bin locations option for zoho inventory (integrated with zoho books) Regards, Ryan

    • TaxJar vs Avalara

      Hi, I'm evaluating adoption of a sales-tax service for US based business. Anyone else have experience with TaxJar and Zoho Books? I am a Zoho One subscriber so anticipate needing to use Flow to make this work. It seems like Avalara are simply too expensive

    • How to check Leads with no Task (open activity)

      Hi everyone, I was wondering if there’s a way to view leads that don’t have any tasks assigned or open activities linked to them.

    • What can we do on our end to improve the Answer bot answers?

      Hi, I'm using the Answer bot card in the Codeless bot builder. I've input several questions and their answers in the FAQ section to feed the Answer bot. The text is all in French, as this is the language our customers communicate in. I've tried testing

    • Taxes for EU B2B Transactions

      Currently, ZC doesn't seem to have a procedure for validating VAT numbers of businesses purchasing in another EU state, and removing local VAT is valid.  This is essential for all inter EU B2B trade.

    • How to upload file to Connect using API?

      Hi there. I looked at the API documentation and nowhere did it mention how to use the API method to upload a file even though it is mentioned that it is possible to be done so. Please help.

    • Items Landed Cost and Profit?

      Hello, we recently went live with Zoho Inventory, and I have a question about the Landed Cost feature. The FAQ reads: "Tracking the landed cost helps determine the overall cost incurred in procuring the product. This, in turn, helps you to decide the

    • Group Tax in Service Line Items

      Hi FSM Team! I noticed that when you update a tax in the service line item the group tax is not showing up as an option. Let me know what can be done thank you!

    • FSM Improvement Idea - Show an Import button when there is no data

      I am setting up FSM for a client and I noticed that there is no option to import data, see screenshot below. Even when you click Create Contact there is only an option to Import from Zoho Invoice. It is only after you add at lease 1 record that the Import

    • Zoho FSM API Delete Record

      Hi FSM Team, It would be great if you could delete a record via API. Thank you,

    • OAUTH_SCOPE_MISMATCH Error for Marketing Automation APIs with CRM Plus Account

      I'm trying to integrate Marketing Automation journey triggering via API but getting OAUTH_SCOPE_MISMATCH errors. I need clarification on API access for CRM Plus users.

    • Access token generate from the refresh token not working for API

      Dear Sir/Madam, When I use my refresh token to obtain new access_token, that token return INVALID_TOKEN when the same API is called. I made sure that my api site is correct and match the auth web site. However the original access_token work fine.

    • Function #4: Schedule Customer Statements

      Regularly sending statements to customers is an imperative part of many business processes as it helps foster strong customer relationships and provides timely guidance on payments. While you can generate the statement of accounts and have it sent over

    • Limiting search or dependencies with an asterisk "*".

      I have a form with several dependency fields with options still developing for each field. Since these options were developing and not yet ready to be a selection in the field, I placed a filter for the dropdown field. In this filter, I selected fields

    • Improved RingCentral Integration

      We’d like to request an enhancement to the current RingCentral integration with Zoho. RingCentral now automatically generates call transcripts and AI-based call summaries (AI Notes) for each call, which are extremely helpful for support and sales teams.

    • Cross Data Center Support for 1:1 Chats with External Users

      Hello Zoho Cliq Team, We hope you're doing well. We appreciate the recent enhancement that enables cross data center collaboration in external channels, which has already improved communication across distributed teams. However, we’ve noticed that this

    • Support Bots and Automations in External Channels

      Hello Zoho Cliq Team, How are you? We actively use Zoho Cliq for collaboration, including with our external developers. For this purpose, external channels are a key tool since they work seamlessly within the same interface as all of our other channels

    • Answer Bot and Personalized Questions

      Hi there, I have the same problem using the SalesIQ Answer Bot and the Zoho Desk Answer Bot (which really need different names, to be honest, in order to avoid confusion...) Customers that visit our website ask questions in the form of "What do you do?"

    • Handling Greetings/Small Talk at the Beginning of a Zobot Conversation

      Hello everyone, I’m currently configuring a **Zobot** in Zoho SalesIQ and everything is working as expected, except for one specific scenario at the very beginning of the conversation. My target audience has the habit of starting with a **greeting or

    • Regex in Zoho Mail custom filters is not supported - but it works!

      I recently asked Zoho for help using regex in Zoho Mail custom filters and was told it was NOT supported. This was surprising (and frustrating) as regex in Zoho Mail certainly works, although it does have some quirks* To encourage others, here are 3 regex

    • Importing a new list into campaigns

      I'm in the middle of switching my email platform to campaigns. I have a list that I want to import, and it overlaps with my existing Zoho CRM list. The fields in my Zoho CTM are more robust. Will this new list that I upload into my campaigns overwrite

    • when I email a invoice how can i see it was sent and also were i can go to see all emails sent

      when I email a invoice how can i see it was sent and also were i can go to see all emails sent?

    • Export Invoices to XML file

      Namaste! ZOHO suite of Apps is awesome and we as Partner, would like to use and implement the app´s from the Financial suite like ZOHO Invoice, but, in Portugal, we can only use certified Invoice Software and for this reason, we need to develop/customize on top of ZOHO Invoice to create an XML file with specific information and after this, go to the government and certified the software. As soon as we have for example, ZOHO CRM integrated with ZOHO Invoice up and running, our business opportunities

    • Showing description in timesheet and timelogs.

      I am wondering if it’s possible in version 5 of Zoho People to have the description show by default or with a manipulation on the user’s part. Let me show you what I mean. As you can see this is the view for the users. Now if they want to see the full

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

    • CRM Blueprint Notification by Cliq

      Dear Zoho team, In Workflow, there is nofication by cliq, but in blueprint, there is no option as cliq notification. I think it is very convenient to get notified by Cliq , as there are multi modules in apps, but we will always check Cliqs

    • Zoho People Attendance Regularization – Wrong Total Hours Displayed

      While using Zoho People, I observed that the attendance regularization is showing wrong total hours when applied to past dates. For example, if a check-in is added at 10:00 AM and check-out at 6:00 PM for a previous date, the system sometimes calculates

    • Sync Contacts in iOS

      What does the "Sync Contacts" feature in the iOS Zoho Mail app do?

    • Live webinar: Craft the ideal sales pitch deck with Show

      Every great sale starts with a great story. And your pitch deck? That’s where the story takes shape. But too often, these presentations end up looking generic, overloaded with text, or lacking structure. The good news is, it's easier to fix than you think!

    • Project Statuses

      Hi All, We have projects that sometimes may not make it through to completion. As such, they were being marked as "Cancelled". I noticed that these projects still show as "Active" though which seems counter intuitive. In fact, the only way I can get them

    • 👋 Welcome to the Zoho MCP Community

      Hello all, glad to have you here! This is your space for everything AI agents, MCP tools, and intelligent business apps. This community is for you — developers, partners, creators, and businesses exploring how agents can transform work. Whether you’re

    • Suitability of Zoho One (Single User License) for Multi-State GST Compliance & Cost Analysis

      Hello Zoho Team, I am an e-commerce business owner selling on platforms like Amazon, Flipkart, and Meesho, and I'm currently using their fulfillment warehouses. I have two GSTIN registrations and am planning to register for an additional 2-3 to expand

    • DNS Manager

      Where Can I find my DNS manager so I can link this to click funnels or AWEBER

    • Next Page