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

    • Subform edits don't appear in parent record timeline?

      Is it possible to have subform edits (like add row/delete row) appear in the Timeline for parent records? A user can edit a record, only edit the subform, and it doesn't appear in the timeline. Is there a workaround or way that we can show when a user

    • New in Cadences: Option to Resume or Restart follow-ups when re-enrolling records into a Cadence, and specify custom un-enrollment criteria

      Managing follow-ups effectively involves understanding the appropriate timing for reaching out, as well as knowing when to take a break and resume later, or deciding if it's necessary to start the follow-up process anew. With two significant enhancements

    • Im Stuck in an EDIT ONLY WITH WIZARD issue

      So I found Wizards to be a really helpful tool in minimizing the exposure of redundant, superfluous fields to staff that would never otherwise have to edit those fields. My issue is, that when the record (in this case a lead) is created with a wizard,

    • Account upgrade

      Good evening, I upgraded my account and paid for it. From standard to professional. Unfortunately after the paiment my account was not upgraded. Please your advise. Best Regards Erik van Staverden

    • How to set ALL default dates of my organization to DD-MM-YYYY format?

      All replies to this question comes from a time where the UI was different. It's extremely frustrating not being able to find how to do this simple setting change. I want everything and everyone in my organizations to have DD-MM-YYYY date format by default.

    • How can I sync from Zoho Projects into an existing Zoho Sprints project?

      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

    • Can we generate APK and IOS app?

      Dears, I want to know the availability to develop the app on zoho and after that .. generate the APK or IOS app  and after that I added them to play store or IOS store.. Is it possible to do this .. I want not to use zoho app or let my customers use it. thanks 

    • Zoho Subform Workflows onAdd of Row

      Suppose I have a form with attached workflows onLoad. If I use the form as a subform, will it inherit the workflows or do I need to create new ones onAdd of row?

    • Session Expired

      I constantly get "Session Expired" and need to relogin or close and open the application again. This gets really frustrating during the day. Is this something that can be solved? This really makes me want to leave the app as it is no go to need to reopen

    • Super Admin removal

      I brought a sub, and I gave the Super admin rights to a person who is no longer with us, so I need to change, and I need to make myself the Super admin

    • Better Notes Commenting

      Hi, I'd like to suggest better collaboration tools for NOTES. The current notes section for Accounts, Contacts and Deals is not ideally suitable for any degree of communication or collaboration. When responding to a note, there is no ability to leave

    • Exporting Templates

      I have just spent 2 hours creating a project template for a Netsuite configuration, and want to share it with other Zoho Projects users - who have a different account. Is there any way to do this?

    • Power of Automation:: Streamline Associated Teams based on the Task Owner update.

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

    • No Response from Zoho Support in 8 Days - Typical?

      I have a couple of issues I'm trying to work through. Initially, I was getting support from support@zohofsm.com, but I have not received a response in 8 days (11 on another question). Is this typical? Can I pay for support? For context, I am not spamming

    • Add QUOTE OWNER profile image to a Quote Template

      I can add their email address.. phone number, DOB. I need to add a users profile picture so when they assign a template to a quote they own it adds their picture to the cover page. I've tried hacking a solution together but there has to be an easier way.

    • Zoho Connections Desk API relative URL PATTERN_NOT_MATCHED

      While i am trying to do this: async function fetchTicketsFromDesk(timeFilter = 'current_month') { try { const response = await ZOHO.CRM.CONNECTION.invoke("desk_connection", { url: "/api/v1/tickets", method: "GET", }); const data = response.details ? JSON.parse(response.details)

    • Zoho CRM - Custom Views for Portal Users

      I'm looking for an option to customise custom views for portal users in CRM. It would be great if "portal user" was a permission on custom views.

    • 【参加無料】10/17(金) 東京 ユーザ交流会 Vol.3 参加登録 受付開始！

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10/17(金)に、東京・新橋で「東京 ユーザー交流会 Vol.3」を開催します！ 今回のユーザー事例セッションのテーマは、「Zoho Flowを活用した他社の決済サービスとの連携事例」です。 さらに、Zoho Flowに限らず、Analytics や Campaigns などの多彩なZohoサービスの活用方法について、豊富なご経験をもとにご紹介いただきます。 また、Zoho社員セッションでは、Zoho CRMを活用して日々の営業業務を効率化する具体的な事例をお話しします。業界を問わず、幅広い方にご参考いただける内容となっています！

    • Zoho Meeting Plug compatibility with newer versions of Outlook

      Documentation states that the zoho meeting plug in for outlook is only compatible with versions up to Outlook 2019 What is available to users of more up to date versions of outlook/office 365?

    • Getting Attachments in Zoho Desk via API

      Is there a way to get attachments into Zoho Desk via an API?      We have a process by which a zoho survey gets sent to the user as a link in a notification.    The survey has several upload fields where they can upload pdf documents.    I've created

    • Introducing Zoho's own SMS gateway

      We're thrilled to announce the launch of our own SMS gateway feature within Zoho Marketing Automation! This new feature enables seamless SMS campaign management alongside your email marketing initiatives, providing a more integrated and efficient way

    • Embedding in Desk articles

      We would like to embed documents in our Desk articles. When we use an iframe for the embed, we get scrollbars and a frame border. Neither of those is acceptable. I've spoken with the Desk Support team about what we want and they tell me that it cannot

    • Zoho CRM button to download images from image upload field

      Hello, I am trying to create a button in Zoho CRM that I can place in my record details view for each record and use it to download all images in the image upload fields. I tried deluge, client scripts and even with a widget, but feel lost, could not

    • Mass Update Contacts In Zoho Campaigns

      Is there a way to mass update contacts in zoho campaigns? I want to be able to change the content of a field for a few hundred contacts, and can't go through all of them individually.

    • report showing assignment type

      Hi, We've created a number of workflows to allow us to auto assign tickets to agents based on keywords and other criteria. I'm struggling to create a report that would show me what is the percentage of tickets that are assigned automatically via workflows

    • Option to Disable Knowledge Base Section in Feedback Widget Popup Hello Zoho Desk Team

      Hello Zoho Desk Team, How are you? We are actively using Zoho Desk and would like to make more use of the Feedback Widget. One of the ways we implement it is through the popup option. At the moment, the popup always displays the Knowledge Base section,

    • Placeholders in Ticket Templates

      We should be able to use placeholders in ticket templates. When we create a new ticket, our description field is shown to the client in the email they receive.  It would be very handy to be able to personalize that description field in our ticket templates to pull in the name of the client that the ticket is for. Using them in the subject field as well, so we can auto populate Account Names, etc. 

    • when the record is created the tag want to Show as Opportunity how i achive this using Deluge Script

      In the quotation i have the work flow schedule for create opportunity record in the module , on that time the quotation tag select as opportunity created. How i achive this using Deluge Script . this like i want to Do tag1 = Map(); tag1.put("name","Nurturing

    • Delete a channel

      I need delete a channel in tickets.

    • Copy / Duplicate Workflow

      I have workflows setup that are very similar to each other. We have a monitoring system watching servers, and all notifications - no matter what client it is about - will come from a  noreply@ address which is not very helpful in having it auto assigned to the right account. I have setup a workflow that will change the contact name of the ticket (currently it would say noreply@) to the correct customer which is based on the subject line, as that mentions which server the alert it is about. I need

    • Subtasks don't update parent task's times

      Hi there: I've recently upgraded to premium and check that subtasks completion % don't update the proportional completion of the parent tasks related to it.  We've been challenging with the problem of having to update twice or sometimes 3 times the completion of the related tasks. I've seen posts similar to this, of 3 years old.  Is there any roadmap for making this happen in a future release? Thanx César Ratto Lima, Perú.

    • Should I Use DMARC?

      When I configure Zoho Mail's DMARC settings, it's mandatory to fill in the RUA and RUF (Aggregate notification email address*, Forensic notification email address*) addresses. When we enter an email address in these fields, we receive reports from the

    • Mail ToDo & Tasks Webhooks

      Our company uses Zoho ToDo inside Mail to manage our tasks. When I create a task and assign it to a team member it does not notify them unless I add a reminder via mail. I'm trying to create a webhook for when a task is created to send a cliq message

    • Can't upload attachments.

      I can't upload attachment in Zoho Mail.

    • Tip #44 – Get Deeper Insights with Zoho Assist’s Custom Reports – ‘Insider Insights’

      In today’s fast-paced IT environment, having a clear view of your remote support activities is more important than ever. Zoho Assist’s Custom Reports feature gives IT teams the ability to generate tailored reports that provide actionable insights and

    • Message "...does not support more than 100 distinct values..." WHY????

      I get this message on one of my reports: Sorry, Zoho Reports currently does not support more than 100 distinct values in columns. 'Account Name * Sum(Amount),Count(Amount Tier)' contains more than 100 distinct values.  Possibly, you can apply filter to reduce the number of distinct values in 'Account Name' or drop the 'Account Name' field in Rows. I want to list all ACCOUNT NAMES (about 500) with SALES BY ACCOUNT.  What is blocking this?

    • Need Easy Way to Update Item Prices in Bulk

      Hello Everyone, In Zoho Books, updating selling prices is taking too much time. Right now we have to either edit items one by one or do Excel export/import. It will be very useful if Zoho gives a simple option to: Select multiple items and update prices

    • Let's Talk Recruit: Super-charge hiring with Zoho Recruit add-ons

      Welcome back to our Let’s Talk Recruit series! This time, we’re diving into something that might seem like a small upgrade but has a huge impact on recruiter productivity: Zoho Recruit add-ons. Think about how much of your day is spent in your inbox or

    • Vendor Signatures Needed for Purchase Orders

      Hello everyone, We have a unique requirement that necessitates that Vendors & Suppliers formally acknowledge our Purchase Orders upon receipt. I was hoping that there would be an option to do so in Zoho Books, but that does not appear to be the case.

    • Is there an API to "File a Ticket" in Desk

      Hi, Is there an API to "File a Ticket" in Desk to zoho projects?

    • Next Page