Kaizen #10 - Search Records API and Query API

Kaizen #10 - Search Records API and Query API

Hello everyone!
Welcome back to this week's Kaizen post! In this post, we will discuss the Query API and the Search Records API, and when you should use them.

Query API

The Query API allows you to query the records using the CRM Object Query Language(COQL). COQL is based on the SQL syntax and uses the SELECT statement in the query to fetch records.

When should you use the Query API?

  • When you want to use DB comparators such as =, >, <, >=, <=, is null, is not null, between, not in, like, and not like in your search query.
  • When you want to search for records that fall into a custom view without actually creating one. Refer to the example below to know more.
  • When you want to fetch data from other modules associated through lookup fields.
  • When you want to search for fields of the data types Date, DateTime, Lookup, Number, Currency, and Boolean, in addition to string fields. Refer to Get Records through a COQL Query, for an example on each of these data types.

Here are a few examples.

1. Below is a custom view to fetch records from Leads, that belong to the Account Zylker and have the owner as shown in the image.
      

The equivalent select query for this custom view is 

 {
     "select_query": "select Last_Name, Owner.last_name from Leads where (Account.Account_Name = 'Zylker' and           (Owner = '3652397000001464001' or Owner = '3652397000000186017'))"
 }

2. Another example is shown below to fetch data from other modules associated through lookup fields.

 {
     "select_query" : 
     "select Last_Name, Account_Name.Parent_Account,Account_Name.Parent_Account.Account_Name
     from Contacts
     where Last_Name is not null and Account_Name.Parent_Account.Account_Name is not null"
}

In the Query API, you can establish a relation with the help of lookup fields that relate one module with the other. Note that you can only establish two relations.

In this query, two relations are established between the Accounts and Contacts modules. The records are fetched using the lookup field Account_Name in the Contacts module and another lookup field Parent_Account in the Accounts module.

Here, the relation Account_Name.Parent_Account returns the ID of the parent account of the account associated with the contact. The relation Account_Name.Parent_Account.Account_Name returns the name of the parent account of the account associated with the contact.

Here is the response to the above query.

