Kaizen #178 - Filters & Criteria in Zoho CRM APIs

Kaizen #178 - Filters & Criteria in Zoho CRM APIs


Hey everyone, and welcome back to another week of Kaizen!

Ever felt overwhelmed by the sheer volume of data in your Zoho CRM? Sifting through countless records to find exactly what you need, or to operate on specific records that meet a certain criteria, can indeed be a real time-sink. Fortunately, Zoho CRM provides powerful filtering capabilities through its APIs to streamline this process.

What is criteria or filter?

In Zoho CRM APIs, criteria refer to the conditions applied to filter records when making API requests. These conditions can be simple (single condition) or complex (multiple conditions) and are specified using various comparators.

Depending on the API type and request method, the filter can be provided either:
  • As a query parameter (filters) – Used in GET requests.
  • As an input key in the request body (criteria)– Used in POST and PUT requests   

Where is "filters" used in Zoho CRM APIs?

The filters parameter is supported across multiple Zoho CRM API endpoints, including but not limited to:

Where is "criteria" used in Zoho CRM APIs?

The criteria key is supported across multiple Zoho CRM API endpoints, including but not limited to:
The filters parameter and criteria key are supported across multiple Zoho CRM API endpoints. Refer to our API help documentation for individual APIs to check if these options are available. Check the Parameters section for filters and the Input JSON section for criteria
 in the API's help documentation.

Constructing a criteria or a filter

Zoho CRM APIs use JSON-based criteria/filters expressions, which consist of:
  • Field API Name – The field to filter (e.g., Email, Created_Time, Status).
  • Comparator – The operator defining how the field should be compared (equals, greater_than, etc.).
  • Value – The specific value to compare against.

Single Condition

A single condition is structured as follows:
{
  "field": {
    "api_name": "Post_Number"
  },
  "comparator": "greater_than",
  "value": 1
}

Here,
  • field specifies the API name of the field to be filtered.
  • comparator indicates the type of comparison to be made (e.g., greater_than).
  • value is the value against which the field is compared.

Multiple Conditions

When multiple conditions are required, they can be grouped using the logical operators "and" or "or". Here’s the syntax of how to structure multiple conditions:
{
  "group_operator": "or",
  "group": [
    {
      "field": {
        "api_name": "Post_Number"
      },
      "comparator": "greater_than",
      "value": 1
    },
    {
      "field": {
        "api_name": "Pre_Number"
      },
      "comparator": "greater_than",
      "value": 1
    }
  ]
}

Here,
  • group_operator can be "and" or "or". It defines how the conditions within the group array are evaluated. "and" means all conditions must be true, while "or" means at least one condition must be true.
  • group
     contains an array of condition objects, each with its own field, comparator, and value.
Following is an example for a criteria with more than two conditions:
{
  "criterion": {
    "group_operator": "and",
    "group": [
      {
        "field": {
          "api_name": "Post_Number"
        },
        "comparator": "greater_than",
        "value": 1
      },
      {
        "group_operator": "or",
        "group": [
          {
            "field": {
              "api_name": "Pre_Number"
            },
            "comparator": "greater_than",
            "value": 1
          },
          {
            "field": {
              "api_name": "Status"
            },
            "comparator": "equals",
            "value": "Active"
          }
        ]
      }
    ]
  }
}

Supported Comparators for Different Field Types

The available comparators depend on the field type. The following table outlines the supported comparators for different field types:

