Kaizen #124 - Manipulating Subform using Zoho CRM APIs
Hello everyone!
Welcome back to another post in our Kaizen series.
In this post, we will discuss how to manipulate the Subform data using Zoho CRM APIs.
Subforms
A Subform is a data section embedded in the primary form to collect details related to the parent record. It helps in maintaining multiple records under a single parent record.
Using subform, you can create a parent-child relationship between modules, where the parent module represents the primary data and the child module contains the related data.
Data Model Representation
The above diagram shows the data model representation when you create a subform in a module. Use Case
Consider Zylker Consulting organization using Zoho CRM to maintain their leads and their projects. Zylker uses the Project Details subform in the Leads module to collect project-specific information collected from their Leads.
The Project Details subform includes fields such as Project Title,Type, Budget, and Status, in addition to the Parent_Id lookup field.
Now, the Zylker's sales team needs to retrieve all the details of the projects from the Leads module for further project analysis, expected budgets, and status. Let's see how to manipulate these data in CRM using Zoho CRM APIs.
The APIs used in this post
API | Methods |
Subforms API | GET, POST, UPDATE |
Records API | POST, UPDATE, DELETE |
Search API | GET |
COQL API | POST |
Bulk Read API | POST, GET |
How to retrieve subform records using the Zoho CRM APIs?
To retrieve subform records from the subform module, specify the subform module's API name to access their records or fields.
Step - 1
To know the API names of the subform fields in a module, make a GET - Fields Metadata API call. Among all the Leads' fields, subform field can be identified by the json key data_type with the value subform. Corresponding subform module can be found from the json associated_module. Below is the API call & response for such a subform field.
Request URL :
{api-domain}/crm/v6/settings/fields/{subform_field_id}?module=Leads
Request Method: GET
Sample Response:
Step - 2
Using the api_name of the subform module, make a GET Fields metadata API call to get the list of fields (along with their api_name) in the subform. One of the fields of the subform module will be Parent_Id with the data_type as lookup, pointing to the parent module (here it is Leads).
Request URL
Request Method: GET
Sample Response:
Now you know how to get the API name of the subform and its corresponding fields.
Step - 3
Sample Request and Response to retrieve subform records
The below request will retrieve all the subform records in the Leads module. The linking of subform record to the Lead's module will be available in the Parent_Id field, which is highlighted. The id key inside the Parent_Id json object is the id of the Leads records.
How to add data to the subforms?
To add records to the subform, you need the API name of the subform and its corresponding field API names.
Request URL:
Request Method: POST
Sample Input
Retrieve Subform Data via Search API and COQL API
Search API
The above response shows all records that meet the specified criteria.
How to retrieve subform records from a particular parent record?
Retrieving Subforms Data via COQL API
Sample Response:
{ "data": [ { "Last_Name": "Patricia", "Company": "Info Technology", "Email" : "patricia@mail.com", "Project_Details": //API name of the subform [ { "Project_Name": "Mobile App Development for Productivity", "Project_Type": "Mobile App Development", "Expected_Budget": 50000, "Status": "Negotiation in Process" }, //API names of the subform fields { "Project_Name": "Big Data Infrastructure Implementation", "Project_Type": "Infrastructure Upgrade", "Expected_Budget": 30000, "Status": "Proposal Submitted" }, { "Project_Name": "Big Data Infrastructure Implementation", "Project_Type": "Infrastructure Upgrade", "Expected_Budget": 30000, "Status": "Proposal Submitted" } ] } ] } |
The above highlighted syntax is used for adding data to the subform records.
Sample Response:
Kaizen #33 - Subforms API explains in detail how to Fetch, Update, and Delete the subform data with sample requests, inputs, and responses.
Retrieve Subform Data via Search API and COQL API
There may be situations where you need to fetch records based upon certain conditions.
Criteria :
The sales team wants to retrieve the subform records whose budget is greater than or equal to $40000. In this case, we will use Zoho CRM's Search API and COQL API. Let's see how to achieve this.
Search API
To retrieve the records that match your search criteria, retrieve subform data using its corresponding module API name. Note that using Search API, you can fetch data quickly from a single module.
Request URL:
Request Method: GET
Sample Response :
The above response shows all records that meet the specified criteria.How to retrieve subform records from a particular parent record?
To retrieve subforms records in a particular lead record that meet the above criteria, follow the below sample request.
Sample Request URL:
Sample Response:
Retrieving Subforms Data via COQL API
We know that the subform is maintained in a separate module. So, retrieve subform data by querying the subform module's API name and it's parent module via the Parent_Id lookup field.
Request URL:
Request Method : POST
Sample Input:
{ "select_query" : "select Expected_Budget from Project_Details where ((Expected_Budget >=40000) and (Parent_Id = 5725767000002105043))" } |
Sample Response:
Using a Parent_Id (lookup field pointing to Leads module) in the COQL criteria automatically adds a left join to the child module (Project_Details). With that join, criteria can be applied to the fields of the parent module also. Below example illustrates that we want to fetch the Expected_Budget field of the Project_Details module where the Expected_Budget is greater than or equal to 40000 for the corresponding Leads with Annual Revenue greater than 1000000.
{ "select_query" : "select Expected_Budget from Project_Details where ((Expected_Budget >=40000) and (Parent_Id.Annual_Revenue > 100000 ))" } |
From the SQL perspective, above COQL can be interpreted as
select pd.Expected_Budget from Project_Details as pd left join Leads as l on pd.Parent_Id=l.id where pd.Expected_Budget>=40000 and l.Annual_Revenue > 1000000 |
Bulk Read API
Bulk Read API allows you to fetch a large set of data i.e., you can fetch a maximum of 200,000 records in a single API call.
To export subform records in the Leads module in CSV file format, use the subform's API name.
Request URL:
Request Method: POST
Sample input to export subform records:
{ "callback": { "method": "post" }, "query": { "module": { "api_name": "Project_Details" //API name of the Subform module }, "file_type": "csv" } } |
Export subform records that meet the specified criteria
To export subform records based on the given criteria above (similar to the criteria for Search and COQL APIs).
Sample Input:
{ . . . "query": { "module": { "api_name": "Project_Details" }, "fields": [ "Project_Name", "Project_Type", "Expected_Budget", "Status" ], "criteria": { "field": { "api_name": "Expected_Budget" }, "comparator": "greater_equal", "value": "40000" //Retrieving subform records with an expected budget greater than or equal to $40,000 } } } |
Export subform records that meet the specified criteria for the particular parent record
To export the subform records of a particular parent record in the Leads module. Check the below sample request.
Sample Input:
{ . . . "query": { "module": { "api_name": "Project_Details" }, "fields": [ "Project_Name", "Project_Type", "Expected_Budget", "Status" ], "criteria": { "group": [ { "field": { "api_name": "Expected_Budget" }, "comparator": "greater_than", "value": "39999" }, { "field": { "api_name": "Parent_Id" }, "comparator": "equal", "value": "5725767000002105043" } ], "group_operator": "AND" } } } |
As the API is an asynchronous API, the response will not be available instantly; the bulk read job is scheduled, and the status can be checked. Once the job is completed, it'll be notified in the callback URL. The records are available in a downloadable CSV file or ICS file (for events). You can export subform records in a module using the subform module API name. See Kaizen #12 Bulk Read API to know how to view the status of the scheduled job and download the file, along with more sample requests and responses.
Frequently Asked Questions
Q. Is the API name of the subform case-sensitive? Also, how can I view the API name of a subform field in the web UI?
Yes, the API name of a subform is case-sensitive. To know the API name of a subform module (e.g. Project Details) Please go to Setup -> Developer Hub -> APIs -> CRM API -> API names -> Click on the parent module where the subform was created (e.g. Leads) and scroll down there you can view the subform field's API name.
Q. I changed the order of subform records and made a GET - Records API call. The system listed the records in the same order as displayed in the UI, rather than the order of their creation. Is this the system design?
When you make a GET - Records API call for a module, it lists the subform records ordered in the UI. Note that you can re-order the subform records. So, when you retrieve those records via the API, they will be listed in the same order as they are arranged in the UI.
Q. Can we change a subform field's API name via API?
You can change the API name of the subform field only through the UI. Go to Setup -> Developer Hub -> APIs -> CRM API -> API names -> Click on the parent module where the subform was created (e.g. Leads) and go to the Field Label section. There you can view the subform field name and edit the API by clicking on the Edit option.
We trust that this post meets your needs and is helpful. Let us know your thoughts in the comment section or reach out to us at support@zohocrm.com
Stay tuned for more insights in our upcoming Kaizen posts!
------------------------------------------------------------------------------------------------------------------------------
Previous Kaizen Post : Kaizen #123 Data Synchronization from a third party application
-------------------------------------------------------------------------------------------------------------------------------
Cheers!
Additional Reading:
Kaizen Posts:
- Kaizen #10 - Search Records API and Query API
- Kaizen #31 - Subforms API
- Kaizen #80 - COQL API - Part I
- Kaizen #81 - COQL API - Part II
Topic Participants
Subramanian K
Vigneshwaran K
Raghuram T
Andres
dkvagency
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 FAQsKaizen #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
Zoho Commerce - Enable Company Name and Tax Number collection for B2B orders in Global Edition
Please enable Company Name and Tax Details option on checkout settings in Zoho Commerce Global Edition. It is still important to collect Company Name and Tax Number for B2B sales in many countries. My business is based in Ireland (in the EU) and I haveZohoSign and ZohoBooks Integration/Workflow
Hello All, We utilize ZohoSign for signatures on tax eFiles. We utilize Dynamic KBA. Additionally, we use ZohoBooks for invoicing for these services. Is there a way to accomplish the following: Send a copy of the Tax Return, Invoice and eFiles in oneManage monthly tasks with projectsf
Hi All I run a finance and operations team where we need both teams to complete monthly tasks to ensure we hit our deadlines. Can Zoho projects be used for this. There many finance focused tools but we have Zoho one so want to explore Thanks WillZoho Suite is very slow
Since today Zoho is incredibly slow over all applications! What's going on?How can I track which zoho users are actively using Zoho CRM
I have several licenses of Zoho CRM. We now need to add a new user. I could purchase a new license, but before I do, I would like to see if any of our existing users are not actively using the license assigned to them. How can I determine the activityIs anyone else having trouble saving a custom image in their email signature, or is it just me?
When I try to save the image I get an error that says "Operation Failed" I opened a support ticket two weeks ago and received a response that it would be debugged, but it still isn’t workingCombine and hide invoice lines
In quickbooks we are able to create a invoice line that combines and hides invoices lines below. eg. Brochure design $1000 (total of lines below, the client can see this line) Graphic Design $600 (hidden but entered to reporting andTransaction Locking with the dynamic date
Is it possible to dynamically update dates on transaction locking. We want to lock transaction x days from todayZoho Devops
We have a Zoho one account which we have integrated with an SAS educational product, sold on a subscription model, using webhooks and API calls. We make some use of custom fields and cross module lookups and relationships. We utilize CRM, Books and billingFuel up your sales with the Zoho SalesIQ + Bigin integration
Hi everyone! We’re happy to bring you the all-new Zoho SalesIQ + Bigin integration. With this, every prospect from your website instantly becomes a contact in Bigin, complete with transcripts and follow-up tasks, so you never lose a lead again. Let'sIntroducing AI-powered Assessments & Zoho's native LLM, Zia
We’ve shipped a cleaner, faster way to create assessments in Zoho Recruit. 🚀 Instead of manually building question banks or copying old templates, you can now generate ready-to-use assessments in just a few clicks, all tailored to the role you’re hiringAbility to Reset Visitor Fields During an Active Chat Flow
Hello Zoho SalesIQ Team, We hope you are doing well. We would like to propose a feature enhancement to Zoho SalesIQ regarding the management of visitor fields within Zobot flows. Use Case: Our bot asks the visitor to provide information about a 3rd personExternal ID in Zoho CRM
Hello everyone! We know that Zoho CRM allows you to integrate third-party apps and manipulate data through APIs. While you integrate a third-party application, you may want to store the third-party reference IDs in Zoho CRM's records. To meet this needSome emails are not being delivered
I have this problem where some of my mail just seems to disappear. When I send it, it appears as sent with no mention of any problem, but my recipient never gets it, not even in the Spam folder. Same for receiving, I have a secondary e-mail address, andNew in Zoho Chat : Search for contacts, files, links & conversations with the all new powerful 'Smart Search' bar.
With the newly revamped 'Smart Search' bar in Zoho Chat, we have made your search for contacts, chats, files and links super quick and easy using Search Quantifiers. Search for a contact or specific conversations using quantifiers, such as, from: @user_name - to find chats or channel conversations received from a specific user. to: @user_name - to find chats or channel conversations sent to a specific user. in: #channel_name - to find a particular instance in a channel. in: #chat_name - to findTemplate modifiactions
Hello, I am struggling with the templates in ZOHO Books. Especially with the placement of some items, like company address, ship to, bill to etc. For example: One item I like from template X (placement of ship to and bill to next to each other in theAggregating the First Value in the Group By of a dataset
Hi I am trying to get the following Aggregate Formula to work in my chart, but cannot seem to get the right format. I have a series of data that I am running an include_groupby and want to SUM only a column in the first row of each group. So for example.Admin Control Over Profile Picture Visibility in Zoho One
Hello Zoho Team, We hope you are doing well. Currently, as per Zoho’s design, each user can manage the visibility of their profile picture from their own Zoho Accounts page: accounts.zoho.com → Personal Information → Profile Picture → Profile PictureTrack Zoho Campaign and Workflow sales impact
I am attempting to measure the performance of our marketing workflows and campaigns by comparing the date each campaign was sent to a contact with the purchase date of the contact. For example, if Contact A was sent Email A on 9/1 and made a purchaseTables for Europe Datacenter customers?
It's been over a year now for the launch of Zoho Tables - and still not available für EU DC customers. When will it be available?What is a line break code for zoho?
Hi, I am archiving data by adding values from a single line field from one form to a multi-line field in another form. So I need a code/function that starts a new line on that multi-line field so it does not just keep adding it on the same line. Example, doing something like this means that it will be on a same line. archive.field1 = archive.field1 + input.Field1 I need a code so the input.Field1 can just start on the next line. Instead of "value 1, 2,3,4,5" It will be: "1 2 3 4 etc.". somethingAutomatic Project Owner change
Is there a way to change Project Owner automatically once a specific Milestone in a project is marked as completed. Different Teams are working on projects in our Org, they have their own Milestones to complete and so we transfer the project from teamButton to add product to cart
Is there a way to have a button on a page, that when clicked, will add Qty 1 of a product to the cart?Problem with Submit Button Design
I have made a template to apply to my forms and under the button controls, I have it set to "standard" and yet it's still filling the container. This is super frustrating and looks weird. Why do we not have full control over button size? How can I fixZoho CRM- Authorize your Microsoft Teams account issue
Hi, I tried to link Zoho CRM with Teams and I got the following message: Clicking "Authorize now" sent me to the following page, Microsoft tried to start a session but, after 3 seconds the page closed and nothing happened. I get the same message eachPassing the CRM
Hi, I am hoping someone can help. I have a zoho form that has a CRM lookup field. I was hoping to send this to my publicly to clients via a text message and the form then attaches the signed form back to the custom module. This work absolutely fine whenIs there a way to associate an email in ZOHO Main to a Vendor record in ZOHO CRM
My situation is as below, I have a vendor in ZOHO CRM lets say "Vend A" and an associated contact, "Cont A" If Cont A sends me an email using the email I've registered in the contact record the standard OOTB email sync will work. But the vendor has someBank charges are applied. Please select a bank account.
Hello, I'm trying to add bank charges to a customer payment, but I get the error message "Bank charges are applied. Please select a bank account." I found this old thread, where it says that I need to "select a Bank account for the 'Deposit To' dropdownKaizen #207 - Answering your Questions | Advanced Queries using COQL API
Hi everyone, and welcome to another Kaizen week! As part of Kaizen #200 milestone, many of you shared topics you would like us to cover, and we have been addressing them one by one over the past few weeks. Today, we are picking up one of those requestsPrésentation de SecureForms dans Zoho Vault
Soyons francs : demander à quelqu’un de transmettre un mot de passe ou des informations sensibles n’est jamais une tâche facile. On attend, on relance, parfois de nombreuses fois. Et quand l’information arrive, elle se retrouve souvent dispersée dansIntroducing Connected Records to bring business context to every aspect of your work in Zoho CRM for Everyone
Hello Everyone, We are excited to unveil phase one of a powerful enhancement to CRM for Everyone - Connected Records, available only in CRM's Nextgen UI. With CRM for Everyone, businesses can onboard all customer-facing teams onto the CRM platform toGranular Email Forwarding Controls in Zoho Mail (Admin Console and Zoho One)
Hello Zoho Mail Team, How are you? At present, the Zoho Mail Admin Console allows administrators to configure email forwarding for an entire mailbox, forwarding all incoming emails to another address. This is helpful for delegation or backup purposes,Sales order & purchase order item links for item details
This is fantastic for checking lots of things, I use it a lot. It would be great to see it extended to invoices & bills On another note, may as well throw in my favourite whinge ..... Wish you guys would get the PO receive differences sorted urgently,Zoho Workdrive - Communication / Chat Bar
Hi Team, Please consider adding an option to allow admins to turn on or off the Zoho Communication Bar. Example of what I mean by Communication Bar: It's such a pain sometimes when I'm in WorkDrive and I want to share a link to a file with a colleagueKaizen #190 - Queries in Custom Related Lists
Hello everyone! Welcome back to another week of Kaizen! This week, we will discuss yet another interesting enhancement to Queries. As you all know, Queries allow you to dynamically retrieve data from CRM as well as third-party services directly withinNeed the ability to have read only fields on a form.
There needs to be functionality in Creator that allows a field on a form to be read only. Most screen building software applications have this capability. I know you can hide certain fields from specific users and that you can also make the whole form read only but that's not the functionality I need. I want to be able to create a form where certain fields are editable and other are for display purposes only (read only). For example if the form was displaying information on an item that the userReverse payment on accidentally closed invoice.
An invoice was closed accidentally with the full payment added. However, only partial payment was paid. How can I reopen the invoice and reverse this to update it to show partial payment?New integration: Track booking page appointments in Google Analytics 4
Hello all, Greetings from the Zoho Bookings team! We’re excited to introduce our new Google Analytics 4 (GA4) integration designed to help you track booking activity, understand customer behavior, and measure marketing performance, all in one place. WhatHow to list emails in a folder, e.g. Inbox, on multiple pages when using Zoho mail webpage?
Something as shown in the figure. There are totally 50 emails in Sent folder. If "Mail per page" equals 20, then the Sent folder is split into 3 pages. When I wander through Sent folder, I can just select a specific page to jump to. BTW, it seems thatZoho Calendar soft bounce on @hotmail.com and @yahoo.com email addresses
Hello, our Zoho calendar recently does not send the calendar invites to emails with hotmail and yahoo domains and comes back with a "soft bounce". other domains like Gmail works fine. Also sending "email" to the same emails to the above domains work wellNext Page