Kaizen #183 - COQL API's Enhancements in Zoho CRM API Version 7
Hello everybody!
Welcome back to another post in our Kaizen series.
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. 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
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" } |
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.
{ "select_query": "select Appointment_For.module.api_name, Appointment_For from Appointments__s where id is not null" } |
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.
Refer to the COQL document for more details.
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" } |
Refer to the JOINs in COQL Queries for more details on both the JOINs.
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!
Topic Participants
Subramanian K
Sticky Posts
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 areKaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achievedKaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI
Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over the
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.unable to send message reason 554 5.1.8 Email outgoing blocked
unable to send message reason 554 5.1.8 Email outgoing blockedI can't log in to my account on Thunderbird
I've just had to rebuild my PC (calamitous mess from Microsoft with Win10/Win 11 'upgrade' - they confirmed I had to start with a new build). I have used Zoho mail for years via Mozilla Thunderbird, but now I've had to download the latest version of TBird,Should I Use DMARC?
When I configure Zoho Mail's DMARC settings, it's mandatory to fill in the RUA and RUF (Aggregate notification email address*, Forensic notification email address*) addresses. When we enter an email address in these fields, we receive reports from theIntroducing Profile Summary: Faster Candidate Insights with Zia
We’re excited to launch Profile Summary, a powerful new feature in Zoho Recruit that transforms how you review candidate profiles. What used to take minutes of resume scanning can now be assessed in seconds—thanks to Zia. A Quick Example Say you’re hiringBooks API Receiving an Error that Doesn't Make Sense when Creating Credit Note - trying to use 'ignore_auto_number_generation' argument
Hello, I'm working on a newly created routine and I'm getting an error that doesn't make sense when trying to create a new Credit Note. Here is my POST request. Endpoint: https://www.zohoapis.com/books/v3/creditnotes?organization_id=########## Body: {How to move emails to Shared Mailbox?
Hello, I created a Shred Mailbox instead of using a distribution group. But I cannot move previous emails to certain shared mailbox. Is it possible move some emails from inbox to shared mailbox?Collaboration with free plan user.
Hello. Do both users need to be on Pro plan to collaborate? Or can a Pro user share a note with a free plan user with the ability to edit?Connection to other user
Zoho Cliq handles sharing of Custom OAuth Connections that require individual user logins.In arattai received message can't be deleted
The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a receivedAnalytics Portal
I have the "standard plan" and want to explore the portal option; I activated the 15-day trial but do not see the pricing for the add-on. How can I get the price under "Upgrade add-ons." Thanks RudyCan I filter a Lookup field to only show related records from another Lookup in Zoho CRM?
In our Leads module, we have two Lookup fields: Recruitment Agency – the agency that the lead came from Recruiter – the individual contact from that agency who sent us the lead Both fields link correctly to their respective records in CRM. However, ourZoho Books API invoice email bouncing with 'relaying-issues' error
I have waited over 30 days for zoho books uk to assist with the following and i have had no replies or tickets erronously closed. The service has been terrible - very unlike zoho! So i am raising this here hoping that a community member can assist: Hello,How do I filter contacts by account parameters?
Need to filter a contact view according to account parameter, eg account type. Without this filter users are overwhelmed with irrelevant contacts. Workaround is to create a custom 'Contact Type' field but this unbearable duplicity as the information alreadyZoho Writer to Zoho Sign Template Misalignment – Request for Community
We are currently facing a critical issue impacting our live business operations due to a misalignment problem between Zoho Writer templates and Zoho Sign output. Issue Summary: We have an automated workflow in Zoho CRM using Deluge scripts that generatesAccount blocked
Yesterday I got my Zeptomail account blocked due to too many hard bounces. My account is used exclusively for sending transactional emails (eg. your order has been shipped, a form has been filled, etc) and the sudden blocking impacted hundreds of websitesMigration Tool Download
Since apparently you have to ask for the migration tool to download (why?), I need the download for the migration tool for exchange servers. Please forward link, thanks.IF Formula
Hi, I'm having trouble setting up a custom formula in a field. Can you help me with it? The formula should do the following: If the created date is less or equal to 30 days the field should display a string New in pieline or Exisitng pipeline if aboveAutorespond to one specific sender
Is there any way to create a rule or filter that sends an automatic reply to messages from one specific email address? I really need to set up something like a "vacation response" but only for one "received from" address.View comprehensive logs of all actions in Custom Module History
Hello everyone, We are happy to announce our new feature, Detail View in Custom Module History, which enhances visibility, control and accountability over your records. Users can now access a detailed timeline of every action that has been performed onTrack ZohoForm Conversion using Postmessage event
Hi, I’ve been using a third-party lead tracking tool to capture leads from my website along with their source. Earlier, with the HubSpot form, the third-party script was able to detect the postMessage event that iframe forms typically send back to theEmail task creator when task is updated/marked complete
I am looking for a way to notify the creator of a task in zoho todo when - Task is updated Task is closed Comments entered 1 and 2 are critical, and I cannot find a zoho flow to do this. There is no way that as a manager I will know when someone has completedEmail Recall Feature In Zoho Mail Which Should Also Work For Outside Organisation Members
Add a feature to recall or undo sending an email within a configurable short time window (e.g., 30 seconds to 2 minutes) after hitting send, similar to Gmail’s undo send. Currently the sent email can not be recall If the recipient is not from within yourHow to implement calculating average value of averaged values?
Hi, I'm trying to implement a formula in my layout for a module that calculates the average of 2 averaged values, but I can't do so using the formula custom field. To calculate the 2 averaged values, I could use the formula custom field, but in the customZoho Recruit update published job boards
If we have a job thats already posted on several different job boards and we edit this job in zoho recruit (e.g. the description of the job), does this then automatically update this new information to all the published job boards? If not, how does theNarrative 14: The magic bazaar of Desk
Behind the scenes of a successful ticketing system: BTS Series Narrative 14: The magic bazaar of Desk Marketplace is a centralized platform where users can discover Zoho and third-party applications that can be integrated with their Desk account to facilitateCollections Management: #2 Late Payment Automation
"Sir, I'll process the payment by evening, please don't apply any late payment charges", the customer said while arranging papers on his desk. Sanjay stood there with a tired smile. He had already visited twice that week. Payment collection was one thing.Create a table in the layout section of modules
Dear Sirs, I would like to know whether is possible to create a table under new fields in layouts section of modules. In fact in this section there are single line, multi line, pick list, multi-select, date, formula etc. but I am unable to add a tableNimble enhancements to WhatsApp for Business integration in Zoho CRM: Enjoy context and clarity in business messaging
Dear Customers, We hope you're well! WhatsApp for business is a renowned business messaging platform that takes your business closer to your customers; it gives your business the power of personalized outreach. Using the WhatsApp for Business integrationPower of Automation :: Automatic Task Status Handling for Users
Hello Everyone, A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automateQuestion - why no way to input a 'tool description' and 'tree of 'tools'
Every business is different, with different business processes. To be truly useful Zoho MCP needs to have user editable tool descriptions (or ruleset) and a 'tool tree' so that the LLM is context aware when being used. For example, the tool descriptionDevelop Zoho Meeting as a Full Native Application (Not a Browser Wrapper)
Hello Zoho Meeting Team, Hope you are doing well. We would like to suggest an important improvement regarding the Zoho Meeting desktop application. At the moment, the Zoho Meeting app feels more like a mini browser window or an iframe that loads the webCan I create a custom function for chart of accounts?
I'm trying to add a custom function that should run whenever a record in the Chart of Accounts module is edited, but on the form for creating a new function, the module dropdown does not include the chart of accounts as an option.billing
hi, I am being billed $12/year, and I can't remember why. My User ID is 691273115 Thanks for your help, --Kitty PearlUnable to confirm Super Admin assignment — confirmation button not working
I’m trying to change the roles within my organization. I am currently a super admin and would like to add another user as a super admin. When I attempt to confirm the action, a screen appears asking for my password to verify my identity. However, whenAutomation#36: Auto-create time-entry after performing the Blueprint transition
Hello Everyone, This week’s edition focuses on configuring a custom function within Zoho Desk to streamline time tracking within the Blueprint. In this case, we create a custom field, and request the agent to enter the spending time within the singleFeature Request: Reviews
Any chance we could get a "Reviews" element to put on sites? It should be extremely simple to do. Basically just take the comments box and add an option for a star rating. At the very least, it would be nice to have more customization options for the comment box so the text could be changed from "Comments" to "Reviews". I've looked at a bunch of embeddable review widgets but nothing meets my needs so this would be a great feature to have added to the site builder.Can we handle a support like (incident management) project in Zoho Projects?
Hi, I have a new profile of a project whereby we provide "ticket" base support to a client. They have a request and ideally we would handle comms via a email exchange logged in Zoho. Today we use Zoho Projects for all out projects, which means that weTrying to show the actual Terms & Conditions on PDF
Hi, On Zoho forms I am trying to have the actual terms and conditions that the user needs to accept also show on the pdf that they receive after. Right now it only says "Agreed". Please help.Installing EMAIL Setup in New Domain
Respected Support team, I'm facing an issue with cloudflare in Pakistan, I want to setup Zoho Mail Setup but I Don't know how to enable Zoho mail setup without cloudflare. My Website https://stumbleguyzzapk.com/, https://fakservices.com/ is using CF,Next Page