Kaizen #81 - COQL API - Part II

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:








    • Sticky Posts

    • Kaizen #197: Frequently Asked Questions on GraphQL APIs

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Kaizen #198: Using Client Script for Custom Validation in Blueprint

      Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

      Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

    • Kaizen #193: Creating different fields in Zoho CRM through API

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Client Script | Update - Introducing Commands in Client Script!

      Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands

    • Recent Topics

    • How can I see content of system generated mails from zBooks?

      System generated mails for offers or invices appear in the mail tab of the designated customer. How can I view the content? It also doesn't appear in zMail sent folder.

    • CRM Blueprint Notification by Cliq

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

    • Zoho People Attendance Regularization – Wrong Total Hours Displayed

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

    • Sync Contacts in iOS

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

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

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

    • Project Statuses

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

    • 👋 Welcome to the Zoho MCP Community

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

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

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

    • DNS Manager

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

    • Forwarder

      Hi, I tried to add a forwarder from which emails are sent to my main zoho account email . However, it asks me for a code that should be received at the forwarder email, which is still not activated to send to my zoho emial account. So how can I get the

    • Forwarder

      Hi, I tried to add a forwarder from which emails are sent to my main zoho account email . However, it asks me for a code that should be received at the forwarder email, which is still not activated to send to my zoho emial account. So how can I get the

    • How do I sync multiple Google calendars?

      I'm brand new to Zoho and I figured out how to sync my business Google calendar but I would also like to sync my personal Google calendar. How can I do this so that, at the very least, when I have personal engagements like doctor's appointments, I can

    • Need to extract date from datetime field

      I have a datetime field and need only the date part from it. I am unable to find a built-in function that would be <DateTime>.Date(). I don't think I want to go the string conversion route of converting the datetime to string and then parsing out values and create a date out of it. Any one out there has a better solution to this? Thanks in adavnce. Regards Moiz Tankiwala Smart Training & IT Solutions

    • How to Hide Article Links in SalesIQ Answer Bot Responses

      I have published an article in SalesIQ, and the Answer Bot is fetching the data and responding correctly. However, it is also displaying the article link, which I don’t want. How can I remove the link so that only the message is shown?

    • New in Cadences: WhatsApp follow-ups, upgraded limits, and options for add-ons

      Hello everyone, We're rolling out two key updates to help you engage better and scale smarter with Cadences in Zoho CRM. Reach customers on WhatsApp, directly from Cadences Previously, Cadences have enabled you to automate follow-ups through emails, calls,

    • additional accounts

      If I brought 5 emails to my account. Can I later buy additional emails.

    • Issue in Zoho People Regularization – Incorrect Hour Calculation

      I have noticed that when applying attendance regularization in Zoho People for previous dates, the total working hours are not calculated correctly. For example, even if the check-in is 10:00 AM and check-out is 6:00 PM, the system shows an incorrect

    • Why I am unable to configure Zoho Voice with my Zoho CRM account?

      I have installed Zoho Voice in my Zoho CRM, but as per the message there is some config needed in Zoho Voice interface. But when I click on the link given in the above message, I get an access denied page.

    • Issue with Hour Calculation in Zoho People Attendance Module

      I have noticed an issue in the attendance regularization feature of Zoho People. When trying to regularize past dates, the total working hours are not calculated correctly. For example, if I enter a check-in and check-out time for a previous day, the

    • Cliq Meeting Calls No Audio and Screen Share

      When in a Cliq channel meeting, the audio does not work at all on pc. When i use my phone as audio source, screen share on pc does not work. I have updated audio drivers but the strangest thing is that during a 1 on 1 call, it works well. Therefore the

    • Bug in Total Hour Calculation in Regularization for past dates

      There is a bug in Zoho People Regularization For example today is the date is 10 if I choose a previous Date like 9 and add the Check in and Check out time The total hours aren't calculated properly, in the example the check in time is 10:40 AM check

    • Work anniversary and birthdays on connect

      Hello, I like the idea of having employee's work anniversary and birthdays on the dashbaord. Do you have to have the employee complete this information themselves in connect settings, or does it pull from their directory settings? (ie. we use Zoho one

    • Alias Email Id already exists

      Hi I'm trying to create an alias : contact @ yoavarielevy.co.il but i get the message  Alias Email Id already exists I had an account with the same name but I deleted it. Can you help? Thanx Yoav

    • BANK FEED - MAYBANK , provider from YODLEE IS NOT WORKING

      As per topic, the provider YODLEE is not working for the BANK FEED. It have been reported since 2023 Q3, and second report on 2023 Q4. now almost end of 2024 Q1, and coming to 2024 Q2. Malaysia Bank Maybank is NOT working. can anyone check on this issue?

    • Feature Request: Ability to Set a Custom List View as Default for All Users

      Dear Zoho CRM Support Team, We would like to request a new feature in Zoho CRM regarding List Views. Currently, each user has to manually select or favorite a custom list view in order to make it their default. However, as administrators, we would like

    • Adding Multiple Products (Package) to a Quote

      I've searched the forums and found several people asking this question, but never found an answer. Is ti possible to add multiple products to a quote at once, like a package deal? This seems like a very basic function of a CRM that does quotes but I can't

    • Zoho Commerce in multiple languages

      When will you be able to offer Zoho Commerce in more languages? We sell in multiple markets and want to be able to offer a local version of our webshop. What does the roadmap look like?

    • webinar registration confirmation are landing in SPMA folders

      I am trialing zoho webinar and do not have access to custom domains. When I test user registrations, they are working but the resulting confirmation email is landing in a spam folder. How can I avoid this?

    • Making digital signatures accessible to all: Introducing accessibility controls in Zoho Sign

      Hi there! At Zoho Sign, we are committed to building an inclusive digital experience for all our users. As part of our ongoing efforts to align with Web Content Accessibility Guidelines (WCAG), we’re updating the application with support that will go

    • Is there a way to set Document Owner/Sender via the API

      When sending requests for zoho sign, it would seem zoho uses the id of the person that created the zoho api cred to determine the owner_id, is there a way to set a default for this?

    • Delegates should be able to delete expenses

      I understand the data integrity of this request. It would be nice if there was a toggle switch in the Policy setting that would allow a delegate to delete expenses from their managers account. Some managers here never touch their expense reports, and

    • Add Save button to Expense form

      A save button would be very helpful on the expense form. Currently there is a Save and Close button. When we want to itemize an expense, this option would be very helpful. For example, if we have a hotel expense that also has room service, which is a

    • Multiple organizations under Zoho One

      Hello. I have a long and complicated question. I have a Zoho One account and want to set it up to serve the needs of 6 organizations under the same company. Some of the Zoho One users need to be able to work in more than 1 organization’s CRM and other

    • Error AS101 when adding new email alias

      Hi, I am trying to add apple@(mydomain).com The error AS101 is shown while I try to add the alias.

    • Unbundle feature for composite items

      We receive composite items from our vendors and sell them either individually or create other composite items out of them. So, there is a lot of bundling and unbundling involved with our composite items. Previously, this feature was supported in form

    • Regarding the integration of Apollo.io with Zoho crm.

      I have been seeing for the last 3 months that your Apollo.io beta version is available in Zoho Flow, and this application has not gone live yet. We requested this 2 months ago, but you guys said that 'we are working on it,' and when we search on Google

    • MTD SA in the UK

      Hello ID 20106048857 The Inland Revenue have confirmed that this tax account is registered as Cash Basis In Settings>Profile I have set ‘Report Basis’ as “Cash" However, I see on Zoho on Settings>Taxes>Income Tax that the ‘Tax Basis’ is marked ‘Accrual'

    • workflow not working in subform

      I have the following code in a subform which works perfectly when i use the form alone but when i use the form as a subform within another main form it does not work. I have read something about using row but i just cant seem to figure out what to change

    • Fetch data from another table into a form field

      I have spent the day trying to work this out so i thought i would use the forum for the first time. I have two forms in the same application and when a user selects a customer name from a drop down field and would like the customer number field in the

    • Record comment filter

      Hi - I have a calendar app that we use to track tasks. I have the calendar view set up so that the logged in user only sees the record if they are assigned to the task. BUT there are instances when someone is @ mentioned in the record when they are not

    • Next Page