Kaizen #132 - Manipulating Subforms from third-party Application using Functions

Kaizen #132 - Manipulating Subforms from third-party Application using Functions

Hey folks! Welcome to a fresh week of Kaizen. 

In this post, we will see how to work with subforms and external fields in a module through CRM functions. 

Consider a scenario where a High School utilizes Zoho CRM to manage student records, academic performance, and offered courses. Additionally, the school employs an Exam portal for students to take examinations. After the examination, the portal sends the mark information back to Zoho CRM along with it's own course id, student id, marks & other details. 

Problem Statement 

When the exam portal initiates a webhook call to CRM, the high school management wants to capture that data and update the CRM subform records accordingly. This subform should act as a semester progress report for each student.  

Solution Overview 

To address this requirement, a custom function must be implemented. The webhook URL will be the API REST URL of the custom function in Zoho CRM. Upon receiving the notification, the function is triggered, and the details from the notification are used to manipulate the subform.

Let us walk through this process of triggering functions using a webhook to manipulate subforms. 

Prerequisites 

From the Third-Party Application 

  • We assume that a webhook will be triggered from the third-party application when the exam is over and evaluated by the teacher. The webhook will carry the following information:  
Field
Data Type
Description
Exam Name
String
Name of the examination that the student attends.
Student ID
Long String
Unique ID of the student within the Portal App.
Course ID
Long String
Unique ID of the course within the Portal App.
Marks
Integer
Total marks of the student in the particular course.
Total Time
Integer
Time taken by the student to complete the exam.

  • The webhook URL should correspond to the API REST URL of the custom function in Zoho CRM.  

From Zoho CRM 

Here is a data model image of the CRM modules involved in this scenario for your ease of understanding.  



The Courses, Students and Student Exams modules are assumed to be pre-loaded with records. Following are the module wise fields that are expected to be already configured in the school's CRM org.  

Courses and Students Modules 

External fields named Student Code in the Students module and Course Code in the Courses module are used to match the IDs deployed in the third-party application. 



Note
      External fields can be updated only using the Update Records API.

Student Exams Module 

Following are the custom fields that we assume to be already available in the Student Exams module. 

Field Name
Data Type
Description
Student
Lookup
The field looks up to the Students module.
Total Marks
Integer (Formula)
A subform aggregate field that sums up the values of the Marks field in the Student Performance subform.
GPA
Integer (Formula)
Calculates the GPA of a student in that particular examination.
Exam Name
String (Picklist)
List downs the examinations that are planned to be conducted for the academic year.
 


Student Performance Subform 

A subform named Student Performance within the Student Exams module, comprising the following fields.

Field Name
Data Type
Description
Course
Lookup
Represents the course name and it looks up to one of the courses from the Courses module.
Total Time
Integer
Time taken by the student to complete the exam.
Marks
Integer
Marks of the student in the particular course.

Grade
String (Formula)
Grading system is infused as formula and it works depending on the value in the Marks field.



The subform is assumed to be empty and will be updated only on receiving a webhook notification from the Portal App. 

Note 
      Make a Modules Metadata API call to get the API names of the modules and the subform. Next, fire the GET Fields API call to get the API names of the fields. We will need them in crafting the custom function. 

Creating the Custom Function 

Step 1: Navigate to the Setup > Developer Hub > Functions and click the New Function button. 



Now, create a Standalone function by filling in the details. 


Flow of the Function 

Argument Setup 

Define function arguments to receive details from the function as shown here: 



Data Retrieval 

Invoke the COQL API within the function to retrieve the necessary records from the Student Exams and Courses module. 

student_examMap = Map();
student_examMap.put("select_query","select id from Student_Exams where Student.Student_Code =" + student_id + " and Exam_Name=" + exam_name + " limit 1");
exam_response = invokeurl
[
type :POST
parameters:student_examMap.toString()
connection:"crm_oauth_connection"
];
exam_id = exam_response.getJSON("data").get(0).get("id");

Here, the query is structured to retrieve the ID of the Student Exam record where the Student lookup field corresponds to the record with the Student Code matching the student_id in the notification, and the Exam Name picklist field matches the value of exam_name from the notification. 

Note: You can use only =, !=, in and not in operators for querying External fields in Zoho CRM. 

courseMap = Map();
courseMap.put("select_query","select id from Courses where Course_Code =" + course_id + " limit 1");
course_response = invokeurl
[
type :POST
parameters:courseMap.toString()
connection:"crm_oauth_connection"
];
course_id = course_response.getJSON("data").get(0).get("id");

In this COQL call, the query fetches the ID of the record from the Courses module whose Course Code field matches the course_id received in the notification. 

Subform Update Handling