{
    "data": [
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Boyle",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310003"
        },
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Patricia",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310012"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

For more details and examples, refer to the Query API.

Note
  • COQL supports only SELECT query with the clauses WHEREFROMORDER BYLIMIT, and OFFSET.
  • COQL keywords are not case-sensitive. SELECT is the same as select.
  • By default, system sorts the records in ascending order based on the record ID, if you do not include order by in the query.
  • The default value for LIMIT is 200 and OFFSET is 0.
  • You can fetch a maximum of 200 records through a single query. To fetch the next set of records, specify the value of OFFSET as 200.
  • You can establish a maximum of two relations.

Search Records API

The Search Records API allows you to run a search in a specific module, and takes a criteria, email address, phone number, or word as the parameter to narrow down the search.

When should you use this API?
  • When you want to search for text in the fields of data type "string", "phone", or "Email".
  • When you want to perform a module-level word search. For example, when your search term is "Zoho" in the Leads module, the search results include "all records" that contain the word "Zoho" in them in any field in the Leads module.

Handling parentheses and comma in the search string
When your search string includes parentheses or comma, you must escape them with a backslash(\).
Example: (Last_Name:starts_with:ABC\(inc\)), (Last_Name:starts_with:Patricia\,Boyle)

Note
If your search string has special characters apart from comma and backslash, you must encode them. Example: When you search by email, you must encode p.Boyle+z@abc.com to p.Boyle%2B@abc.com.
Refer to our Kaizen post URL Encoding while making API Calls for more details on URL encoding.

Here is an example Search Records API call.

curl "{{api-domain}}/crm/v2/Leads/search?criteria=((Last_Name:equals:Burns\,B)and(First_Name:starts_with:M))"
-X GET
-H "Authorization: Zoho-oauthtoken 1000.8cb99dxxxxxxxxxxxxx9be93.9b8xxxxxxxxxxxxxxxf"

For more details and examples, refer to the Search Records API.

Note
  • When you specify more than one parameter, only one of them would work at a time, and the order of preference is criteria, email, phone, or word.
  • When you search for records that were modified recently, those records will not be available in response due to the delay in data indexing time. So, this API is best suited for searching texts in a faster way.

We hope you found this post useful. Let us know your thoughts in the comment section or reach us out at
support@zohocrm.com.

We wish you all a Merry Christmas!
Cheers!












    Access your files securely from anywhere



                          Zoho Developer Community




                                                  • Desk Community Learning Series


                                                  • Digest


                                                  • Functions


                                                  • Meetups


                                                  • Kbase


                                                  • Resources


                                                  • Glossary


                                                  • Desk Marketplace


                                                  • MVP Corner


                                                  • Word of the Day


                                                  • Ask the Experts





                                                            Manage your brands on social media



                                                                  Zoho TeamInbox Resources



                                                                      Zoho CRM Plus Resources

                                                                        Zoho Books Resources


                                                                          Zoho Subscriptions Resources

                                                                            Zoho Projects Resources


                                                                              Zoho Sprints Resources


                                                                                Qntrl Resources


                                                                                  Zoho Creator Resources



                                                                                      Zoho CRM Resources

                                                                                      • CRM Community Learning Series

                                                                                        CRM Community Learning Series


                                                                                      • Kaizen

                                                                                        Kaizen

                                                                                      • Functions

                                                                                        Functions

                                                                                      • Meetups

                                                                                        Meetups

                                                                                      • Kbase

                                                                                        Kbase

                                                                                      • Resources

                                                                                        Resources

                                                                                      • Digest

                                                                                        Digest

                                                                                      • CRM Marketplace

                                                                                        CRM Marketplace

                                                                                      • MVP Corner

                                                                                        MVP Corner





                                                                                          Design. Discuss. Deliver.

                                                                                          Create visually engaging stories with Zoho Show.

                                                                                          Get Started Now


                                                                                            Zoho Show Resources


                                                                                              Zoho Writer Writer

                                                                                              Get Started. Write Away!

                                                                                              Writer is a powerful online word processor, designed for collaborative work.

                                                                                                Zoho CRM コンテンツ










                                                                                                  Nederlandse Hulpbronnen


                                                                                                      ご検討中の方




                                                                                                            • Recent Topics

                                                                                                            • need a third party to fix email authentication dns records

                                                                                                              at my wit's end - zoho began giving me spf, dmarc, dkim errors two weeks ago fussed with it since and now it seems dkim is the only problem and when i added the dkim record with the key from zoho mail it still wont work tired of this, need someone who
                                                                                                            • Custom code ass to header and footer code doesn't appear

                                                                                                              I am trying to insert custom code into both the header and footer of my Zoho site. I edit the site "general settings" as demonstrated in the various articles, click save, but the code does not appear on my site. Is there something special I need to do
                                                                                                            • Differentiating between invoice templates when adding custom fields

                                                                                                              We are adding HS codes to the items which we would like published on our international customers invoices plus other export data required. We don't need this data on our domestic customer invoices. We have setup an invoice template for them but it if
                                                                                                            • Market Automation is hung in sync with crm in zoho one

                                                                                                              Sync between Market automation and crm stuck in loop. MA says they are connected, but does not allow module selection, building of lists, etc. can not go back around and disconnect and restart. Does not show in "Connected Apps", yet when you ty to connect
                                                                                                            • Currency Field Does Not show commas upon entry - leading to inaccurate entries!

                                                                                                              Hello Zoho.. When our sales reps are entering deals and the profit/revenue it is difficult to accurately enter numbers with lots of zeros when there commas are NOT added until the record is saved. Could commas be added to this filed type as it is being
                                                                                                            • Field of Lookup Module 2 Way Dynamic Connection

                                                                                                              I have several lookup fields in my custom Module And I have added fields from lookup fields to my module In my account module, I have a field called Quarterly Revenue Forecast But this is only one way i-e the field value is only fetched during record
                                                                                                            • Kaizen #116 - Client Types in Zoho API Console

                                                                                                              Hello everyone! Welcome back to another post in the Kaizen series! This week, we will discuss different client types available in Zoho API Console, and when to use each. When you register an app in Zoho API Console, you typically choose a client type
                                                                                                            • Used Car Inventory Tracker

                                                                                                              I need a to create an inventory database that has detailed information about each car we have in our inventory. Specifically, I need to know exactly how much labor and parts we have into each car as well as the cars purchase price, VIN, color, year, source etc. Budget : Negotiable | Duration : 1 week To leave a private message, please click the link for private response in post Action section.
                                                                                                            • Create Funnel to Track Email Outreach Conversion

                                                                                                              Hello, We would like to create a funnel that measures: N° of emails sent -> N° of emails opened -> N° of emails responded We would like to measure this email response conversion rate for each of our SDRs. We use the analytics tool of Zoho CRM and not
                                                                                                            • Zoho CRM - Analytics: How Can I Set Monday as First Day of The Week?

                                                                                                              Hello, In the analytics section of our Zoho CRM (not Zoho Analytics), we would like to track our SDRs' outreach activity. However, it appears that Zoho CRM treats Sunday as the first day of the week for the "This Week" and "Previous Week" filters. How
                                                                                                            • Flow-Level Access Permissions in Zoho Flow

                                                                                                              Hi Zoho Flow Team, Hope you're doing well. We would like to request a feature enhancement for Zoho Flow related to user-level access control. Use Case: As Zoho One users, we often work with multiple teams and departments. In our setup, we have several
                                                                                                            • Tip of the week 63- Know your contacts well with polls in Zoho Campaigns

                                                                                                              Communication in its true form is characterised by feedback. In email campaigns there are a few avenues via which you can achieve this. Using polls in Zoho Campaigns is one such way. With polls enabled in campaigns, your contacts can interact with your emails and help you understand them well.     There are three types of polls available to be enabled in your email campaigns:   Basic poll Rating based poll Reaction based poll     Basic poll-    Provide a question and allow the email recipients to
                                                                                                            • Subform data to Sheets

                                                                                                              I have been trying to setup a Zoho Flow automation to bring any Subform input to a Zoho Sheets but it seems impossible to post the subform entries to a Zoho Sheet. Is there any way to do it via Zoho Sheet API? https://www.zoho.com/sheet/help/api/v2/#CONTENT-Insert-row-with-JSON-data
                                                                                                            • The Invoice Status in Zoho Finance is misleading

                                                                                                              We have many overdue invoices, but when we try to filter it by Status Overdue in the Zoho Finance Module it shows it as none This is also creating a problem when I need to Create a Chart or KPI for overdue Invoices If I open the Invoice I can see the
                                                                                                            • Filter by technical IDs that should not be displayed

                                                                                                              Hello Zoho and Cumminity. I know I have already found similar requests elsewhere, but have not yet received a solution from Zoho. Therefore I would like to refresh the topic and hope that a solution can be found. I have reports in the Creator, which I
                                                                                                            • Problem Pasting Into Notes

                                                                                                              Occasionally I want to create a note by copying and pasting a few paragraphs from an article on line. When I create a new note and paste in the section the newly created note winds up with each paragraph in white text on a dark background rather than
                                                                                                            • Bank transaction automation

                                                                                                              I would like to automatically update the "Reference#" field with whatever text is in the "Description" field each time Zoho Books pulls my bank feeds. I've figured out the automation all the way up to the Field Update, where "Reference Number = ...???"
                                                                                                            • Payment Gateways For South Africa

                                                                                                              Hi, Can you please incorporate a payment gateway like Payfast etc for South African clients?
                                                                                                            • 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
                                                                                                            • Sales IQ - Bot Builder - Forward to Operator Action Card Improvement

                                                                                                              Hi team, It would be a great improvement to have an additional branch out of the Forward to Operator Action Card. I would like to allow 10 seconds for an operator to pick up the chat, if they don't or if they reject the chat I would like the Bot to continue
                                                                                                            • Email template for customer notification on ticket reply is not being used

                                                                                                              I've set up an email template for notify contact up upon receiving a reply in ticket. When I send a reply to the ticket, it is not using that email template. It just sends a normal looking email. How do I make it so that the email template is being used?
                                                                                                            • Global Fields

                                                                                                              Just like Global Sets for Picklists, we would like to have global fields for any kind of field. Three things that should be saved globally: 1. The Existence of the field 2. The Name and 3. Association with a module should be set up in a respective place
                                                                                                            • "In Zoho CRM, during the Blueprint transition to the QC stage, I want to make the 'Packing Proof' image field mandatory."

                                                                                                              @Dr Saurabh Joshi @Haiku Technical Support @Ishwarya SG @Sparrow Hill President @Hugh Marshall "In Zoho CRM, during the Blueprint transition to the QC stage, I want to make the 'Packing Proof' image field mandatory."
                                                                                                            • how do i see a list of active End Users?

                                                                                                              looking for a list of who i sent invitations to be an End User?  who has signed up? 
                                                                                                            • Modular cannot Edit in portals

                                                                                                              I have a custom module in CRM. If I create in either CRM or portals, I can edit it in the CRM but I can't edit in the portal. Even if it is created in the portal it wont edit. Anyone know why? I've created a new module and it works fine but this one
                                                                                                            • Zoho Desk - Feature Request - Add more social channels on Community user profile

                                                                                                              Hi Team, While updating my profile here I noticed that it is only possible to add Facebook and Twitter social links. 1. Please consider adding at least LinkedIn and if possible, other popular channels. 2. Please consider renaming Twitter field name to
                                                                                                            • Hide Admin Only settings

                                                                                                              It would be nice if the setup menu items and settings weren't visible to non-admins. Seems like there is some confusion with users going through setup pages but not having permissions and getting an invalid permission error.
                                                                                                            • "In Zoho CRM, during the Blueprint transition to the image field mandatory and file upload field mandatory

                                                                                                              I want image and file upload field want to be mandatory during the blueprint transition In the QC done Transtion Iwant to make QC done file upload and Case QR Sticker Mandatory In Pre delivery Stage i want to make Site ready file upload field mandagtory
                                                                                                            • Lookups from Standard Modules to Custom Modules

                                                                                                              I have created an "External Contacts" Custom Module for adding Contacts who aren't directly associated with a Customer or Vendor but who are related to Orders by being a Site Contact, Job Contact, Warehouse Contact, etc for third party. How can I go about
                                                                                                            • Using Zobot with ChatGPT Assistant Function to Trigger API Call (e.g., Weather Info)

                                                                                                              I am currently integrating Zobot with an OpenAI ChatGPT Assistant using function calling. The goal is to let the Assistant trigger specific API actions based on user queries — for example, fetching the current weather when a user asks, “What’s the weather
                                                                                                            • Train Zia answer bot on only part of Knowledge Base?

                                                                                                              We are trialing Zia answer bot and hope to use it on the knowledge base to help our users find the information they are looking for. I have found how to train Zia on the entirety of our knowledge base. But is there a way to train it on only certain categories
                                                                                                            • Recurring Events Not Appearing in "My Events" and therefore not syncing with Google Apps

                                                                                                              We use the Google Sync functionality for our events, and it appears to have been working fine except: I've created a set of recurring events that I noticed were missing from my Google Apps calendar. Upon further research, it appears this is occurring
                                                                                                            • How to identify a ticket merge through Webhooks?

                                                                                                              The ticket merge functionality moves all Threads and Comments from one Ticket to another. I want to identify this action to correctly apply these changes on my application end. Currently, my application only receives a Ticket_Update notification for the
                                                                                                            • Native SMS Integration in Zoho Desk

                                                                                                              I’d like to request the addition of native SMS integration within Zoho Desk. While email and chat are still widely used, SMS has become a critical channel for fast, effective customer support, especially for urgent or time-sensitive issues. At the moment,
                                                                                                            • Announcing Early Access to "Zoho CRM for Everyone" — A new and exciting update to Zoho CRM

                                                                                                              We are delighted to announce an Early Access to Zoho CRM for Everyone— a truly democratic approach to managing a CRM, gift-wrapped in an exciting and intuitive user interface. Here, multiple teams across an organization can coordinate among each other
                                                                                                            • Share Projects with Vendor Zoho Projects Portal

                                                                                                              I have a vendor/reseller of my services. They private label my services. My portal is branded. Can an individual project be "shared" or the data sync with another portal? I believe that this can be done with CRM.
                                                                                                            • Zoho Books - Sales Person Information

                                                                                                              Hi Team, On Invoices, Quotes, etc... I can include the Sales Person, but it only shows their name and not their email or phone number. It would be great to have place on invoice templates where we can manage what sales person information should be shows
                                                                                                            • Feature Request – Support for Stripe Direct Debit for Canadian Customers in Zoho Books

                                                                                                              I’d like to request support for Stripe Direct Debit as a payment option for Canadian customers within Zoho Books. Currently, while Stripe credit card payments are supported for Canadian businesses, there is no option to enable Direct Debit (ACH/EFT) through
                                                                                                            • Zoho Desk blank page

                                                                                                              1. Click Access zoho desk on https://www.zoho.com/desk/ 2. It redirects to https://desk.zoho.com/agent?action=CreatePortal and the page is blank. Edge browser Version 131.0.2903.112 (Official build) (arm64) on MacOS
                                                                                                            • Timentry and Support Plan Relationship

                                                                                                              Timentry and Support Plan Relationship A customer can buy multiple products and request different SLAs and support plans for each product. We can enter different support plans and define the credit. The scenario I want to happen; - To reduce the credits
                                                                                                            • Next Page