Hello all!!
Welcome back to another post in the Kaizen series!
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:
------------------------------------------------------------------------------------------------------------------------------------
6. How to retrieve Multi Select lookup field data using COQL?
Answer:
------------------------------------------------------------------------------------------------------------------------------------
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.
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:
------------------------------------------------------------------------------------------------------------------------------------
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:
------------------------------------------------------------------------------------------------------------------------------------
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:
------------------------------------------------------------------------------------------------------------------------------------
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.
------------------------------------------------------------------------------------------------------------------------------------
We trust that this post meets your needs and is helpful.
Stay tuned for more insights in our upcoming Kaizen posts!

Other FAQs in Kaizen
Cheers!!!
✨Happy 2025 ✨
Recent Topics
Sales IQ - Bot Builder - Forward to Operator Action Card Improvement
Hi team, It would be a great improvement to have an additional branch out of the Forward to Operator Action Card. I would like to allow 10 seconds for an operator to pick up the chat, if they don't or if they reject the chat I would like the Bot to continue
Email template for customer notification on ticket reply is not being used
I've set up an email template for notify contact up upon receiving a reply in ticket. When I send a reply to the ticket, it is not using that email template. It just sends a normal looking email. How do I make it so that the email template is being used?
Global Fields
Just like Global Sets for Picklists, we would like to have global fields for any kind of field. Three things that should be saved globally: 1. The Existence of the field 2. The Name and 3. Association with a module should be set up in a respective place
"In Zoho CRM, during the Blueprint transition to the QC stage, I want to make the 'Packing Proof' image field mandatory."
@Dr Saurabh Joshi @Haiku Technical Support @Ishwarya SG @Sparrow Hill President @Hugh Marshall "In Zoho CRM, during the Blueprint transition to the QC stage, I want to make the 'Packing Proof' image field mandatory."
how do i see a list of active End Users?
looking for a list of who i sent invitations to be an End User? who has signed up?
Filter by technical IDs that should not be displayed
Hello Zoho and Cumminity. I know I have already found similar requests elsewhere, but have not yet received a solution from Zoho. Therefore I would like to refresh the topic and hope that a solution can be found. I have reports in the Creator, which I
Modular cannot Edit in portals
I have a custom module in CRM. If I create in either CRM or portals, I can edit it in the CRM but I can't edit in the portal. Even if it is created in the portal it wont edit. Anyone know why? I've created a new module and it works fine but this one
Zoho Desk - Feature Request - Add more social channels on Community user profile
Hi Team, While updating my profile here I noticed that it is only possible to add Facebook and Twitter social links. 1. Please consider adding at least LinkedIn and if possible, other popular channels. 2. Please consider renaming Twitter field name to
Hide Admin Only settings
It would be nice if the setup menu items and settings weren't visible to non-admins. Seems like there is some confusion with users going through setup pages but not having permissions and getting an invalid permission error.
"In Zoho CRM, during the Blueprint transition to the image field mandatory and file upload field mandatory
I want image and file upload field want to be mandatory during the blueprint transition In the QC done Transtion Iwant to make QC done file upload and Case QR Sticker Mandatory In Pre delivery Stage i want to make Site ready file upload field mandagtory
Lookups from Standard Modules to Custom Modules
I have created an "External Contacts" Custom Module for adding Contacts who aren't directly associated with a Customer or Vendor but who are related to Orders by being a Site Contact, Job Contact, Warehouse Contact, etc for third party. How can I go about
Using Zobot with ChatGPT Assistant Function to Trigger API Call (e.g., Weather Info)
I am currently integrating Zobot with an OpenAI ChatGPT Assistant using function calling. The goal is to let the Assistant trigger specific API actions based on user queries — for example, fetching the current weather when a user asks, “What’s the weather
Train Zia answer bot on only part of Knowledge Base?
We are trialing Zia answer bot and hope to use it on the knowledge base to help our users find the information they are looking for. I have found how to train Zia on the entirety of our knowledge base. But is there a way to train it on only certain categories
Recurring Events Not Appearing in "My Events" and therefore not syncing with Google Apps
We use the Google Sync functionality for our events, and it appears to have been working fine except: I've created a set of recurring events that I noticed were missing from my Google Apps calendar. Upon further research, it appears this is occurring
How to identify a ticket merge through Webhooks?
The ticket merge functionality moves all Threads and Comments from one Ticket to another. I want to identify this action to correctly apply these changes on my application end. Currently, my application only receives a Ticket_Update notification for the
Native SMS Integration in Zoho Desk
I’d like to request the addition of native SMS integration within Zoho Desk. While email and chat are still widely used, SMS has become a critical channel for fast, effective customer support, especially for urgent or time-sensitive issues. At the moment,
need a third party to fix email authentication dns records
at my wit's end - zoho began giving me spf, dmarc, dkim errors two weeks ago fussed with it since and now it seems dkim is the only problem and when i added the dkim record with the key from zoho mail it still wont work tired of this, need someone who
Announcing Early Access to "Zoho CRM for Everyone" — A new and exciting update to Zoho CRM
We are delighted to announce an Early Access to Zoho CRM for Everyone— a truly democratic approach to managing a CRM, gift-wrapped in an exciting and intuitive user interface. Here, multiple teams across an organization can coordinate among each other
Share Projects with Vendor Zoho Projects Portal
I have a vendor/reseller of my services. They private label my services. My portal is branded. Can an individual project be "shared" or the data sync with another portal? I believe that this can be done with CRM.
Zoho Books - Sales Person Information
Hi Team, On Invoices, Quotes, etc... I can include the Sales Person, but it only shows their name and not their email or phone number. It would be great to have place on invoice templates where we can manage what sales person information should be shows
Feature Request – Support for Stripe Direct Debit for Canadian Customers in Zoho Books
I’d like to request support for Stripe Direct Debit as a payment option for Canadian customers within Zoho Books. Currently, while Stripe credit card payments are supported for Canadian businesses, there is no option to enable Direct Debit (ACH/EFT) through
Zoho Desk blank page
1. Click Access zoho desk on https://www.zoho.com/desk/ 2. It redirects to https://desk.zoho.com/agent?action=CreatePortal and the page is blank. Edge browser Version 131.0.2903.112 (Official build) (arm64) on MacOS
Timentry and Support Plan Relationship
Timentry and Support Plan Relationship A customer can buy multiple products and request different SLAs and support plans for each product. We can enter different support plans and define the credit. The scenario I want to happen; - To reduce the credits
Issue with ticket replies via Slack: '+' symbols replacing spaces in emails
Hello, support team! We're experiencing an issue when replying to tickets directly through Slack. When the reply is sent to the email, spaces are being replaced by '+' symbols. This makes the message harder to read and understand. Is there any solution
Allow 2 logos for Branding, one for Light Mode and one for Dark Mode?
Our logo has a lot of black text on it. If we leave the background transparent, per recommendation of Zoho, when a user is viewing a file and turns on dark mode, our logo is not really visible and looks really weird. It would be really great if we could
Zoho Creator Populate radio field with values with all the created rows subfor
I have Main Form where i have a lookup field where i get brewery names and the number of tanks as a multiline text field with a list of beer names Based Brewery selected and bbt_tanks number i create rows in the subform and now i want to populate list
Currency column showing $ symbol
Hello, I'm importing data from Zoho Projects to Zoho Analytics and I was wondering why "Budget amount" column is set in dollars even if the "Currency" column = EUR: Is there a way to get the budget amount as "EUR" + nnnnnn? Thank you
Android notifications not working
I've set push notifications to 'on' in ZohoMail for android settings but nothing doing. Can anyone help? I do use a VPN.
my clients are not receiving mails
Hi, My clients are not receiving my mails sent . may we know the reason My dns server and imap settings are perfect
Múltiple Deals when converting a Lead
Hello!!! I hope someone can help me figure out the best way to handle this scenario. I have a multi-select field named “Service” in the Leads module that captures either Service A, Service B, or both. When converting a lead, Zoho CRM currently creates
zoho mail and crm is very slow
I have recently employed Zoho in our organisation. Even after taking high speed internet, mail and CRM takes many minutes to even load. Its really slow and faces lot of downtime.
How to use if_case with expressions other than equals
I'm trying to define a formula column that implements logic like this case statement would: case when numfld1 is null then null when numfld2 > 0 then 100*numfld2 when numfld2 < 0 then numfld2 else 0.0 end In formula columns, the docs say you need to use
Zoho CRM's V8 APIs are here!
Hello everyone!!! We hope you are all doing well. Announcing Zoho CRM's V8 APIs! Packed with powerful new features to supercharge your developer experience. Let us take a look at what's new in V8 APIs: Get Related Records Count of a Record API: Ever wondered
Create global project dashboard for all users
Would like to be able to create a custom dashboard for projects with certain widgets that are default for all new projects. right now, I have to modify each project dashboard per project per user. This is not practical.
Zoho Error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details
Hello There, l tried to verify my domain (florindagoreti.com.br) and its shows this error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details. Screenshot Given Below - please check what went wrong. Thanks
What's New in Zoho Inventory | January - March 2025
Hello users, We are back with exciting new enhancements in Zoho Inventory to make managing your inventory smoother than ever! Check out the latest features for the first quarter of 2025. Watch out for this space for even more updates. Email Insights for
Inline images are not shown on iPhone
When I add an image inline it gets displayed on a Zoho's computer software or web browser, but not on Zoho's iPhone app - the image appears to be broken and cannot be copied neither saved. What's the problem with displaying images inline when reading
Kaizen #186 : Client Script Support for Subforms
Hello everyone! Welcome back to another exciting Kaizen post on Client Script! In this edition, we’re taking a closer look at Client Script Support for Subforms with the help of the following scenario. " Zylker, a manufacturing company, uses the "Orders"
Viewing Live data
Where can I see the live data that is sent from the device?
Canvas templates can now be shared with different CRM organizations
----------------------------------------Moderated on 14th February, 2023------------------------------------------- Dear all, This feature is now open for all users in all DCs. To learn more about importing and exporting canvas templates, read our help
Next Page