The custom function should address the following three cases inorder to effectively manipulate the subform data from the third-party application. 
  • Every webhook call from the Exam portal should be added as a new entry to the subform.
  •  If entries already exist in the subform, the function should perform PATCH operation.
  • Before executing the PATCH operation, it is crucial to check whether the webhook call is for a new course or an update to the existing course. In such cases, the function should refrain from adding the course; instead, it should update the corresponding entry. 
To append new entries to the subform, you can use the UPDATE Records API. Refer to this kaizen to learn more about manipulating subforms using Zoho CRM APIs. 

Student Performance is a module created for the subform configured in the Student Exams module. In order to perform the PATCH operation with an Update API, make a COQL API call to the Student Performance module and fetch the existing data, if any.  

subform_examMap = Map();
subform_examMap.put("select_query","select Course, Total_Time, Marks from Student_Performance where Parent_Id.id =" + exam_id);
subform_response = invokeurl
[
type :POST
parameters:subform_examMap.toString()
connection:"crm_oauth_connection"
];

This query retrieves the exam records of the student for that particular exam in Student Exams module, from the Student Performance subform. The subform should correspond to the parent record that matches the notification data. Since we already have the ID of that parent record from one of the previous COQL call, we have directly used the response in this query. 

Now that we have the existing subform data, the function has to verify whether the data from webhook call matches any of the Course in the subform data. If it matches, the new entry replaces the existing one; otherwise, the new entry is added to the existing entries and forms a request payload called examinfo. This payload will be used in the later part of the functions to update the record in Student Exams module. 

Following is how you should achieve this using for-each loop. 

final_subform = List();
flag = true;
if(!isBlank(subform_response))
{
subform_data = subform_response.getJSON("data").toList();
if(subform_data.len() > 0)
{
for each  data in subform_data
{
if(data.get("Course").get("id") == course_id)
{
final_subform.add({"Course":{"id":course_id},"Total_Time":time_taken,"Marks":marks});
flag = false;
}
else
{
final_subform.add(data);
}
}
}
}
if(flag || isBlank(subform_response))
{
final_subform.add({"Course":{"id":course_id},"Total_Time":time_taken,"Marks":marks});
}
examinfo = {"Student_Performance":final_subform};

Next, let us update the Student Performance subform in the Student Exams module using the pre-defined integration task called Update Record in Zoho CRM functions. This task makes an Update Records API call to replace all entries within the subform. It updates the entries with the same course_id provided in the notification, inserts new entries and removes old entries accordingly.   

response = zoho.crm.updateRecord("Student_Exams",exam_id,examinfo);
return "nothing";

Save the function and click the more icon of the function you have created and choose REST API.  



Enable the OAuth2 and API Key for the function. 

Next copy the API key and provide it in the webhook URL of the Portal App. 



On receiving notifications to this URL the function will be triggered and the records will be updated. 



We believe you found this post both beneficial and informative!

Your thoughts and perspectives matter to us. If there is a topic you would like us to delve into or if you have any questions, please feel free to drop a comment below or send us an email at support@zohocrm.com.

Stay tuned until we circle back to you on next Friday! 

--------------------------------------------------------------------------------------------------------

Recommended Reads

---------------------------------------------------------------------------------------------------------


    • 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

    • SMTP email sending problem

      Hello, I've sent emails before, but you haven't responded. Please respond. My work is being disrupted. I can't send emails via SMTP. Initially, there were no problems, but now I'm constantly receiving 550 bounce errors. I can't use the service I paid

    • billing

      hi, I am being billed $12/year, and I can't remember why. My User ID is 691273115 Thanks for your help, --Kitty Pearl

    • How to add receipts

      How to add receipts

    • Unable to enable tax checkboxes

      Hi Zoho Commerce Support, I'm writing to report an issue I'm having with the tax settings in my Zoho Commerce store. I've created several tax rates under Settings > Taxes, but all of them appear with the checkbox disabled. When I try to enable a checkbox,

    • 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 have

    • ZohoSign 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 one

    • Manage 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 Will

    • Zoho 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 activity

    • Is 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 working

    • Combine 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 and

    • Transaction Locking with the dynamic date

      Is it possible to dynamically update dates on transaction locking. We want to lock transaction x days from today

    • Zoho 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 billing

    • Fuel 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's

    • Introducing 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 hiring

    • Ability 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 person

    • External 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 need

    • Some 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, and

    • New 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 find

    • Template 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 the

    • Aggregating 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 Picture

    • Track 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 purchase

    • Tables 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.".  something

    • Automatic 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 team

    • Button 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 fix

    • Zoho 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 each

    • Passing 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 when

    • Is 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 some

    • Bank 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' dropdown

    • Kaizen #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 requests

    • Pré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 dans

    • Introducing 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 to

    • Granular 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 colleague

    • Kaizen #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 within

    • Need 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 user

    • Next Page