Kaizen #60 - Get Records, Search Records, Query and Bulk Read APIs

Hi everyone.. Welcome back!

In this post, we will discuss when to use Get Records, Search Records, Query, and Bulk Read APIs to fetch records. Please note that the information in the article holds true for version 3 of Zoho CRM APIs.

Get Records API

The GET Records API allows you to fetch all the records from a module in your CRM account. 

When should you use GET Records API?

       Use this API to fetch a specific set of records whose IDs you already know, or to fetch all the records from a module without any filters, or using the pre-defined filters.

      For instance, when you want to render a mobile/web UI to show the list of records in a module or a specific custom view, you can make use of Get Records API.

Pagination

• You can retrieve up to 200 records using this API in a single call.
• To fetch more than 200 records, you must use pagination and make further API requests with the page parameter.
• For more than 2000 and up to a maximum of 100,000 records, use the page_token parameter in the request. The value for this parameter is available from the key "next_page_token" in the response of the first Get Records call. 
• You cannot use the page_token parameter with the page parameter.

Points to remember

• You should specify the field API names to fetch the records.
• You can fetch up to 50 fields using this API.
• You can sort the records by id, Created_Time and Modified_Time fields.
• Each Get Records API call consumes one API credit.

Examples

1. To fetch the records from 1801-2000 in the Leads module with the Last_Name and Email fields

[GET] "https://www.zohoapis.com/crm/v3/Leads?fields=Last_Name,Email&per_page=200&page=10"

The info JSON object in the response contains the next_page_token to retrieve the next set of records. 

2. To fetch after 2000 records

[GET] "https://www.zohoapis.com/crm/v3/Leads&fields=Last_Name,Email&per_page=200  &page_token="db8d4276axxxxxxx713748a4e""

Search Records API

Search Records API lets you search for records in a module based on a criteria that you define. You can search for records with a specific word in the text fields, or by email or phone fields across the specified module. You can also search for a variety of other fields like currency, Date, DateTime, picklists etc.

When should you use Search Records API?

      Use this API when you want to search for a specific text, phone or email in the records of a module, or when you want to do a module-level word search. Search API is the go-to API  when you know the details like email, phone or a specific text, but not the individual IDs of the records.

      For example, when you want to render a search box in your web/mobile UI, and do a module-level search for the word, use Search Records API. 

Pagination

• You can search for up to 200 records in one API request.
  
• To fetch more than 200 and up to 2000 records, use the page parameter.
  

Points to remember

• Your search criteria can have  equals, starts_with, in, equals, not_equal, greater_equal, greater_than, less_equal, less_than, starts_with, or between conditions.
• There will be a delay in accessing recently inserted or updated records using this API due to data indexing.
  
• Due to the indexing delay, it is advised not to use Search API to check for record duplication while creating a new record. 
• Each Search Records API call consumes one API credit. 
  

Examples

1. To search for all the Leads with the word Zoho in the record

[GET] curl "https://www.zohoapis.com/crm/v3/Leads/search?word=Zoho"

2. To fetch records from the Leads module whose Last_Name is 'Thomas' and First_Name starts with 'M'

[GET] curl "https://www.zohoapis.com/crm/v3/Leads/search?criteria=((Last_Name:equals:Thomas)and(First_Name:starts_with:M))"

Query API

Query API lets you query for records based on queries using the CRM Object Query Language(COQL). COQL is based on the SQL query syntax, and supports the SELECT query to fetch records. Using this API, you can query for data across different modules that are linked using lookup fields.  This API allows you to search for a variety of fields like picklist, Date, DateTime, Lookup, Number, Currency, Boolean, or string fields, and sort the records on the basis of any field.

When should you use Query API?

      To query for a module's data and its lookup related data using various comparators, or when you want to query for records that fall into a custom view without actually creating one.

      For instance, when you want to filter all the products in a specific price range, with 4 stars and up rating, use Query API.

      You can also use Query API to query for records from cross module (linked via lookup field). Eg: Filtering deals based on the account's location and industry type. In this example, Deals is the primary module linked with Accounts module via the lookup field 'Accounts_Name'.

Pagination

• You can retrieve 200 records in one API call using this API.
  
• To fetch more than 200 records and upto 10000 records, use the OFFSET clause. 
  

Points to remember

• You can use DB comparators such as =, >, <, >=, <=, is null, is not null, between, in, not in, like, and not like in your search query.
  
• You can fetch a maximum of 10000 records using this API.
  
• You can use Query API to check for record duplication while creating a new record in your web/mobile UI apps.
  

• Each Query API call consumes one API credit.

Examples