Field Type
Supported Comparators
Example
text/ textarea
equal, not_equal, contains, not_contains , starts_with, ends_with
{
   "comparator": "equal",
   "field": {
       "api_name": "text"
   },
   "value": "zoho"

}
email
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "contains",
   "field": {
       "api_name": "Email"
   },
   "value": "patricia"
}
phone
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "equal",
   "field": {
       "api_name": "Phone"
   },
   "value": "9999999999"
}
website
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "contains",
   "field": {
       "api_name": "website"
   },
   "value": "zoho"
}
picklist
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "equal",
   "field": {
       "api_name": "picklist"
   },
   "value": [
       "Cold Call",
        "Call"
   ]
}
multiselectpicklist
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "equal",
   "field": {
       "api_name": "Multi_Select_Picklist"
   },
   "value": [
         "Cold Call",
        "Call"
   ]
}
date
equal, not_equal, less_than, greater_than, between, not_between, less_equal, greater_equal
Example 1:
{
   "comparator": "less_than",
   "field": {
       "api_name": "date"
   },
   "value": "01-01-2025"

}
Example 2:
{
   "comparator": "between",
   "field": {
       "api_name": "date"
   },
   "value": [
          "01-01-2025",
           "02-01-2025",
}
datetime
equal, not_equal, less_than, greater_than, between, not_between, less_equal, greater_equal
{
   "comparator": "equal",
   "field": {
       "api_name": "date"
   },
   "value": "2025-01-01T12:12:06+05:30"
}
autonumber
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "starts_with",
   "field": {
       "api_name": "Auto_Number"
   },
   "value": "A01"
}
integer
equal, not_equal, less_than, less_equal, greater_than, greater_equal, between, not_between
{
   "comparator": "equal",
   "field": {
       "api_name": "integer"
   },
   "value": 4
}
currency
equal, not_equal, contains, not_contains, starts_with, ends_with
{
   "comparator": "equal",
   "field": {
       "api_name": "Currency"
   },
   "value": "INR"
}
double
equal, not_equal, less_than, less_equal, greater_than, greater_equal, between, not_between
{
   "comparator": "equal",
   "field": {
       "api_name": "Double"
   },
   "value": "123"
}
Boolean
equal
{
   "comparator": "equal",
   "field": {
       "api_name": "Boolean"
   },
   "value": true
}
big int
equal, not_equal, less_than, less_equal, greater_than, greater_equal, between, not_between
{
   "comparator": "equal",
   "field": {
       "api_name": "id"
   },
   "value": "1234800023843923"
}
lookup
equal, not_equal, less_than, less_equal, greater_than, greater_equal, between, not_between
{
   "comparator": "equal",
   "field": {
       "api_name": "Lookup.name"
   },
   "value": "Name1"
}
ownerlookup/userlookup
in, not_in 
{
   "comparator": "equal",
   "field": {
       "api_name": "Modified_By",
       "id": "400029000000000479"
   },
   "value": [
       {
           "id": "400029000003984001",
           "name": "User1"
       },
       {
           "id": 400029000003984002,
           "name": "User2"
       }
   ]
}
formula
Comparator will depend on the return type
-



Notes
Note: 
  • Use quotes for strings and picklists; do not use quotes for numbers and boolean values.
  • While Zoho CRM’s filtering capabilities are extensive, certain field types — including subforms, multi-select lookup fields, multi-user lookup fields, and multi-module lookup fields — are not supported in filters and criteria. The above table lists all the supported field types. 

Filtering in COQL Queries

COQL (CRM Object Query Language) provides a structured way to retrieve records from Zoho CRM using SQL-like queries. Unlike the standard API filters discussed above, COQL uses the WHERE clause to define conditions.

Here is a sample COQL query:

{
  "select_query": "SELECT Full_Name, Email FROM Leads WHERE Lead_Status = 'Qualified'"
}

Here, Lead_Status = 'Qualified' acts as the filtering criteria.

{
  "select_query": "SELECT Full_Name, Email FROM Leads WHERE (Lead_Status = 'Qualified' AND City = 'New York') OR (Industry = 'Software')"
}

The AND and OR operators allow for multiple conditions in the WHERE clause. We have covered COQL API, and all the supported field types and operators in detail in our Kaizen posts. Kindly refer to them for more details.

Criteria in Search API

In Search API, filtering records is done using the criteria (string) input key in the request body. Unlike filters in GET requests or criteria in other APIs, criteria here is a text-based filter expression that follows a specific syntax.

