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 #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.

    • Kaizen #226: Using ZRC in Client Script

      Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this

    • Kaizen #222 - Client Script Support for Notes Related List

      Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore how

    • 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 are

    • Recent Topics

    • Calling the new 'Custom API' feature from within a Custom Widget

      From what I've learned it is not possible to call an endpoint from the new "Custom API" feature within a Creator Widget. The SDK's doesn't support it yet, when calling it natively you end up with CORS issues or at least I couldn't get it working even

    • Cannot post to Instagram. I get the error: Insufficient permission to publish on this page. Check with your page Admin to update permissions.

      We had another admin that left the company. Since he left, we get this error when posting to Instagram.

    • Zoho desk desktop application

      does zoho desk has a destop applicaion?

    • Ability to re-order Workflow Rules

      We really need the ability to re-order workflow rules within departments.  Either the ability to drag/drop them into a different order or something.

    • Create an article template

      I have a question concern the Knolwedge Database from Zoho Desk. There is any possibility to create an article template ? Exemple of what I research : TODAY : I want to create a lot af articles with the same baseline > I create one article > I copy/paste

    • Change of Blog Author

      Hi, I am creating the blog post on behalf of my colleague. When I publish the post, it is showing my name as author of the post which is not intended and needs to be changed to my colleague's name. How can I change the name of the author in the blogs?? Thanks, Ramanan

    • Zoho FSM API Delete Record

      Hi FSM Team, It would be great if you could delete a record via API. Thank you,

    • Instant Messaging Update | Now migrate your WhatsApp Business phonenumber from another BSP to Zoho Desk | Dec'23

      Hi All, Everything seems to move at lightning speed these days, and time is certainly flying by for the IM team. But we are delighted at how much ground we covered this year. 🚀 For one, we enabled WhatsApp business phone number migration so that you

    • Upload ticket attachments via Drag-&-Drop

      Hello, if you want to upload a file to the ticket attachment you need to click the button and use the file browser to select and upload the desired file. In many cases, it would be much more efficient if you could simply drag the file to the browser window...

    • Can we disable add to cart in Zoho Commerce?

      Hello, Our sales happen on the phone. During the call the customer is directed to our website to inspect the products together with the sales rep. So we need a way to present our inventory. I figured Zoho Commerce is a good fit for this use case. However

    • Introducing WhatsApp integration in Bigin

      Greetings! In today's business landscape, messaging apps play a significant role in customer operations. Customers can engage with businesses, seek support, ask questions, receive personalized recommendations, read reviews, and even make purchases—all

    • Zoho One account closure vs deactivation

      I wonder what are the best practices and guidelines around deactivating vs deleting Zoho accounts in organisations? Any practical considerations?

    • Schedule Zoho CRM reports only on Business Days

      Hello, Is it possible to schedule reports only on business days ? We currently get daily sales reports on weekend which has no value since the sales team doesn't work on weekends. Thanks

    • How to install Widget in inventory module

      Hi， I am trying to install a app into Sales Order Module related list, however there is no button allow me to do that. May I ask how to install widget to inventory module related list?

    • How to track a contact or customer's past product purchases in Bigin Premiere?

      Hello there. I am the sole user of Bigin for a small company (chess club), and would like to track current and past purchases made by a contact (player) or company (family which includes the player). Players may register for multiple tournaments, take

    • LENTITUD EN RECIBIR MAIL

      Buenas tardes, Estamos experimentando lentitud en la recepción de correos electrónicos.

    • Deactivated Zoho One account can sign in

      I am concerned by the fact that deactivated users in Zoho One have the ability to sign in even after their account has been deactivated (not deleted). these inactive identities have no access to individual Zoho apps or data. based on my experience they

    • Items should display under specific warehouse

      I have configured the multi warehouse but it show all the items under all warehouse which is not correct according to our business logic, so i want that items should only display under that specific warehouse not under all the warehouses not even with zero quantity. Some items should be common but not all so is there any option for that purpose so i can specific the items to its warehouse. Regards

    • Partial refunds

      I am trying to process refund for a one item invoice, however the refund is partial: i am getting this error while creating credit note, can anyone share some wisdom about this

    • Best practice importing items and matching assemblies

      Hi, I was wondering what would be the best practice to import items and composite items (assemblies) From my backup, what should I import first? The items or the composite items? I am on Zoho one, using inventory and books. Kind regards, Sabine

    • Bulk Fill In & Edit PO/Bill/SO/Invoice

      Hello, I am adding stock in bulk on a PO, the system is automatically populating the Rate (price) and Tax from the item data. Problem is that the bill rate is different from the rate on the item data, so I have to manually erase each and enter the price.

    • Items Below Reorder Point Report?

      Is there a way to run a report of Items that are below the Reorder Point? I don't see this as a specific report, nor can I figure out how to customize any of the other stock reports to give me this information. Please tell me I'm missing something s

    • Cancelled Transfer order problem

      Hello, We've canceled a transfer order, and we can't add the related items to a new Transfer Order. The system tells us that the bin doesn't have the required quantity, but when we check the item, it indicates that there are 2 units in the bin. It also

    • Creating a Chart from a Report

      In Zoho Analytics, is it possible to create a chart from a Pivot View report? We are looking to use Zoho Analytics to replace Excel for Sales reports and would like to be able to show both the table and the chart together.

    • Client Portal ZOHO ONE

      Dear Zoho one is fantastic option for companies but it seems to me that it is still an aggregation of aps let me explain I have zoho books with client portal so client access their invoice then I have zoho project with client portal so they can access their project but not their invoice without another URL another LOGIN Are you planning in creating a beautiful UI portal for client so we can control access to client in one location to multiple aps at least unify project and invoice aps that would

    • Email Insights included in Bigin emals are marked as SPAM everywhere

      Today I noticed that email recipients who use Office 365 never receive emails sent from Bigin. Further examination showed that all Email Insights links in email headers are marked as spam/phishing by Office 365. Example screen included. The problem is

    • Data Import | Zoho Analytics Custom Query Window Size

      Please increase the window size for the Custom Query Data Import. It's impossible to work with such a small query window.

    • Name changed in settings for mailbox but still not changed when typed in To field

      In the email account secretary@ i have updaetd the new staff members details but the old members name still appears when I type secretary@ in the To field. I cant work out where Zoho is finding the old name from. I have deleted the browser cache. If I

    • Cannot add my name to my domain name

      I want to have My name@mydomain.com and it says my name is linked to another account already. Please fix it since I do not have another account.

    • Establishing new Zoho email account on laptop

      Good Morning: I am very long time Outlook business user and decided to try your email service last night and had established an account. I am trying to verify my account; how do I establish my Zoho email account on my laptop? I opened the account with

    • unable to send message reason 550 5.4.6 unusual sending activity

      My email account can't send message. It shows unable to send message reason 550 5.4.6 unusual sending activity detected

    • how to add email to existing organization i w

      I am already registered my organization and i have an email id. I need one more email id but i can't find anywhere .i want the cheapest email id . how to add ?

    • e-mail bloqueado

      Estou com meu e-mail lucas@peplus.me bloqueado, preciso desbloquear para retorno de usos em minhas atividades.

    • zoho labels api not working

      We're using n8n to automte email reply using zoho api. I'm facing issue with label api. I added the required scopes but its not working. i followed zoho api documentation but didn't work. also, where do i find/how do i create zoho oauth token mentioneeed

    • Desk DMARC forwarding failure for some senders

      I am not receiving important emails into Desk, because of DMARC errors. Here's what's happening: 1. email is sent from customer e.g. john@doe.com, to my email address, e.g info@acme.com 2. email is delivered successfully to info@acme.com (a shared inbox

    • Streams/Shared email doesn't show up in windows trident app. It works fine on MAC. Is there any difference between 2 install ?

      I can see streams/share email boxs on my MAC version of trident app but i can't see them in windows version of trident app. Is there any difference between 2 install? I try to find setting but not able to see any setting to add stream/share email boxes.

    • add zoho account

      How to add a zoho mail to previous zoho account? I have two

    • Zoho Desk Mobile App Year-End Roundup - 2025

      Dear Zoho Desk users, Greetings! As you gear up for the festive season, we are excited to share a quick journey into all that is released in 2025! Zia's generative AI capabilities Zia insights can be highly beneficial in helping agents manage daily support

    • Narrative 17: The role of Zia AI in customer support

      Behind the scenes of a successful ticketing system: BTS Series Narrative 17: The role of Zia AI in customer support Overview Zia in Zoho Desk is a layered AI assistant that combines generative AI, prediction, and automation to support agents, automate

    • Domain Transaction

      I have purchased a domain name called trainedworkforce.co.in I made the payment got the receipt but the domain is still not purchased after successful transaction .

    • Next Page