1. Cross Module Filtering : To filter deals based on its corresponding account's location and industry type.

"select_query": "select Deal_Name, Stage, Probability, Type, Account_Name from Deals where ((Account_Name.Location = 'Texas') AND (Account_Name.Industry = 'Military'))"

2. Consider the following custom view.

You can fetch the records in the custom view using the following SELECT query, without actually creating the custom view.

{ "select_query": "select Last_Name, Owner.last_name from Leads where ((Industry  = 'ERP') and (Owner = '4876876000000327001')) OR (Rating =    'Acquired')" }

For more examples, refer to our Get Records through COQL page. 

TIP : If you want to fetch all the records in a module using COQL Query API, you can still use the COQL query to do it. For example, the below example will fetch all the records in the Leads module.

{  "select_query": "select Last_Name, Owner.last_name from Leads where ((Industry  = 'ERP') OR (Industry  != 'ERP'))"}

Bulk Read API

Bulk Read API lets you export data in bulk from a module. Using this API, you can export records based on a query, or export all the records in a module with or without filters. The main advantage of Bulk read API is in the huge number of records it can retrieve in one API call. While you can fetch 200 records per API call for GET Records API, Search Records API and Query API, you can export 200,000 records per API call using the Bulk read API. 

When should you use Bulk read API?

When you want to schedule a job to export huge volume of CRM data.

For instance, when you want to export the list of all the records from a  Zoho CRM  module to sync with your local ERP system's data store, use Bulk read API. 

Points to remember

• Bulk read is an asynchronous API.
  
• The result of the bulk read job will be available as a downloadable CSV/ICS file.
  
• Each Bulk read API call consumes 50 API credits.
  
• To export more than 200,000 records, use the page key in the request body. Setting page to '2' exports data starting from 200,001.
  

Sample Scenario

Let us now consider the case where you want to fetch (a)2000, (b)5000, (c)10000 and (d)15000 records from a module, and see which API is best-suited for each case.

Number of records	Get Records API	Search Records API	Query API	Bulk read API
2000	10 API calls 10 API credits Use pagination	10 API calls 10 API credits Use pagination	10 API calls 10 API credits Use OFFSET	1 API call 50 API credits
5000	25 API calls 25 API credits use pagination (up to 2000 records) & page_token (after 2000 records)	Not possible to fetch more than 2000 records	25 API calls 25 API credits Use OFFSET	1 API call 50 API credits
10000	50 API calls 50 API credits use pagination (up to 2000 records) & page_token (after 2000 records)	Not possible to fetch more than 2000 records	50 API calls 50 API credits Use OFFSET	1 API call 50 API credits
15000	75 API calls 75 API credits use pagination (up to 2000 records) & page_token (after 2000 records)	Not possible to fetch more than 2000 records	Not possible to fetch more than 10000 records	1 API call 50 API credits

To sum up, for up to 2000 records, the Get Records API, Search Records API, and Query API all use the same API credits. However, Query API has the edge over the other two because it provides you with greater flexibility to tweak your criteria, thanks to all the comparators and logical operations it supports.  If you intend to fetch 10,000 or more records asynchronously, the Bulk Read API is preferable in terms of API credits consumed. For synchronously fetching more than 10,000 records, use the Get Records API.

Feature	Get Records API	Search Records API	Query API	Bulk Read API
Maximum number of records per API call	200	200	200	200,000  For Events - 20,000
Maximum number of records that can be fetched	100,000	2000	10,000	100,000,000
Indexing Delay	No	Yes	No	NA
Pagination	Use page parameter to retrieve more than 200 records and page_token parameter for more than 200 records	Use page parameter to retrieve up to 2000 records	Use LIMIT and OFFSET to navigate and retrieve a maximum of up to 10,000 records	Use page key in the request body to fetch more than 200,000 records. Maximum possible value for page is 500
Sorting support	Limited (id, Created_Time and Modified_Time fields )	No	Yes	No
Credits per API call	1  3 - with CVID parameter	1	1	50

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

Thank You!

Previous Kaizen Post : Creating alerts and custom messages using Client Script

Next Kaizen Post : Composite API

• Topic Participants

• Anu Abraham

  

• Abhishek - Zoho Partner

  

• Juan

• Ishwarya SG

  

• Rodger Brehaut

• 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

• CRM : Function to add user name to text field

  I have a lookup field in a module that is linked to the CRM users so we can assign a Project Lead to the customer. Sadly Zoho Marketing Automation doesn't sync Lookup fields so I need to extract information from the lookup to text fields: Lookup field