(({api_name}:{operator}:{value})and/or({api_name}:{operator}:{value}))
  • You can combine up to 10 conditions using and or or.
  • Only the equals operator is supported for encrypted fields.
  • When using "equals" in Search API, it behaves like "contains", meaning it fetches records that include the specified value, not just exact matches.
  • If you use parentheses (), commas, or backslashes \ in values, you must escape them properly and encode the value before making the request.
Field Type
Supported Operators
Date, DateTime
equals, not_equal, greater_equal, greater_than, less_equal, less_than, between, in
Integer, Currency, Decimal
equals, not_equal, greater_equal, greater_than, less_equal, less_than, between, in
Boolean
equals, not_equal
textarea
equals, not_equal, starts_with
Lookup (user/owner)
equals, not_equal, in
Picklist, Autonumber
equals, not_equal, in
Text, Email, Phone, Website 
equals, not_equal, starts_with, in
multiselectpicklist
equals, not_equal, in, starts_with.
bigint
equals, not_equal, greater_than, greater_equal, less_than, less_equal, between, in
percent
equals, not_equal, greater_than, greater_equal, less_than, less_equal, between, in
formula
The supported operators of the formula datatype will depend on the return type of the formula


Have questions or use cases to share? Feel free to share them in the comments below or reach out to our support team directly at support@zohocrm.com.

