Kaizen 81 - COQL API - Part II
Hello everyone!
Welcome to another week of Kaizen!
In continuation to last week's post on COQL API, we will discuss the rest of the field types and their supported operators with detailed examples in this post.
Supported field types and Operators
Please refer to the COQL API - Part I Kaizen post for the first three field types and their supported operators.
4) Boolean
The supported operator for Boolean field is =. You should provide the value as either true or false.
Sample Query
{ "select_query" : "select Product_Name, Product_Code from Products where Product_Active = 'false' limit 2" } |
This sample query selects the Product_Name and Product_Code fields from the Products module where Product_Active is false, and limits the result to only 2 records.
Sample Response:
{ "data": [ { "Product_Code": "SR-001", "Product_Name": "Sigma", "id": "4876876000001036109" }, { "Product_Code": "SR-003", "Product_Name": "Alpha", "id": "4876876000001039006" } ], "info": { "count": 2, "more_records": false } } |
what_Id support in COQL
=, !=, in, not in, is null, is not null are the supported operators. what_Id support is extended only to Tasks, Calls and Events. The what_Id field could be a lead or a contact.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
This sample query queries for the last name of leads associated with tasks where the lead ID is either '4876876000000900134' or '4876678000000900987', and the lead's industry is not 'ERP'.
Sample Response:
{ "data": [ { "What_Id->Leads.Last_Name": "Perry", "id": "4876876000001103044" } ], "info": { "count": 1, "more_records": false } } |
Roles and Profiles support in COQL
COQL supports roles and profiles lookup fields, allowing users to retrieve the owner's profile details such as ID, name etc, and the owner's role details using COQL queries. The supported comparators are =, !=, in, not in, is null, is not null.
Sample Query
{ "select_query": "select Owner.role.id,Owner.role.name,Owner.role.reporting_to, Owner.role.share_data_with_peers, Owner.role.description, Owner.profile.id from Leads where Last_Name is not null LIMIT 1" } |
This query fetches the Profile details and Role details of the owner of one record from the Leads module.
Sample Response:
{ "data": [ { "Owner.role.reporting_to": null, "Owner.profile.id": "4876876000000026011", "Owner.role.name": "CEO", "Owner.role.description": "crm.security.role.admin.desc", "Owner.role.share_data_with_peers": true, "Owner.role.id": "4876876000000026005", "id": "4876876000000333403" } ], "info": { "count": 1, "more_records": true } } |
Alias support
Supported operators : =, !=, in, not in, is null, is not null
Alias support in COQL allows users to simplify complex SELECT field names by giving them custom names or aliases. Using aliases in the SELECT column and ORDER BY class can make column names easier to understand and remember. For instance, What_Id->Leads.Last_Name from our previous example can be given an alias in the SELECT query as follows.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' AS 'Lead Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
Sample Response:
{ "data": [ { "id": "4876876000001103044", "Lead Name": "Perry" } ], "info": { "count": 1, "more_records": false } } |
If you compare this response with the sample response in the what_Id section above, you can see that the key 'What_Id->Leads.Last_Name' is replaced with the Alias 'Lead Name' in here.
Aggregate Function Support in COQL
Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions include SUM, MIN, MAX, AVG, and COUNT.
1. SUM
Use this function to sum up the values of an aggregate field in a module.
Sample Query 1
{ "select_query":"select SUM(Grand_Total) from Quotes WHERE Quote_Stage = 'Draft' " } |
This Query uses the SUM function to calculate the total value of the Grand_Total field for all quotes in the Draft stage in the Quotes module.
Sample Response:
{ "data": [ { "SUM(Grand_Total)": 1073660 } ], "info": { "count": 1, "more_records": false } } |
Sample Query 2
{ "select_query": "select SUM(Grand_Total), Account_Name.Account_Name AS 'Account Name', Billing_Country from Invoices where ((Status != 'Paid') and ((Billing_Country ='USA') OR (Billing_Country = 'Canada'))) GROUP BY Account_Name.Account_Name, Billing_Country ORDER BY 'Account Name' ASC" } |
This query retrieves the total SUM of the Grand_Total field, along with the Account_Name and Billing_Country from the Invoices module for those records whose Status field is not equal to 'Paid' and the Billing_Country field is either 'USA' or 'Canada'. The results are grouped by the Account_Name and Billing_Country fields and ordered in ascending order based on the 'Account Name' field.
Note that the 'Account Name' field is specified using the AS keyword to provide an alias for the Account_Name.Account_Name field. We have used the alias for the ORDER BY clause.
Sample Response:
{ "data": [ { "Account Name": "SR Systems", "Billing_Country": "USA", "SUM(Grand_Total)": 2430000 }, { "Account Name": "SR Systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 60000 }, { "Account Name": "Zylker systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 378000 } ], "info": { "count": 3, "more_records": false } } |
The response JSON contains the SUM of the Grand_Total fields, Account Name and Billing_Country for each unique combination of Account Name and Billing Country fields that satisfies the filter criteria specified in the query.
2. MAX
Use this aggregate function to find the largest value of a field in a module.
Sample Query
{ "select_query": "select MAX(Amount) AS 'Maximum Deal Amount', Account_Name.Account_Name from Deals where ((Stage = 'Closed Won') AND (Type = 'Existing Business')) Group by Account_Name.Account_Name" } |
This Query selects the maximum Deal Amount for each account from the Deals module where the stage is Closed Won and the type is Existing Business.
Sample Response:
{ "data": [ { "Maximum Deal Amount": 6750000, "Account_Name.Account_Name": "Benton" }, { "Maximum Deal Amount": null, "Account_Name.Account_Name": "King" }, { "Maximum Deal Amount": 945000, "Account_Name.Account_Name": "Morlong Associates" } ], "info": { "count": 3, "more_records": false } } |
3. MIN
Use this aggregate function to find the smallest value of a field in a module. For example, you can use this function to find the Lowest Annual Revenue in the Leads module.
Sample Query: 1:
{ "select_query": "select MIN(Annual_Revenue) from Leads where Annual_Revenue is not null" } |
Sample Response:
{ "data": [ { "MIN(Annual_Revenue)": 348.3 } ], "info": { "count": 1, "more_records": false } } |
4. AVG
Use this aggregate function to find the average value of a field in a module.
Sample Query
{ "select_query": "SELECT AVG(Amount) AS 'Average Deal Amount' FROM Deals WHERE Type = 'New Business'" } |
This query calculates the average deal amount for all new business deals in the "Deals" module.
Sample Response:
{ "data": [ { "Average Deal Amount": 877366.8359999999 } ], "info": { "count": 1, "more_records": false } } |
5. COUNT
Use this aggregate function to count the number of records in a module satisfying a certain criteria. You can use this aggregate function only for number, lookup, and picklist fields.
Sample Query:
{ "select_query": "SELECT COUNT(Product_Category) FROM Products WHERE Product_Active = 'false'" } |
This sample query queries for the total number of inactive products in the Product module. Please be aware that if there are any products with no value for the product category field, they will not be included in the count.
Important points to remember
- COQL keywords are not case-sensitive.
- To prevent ambiguity, the criteria listed in the WHERE clause must be properly enclosed in brackets. If WHERE has more than two criteria such as A, B, and C, use them as either of the following:
- (A and (B and C))
- ((A and B) and C)
- A and (B and C)
- (A and B) and C.
- COQL supports only SELECT queries with WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT().
- The default sort order is ascending based on record ID if ORDER BY is not specified.
- Use the following format for :
- Date : YYYY-MM-DD
- DateTime : YYYY-MM-DDTHH:mm:ss+/-HH:mm
- The default value for LIMIT is 200 and OFFSET is 0.
- A maximum of 10,000 records can be fetched using the API.
- Multi-line fields cannot be used in criteria.
- COQL doesn't support consent_lookup, subform, profileimage, multiselectlookup, fileupload datatypes.
- A maximum of 25 criteria can be included in the WHERE clause.
- A maximum of 50 field API names can be used in the SELECT column of the query.
Data Type | Supported Operators |
text | =, !=, like, not like, in, not in, is null, is not null |
picklist | =, !=, like, not like, in, not in, is null, is not null |
ownerlookup | =, !=, in, not in, is null, is not null |
lookup | =, !=, in, not in, is null, is not null |
date | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
datetime | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
currency | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
boolean | = |
email | =, !=, like, not like, in, not in, is null, is not null |
phone | =, !=, like, not like, in, not in, is null, is not null |
website | =, !=, like, not like, in, not in, is null, is not null |
double | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
bigint | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
integer | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
formula | If the return type is: Decimal/Currency/Date/Datetime : =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null String : =, !=, like, not like, in, not in, is null, is not null Boolean : = |
autonumber | =, !=, like, not like, in, not in, is null, is not null |
We hope you found this post useful. We will meet you next week with another interesting topic.
If you have any questions or feedback, please let us know in the comments below, or write to us at support@zohocrm.com. We would love to hear from you!
Additional Reading:
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
Arose Unity
Haiku Technical Support
Subramanian K
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 FAQsKaizen #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
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Reports: Custom Search Function Fields
Hi Zoho, Hope you'll add this into your roadmap. Issue: For the past 2yrs our global team been complaining and was brought to our attention recently that it's a time consuming process looking/scrolling down. Use-case: This form is a service report withZoho Projects app update: Voice notes for Tasks and Bugs module
Hello everyone! In the latest version(v3.9.37) of the Zoho Projects Android app update, we have introduced voice notes for the Tasks and Bugs module. The voice notes can be added as an attachment or can be transcribed into text. Recording and attachingzurl URL shortener Not working in Zoho social
zurl URL shortener Not working in while creating a post in Zoho socialIn the Zoho CRM Module I have TRN Field I should contain 15 digit Number , If it Contain less than 15 digit Then show Alert message on save of the button , If it not contain any number not want to sh
Hi In the Zoho CRM Module I have TRN Field I should contain 15 digit Number , If it Contain less than 15 digit Then show Alert message on save of the button , If it not contain any number not want to show alert. How We can achive in Zoho CRm Using customPower of Automation::Streamline log hours to work hours upon task completion.
Hello Everyone, A Custom Function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as to when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:-Zoho Bookings know-how: A hands-on workshop series
Hello! We’re conducting a hands-on workshop series to help simplify appointment scheduling for your business with Zoho Bookings. We’ll be covering various functionalities and showing how you can leverage them for your business across five different sessions.Custom report
Hello Everyone I hope everything is fine. I've tried to To change the layout of the reports, especially the summary page report, and I want to divide summary of each section in the survey but I can't For example: I have a survey containing five differentZoho Journey - ZOHO MARKETING AUTOMATION
I’ve encountered an issue while working with a journey in Zoho Marketing Automation. After creating the journey, I wanted to edit the "Match Criteria" settings. Unfortunately: The criteria section appears to be locked and not editable. I’m also unableCustom Fields in PDF outputs
I created a couple of custom fields. e.g Country of Origin and HS Tariff Code. I need these to appear on a clone of a sales order PDF template but on on the standard PDF template. When I select "appear on PDFs' it appears on both but when I don't selectHow to create a Service Agreement with Quarterly Estimate
Hello, I'm not sure if this has been asked before so please don't get mad at me for asking. We're an NDIS provider in Australia so we need to draft a Service Agreement for our client. With the recent changes in the NDIS we're now required to also includeChange Currency symbol
I would like to change the way our currency displays when printed on quotes, invoices and purchase orders. Currently, we have Australian Dollars AUD as our Home Currency. The only two symbol choices available for this currency are "AU $" or "AUD". I wouldPython - code studio
Hi, I see the code studio is "coming soon". We have some files that will require some more complex transformation, is this feature far off? It appears to have been released in Zoho Analytics alreadyZoho Social - Post Footer Templates
As a content creator I often want to include some information at the end of most posts. It would be great if there was an option to add pre-written footers, similar to the Hashtag Groups at the end of posts. For example, if there is an offer I'm runningAllow to pick color for project groups in Zoho Projects
Hi Zoho Team, It would be really helpful if users could assign colors to project groups. This would make it easier to visually distinguish groups, improve navigation, and give a clearer overview when managing multiple projects. Thanks for consideringZoho Books - Quotes to Sales Order Automation
Hi Books team, In the Quote settings there is an option to convert a Quote to an Invoice upon acceptance, but there is not feature to convert a Quote to a Sales Order (see screenshot below) For users selling products through Zoho Inventory, the workflowCan't find imported leads
Hi There I have imported leads into the CRM via a .xls document, and the import is showing up as having been successful, however - when I try and locate the leads in the CRM system, I cannot find them. 1. There are no filters applied 2. They are notCustom Button Disappearing in mobile view | Zoho CRM Canvas
I'm working in Zoho CRM Canvas to create a custom view for our sales team. One of the features I'm adding is a custom button that opens the leads address in another tab. I've had no issue with this in the desktop view, but in the mobile view the buttonThe connected workflow is a great idea just needs Projects Integrations
I just discovered the connected workflows in CRM and its a Great Idea i wish it was integrated with Zoho Projects I will explain our use case I am already trying to do something like connected workflow with zoho flow Our requirement was to Create a TaskZoho Projects MCP Feedback
I've started using the MCP connector with Zoho Projects, and the features that exist really do work quite well - I feel this is going to be a major update to the Zoho Ecosystem. In projects a major missing feature is the ability to manage, (especiallyFunction #10: Update item prices automatically based on the last transaction created
In businesses, item prices are not always fixed and can fluctuate due to various factors. If you find yourself manually adjusting the item rates every time they change, we have the ideal time-saving solution for you. In today's post, we bring you customemail template
How do I create and save an email templateEnhancements in Portal User Group creation flow
Hello everyone, Before introducing new Portal features, here are some changes to the UI of Portals page to improve the user experience. Some tabs and options have been repositioned so that users can better access the functionalities of the feature. FromArchiving Contacts
How do I archive a list of contacts, or individual contacts?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 alreadyCan I add Conditional merge tags on my Templates?
Hi I was wondering if I can use Conditional Mail Merge tags inside my Email templates/Quotes etc within the CRM? In spanish and in our business we use gender and academic degree salutations , ie: Dr., Dra., Sr., Srta., so the beginning of an email / letterZoho CRM button to download images from image upload field
Hello, I am trying to create a button in Zoho CRM that I can place in my record details view for each record and use it to download all images in the image upload fields. I tried deluge, client scripts and even with a widget, but feel lost, could notemail moderation issue when email is sent in the name of a mail group
Symptom: an email that is sent by a mail group moderator in the name of a moderated mail group is held back for approval. Reproduction: Create a moderated mail group with members and moderators. Allow that mails can be sent in the name of the group (extended settings). Send an email to the group as a group moderator, but in the name of the group. This mail is held back for moderation which is unexpected. Expected: A mail sent by group moderator to a moderated group are not held back for moderationblank page after login
blank page after logging into my email account Thanks youIntroducing the revamped What's New page
Hello everyone! We're happy to announce that Zoho Campaigns' What's New page has undergone a complete revamp. We've bid the old page adieu after a long time and have introduced a new, sleeker-looking page. Without further ado, let's dive into the mainAlways display images from this sender – Is this feature available?
In Zoho mail, I had my "Load external images" setting set to "Ask me", and that's fine. That's the setting I prefer. What's not fine though is I always need to tick "Display now" for each email I get, regardless if I've done that multiple times from severalFunction #9: Copy attachments of Sales Order to Purchase Order on conversion
This week, we have written a custom function that automatically copies the attachments uploaded for a sales order to the corresponding purchase order after you convert it. Here's how to configure it in your Zoho Books organization. Custom Function: HitFree webinar: Security that works: Building resilience for the AI-powered workforce
Hello there, Did you know that more than 51% of organizations worldwide have experienced one or more security breaches, each costing over $1 million in losses or incident response? In today’s threat landscape, simply playing defense is no longer enough."Subject" or "Narration"in Customer Statement
Dear Sir, While creating invoice, we are giving in "Subject" the purpose of invoice. For Example - "GST for the month of Aug 23", IT return FY 22-23", "Consultancy", Internal Audit for May 23". But this subject is not coming in Customer Statement. OnlyApply Vendor Credit Automatically
Hello!!! Is there a way where in we can apply vendor credits automatically on the FIRST OUTSTANDING BILL of the vendor?? We have lots of VENDOR CREDITS ISSUES mostly!!! Applying it manually is a pain for us. Would be great if we have a way to apply theZoho Notebook Sync problem
I'm facing a problem with syncing of notebook on android app. It's not syncing. Sometimes it syncs after a day or two. I created some notes on web notebook but it's not syncing on mobile app. Please help!!!!Cliq iOS can't see shared screen
Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? RegardsRetainer Invoice.
Why ZOHO not have facilities to deduct partially advance payment from an invoice.Apply Vendor Credits Automatically
We are bulk importing Vendor credits in Zoho Books!!! Is there a way to apply vendor credits automatically to the first UNPAID bill of the Vendor?Cant Save Gauge on Creator Page
How to Save Gauge on Creator Page see movie https://vimeo.com/1116410860?share=copy#t=0Apply Advance option not shown in report
We are facing an issue in Zoho Expenses. While approving an Expense Report, the "Apply Advance" option is not appearing under the three dots (More Options). Details: Module: Expense Reports Issue: "Apply Advance" option not visible Status of Report: AwaitingNext Page