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

    • Collaps Notes

      There are times when long/large notes are added to a record i.e. Accounts or Deals etc. Currently, the full note is displayed in the notes related list section. It would be great if by default only 5 to 10 rows of the note are displayed when the note

    • Zoho Down

      I have a drop in my Zoho One services.

    • Runing RPA Agents on Headless Windows 11 Machines

      Has anyone tried this? Anything to be aware of regarding screen resolution?

    • Problem for EU users connecting Zoho CRM through Google Ads for Enhanced conversions

      Has anyone else experienced this problem when trying to connect Zoho CRM through Google Ads interface to setup enhanced conversions? Did you guys get it fixed somehow? The Problem: The current Google Ads integration is hardcoded to use Zoho's US authentication

    • Why am I getting event Pop-up Notification for events that have been cancelled?

      Why is Calendar Notification still popping up for events that have been cancelled or changed? Each time events are cancelled or changed, I have observed that I am still getting notifications for them. Below is a sample pop-up notification for one of the

    • Whatsapp Limitation Questions

      Good day, I would like to find out about the functionality or possibility of all the below points within the Zoho/WhatsApp integration. Will WhatsApp buttons ever be possible in the future? Will WhatsApp Re-directs to different users be possible based

    • Create a draft in reply to an email via Emails API

      Hi, I’d like to use the outgoing webhook to automatically create a draft reply to incoming mail. How can I use the Emails API to create a draft reply that is linked to an existing email thread? I couldn’t find the relevant method in the documentation.

    • India Tech Support

      Is there no phone tech support number for India? And no chat facility either?

    • Billing Management: #1 Billing a Universal Business Routine

      Hello, As the saying goes, "Do the hardest job first." We started with the complex subject of finance and revenue management, which is considered the backbone of any business. Now, let's shift our focus and take a deep dive into this Billing Management,

    • Show/ hide specific field based on user

      Can someone please help me with a client script to achieve the following? I've already tried a couple of different scripts I've found on here (updating to match my details etc...) but none of them seem to work. No errors flagged in the codes, it just

    • What is a a valid JavaScript Domain URI when creating a client-based application using the Zoho API console?

      No idea what this is. Can't see what it is explained anywhere.

    • 5名限定 課題解決型ワークショップイベント Zoho ワークアウト開催のお知らせ (9/25)

      ユーザーの皆さま、こんにちは。Zoho ユーザーコミュニティチームの藤澤です。 9月開催のZoho ワークアウトについてお知らせします。 今回はZoomにて、オンライン開催します。 諸事情につき、今月の開催回は中止となりました。 次回は10/31（金）14時からの開催を予定しています。 ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目的とした「Zoho ワークアウト」を開催します。 Zoho

    • Zoho Calendar not syncing correctly with personal Google Calendar

      Coming to this forum as Zoho Calendar support team is not responding, any more. For the past 8 weeks, I have been having an issue with Zoho Calendar not syncing with my personal Google Calendar correctly. I subscribed to Zoho Calendar iCal in my personal

    • Introducing Assemblies and Kits in Zoho Inventory

      Hello customers, We’re excited to share a major revamp to Zoho Inventory that brings both clarity and flexibility to your inventory management experience! Presenting Assemblies and Kits We’re thrilled to introduce Assemblies and Kits, which replaces the

    • Customer Parent Account or Sub-Customer Account

      Some of clients as they have 50 to 300 branches, they required separate account statement with outlet name and number; which means we have to open new account for each branch individually. However, the main issue is that, when they make a payment, they

    • need a packing list feature

      In our business, goods listed on an invoice are packed in separate boxes and shipped off. for e.g. an invoice may have 10 items. each item could then be packed in different boxes depending on qty of each item. this packing list is as important as the invoice for purposes of shipping documents.  Request you to add this feature asap.

    • Workdrive 5.0 / API Documentation Workflows

      Hi Zoho, When will the API documentation of the workflows be published? We are interested in using it to trigger manual workflows from an external application. Greetings, Justin

    • How to keep track of bags, cans, drums of inventory?

      We buy and sell products that are packaged in bags 🛍️, cans🥫, drums🛢️, etc. with batch numbers. When we get a shipment of one of the products, how do we track we received (say) 10 cans each of 5L of a product and maybe we received 10 cans of another

    • Zoho Error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details

      Hello There, l tried to verify my domain (florindagoreti.com.br) and its shows this error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details. Screenshot Given Below -  please check what went wrong. Thanks

    • How many ZOHO-Sites does the ZOHO-One Suite allow for?

      The free version of ZOHO-Sites allows for two sites, but it seems that the professional version (which is included in the ZOHO-One Suite) only allows for two websites. Is that correct? How many sites can I have within one ZOHO-One account?

    • Replace Lookup fields ID value with their actual name and adding inormation from subforms

      Hi everyone,  I wanted to see if someone smarter than me has managed to find any solutions to two problems we have. I will explain both below.  To start we are syncing data from Zoho CRM to Zoho Analytics and I will use the Sales Order module when giving

    • Webhook from Zobot to Zoho Flow fails

      I'm trying to connect from zobot to zoho flow. When testing in zflow, I am receiving all entered data from the connector correctly. The SalesIQ connector's "outputreaction" is {} (is this normal or is there a problem?). But as soon as I try my chat bot

    • Transition from Sole Proprietorship to GmbH (Limited Liability Company) – Best Approach in Zoho Books / Zoho One

      Hello everyone, I am currently operating under a Zoho One plan with a sole proprietorship in Switzerland. As of January 1st, 2026, I will be incorporating a new legal entity – a GmbH (Swiss equivalent of a Limited Liability Company). While the business

    • Best way to display complex Bookings Consultation Descriptions on Zoho Site?

      I am a new user so apologies if this has been asked before. I couldn't find any answers in the forum. We offer 18 complex Consultations to our subscribers. Our current platform lets me put detail on these Consultations thoroughly (200-300 words) during

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

    • Issue Connecting My Domain to Zoho Sites Despite Purchasing It from Zoho

      Hello, I am facing an issue connecting my domain to my website on Zoho Sites. Details of the issue: I purchased the domain directly from Zoho. I am already using the same domain successfully with Zoho Mail. However, when I try to assign this domain to

    • Insert auto number from main form into subform rows

      Hello. I'm trying to take from my main form "order number" which i have setup as an auto generated number into every line created in my subform. So when a row is created in my subform i want the "order number " from the main form to be inserted automatically.

    • Dark Mode - Font Colors Don't Work

      When editing a document in Dark Mode and selecting font colors, they don't show up on screen.  Viewing/editing the same document in Light Mode shows them just fine.

    • Integrate Bunq with ZOHO Bookes

      We are new users of ZOHO Books, and our bank (BUNQ, in the Netherlands) isn't listed on the bank integrations. Is there a way to handle this?

    • 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 ? Regards

    • Access Denied

      I am iOS Developer and updating our clients project and shifted ZohoDeskPortalCore SDKs from cocoapods to SPM and changed few lines of code but now i am get access denied, the help center app is unavailable. please contact administrator.

    • Using Zoho Desk to support ISMS process

      Hi, I am evaluating using Zoho Desk for security incident management. This seems to be aligned with Zoho Desk purpose as its just another type of incident. However in security incident management, ideally I can link incidents (tickets) with a risk from

    • Bin Locations

      Dear all, I am wondering if someone has the ability to develop the bin locations option for zoho inventory (integrated with zoho books) Regards, Ryan

    • TaxJar vs Avalara

      Hi, I'm evaluating adoption of a sales-tax service for US based business. Anyone else have experience with TaxJar and Zoho Books? I am a Zoho One subscriber so anticipate needing to use Flow to make this work. It seems like Avalara are simply too expensive

    • How to check Leads with no Task (open activity)

      Hi everyone, I was wondering if there’s a way to view leads that don’t have any tasks assigned or open activities linked to them.

    • What can we do on our end to improve the Answer bot answers?

      Hi, I'm using the Answer bot card in the Codeless bot builder. I've input several questions and their answers in the FAQ section to feed the Answer bot. The text is all in French, as this is the language our customers communicate in. I've tried testing

    • Taxes for EU B2B Transactions

      Currently, ZC doesn't seem to have a procedure for validating VAT numbers of businesses purchasing in another EU state, and removing local VAT is valid.  This is essential for all inter EU B2B trade.

    • How to upload file to Connect using API?

      Hi there. I looked at the API documentation and nowhere did it mention how to use the API method to upload a file even though it is mentioned that it is possible to be done so. Please help.

    • Items Landed Cost and Profit?

      Hello, we recently went live with Zoho Inventory, and I have a question about the Landed Cost feature. The FAQ reads: "Tracking the landed cost helps determine the overall cost incurred in procuring the product. This, in turn, helps you to decide the

    • Group Tax in Service Line Items

      Hi FSM Team! I noticed that when you update a tax in the service line item the group tax is not showing up as an option. Let me know what can be done thank you!

    • Next Page