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

[

url :"https://www.zohoapis.com/crm/v6/coql"

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

[

url :"https://www.zohoapis.com/crm/v6/coql"

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

[

url :"https://www.zohoapis.com/crm/v6/coql"

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!

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

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

Previous Post : Kaizen #131 - Bulk Write for parent-child records using Scala SDK | Kaizen Collection: Home

Access your files securely from anywhere

Zoho Developer Community

New to Zoho LandingPage?

Access Zoho LandingPage

Zoho LandingPage Resources

New to Zoho Survey?

Access Zoho Survey

Latest Feature Updates

Explore our Pricing & Plans

Zoho Survey Resources

Android Mobile Surveys

Insurvey Blogs

Insight Blogs

Tips & Tricks

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho CRM Plus?

Deliver unforgettable customer experiences

ACCESS ZOHO CRM PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Zoho Marketing Plus?

Everything you need to run your marketing

ACCESS ZOHO MARKETING PLUS

New to Bigin?

Signup Now

Access Bigin

Latest Feature Updates

Zoho Desk Resources

Desk Community Learning Series
Digest
Functions
Meetups
Kbase
Resources
Glossary
Desk Marketplace
MVP Corner
Word of the Day

Tout forum

France ZUGs

Zoho SalesIQ Resources

Topic Participants
Kiruthiga G

Sticky Posts
Kaizen#162 : Add Tags and Open Pre-filled Email Drafts via Client Scripts
Hello everyone! Welcome to another informative Kaizen post. In this post, let us see how to accomplish the following using Client Script. 1. How to auto-tag a record based on field update? 2. How to Open a pre-filled email draft This post will provide
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
Extension Pointers - JS SDK series #4: Managing data from a widget using ZOHO.CRM.HTTP
Handling and managing the data between two applications is a major factor for an efficient business. There are a variety of ways to handle data between Zoho CRM and other third-party applications. You can use deluge CRM tasks, create connectors, use API
Kaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achieved
Extension pointers for integrating Zoho CRM with Zoho products #8: Upload and manage Zoho Workdrive folders and files from within Zoho CRM
Keeping records on your customers and business prospects is essential for tracking data, conducting follow-ups, and running a business smoothly. When you use two separate applications, and store relevant data in each, checking and tracking data becomes