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

• 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

• Weekly Tips : Customize your Compose for a smoother workflow

  You are someone who sends a lot of emails, but half the sections in the composer just get in your way — like fields you never use or sections that clutter the space. You find yourself always hunting for the same few formatting tools, and the layout just

• Zoho Slowness - Workarounds

  Hi all, We've been having intermittent slowness and Zoho just asks for same stuff each time but never fix it. It usually just goes away on it's own after a couple weeks. Given that speed is a very important thing for companies to be able to keep up with

• Custom Bulk Select Button

  Zoho CRM offers the ability to select multiple records and invoke a Custom Button This functionality is missing from Recruit Currently we can only add buttons in the detail page and list But we cannot select Multiple Records and invoke a function with

• How to create a Zoho CRM report with 2 child modules

  Hi all, Is it possible to create a Zoho CRM report or chart with 2 child modules? After I add the first child module, the + button only adds another parent module. It won't let me add multiple child modules at once. We don't have Zoho Analytics and would

• Zoho CRM still doesn't let you manage timezones (yearly reminder)

  This is something I have asked repeatedly. I'll ask once again. Suppose that you work in France. Next month you have a trip to Guatemala. You call a contact there, close a meeting, record that meeting in CRM. On the phone, your contact said: "meet me

• Power of Automation :: Smart Ticket Management Between Zoho Desk and Projects

  Hello Everyone, A custom function is a software code that can be used to automate a process and this allows you to automate a notification, call a webhook, or perform logic immediately after a workflow rule is triggered. This feature helps to automate

• First day of trying FSM in the field.

  What we found. 1. with out a network connection we were unable to start a service call? 2. if you go to an appointment and then want to add an asset it does not seem possible. 3. disappointed not to be able to actually take a payment from within the app

• BUG - Google Business Buttons - Add a button to GBP Post

  I am experiencing an issue with the "Add a button" feature when creating posts for my Google Business Profile (GBP) through Zoho Social. When I schedule or publish a GBP post and include a call-to-action button with a specific URL, the post itself publishes

• Rich text Merge field - Not using font specified in HTML

  I have a rich text merge field in a writer template which is creating a table. I have chosen to use this method instead of a repeat region because I need to specify specific cell background colours which change every time the document is created. The

• Support for Custom Fonts in Zoho Recruit Career Site and Candidate Portal

  Dear Zoho Recruit Team, I hope you're doing well. We would like to request the ability to use custom fonts in the Zoho Recruit Career Site and Candidate Portal. Currently only the default fonts (Roboto, Lato, and Montserrat) are available. While these

• CC and/or BCC users in email templates

  I would like the ability to automatically assign a CC and BCC "User (company employee)" into email templates. Specifically, I would like to be able to add the "User who owns the client" as a CC automatically on any interview scheduled or candidate submitted

