Hello everybody!
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. Enhanced Field Limits in the SELECT Clause
You can now retrieve up to 500 fields in a single query. Previously, the limit was 50. This allows you to fetch more data in a single request, reducing the number of API calls and improving performance.
Sample query:
{ "select_query": "select Last_Name, Email, Phone, Industry, Revenue, ... (500 fields) from Leads where id is not null" } |
2. Enhanced Value Limits for IN and NOT IN Operators
The IN and NOT IN operators now support up to 100 values each, doubling the previous limit of 50. This enhancement allows for more comprehensive filtering in your queries. Users can now include a larger set of criteria in a single query, making it easier to retrieve your data without needing multiple queries.
Sample query for IN:
{ "select_query": "select Account_Name from Accounts where Industry in ('IT', 'Finance', 'Healthcare', ... (100 values))" }
|
Sample query for NOT IN:
{ "select_query": "select Account_Name from Accounts where Industry not in ('IT', 'Finance', 'Healthcare', ... (100 values))" } |
3. Custom View support in COQL API
No more manual filtering! Simply use a custom view ID directly in the
FROM clause. Retrieve custom view IDs using the
Custom View API and include them in your queries to retrieve specific-custom view data.
Unsupported Custom Views
- Custom View in Other Modules : The following custom views are not supported in any module.
- Shared By Me
- Co-owner
- Shared To Me
In the
Custom View API response for a module (e.g., Leads), search for the key
"system_name". If the value matches any of the unsupported custom views (Shared By Me, Co-owner, Shared To Me), that view is not supported.
Example : Shared By Me
Key and Value Structure | "system_name": "{module_name}SHAREDBYME" |
Example | "system_name": "LEADSSHAREDBYME" |
COQL supports CVID in the following format: FROM {module_API_name}#{CVID}.
See the sample query below for reference.
Sample query:
{ "select_query": "select Lead_Status from Leads#5725767000000087501 WHERE id is not null" } //CVID: 5725767000000087501 - This is the Custom View ID in the Leads module.
|
4. Retrieve Corresponding Field Metadata via COQL
Need to retrieve field details? You can
fetch both the required data and field metadata in a single query. Just add
"include_meta" key to your query to retrieve field metadata. This key fetches metadata for the fields specified only in the
SELECT column. Also, it eliminates making an additional
GET - Fields Metadata API call to know the field metadata and save API credits.
Sample query:
{ "select_query": "select Lead_Status, Phone from Leads where id is not null", "include_meta": [ "fields" ] } |
Ensure you have the ZohoCRM.settings.fields.READ scope to avoid an OAUTH_SCOPE_MISMATCH error.
Sample Response:
{ "data": [ { "Lead_Status": "Attempted to Contact", "Phone": "555-555-5555", "id": "5725767000000420480" }, { "Lead_Status": "Contact in Future", "Phone": "555-555-5555", "id": "5725767000000420481" }, . . . ], "fields": { "Lead_Status": { "webhook": true, "operation_type": { "web_update": true, "api_create": true, "web_create": true, "api_update": true }, "colour_code_enabled_by_system": false, "field_label": "Lead Status", "tooltip": null, "type": "used", "table_name": "CrmLeadDetails", "field_read_only": false, "required": false, "display_label": "Lead Status", "read_only": false, "association_details": null, "multi_module_lookup": {}, "id": "5725767000000002611", "created_time": null, "filterable": true, "visible": true, "refer_from_field": null "auto_number": {} } }, "info": { "count": 200, "more_records": true } }
|
5. Field Limit Restriction in the ORDER BY Clause
The fields limit has been restricted in the ORDER BY clause. You can specify up to 10 fields in the ORDER BY clause for more precise sorting. Previously, there was no limit. This restriction helps improve query performance.
sample query:
{ "select_query" : "select Last_Name from Leads where id is not null order by Lead_Source, Lead_Status, Email, ... (up to 10 fields)" } |
6. Duplicate fields restriction
Duplicate fields are restricted in ORDER BY, GROUP BY, and AGGREGATE clauses. That is, if the same field is specified multiple times within the same clause, leading to unnecessary repetition and potential errors. This ensures cleaner and more accurate query results. Previously, this restriction applied only to the SELECT column.
Bad query:
{ "select_query":"select id from Contacts where id is not null order by id,id" }
|
Duplicate aggregate fields in a query will result in a "DUPLICATE_DATA" error in the response.
7. Retrieve territory information
You can now retrieve territory information from a module by including the territory field API name in the SELECT column. This is useful to understand where your data has been distributed in various geographical locations.
Sample query 1 :
{ "select_query": "select Account_Name, Territories from Accounts where id is not null" } |
Sample query 2 :
{ "select_query" : "select Territories, Account_Name.Territories from Contacts where id is not null" } |
Sample response:
{
"data": [ { "Account_Name.Territories": [ { "name": "USA", "id": "5725767000000454003" } ], "id": "5725767000000420261", "Territories": [ { "name": "China", "id": "5725767000000454981" } ] }, . . . ], "info": { "count": 174, "more_records": false } }
|
Supported modules to retrieve territory details: Contacts, Accounts, Leads, and Deals.
Note:
The territory field API names differ by module:
- Accounts, Contacts, and Leads : Territories
- Deals : Territory
8. "Full_Name" retrieval based on user preferences
The
full name can be retrieved based on the current user's display name pattern, as set in their
preferences. This ensures that the full name format aligns with the user's settings.
The "Full_Name" field contains the concatenated values of the First_Name and Last_Name fields. This is a read-only field available only in the Leads and Contacts modules.
Sample query:
{ "select_query": "select Full_Name from Contacts where id is not null" } |
9. Support for Multiple Modules in "what_id"
A Multi-Module Lookup (MML) field is a special type of lookup field that allows you to establish a relationship between a record in one module and records from multiple other modules. Unlike a standard lookup field that references a single module, an MML field can point to multiple module types.
Currently, an MML field is available in the Appointments module. The Appointment_For field is an MML field that can reference multiple modules, such as Leads and Contacts.
Sample query:
{ "select_query": "select 'What_Id->Leads.Company','What_Id->Accounts.Account_Type' from Calls where id is not null" }
|
9.1 Support to query Multi-module Lookup (MML)'s inner field
You can now retrieve inner fields of linked modules within the MML field. This provides deeper insights into related fields data in a module.
For example, you can now query specific fields from a related module that is linked through a Multi-Module Lookup, making your data retrieval more precise and informative.
{ "select_query": "select 'Appointment_For->Contacts.Lead_Source' from Appointments__s where id is not null" } |
This query retrieves the
Lead Source field from the
Contacts module within the
Appointment_For MML field.
9.2 Support for Querying Multi-Module Lookup Module Name
Retrieve the module name associated with each record in a Multi-Module Lookup (MML) field. This helps in identifying the source module of the linked records, providing a better context for your data.
Sample query:
{ "select_query": "select Appointment_For.module.api_name, Appointment_For from Appointments__s where id is not null" } |
The
Appointment_For.module.api_name returns the module name (e.g., Contacts or Accounts) for each record linked in the
Appointment_For MML field.
10. Retrieving Consent-Related Information
You can now retrieve
consent-related information using the
Consents Lookup fields in a module. This is particularly useful for
GDPR compliance, ensuring you have consent records.
For more details, refer to the
Consent Management documentation. This is particularly useful for GDPR compliance, ensuring you have all necessary consent information.
Note:
- Once GDPR is enabled and configured for a module, a new Data Processing Basis Details field will be created in the respective module with the data type consent_lookup ("data_type": "consent_lookup").
- You can retrieve the Data Processing Basis Details field created in the module using the GET Fields Metadata API.
- To know all available fields in the Consents module, make another GET Fields Metadata API call.
Sample query:
{ "select_query": "select Data_Processing_Basis_Details.Contact_Through_Survey, Data_Processing_Basis_Details.Data_Processing_Basis from Leads where Data_Processing_Basis_Details is not null" }
|
11. Enhanced Lookup Field Response
Previously, when a lookup field was included in the SELECT column JOIN, only the lookup field’s ID was displayed. From V7, the corresponding display field value is also provided. Now, when you specify a lookup field in the SELECT column, the response includes the id of the Lookup record ID and the display field value of the lookup record.
This eliminates the need for an additional SELECT column JOIN to fetch the display value (e.g., Owner.last_name).
Sample query:
{ "select_query" : "select Owner from Accounts where id is not null" } |
Sample response
V6 | V7 |
{ "data": [ { "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420169" },
{ "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } | { "data": [ { "Owner": { "name": "Boyle", "id": "5725767000000411001" }, "id": "5725767000000420169" },
{ "Owner": { "name": "Kate", "id": "5725767000000411482" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } |
Note:
- If you query the Users lookup field in the SELECT column, the corresponding record’s last name (display field) will be displayed in the name key of the corresponding lookup JSON (e.g. Owner) response, from V7.
- Display field value will not be available for Consent Lookup and Multi-Module Lookup (MML) fields.
12. Increasing "Profile Image" field comparators
The Profile Image field now supports additional comparators.
V6 | V7 |
is null, is not null | is null, is not null, =, !=, in, not in, like, not like |
You can use a record image's ID to fetch related data. For example, if you only have a record image's ID and want to retrieve the respective record's last name, you can include the record image ID in your query.
Sample query:
{ "select_query" : "select Record_Image from Leads where Record_Image = 'siwz79472749456ff433f8b88d0a795ae9855' " } |
13. Enhanced "Rollup Summary" field comparators
The Rollup Summary field now supports additional comparators, making it easier to filter and analyze your data with more precision.
| V6 | V7 |
is null, is not null | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
Sample query:
{ "select_query" : "select Last_Name from Leads where Total_Calls between 5 and 10" }
|
13.1 Enhanced response for the Date and Datetime return types of the Rollup Summary
The Date and Datetime return types in the Rollup Summary field are now provided in ISO date and ISO datetime formats based on the user's time zone.
Sample query:
{ "select_query": "select Last_Note_Added from Leads where id is not null" } |
Response| V6 | V7 |
{ "data": [ { "Last_Note_Added": "1721302770000", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } | { "data": [
{ "Last_Note_Added": "2024-07-04T03:49:48-07:00", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } |
14. Enhanced LIMIT and OFFSET Value Limit
You can now retrieve up to 100,000 records using pagination with the LIMIT and OFFSET clauses. This significantly increases the amount of data you can handle in a single query, making it easier to work with large datasets. Previously, the maximum limit was 10,000. For the next set of records, add another criteria id > {1,00,000th record ID} with the "AND" operator, and go on to fetch the next 1,00,000 records.
Sample query:
{ "select_query" : "select Last_Name, Created_Time from Leads where (Last_Name is not null and id > 5725767000003160016)" } |
15. Enhanced JOIN Support Limit
Base Column JOIN
If a query contains any lookup_field.{any_field} in any clause other than the SELECT column such as ORDER BY, GROUP BY, or AGGREGATE columns, then it will be treated as Base JOIN. The Base JOIN support limit has been increased from two to five. This enhancement enables more complex queries across multiple modules, improving data retrieval flexibility.
Sample query:
{ "select_query" : "select id from Contacts where id is not null order by Owner.role, Created_By.profile" } |
Refer to the
Base JOIN section in the COQL API document for more details.
15.1 Introducing the SELECT Column JOIN
A new SELECT column JOIN has been introduced, with a maximum limit of 15.
Advantages of SELECT Column JOIN
- If a lookup field is included in the SELECT column and another lookup field is used in the Base column JOIN, they will be treated as separate JOINs. This ensures that the SELECT column JOIN does not affect the Base JOIN.
- Previously, when two lookup fields in the SELECT pointed to the same module (e.g., Users), they were treated as separate JOINs. From V7, they are now considered a single JOIN, optimizing query performance and reducing redundancy.
Sample query:
{ "select_query" : "select Owner.last_name, Created_By.role from Contacts where id is not null" } |
16. Subquery in COQL
A subquery support has been given in COQL. A subquery is a child query that is nested or embedded within a parent query i.e., outer query. Subqueries can be used as an alternative to JOINs in a query. Use subqueries when a JOIN field is only needed in the criteria, i.e., in the WHERE clause. The primary reason for using subqueries is to improve performance by saving time and memory.
Sample query:
{ "select_query" : "select Lead_Source,Email from Leads where Contacts in (select id from Contacts where Email=' patricia@mail.com')" } |
You can retrieve up to 100 records per subquery in a single query. A maximum of 5 subqueries can be queried in a parent query.
Refer to the
Subquery documentation for more details.
A detailed post on Subquery will be covered in an upcoming Kaizen post.
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.
Stay tuned for more insights in our upcoming Kaizen posts!
Recent Topics
How we cut CRM updates from ~20 minutes down to 2, our real workflow
Updating the Zoho CRM after every call used to be one of the biggest time sucks for our team. By the time you write your notes, clean them up, fill in the fields, and log everything properly… you’ve easily lost 15–20 minutes per call. We started experimenting
Add home page or dashboard in CRM customer portal
is it possible to add home page or dashboard in CRM customer portal?
User Tips: How to change the the label display name of a system defined field
Most users know how to change field label names via Settings > Modules & Fields but if you want to change the name of a system defined field you can’t as there is no “edit properties” option. However with a simple hack you can edit any system defined
Search not working!
I have items in my notebook tagged but when I search for a tag nothing comes up! Any fix for this?
Zoho CRM Community Digest - October 2025 | Part 1
Hello Everyone! Here's a quick recap of first two weeks of October! Product Updates: Zoho CRM Android App Update: Surveys, Blueprints, and Smarter Mobile Features! Zoho CRM’s Android app just got a useful upgrade. You can now share records, upload your
Automate onboarding emails with CRM Workflow and Accounts module
We’re a B2B SaaS company selling to public-sector organisations. Each organisation is stored as an Account in Zoho CRM, and each organisation typically has multiple associated Contacts. Our backend syncs product-usage data (setup status, user activity,
Using a CRM Client Script Button to create a Books Invoice
Hello, I need help handling error messages returned to my client script from a function. The scenario I have setup a client script button which is available from each Deal. This CS executes a crm function, which in turn creates an invoice based on the
Important update: Enhanced security measures for account operations in Zoho Cliq
Greetings from the Zoho Cliq team! We’d like to share an important security update that has an influence on some admin actions such as password reset, MFA reset, and MFA backup code generation. What’s changing? With our latest security enhancements, these
Sales Receipts Duplicating when I run reports why and how do we rectify this and any other report if this happens
find attached extract of my report
No Functional Autosave or Manual Save Button
Application : Zoho Notebook So I wanted to try Zoho Notebook(On Ubuntu) as an application, I installed the application and went solving my LeetCode problems visually(Drawing mode), at one point the app just stopped saving anything... Every time I tried
Enterprise subscription support
My organization sells subscription services to enterprise customers, which is a different model from the consumer subscription model that Zoho Billing has been designed to support and I beleve this capability should be added. An enterprise subscription
Issue with Creator's IF logic
Hi, I found the following code produces unexpected results: if(-1.0 < 0.0000000) { info "True"; } else { info "False"; } if(-1.0 < 0.000000) { info "True"; } else { info "False"; } The output returned is: False True However, the
Need option to send Package PDF in shipment email (Shipment PDF is missing Lot info)
Is there any way to automatically attach the Package PDF instead of (or alongside) the Shipment PDF in the notification emails? We really need this feature because the default Shipment PDF creates a blind spot for our customers. It does not display Batch/Lot
zoho creator view is not present in the workspace and blank reports
Hi Support, Users who have "write" permissions keep getting this error for all of our embedded reports all of a sudden. See screen shot below: Meanwhile, my developer permissions account sees a blank screen in view and edit mode as shown in the screenshots
Customize portal email template
Can i fetch only first name of the user in portal email template instead of the below code Hi ${User.FULL_NAME}
Can't we let users decide which options they'd like to add at embed widget?
It seems embed widget DOES NOT offer a feature, where users can choose options upon subscribing plans. What Zoho has instead, is that admins have to manually create plan with options. How come no one in Zoho dev team never raised issue about usability
Reupload and rename from one field to another field (file upload)
Hi Everyone, Sorry, i have question to use invoke url for rename and reupload attachments file to another field. Tested on development mode. Zoho C6. Refer to https://www.zoho.com/creator/help/api/v2/upload-file.html look my error notification. Does anyone
Printing Multi-Page Reports (PDF Export)
Hi, I am moving a report from Google's Looker Studio to Zoho Analytics and trying to reproduce the Looker page by page dashboard editing experience. With Google, what you see is what you get when you print to PDF. But I can't seem to create the same experience
Resume Harvester: New Enhancements for Faster Sourcing
We’re excited to share a set of enhancements to Resume Harvester that make sourcing faster and more flexible. These updates help you cut down on repetitive steps, manage auto searches more efficiently, and review candidate profiles with ease. Why we built
I NEED TO NUMBER TO TEXT NO HERE
=NUMBERTEXT NEEED
Error: View is not present in the workspace
When saving a dashboard, user receives a popup with the following error. "View is not present in the workspace" What does this mean or refer to? There is no further insight given.
Dear Zoho CEO: Business Growth is about how you prioritise!
All of us in business know that when you get your priorities right, your business grows. Zoho CRM and Zoho Books are excellent products, but sadly, Zoho Inventory continues to lag behind. Just this morning, I received yet another one-sided email about
Is there any way to send an Excel received by email to Dataprep?
Every day I receive an email alert with an Excel file that I want to process through a Dataprep pipeline. To do this, I need to: -Save the file to disk -Open the pipeline -Run the pipeline -Update the source -Several clicks to select and open the saved
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
Create and populate a record in an instant: Introducing zero-shot field prompting to Zia's ICR
A couple of months ago, we upgraded our in-house AI image detection and validation tool, Zia Vision, with intelligent character recognition (ICR). By training Zia with sample images, you could create and enrich CRM records with data extracted from standard
How to Prevent Users From Skipping LMS Videos in Zoho People
How to Prevent Users From Skipping LMS Videos in Zoho People Hello Zoho Developers, In this blog, we will quickly look at how you can stop users from skipping or fast-forwarding videos in Zoho People LMS. Zoho People provides a feature called Disable
[Integration Edition] Deluge Learning Series – Custom API with Deluge | November 2025
We’re excited to conclude this four-month Integration Edition of the Deluge Learning Series: Session 1 – Integrating Zoho Apps with Deluge Using Built-In Integration Tasks Session 2 – Integrating Zoho Apps with Deluge Using invokeURL and invokeAPI Session
Automate Backups
This is a feature request. Consider adding an auto backup feature. Where when you turn it on, it will auto backup on the 15-day schedule. For additional consideration, allow for the export of module data via API calls. Thank you for your consideration.
Tips for Organizing Workflows and Improving Team Coordination in Zoho
Hi everyone, I’m looking for some general advice on how different teams are organizing their daily work within Zoho’s apps. Our team recently expanded, and we’re trying to streamline how tasks, discussions, and documents are shared so everything stays
Pre-fill webforms in Recruit
I don't want to use the career site portal (as I have my own already), but I would like to direct users to the application forms for each role, from my website job pages. Is there a way to pre-fill fields in Recruit application forms, so that I only have
Prevent user from viewing all records?
I have a report that is meant to be used by vendors to view only the records that are assigned to them. All the vendor information is stored in a separate application, so I need to call a function to get the current user's ID (not the zoho user ID). The report settings criteria doesn't support using function calls, so instead I'm embedding the report in an html page like this: if (thisapp.Global.CurrentUserIs("Vendor")) { personID = common.getLoggedInPersonID(); query = "Assigned_Vendor.ID="
Zoho Site pages not displaying in iframes
I simply want to show a Zoho Site page inside an iframe on another non Zoho website. When testing this across many browsers, the iframe content simply does not appear. IE reports that the host does not allow their content to be displayed in iframes. Very disappointing. Is there a way around this please? Here is the URL of the page I would like to appear in an iframe. http://ips-properties-to-rent.zohosites.com
Zoho Inventory as connector in Zoho Creator
Hello, It doesn't appear that Zoho Inventory is one of the many built in connectors in Zoho Creator? I see that there are non-Zoho inventory applications that have built in connectors such as Cin7, which leads me to believe that I'm missing something
Send Zoho Forms Link using Zoho CRM Email Templates
I have set up Zoho Forms and CRM integration to pre-populate data from Zoho CRM to Zoho Forms. The setup is working fine. I have also created an email template in the Zoho CRM deals module to send Zoho forms links. So when I send an email using that template
My go to On Load Client Script - Fast, efficient, and works for ALL profiles; Hides everything except initial fields
This is my on Load client script that I use for Create pages. I use a modified version for Edit and Display pages which you can create yourself using the same basic structure that I will give you below. First up, the script. Below that will be an explanation
CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive
Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage files
Custom Search using HTM+CSS Snippet
Suppose I wanted to create my own list view using HTML and CSS snippets inside a Page with a custom search input at the top of the list (not the Search snippet). Without Javascript, is there a way to retrieve a user's entry from that search input and
Mass Update of Lookup Fields not possible
Hello List I've created a custom field for Leads and Contacts 'Current Campaign'. This is very Handy as I can filter leads and then related them to a campaign. Everything ready, but then I realized that mass update doesn't work for lookup fields... a
Zoho CRM Kiosk Upload Files
Hello all, We are trying out Kiosks at the moment to see where it can fit best in our business. We are still a bit off in the application but lets say we will sort this out. My question is the following - when I create a Kiosk I can add "File Upload"
Double opt-in notifications and customizable confirmation messages for your webforms
Dear CRM Community, We are excited to announce a major upgrade to our Webforms feature. You can now customize the confirmation message shown to your users who double opt-in from your webform and also customize your confirmation emails when they submit
Next Page