Thank you for joining us this week. Stay tuned for more insights in our upcoming Kaizen posts! Happy Coding!!



      • Recent Topics

      • 2024 Email Authentication Standards: Elevating Security with Google and Yahoo

        In contemporary email communication, email authentication plays a pivotal role in mitigating email fraud, spam, and phishing attacks. Brace yourself for a new level of security. Starting February 2024, Gmail and Yahoo will be implementing robust email

      • New features released: Data from Picture for Web, Pattern Fill, and Translation in Zoho Sheet!

        Hello Sheet users, In 2024, the Zoho Sheet team focused on research and development to bring new features that add to functionalities like productivity, data management, collaboration, and more. This year, we're all set to roll them out incrementally

      • Kaizen #59 - Creating alerts and custom messages using Client Script

        Hello everyone! We are happy to resume our Zoho CRM Developer Community series - The Kaizen series! Welcome back to the new start of Kaizen! This post is about Client Script and its simple use cases involving ZDK Client functions. What is Client Script?

      • [Webinar] Zoho Writer for content creators and publishing houses

        Managing multiple drafts, edits, and client reviews doesn't have to slow you down. Join our upcoming webinar to see how Zoho Writer helps content creators and publishing houses create, edit, and publish seamlessly—all in one place. You'll learn how to:

      • Universal search

        Hi, it would be useful if the search bar was universal-so if you entered a term, it would bring up results from contacts, candidates, clients etc all at the same time (but broken down under the relevant headings)

      • Attachment reminder?

        My team and I often need to attach files to our messages, e.g. an explanatory screenshot or a shipping label. More often that I want to admit I mention the attachment but forget to actually attach it. Some email clients have a check-for-missing-attachments

      • ZIA in Zoho Cliq

        Is It possible to use the ZIA feature from Zoho Analytics in the Zoho Cliq?

      • Automating CRM backup storage?

        Hi there, We've recently set up automatic backups for our Zoho CRM account. We were hoping that the backup functionality would not require any manual work on our end, but it seems that we are always required to download the backups ourselves, store them,

      • Multiple upload field CRM

        I desperately need the functionality to add more than one upload field to web to contacts form. How can I do this?

      • Critical Need for Global Search in Zoho FSM

        Hello Zoho FSM Team, We are currently in the process of deciding whether to fully transition to Zoho FSM for managing our field service operations. At present, our team actively uses Zoho Desk (with over 50 users) and Service Fusion, which we are considering

      • Collections Management: # 1 Payment Collection is All About Convenience

        "Sir, can you come tomorrow? My manager wasn't available for the cheque sign-off", the customer said, avoiding eye contact. Ravi forced a polite smile, but inside, he felt a sense of defeat. He had already visited the customer's office twice in the last

      • Can the Product Image on the Quote Template be enlarged

        Hello, I am editing the Quote Template and added ${Products.Product Image} to the line item and the image comes up but it is very tiny. Is there anyway that you can resize this to be larger? Any help would be great! Thanks

      • Audio/video quality issues with Zoho Meeting – Any roadmap for improvement?

        Hi Zoho Team, We’ve been using Zoho Meeting for both internal and external meetings, and unfortunately, the experience has been consistently poor. The video and audio quality are so unreliable that it often renders meetings ineffective—especially with

      • Cash based businesses cannot use the new fixed asset module

        Hello all, If your bookkeeping is reporting in cash, you cannot use the new fixed acid module, as it does all the depreciation bookings accrual and not cash. This is definitive and you can't turn them into a cash booking. They will never appear in your

      • Zoho Learn & Zoho Connect

        Hi, Is there a way to sync the knowledge base we have in Zoho Learn with the manuals section is Zoho Connect? Thanks,

      • Apply Payment Received Amount Zoho Books Invoice

        Hello team here is the sample code How can apply the payment received record over a unpaid zoho books invoice. //......................... paymentID = customer_payment.get("payment_id"); organizationID = organization.get("organization_id"); paymentmaplist

      • Zoho Books Finance Modules Not Accessible in Zoho CRM Mobile App

        We have integrated Zoho CRM with Zoho Books using the Zoho Finance Suite integration. In the CRM web version, we can see the Finance modules (Estimates/Quotes, Invoices, Sales Orders, Items, Payments) and are able to create invoices and quotes directly

      • [Live Webinar] New in Zoho WorkDrive: AI enhancements, Data Loss Prevention, Version Controls, and more

        Hello everyone, We're excited to bring you another round of powerful updates in Zoho WorkDrive! Join us on May 15 for an exclusive live webinar where we’ll unveil the latest features designed to enhance your team’s productivity, collaboration, and data

      • Live webinar: Streamlining legal operations: Leveraging Zoho WorkDrive for law firm success

        Hello everyone, Managing legal documents across departments and jurisdictions can be complex, but it doesn’t have to be. Join us on March 6 for an exclusive webinar where we’ll show you how Zoho WorkDrive empowers legal teams to stay compliant, organized,

      • Live Webinar: Optimizing back-office operations in the manufacturing industry to maximize profitability

        Hello everyone, We’re excited to invite you to our upcoming live webinar on February 6! Discover how Zoho WorkDrive can help manufacturing businesses optimize back-office operations, improve efficiency, and boost profitability. Our product experts will

      • Live webinar: 2024 recap of Zoho WorkDrive

        Hello everyone, We’re excited to invite you to our year-end live webinar! This session will take you through the transformative features and updates we’ve introduced in Zoho WorkDrive this year, helping you streamline document management like never before.

      • Live webinar: Explore WorkDrive's seamless integrations with key Zoho apps

        Hello everyone, We’re excited to invite you to our upcoming live webinar, where we'll delve into the seamless integration of WorkDrive with other key Zoho applications! This is a fantastic opportunity to enhance your productivity and streamline your workflows

      • Live webinar: Getting the most out of WorkDrive in Zoho Workplace

        Hello everyone, We’re excited to invite you to our upcoming live webinar, where we’ll explore how to maximize your use of WorkDrive as part of the Zoho Workplace bundle. This is a fantastic opportunity to elevate your productivity and streamline your

      • Live webinar: Mastering data migration, organization, and team collaboration

        Hello everyone, We’re excited to invite you to our upcoming live webinar! Discover how to seamlessly migrate your data, optimize file organization, and boost team collaboration using Zoho WorkDrive’s powerful features. This is a fantastic opportunity

      • BARCODE PICKLIST

        Hello! Does anyone know how the Picklist module works? I tried scanning the barcode using the UPC and EAN codes I added to the item, but it doesn’t work. Which barcode format does this module use for scanning?

      • Join our live webinar: Explore the WorkDrive TrueSync application!

        Hello everyone, We are thrilled to invite you to a live webinar focused on mastering the WorkDrive TrueSync application. Discover how to seamlessly sync your content between the cloud and your computer, ensuring smooth and efficient file management. Our

      • Bug tracking

        Hi, does anyone know how to track errors during picking or packing? This way I can keep track and see how to improve and prevent errors in this area.

      • Zoho Creator customer portal limitation | Zoho One

        I'm asking you all for any feedback as to the logic or reasoning behind drastically limiting portal users when Zoho already meters based on number of records. I'm a single-seat, Zoho One Enterprise license holder. If my portal users are going to add records, wouldn't that increase revenue for Zoho as that is how Creator is monetized? Why limit my customer portal to only THREE external users when more users would equate to more records being entered into the database?!? (See help ticket reply below.)

      • Script Editor not an option

        I am trying to apply a script to a sheet and Script Editor is not an option. I don't want to go outside Sheets to do this (like Creator) if it can be done inside Sheets.

      • Envio de mails

        Hola! No puedo enviar mails pero si recibirlos. No se como solucionarlo! Mi dominio es chidobebes.com.ar

      • ERROR CODE :554 - Your access to this mail system has been rejected due to poor reputation of a domain used in message transfer

        In my email configuration: The domain's MX Records are pointed to Zoho The domain's SPF Records have been pointed out successfully DKIM is enabled. DMARC Record is pointed for the domain. The domain name is digioorja.in. Still facing the issue of Error:

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

        l tried to verify my domain (casalimpaeperfumada.com.br) and its shows this error: This Operation has been restricted. Please contact support-as@zohocorp.com for further details.

      • SLOW EMAILS

        Is there an issue with the Zoho server? For two days now I've been having issues with very long buffering. Please advise. Thank you.

      • POP3 authentication error - SOLVED

        Just in case others are as forgetful as me ... As Zoho has changed the POP server for personal and free organisational users, I needed to change the POP server on my email client. This failed persistently but eventually I remembered that I had chosen

      • Solution to Import PST File into Office 365.

        MailsDaddy OST to Office 365 Migration Tool is an outstanding solution to recover OST files and migrate them into Office 365 without any hassle. Using this software users can multiple OST files into Office 365 with complete data security. It offers users

      • Add Zoho One Groups/Departments to Shared Mailbox Access

        Hi, I hope you're doing well. Currently, in Zoho Mail, I can manually add specific users or the entire organization to a shared mailbox. However, there is no option to add Zoho One groups or departments. Feature Request: We would like the ability to assign

      • Allow Filters with Only Special Characters in Zoho Mail

        Hi Zoho Mail Team, I hope you're doing well. We have noticed that currently, Zoho Mail does not allow creating filter criteria using only special characters, such as = or #. However, there are scenarios where such a filter is necessary. For example: Filtering

      • How to save email as PDF?

        I saw 2 previous threads about this. One is from 14 years ago. The other was closed as "answered" a year ago but the feature was never implemented: https://help.zoho.com/portal/en/community/topic/how-to-download-save-emails-as-pdf Is the "save as PDF"

      • Flexible plans

        Hi, I have a Workplace Standard subscription. On Zoho's website, it mentions that with the annual plan it's possible to have multiple plans under the same organization—for example, Workplace Standard and Mail Lite. However, I can’t find a way to do this

      • Weekly Tips : Teamwork made easy with Multiple Assignees

        Let's say you are working on a big project where different parts of a single task need attention from several people at the same time—like reviewing a proposal that requires input from sales, legal, and finance teams. Instead of sending separate reminders

      • Next Page