• Upload API

  I'm trying to use the Upload API to upload some images and attach them to comments (https://desk.zoho.com/DeskAPIDocument#Uploads#Uploads_Uploadfile) - however I can only ever get a 401 or bad request back. I'm using an OAuth token with the Desk.tickets.ALL

• Losing description after merging tickets

  Hello, We merge tickets when they are about the same topic from the same client. It happens sometimes. We recently noticed that after the merger only the description from the master ticket is left in a thread. And the slave-ticket description is erased.

• Option to Empty Entire Mailbox or Folder in Zoho Mail

  Hello Zoho Mail Team, How are you? We would like to request an enhancement to Zoho Mail that would allow administrators and users to quickly clear out entire folders or mailboxes, including shared mailboxes. Current Limitation: At present, Zoho Mail only

• update linked contacts when update happens in account

  Hi, I have a custom field called Licence in the Accounts module. When someone buys a licence, I’d like to update a custom field in the related Contacts. How can I achieve this? I noticed that workflows triggered on Accounts only allow me to update fields

• Problem Management Module

  I am looking for a Problem Management module within Zoho Desk. I saw in some training videos that this is available, and some even provided an annual price for it. I want an official confirmation on whether this is indeed available. This is not a particularly

• Deluge sendmail in Zoho Desk schedule can't send email from a verified email address

  I am trying to add a scheduled action with ZDesk using a Deluge function that sends a weekly email to specific ticket client contacts I've already verified the email address for use in ZDesk, but sendmail won't allow it in its "from:" clause. I've attached