• Trying to export a report to Excel via a deluge script

  I have this code from other posts but it gives me an error of improper statement, due to missing ; at end of line or incomplete expression. Tried lots of variations to no avail. openUrl(https://creatorapp.zoho.com/<username>/<app name>/XLSX/#Report:<reportname>,"same

• Zoho Reports Duplicating Entries

  I have a custom costing tab with a table where we entre invoices. These are under a Heading (PO Subject) and notes added in the form with different line items. In the reports, I have organised the report to group per PO Subject, with the total of the

• Need help to create a attach file api

  https://www.zoho.com/crm/developer/docs/api/v8/upload-attachment.html Please help me to create it... It's not working for while. Do you have some example?

• Export view via deluge.

  Hi, Is it possible to export a view (as a spreadsheet) via deluge? I would like to be able to export a view as a spreadsheet when a user clicks a button. Thanks     

• how to add subform over sigma in the CRM

  my new module don't have any subform available any way to add this from sigma or from the crm

• Outdated state in mexico

  Hello Zoho team, the drop down to add the state for customers, when they introduce their state in mexico has a city named “Distrito Federal” that name changed many years ago to “ciudad de mexico”. could you please update this so my clients can find the

• Support new line in CRM Multiline text field display in Zoho Deluge

  Hi brainstrust, We have a Zoho CRM field which is a Muti Line (Small) field. It has data in it that has a carriage return after each line: When I pull that data in via Deluge, it displays as: I'm hoping a way I can change it from: Freehand : ENABLED Chenille

• Possible to generate/download Quote PDF using REST API?

  See title. Is there any way after a quote has been created to export to a PDF using a specified template and then download it? Seems like something that should be doable. Is this not supported in the API v2.0?

• Creating an invoice to be paid in two installments?

  Hi there, I own a small Photographic Services business and have not been able to find a way to fit my billing system into Zoho, or any other Accounting software. The way my payments work is: 1. Customer pays 50% of total price of service to secure their

• Bug in allowing the user to buy out of stock items

  Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

• Zoho CRM Calendar | Custom Buttons

  I'm working with my sales team to make our scheduling process easier for our team. We primary rely on Zoho CRM calendar to organize our events for our sales team. I was wondering if there is a way to add custom button in the Calendar view on events/meeting

• 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

• Can a Zoho Sites page be embedded into another website (outside Zoho)

  Hi All, We have a request from a client - they'd like to take one of our information pages created in Zoho Sites and embed it into their own website? I was told through an email with Zoho that this was possible >>Thank you for your patience regarding

• Bug in allowing the user to buy out of stock items

  Hi i want to allow the user to buy out of stock items, according to the commerce documentation if i disable Restrict "Out of stock" purchases it will, but it doesnt work, so i want to know if it had any relation with zoho inventory, and if theres any

• Transition Criteria Appearing on Blueprint Transitions

  On Monday, Sept. 8th, the Transition criteria started appearing on our Blueprints when users hover over a Transition button. See image. We contacted Zoho support because it's confusing our users (there's really no reason for them to see it), but we haven't

• Zoho CRM Sales Targets for Individual Salespeople

  Our organistion has salespeople that are allocated to different regions and have different annual sales targets as a result. I am building an CRM analytics dashboard for the sales team, which will display a target meter for the logged in salesperson.

• All new Address Field in Zoho CRM: maintain structured and accurate address inputs

  The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Managing addresses

• Transfer all Related Data to new Account Owner

  Currently when I change the account Owner I only see the option to change only the open deals But I want the new account owner to take over all the related modules and all the deal stages Is it not possible right now? Am I missing something? Do I really

• Can i connect 2 instagram accounts to 1 brand?

  Can i connect 2 instagram accounts to 1 brand? Or Do i need to create 2 brands for that? also under what subscription package will this apply?

• How to Calculate MTTR (Mean Time to Resolve)

  We want to calculate MTTR (Mean Time to Resolve) in our Zoho Analytics report under Tickets. Currently, we are using the following fields: Ticket ID Ticket Created Time Ticket Closed Time Ticket On Hold Time We are planning to calculate MTTR (in days)

• How to export project tasks, including the comments

  Hi, how can I export the project tasks, whereby I can also see the comments associated to a specific task? The use-case is that often we use comments to discuss or update a task related ideas. I would like to export the tasks, where we can also see the

• How to Install Zoho Workdrive Desktop Sync for Ubuntu?

  Hi. I am newbie to Linux / Ubuntu. I downloaded a tar.gz file from Workdrive for installing the Workdrive Desktop Sync tool. Can someone give me step by step guide on how to install this on Ubuntu? I am using Ubuntu 19.04. Regards Senthil

• Introducing Version-3 APIs - Explore New APIs & Enhancements

  Happy to announce the release of Version 3 (V3) APIs with an easy to use interface, new APIs, and more examples to help you understand and access the APIs better. V3 APIs can be accessed through our new link, where you can explore our complete documentation,

• Round robin

  Hi, I'm trying to set up a round robin to automatically distribute tickets between agents in my team but only those tickets that are not otherwise distributed by other workflows or direct assignments. Is that possible and if so which criteria should I

• Next Page