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




                                  Zoho Desk Resources

                                  • Desk Community Learning Series


                                  • Digest


                                  • Functions


                                  • Meetups


                                  • Kbase


                                  • Resources


                                  • Glossary


                                  • Desk Marketplace


                                  • MVP Corner


                                  • Word of the Day



                                      Zoho Marketing Automation


                                              Manage your brands on social media



                                                    Zoho TeamInbox Resources

                                                      Zoho DataPrep Resources



                                                        Zoho CRM Plus Resources

                                                          Zoho Books Resources


                                                            Zoho Subscriptions Resources

                                                              Zoho Projects Resources


                                                                Zoho Sprints Resources


                                                                  Qntrl Resources


                                                                    Zoho Creator Resources



                                                                        Zoho Campaigns 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