• Unable to explore desk.zoho.com

  Greetings, I have an account with zoho which already has a survey subscription. I would like to explore desk.zoho.com, but when I visit it while logged in (https://desk.zoho.com/agent?action=CreatePortal) I just get a blank page. I have tried different

• Offline support for mobile app

  Accessing your files and folders from your mobile devices is now quicker and simpler, thanks to the power of offline support. Whether on an Android or iOS device, you can use the Offline function to save files and folders, so you can review them even

• Zoho Desk KB article embedded on another site.

  We embed KB articles from Zoho Desk on another site (our application). When opening the article in a new tab, there is no issue, but if we choose lightbox, we are getting an error "To protect your security, help.ourdomain.com will not allow Firefox to

• Transitioning to API Credits in Zoho Desk

  At Zoho Desk, we’re always looking for ways to help keep your business operations running smoothly. This includes empowering teams that rely on APIs for essential integrations, functions and extensions. We’ve reimagined how API usage is measured to give

• List of packaged components and if they are upgradable

  Hello, In reference to the article Components and Packaging in Zoho Vertical Studio, can you provide an overview of what these are. Can you also please provide a list of of components that are considered Packaged and also whether they are Upgradable?

• Does Attari Messaging app have Bot option and APIB

  Hi, Does Attari also have Bot and API as we use in WhatsApp??

• how to use validation rules in subform

  Is it possible to use validation rules for subforms? I tried the following code: entityMap = crmAPIRequest.toMap().get("record"); sum = 0; direct_billing = entityMap.get("direct_billing_details"); response = Map(); for each i in direct_billing { if(i.get("type")

• How to add application logo

  I'm creating an application which i do not want it to show my organization logo so i have changed the setting but i cannot find where to upload/select the logo i wish to use for my application. I have seen something online about using Deluge and writing

• Introducing Keyboard Shortcuts for Zoho CRM

  Dear Customers, We're happy to introduce keyboard shortcuts for Zoho CRM features! Until now, you might have been navigating to modules manually using the mouse, and at times, it could be tedious, especially when you had to search for specific modules

• Empowered Custom Views: Cross-Module Criteria Now Supported in Zoho CRM

  Hello everyone, We’re excited to introduce cross-module criteria support in custom views! Custom views provide personalized perspectives on your data and that you can save for future use. You can share these views with all users or specific individuals

• Send Automated WhatsApp Messages and Leverage the Improved WhatsApp Templates

  Greetings, I hope all of you are doing well. We're excited to announce a major upgrade to Bigin's WhatsApp integration that brings more flexibility, interactivity, and automation to your customer messaging. WhatsApp message automation You can now use

• Verifying Zoho Mail Functionality After Switching DNS from Cloudflare to Hosting Provider

  I initially configured my domain's (https://roblaxmod.com/) email with Zoho Mail while using Cloudflare to manage my DNS records (MX, SPF, etc.). All services were working correctly. Recently, I have removed my site from Cloudflare and switched my domain's

• Zoho Analytics Regex Support

  When can we expect full regex support in Zoho Analytics SQL such as REGEXP_REPLACE? Sometimes I need to clean the data and using regex functions is the easiest way to achieve this.

• 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

• Show Attachments in the customer portal

  Hi, is it possible to show the Attachments list in the portal for the particular module? Bests.

• Does Zoho Docs have a Line Number function ?

  Hi, when collaborating with coding tasks, I need an online real time share document that shows line numbers. Does Zoho's docs offer this feature ? If yes, how can I show them ? Regards, Frank

• Please make it easier to Pause syncing

  right now it takes 3 clicks to get there. sounds silly, but can you make it just 2 clicks to get it done instead? thats how dropbox does it, 2 clicks to pause instead of 3.

• Feature Request - Insert URL Links in Folders

  I would love to see the ability to create simple URL links with titles in WorkDrive. or perhaps a WorkDrive extension to allow it. Example use case: A team is working on a project and there is project folder in WordDrive. The team uses LucidChart to create

• Organization Emails in Email History

  How can I make received Org Emails to show up here?

• Converting Sales Order to Invoice via API; Problem with decimal places tax

  We are having problems converting a Sales Order to an Invoice via API Call. The cause of the issue is, that the Tax value in a Sales Order is sometimes calculated with up to 16 decimal places (e.g. 0.8730000000000001). The max decimal places allowed in

• how to differentiate if whatsapp comes from certain landing page?

  I create a Zobot in SalesIQ to create a Whatsapp bot to capture the lead. I have 2 landing pages, one is SEO optimized and the other want is optimized for leads comes from Google Ads. I want to know from which landing page this lead came through WhatsApp

• How to sync from Zoho Projects into an existing Sprint in Zoho Sprints?

  Hi I have managed to integrate Zoho Projects with Zoho Sprints and I can see that the integration works as a project was created in Zoho Sprints. But, what I would like to do is to sync into an existing Zoho Sprints project. Is there a way to make that

• How to record company set up fees?

  Hi all, We are starting out our company in Australia and would appreciate any help with setting up Books accounts. We paid an accountant to do company registration, TFN, company constitution, etc. I heard these all can be recorded as Incorporation Costs, which is an intangible asset account, and amortised over 5 years. Is this the correct way to do it under the current Australian tax regulations? How and when exactly should I record the initial entry and each year's amortasation in Books? Generally

• How to create a drop down menu in Zoho Sheets

  I am trying to find out, how do I create a drop down option in Zoho sheet. I tried Data--> Data Validation --> Criteria --> Text  --> Contains. But that is not working, is there any other way to do it.  Thanks in Advance.

• Show Payment terms in Estimates

  Hi,  we are trying to set up that estimates automatically relates payment terms for the payment terms we introduced on Edit contact (Field Payment terms).  How can it be done? Our aim is to avoid problems on payment terms introduced and do not need to introduce it manually on each client (for the moment we are introducing this information on Terms and Conditions.  Kind Regards, 

• Rich-text fields in Zoho CRM

  Hello everyone, We're thrilled to announce an important enhancement that will significantly enhance the readability and formatting capabilities of your information: rich text options for multi-line fields. With this update, you can now enjoy a more versatile

• How can I calculate the physical stock available for sale?

  Hey Zoho Team,  I've tried to calculate the physical stock on hand in various ways - but always receive a mismatch between what's displayed in Zoho Inventory & analytics.  Can you please let me know how the physical stock available for sale is calculated?

• When dispatched to crew, assigning lead missing

  Hello, For the past two or three weeks, whenever an officer assigns Service Appointment to a team, the lead person is missing from the assigned service list. Therefore, we have to reschedule the SA and then the lead person becomes visible in the assigned

• Create Lead Button in Zoho CRM Dashboard

  Right now to create Leads in the CRM our team is going into the Lead module, selecting the "Create Lead" button, then building out the lead. Is there anyway to add the "Create Lead" button or some sort of short cut to the Zoho CRM Dashboard to cut out

• open word file in zoho writer desktop version

  "How can I open a Microsoft Word (.doc or .docx) file in Zoho Writer if I only have the file saved on my computer and Zoho Writer doesn't appear as an option when I try 'Open with'? Is there a way to directly open the .doc file in Zoho Writer?"

• Generate leads from instagram

  hello i have question. If connect instagram using zoho social, it is possible to get lead from instagram? example if someone send me direct message or comment on my post and then they generate to lead

• I want to transfer the project created in this account to another account

  Dear Sir I want to transfer the project created in one account to another account

• Inactive User Auto Response

  We use Zoho One, and we have a couple employees that are no longer with us, but people are still attempting to email them. I'd like an autoresponder to let them no the person is no longer here, and how they can reach us going forward. I saw a similar

• Next Page