Kaizen #124 - Manipulating Subform using Zoho CRM APIs

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.
Consider adding a subform field named Project Details in the Leads module (parent module). Zoho CRM will automatically create a separate module for the subform field with the specified subform field name - Project Details. Each record within the subform module can have multiple fields, in addition to the system-defined Parent_Id lookup field, establishing a connection between the parent (Leads module) and child (subform module) modules. Through this linking process, one can easily identify which subform record corresponds to which specific lead record.

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
{
    "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

For more information on COQL API, refer to the Kaizen posts  COQL Part -1 and COQL Part - 2

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:





    • 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

    • Clone a Module??

      I am giong to repurpose the Vendors module but would like to have a separate but very similar module for another group of contacts called Buyers. I have already repurposed Contacts to Sellers. Is it possible to clone (make a duplicate) module of Vendors

    • Advance PDF creation from CRM data

      I'm trying to create a PDF export of data in the CRM. My problem is I want a pretty complicated format for the data. I'm trying to export multiple modules worth of data, with nested one-to-many relationships between the modules. Along with that, I want

    • How to change the text in WhatsApp Zobot integrated to Zoho Booking?

      I have integrated Zoho Bookings into Zoho SalesIQ, I want to change the text in WhatsApp when creating a booking in Zobot how to change those text?

    • Updating Subform Record from other Form

      Just wanted to ask how to properly approach this. I have 2 forms and would like to trigger an auto update on the subform once record submitted. block below only updates 1 row for each recordRow in input.AV_System { AssetRecord = Site_Asset_Services[SOR_No

    • Zoho Books - Hide Convert to Sales Order if it can't be used.

      Hi Books team, I noticed that it is not possible to convert a Quote to a Sales Order when a Quote is not yet marked as accepted. My idea is to not show the Convert to Sales Order button when it is not possible to use it, or show it in a grey inactive

    • How do I bulk archive my projects in ZOHO projects

      Hi, I want to archive 50 Projects in one go. Can you please help me out , How can I do this? Thanks kapil

    • Cross-Data Center Collaboration and / Or allowing users to choose DC

      Dear Zoho Cliq Support Team, We are writing to request a significant enhancement to Zoho Cliq that would greatly benefit our geographically dispersed development team. Current Challenge: Currently, Zoho Cliq automatically routes users to specific data

    • New Mandatory One-Click Unsubscribe Link Overshadowing Custom Unsubscribe Link

      I was recently informed by Zoho CRM Support that they are now mandated by the large email service providers like Google and Yahoo to provide a one-click unsubscribe option in the header (not the body) of all mass emails. I have a custom unsubscribe link

    • Send / Send & Close keyboard shortcuts

      Hello! My team is so close to using Zoho Desk with just the keyboard. Keyboard shortcuts really help us to be more efficient -- saving a second or two over thousands of tickets adds up quickly. It seems like the keyboard shortcuts in Desk are only for

    • Is it possible to register webhooks in Zoho CRM using API?

      Hello, I am trying to register a webhook in Zoho CRM programmatically (using the API). Specifically, I want to register a webhook that is fired when new Contacts are created in the CRM. I was able to setup a webhook using the UI, by creating a rule that

    • Calls where the local audio is shared, have echo

      When another user is sharing their screen with audio, I get echo from my own voice. We tested this with multiple users, with different audio setups, and there's no obvious way to fix it. Is this a bug you could look into, or are we missing something?

    • Update application by uploading an updated DS file

      Is it possible? I have been working with AI on my desktop improving my application, and I have to keep copy pasting stuff... Would it be possible to import the DS file on top of an existing application to update the app accordingly?

    • Markdown support, code cells...

      Hi Zoho I'd like to vote for a feature that markdown is supported with: Headings Code highlighting Quoteblocks ... Furthermore a inline card(like inline sketch card) for special text like Code would be great. And just to add my vote as well for "Tags"!

    • Minimise chat when user navigates to new page

      When the user is in an active chat (chatbot) and is provide with an internal link, when they click the link to go to the internal page the chat opens again. This is not a good user experience. They have been sent the link to read what is on the page.

    • How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

      How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

    • Reports: Custom Search Function Fields

      Hi Zoho, Hope you'll add this into your roadmap. Issue: For the past 2yrs our global team been complaining and was brought to our attention recently that it's a time consuming process looking/scrolling down. Use-case: This form is a service report with

    • Zoho Projects app update: Voice notes for Tasks and Bugs module

      Hello everyone! In the latest version(v3.9.37) of the Zoho Projects Android app update, we have introduced voice notes for the Tasks and Bugs module. The voice notes can be added as an attachment or can be transcribed into text. Recording and attaching

    • zurl URL shortener Not working in Zoho social

      zurl URL shortener Not working in while creating a post in Zoho social

    • In the Zoho CRM Module I have TRN Field I should contain 15 digit Number , If it Contain less than 15 digit Then show Alert message on save of the button , If it not contain any number not want to sh

      Hi In the Zoho CRM Module I have TRN Field I should contain 15 digit Number , If it Contain less than 15 digit Then show Alert message on save of the button , If it not contain any number not want to show alert. How We can achive in Zoho CRm Using custom

    • Power of Automation::Streamline log hours to work hours upon task completion.

      Hello Everyone, A Custom Function is a user-written set of code to achieve a specific requirement. Set the required conditions needed as to when to trigger using the Workflow rules (be it Tasks / Project) and associate the custom function to it. Requirement:-

    • Zoho Bookings know-how: A hands-on workshop series

      Hello! We’re conducting a hands-on workshop series to help simplify appointment scheduling for your business with Zoho Bookings. We’ll be covering various functionalities and showing how you can leverage them for your business across five different sessions.

    • Custom report

      Hello Everyone I hope everything is fine. I've tried to To change the layout of the reports, especially the summary page report, and I want to divide summary of each section in the survey but I can't For example: I have a survey containing five different

    • Zoho Journey - ZOHO MARKETING AUTOMATION

      I’ve encountered an issue while working with a journey in Zoho Marketing Automation. After creating the journey, I wanted to edit the "Match Criteria" settings. Unfortunately: The criteria section appears to be locked and not editable. I’m also unable

    • Custom Fields in PDF outputs

      I created a couple of custom fields. e.g Country of Origin and HS Tariff Code. I need these to appear on a clone of a sales order PDF template but on on the standard PDF template. When I select "appear on PDFs' it appears on both but when I don't select

    • How to create a Service Agreement with Quarterly Estimate

      Hello, I'm not sure if this has been asked before so please don't get mad at me for asking. We're an NDIS provider in Australia so we need to draft a Service Agreement for our client. With the recent changes in the NDIS we're now required to also include

    • Change Currency symbol

      I would like to change the way our currency displays when printed on quotes, invoices and purchase orders. Currently, we have Australian Dollars AUD as our Home Currency. The only two symbol choices available for this currency are "AU $" or "AUD". I would

    • Zoho Social - Post Footer Templates

      As a content creator I often want to include some information at the end of most posts. It would be great if there was an option to add pre-written footers, similar to the Hashtag Groups at the end of posts. For example, if there is an offer I'm running

    • Allow to pick color for project groups in Zoho Projects

      Hi Zoho Team, It would be really helpful if users could assign colors to project groups. This would make it easier to visually distinguish groups, improve navigation, and give a clearer overview when managing multiple projects. Thanks for considering

    • Zoho Books - Quotes to Sales Order Automation

      Hi Books team, In the Quote settings there is an option to convert a Quote to an Invoice upon acceptance, but there is not feature to convert a Quote to a Sales Order (see screenshot below) For users selling products through Zoho Inventory, the workflow

    • Can't find imported leads

      Hi There I have imported leads into the CRM via a .xls document, and the import is showing up as having been successful, however - when I try and locate the leads in the CRM system, I cannot find them.  1. There are no filters applied  2. They are not

    • Custom Button Disappearing in mobile view | Zoho CRM Canvas

      I'm working in Zoho CRM Canvas to create a custom view for our sales team. One of the features I'm adding is a custom button that opens the leads address in another tab. I've had no issue with this in the desktop view, but in the mobile view the button

    • The connected workflow is a great idea just needs Projects Integrations

      I just discovered the connected workflows in CRM and its a Great Idea i wish it was integrated with Zoho Projects I will explain our use case I am already trying to do something like connected workflow with zoho flow Our requirement was to Create a Task

    • Zoho Projects MCP Feedback

      I've started using the MCP connector with Zoho Projects, and the features that exist really do work quite well - I feel this is going to be a major update to the Zoho Ecosystem. In projects a major missing feature is the ability to manage, (especially

    • email template

      How do I create and save an email template

    • Enhancements in Portal User Group creation flow

      Hello everyone, Before introducing new Portal features, here are some changes to the UI of Portals page to improve the user experience. Some tabs and options have been repositioned so that users can better access the functionalities of the feature. From

    • How do I filter contacts by account parameters?

      Need to filter a contact view according to account parameter, eg account type. Without this filter users are overwhelmed with irrelevant contacts. Workaround is to create a custom 'Contact Type' field but this unbearable duplicity as the information already

    • Can I add Conditional merge tags on my Templates?

      Hi I was wondering if I can use Conditional Mail Merge tags inside my Email templates/Quotes etc within the CRM? In spanish and in our business we use gender and academic degree salutations , ie: Dr., Dra., Sr., Srta., so the beginning of an email / letter

    • email moderation issue when email is sent in the name of a mail group

      Symptom: an email that is sent by a mail group moderator in the name of a moderated mail group is held back for approval. Reproduction: Create a moderated mail group with members and moderators. Allow that mails can be sent in the name of the group (extended settings). Send an email to the group as a group moderator, but in the name of the group. This mail is held back for moderation which is unexpected. Expected: A mail sent by group moderator to a moderated group are not held back for moderation

    • blank page after login

      blank page after logging into my email account Thanks you

    • Always display images from this sender – Is this feature available?

      In Zoho mail, I had my "Load external images" setting set to "Ask me", and that's fine. That's the setting I prefer. What's not fine though is I always need to tick "Display now" for each email I get, regardless if I've done that multiple times from several

    • Next Page