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

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


    • Recent Topics

    • Create online meetings for Booking Pages with Zoho Meetings and Zoom

      Greetings, We hope you're all doing well. We're excited to share some recent enhancements to Bigin's Booking Pages. As you know, Booking Pages let you create public pages to share your availability so that your customers can easily book time slots with

    • Filters in audit logs

      Greetings, I hope all of you are doing well. We're happy to announce a few recent enhancements we've made to Bigin. We'll go over each one in detail. Previously, there were no filters available to narrow down data in audit logs. Now, we've introduced

    • Enhanced help options in Bigin

      Greetings, We're excited to introduce a new enhancement to Bigin's Help section: a comprehensive Help Options panel that brings together all your support resources in a single, well-organized space. Previously, the Need Help? menu provided only a limited

    • Zoho FSM API Developer Needed

      Hi, I’m looking for a developer with experience using Zoho FSM APIs. Scope: Connect WordPress website booking form to Zoho FSM Check availability (date, time, region) Create Work Orders + Service Appointments automatically Notify both customer and scheduler

    • Revenue Management: #4 What if there are uncertainties in project or service delivery?

      Our previous post taught us how Zoho Billing makes life easy for businesses with its automated revenue recognition rule. However, certain businesses have more challenges that an automated system cannot handle, and there are certain situations where automated

    • This mobile number has been marked spam. Please contact support-as@zohocorp.com

      Bom dia, estou tentando colocar o número 11 94287-6695 e esta com erro "This mobile number has been marked spam. Please contact support-as@zohocorp.com" pode me ajudar, por favor?

    • Items Serial Tracking Issue

      We enabled Zoho Items inventory tracking then disabled it after some time now we want to enable it again When I check the missing serial number reports I see one item But I cant see any option to Add the serial numbers Where and how to add the serial

    • Composite Services and Account Tracking

      I am looking to garner support/request the ability to make composite services. A quick search in the forums brings up multiple requests for this feature. I fail to see why an item is mandatory while services are optional. I also would like to see the

    • Zoho Payroll integration with Zoho Books - unable to match multiple bank feeds to one wage payment

      For one employee's wage, I make two partial payments. Those bank feed transactions come into Zoho Books via bank integration. I make one pay-run for the month in Zoho Payroll and that comes into Zoho Books via the Zoho integration. Zoho Books doesn't let me match multiple bank feed transactions against a single wage item. Please fix urgently. I can't complete my books because of this.

    • Add Checkbox Selection & Bulk Actions to Delivery Challans Module

      Hi Zoho Team, I’ve noticed that in the Sales Orders module, there are checkboxes beside each entry that allow users to select multiple records for bulk actions such as print, email, or delete. However, in the Delivery Challans module, this option appears

    • Can't be able to check-in in laptop

      even after giving location access still i can't be able to check-in in laptop.

    • Compensation Cess on Coal ₹400 per tonne. ?????

      The compensation cess rate varies by the type of product. And the cess is calculated based on the value of the product without GST. Coal, for example, comes with a cess of ₹400 per tonne. That means that if you sell 2 tonnes of coal that have a value

    • 7 month over zoho book purchase but still not immpliments Golive

      7 month over zoho book purchase but still not immpliments Golive one problems zoho team short out then other problems come still very poor mangments and immliments team . struggling with the templates in ZOHO Books. Especially with the placement of some

    • Average Costing / Weighted Average Costing

      Hello fellow maadirs. I understand Zoho Books uses FIFO method of dealing with inventory costing, but do you guys have any plans to introduce average costing? We indians need average costing. It's part of our culture. Please. I beg thee. Thanks.

    • SMS to customers from within Bigin

      Hi All, Is there anyone else crying out for Bigin SMS capability to send an SMS to customers directly from the Bigin interface? We have inbuilt telephony already with call recordings which works well. What's lacking is the ability to send and receive

    • How to update/remove file in zoho creator widgets using javascript API

      Hi Team, I have developed a widget which allows inserting and updating records I have file upload field with multiple file upload. Now while doing insert form record, I am using uploadFile API to upload files for that record. I am using updateRecord API

    • Parent & Member Accounts (batch updating / inheritance)

      Hello, I find the Parent Account functionality very useful for creating custom views and reports, but was wondering if I can also carry out batch editing on all members (aka children) of a Parent Account at the same time. Alternatively, can I set members to automatically inherit the values of the parent? For example: We have a chain of supermarkets that buy our products. These supermarkets are all members of a Parent Account in our CRM. We release a new product and all of the member stores wish to

    • Edit Legend of Chart

      I would like to edit the legend of the chart. Every time I enable the legend, I get a very unhelpful (1), and when I try to type to change to what I would desire, nothing happens, which is very frustrating. I've gone through your online tutorials and nowhere can I find a legend settings button. This seems a simple fix, where can edit the legend? Thanks.

    • Extended timeouts for APIs beyond 40secs for to accomodate LLMs

      A 40 second max response time for API calls is fine when connecting to most services, however is unsuitable when dealing with LLMs (ChatGPT/Claude/Gemini) where the response timing is very uncertain. Is there any way to increase this? It would be great

    • Deletion of Zoho Account

      To whom it may concern, Good day, My account has been created incorrectly in Zoho and I am not able to join my Company's Zoho account - attached screenshot for your kind reference Alphatronmarine - Portal Kindly advise procedure to delete this current

    • Workflow for deposit to bank account

      Hello, Is it possible to make a workflow when a deposit is made to your bank account which is coupled to Zoho books? I want Zoho to sent an email each time a deposit is made to our bank account via a workflow. Regards, Steven

    • Marking Retainer invoice paid through Deluge

      Hey Everyone, We have a scenario where we are collecting deposit payments on our website. Now, in zoho books, we need to create a retainer invoice and mark it as paid automatically using deluge just like we can mark normal invoices as paid. I have tried

    • Create a new record in custom module vi custom button

      I have zoho books premium plan . I have 2 custom modules in zoho books. 1. Goods Receipt 2. Delivery Order, I need to select multiple records from Goods Receipt and create a new Delivery order from these multiple records. (like multilple sales order into

    • Profile date settings

      At present I have "EEE, MMMM dd, yyyy" but this takes an exessive amount of column space, we should be able to input our own format. I would like to use "EEE, MMM dd, yy" - a much shorter version of the above but with the same abbreviated info, requiring

    • Delivery Method Field in Sales Order Module

      In Books and in Sales orders, the "Delivery Method" field seems to allow for anything to be entered and it seems to store those entries for future use.  When you chose to convert a sales order to a purchase order, the related field is now called "Shipment

    • Editing / Removing stages for pipeline

      Hello, I'm trying to create a new pipeline. I created a new stage and made an error when entering the probability. How can I edit fields in stages that I created? Can I delete these stages from "Add Stages" list?

    • Dynamically Filter User Lookup in CRM Subform

      We have a subform called Pricing Calculator in the Zoho CRM Opportunity module and need some assistance. Current Setup: First column: Picklist (Level) Second column: User Lookup field When a Level is selected, we want the User lookup to display only users

    • change time zone

      can't seem to figure out how to change the time zone of the project

    • Bigin iOS app update: Built-in telephony and RingCentral support

      Hello everyone! We are excited to introduce Built-In Telephony and RingCentral support in the latest iOS version(v1.11.13) of the Bigin mobile app. Once the integration is completed on the Bigin desktop site(bigin.zoho.com), you can choose the Built-In

    • Add Image or Update Image API - for Items Module

      I am trying to add new Items to Zoho Inventory from Zoho Creator. I achieved this using Zoho Inventory Create Item API, but how to add or update the item image from Zoho Creator to Zoho Inventory Item Module?

    • Introducing Booking Pages—a topping for your Calendar Scheduling needs!

      Greetings, We're here with a new topping for Bigin! Let's dive into the details. What does this topping do? Scheduling appointments with customers is one of the most common challenges small businesses face on a daily basis, as it often involves frequent

    • Debugging `try` blocks : Tip

      I find it annoying that if one line inside a `try` block has an error, the Deluge arser points the beginning of the block to the location of the error. BUT, if you temporarily comment out the initial `try {`  The parser goes through the whole block and

    • [Product Update] TimeSheets module is now renamed as Time Logs in Zoho Projects.

      Dear Zoho Analytics customers, As part of the ongoing enhancements in Zoho Projects, the Timesheets module has been renamed to Time Logs. However, the module name will continue to be displayed as Timesheets in Zoho Analytics until the relevant APIs are

    • Use approval workflow comments in record scripts

      Greetings, i'm running an approval workflow for my records, during approval/rejection there is a step where comments are entered. i want to add there comments to the record and to use them in various deluge scripts like sending emails and so on.  how

    • ZOHO Store

      Not able to make a payment We are using Zoho One, and we are from India. The payment currency, which shows for us, is in USD. But the system says we can choose Country/Region India if it shows INR only. Attaching screenshots for more info.

    • Support Migration into Aliases in Zoho Mail

      Hello Zoho Mail Team, How are you? We are in the process of migrating some of our users from Google Workspace (Gmail and Google Drive) to Zoho. During this process, we noticed that Zoho Mail currently only supports migration into a primary mailbox and

    • API for Z Workdrive Flow Make-Integromat ?

      We are zoho workdrive fans Also we would like to have an api to work with Zoho Flow or with Make better known by its old name INTEGROMAT Is it planned and when? 3 months -6 months or more?

    • Apps Pane no longer visible

      I have read all the info and help and nothing works, I do not have a "show apps" anywhere and I can no longer see my Apps pane in the left hand side of mail, please advise how to get this back

    • Canvas View - Print

      What is the best way to accomplish a print to PDF of the canvas view?

    • 5名限定 課題解決型ワークショップイベント Zoho ワークアウト開催のお知らせ(8/21)

      ユーザーの皆さま、こんにちは。Zoho ユーザーコミュニティチームの藤澤です。 8月開催のZoho ワークアウトについてお知らせします。 今回はZoomにてオンライン開催します。 ▷▷参加登録はこちら：https://us02web.zoom.us/meeting/register/eVOEnBsSQ2uvX4WN5Z5DeQ ━━━━━━━━━━━━━━━━━━━━━━━━ Zoho ワークアウトとは？ Zoho ユーザー同士で交流しながら、サービスに関する疑問や不明点の解消を目的とした「Zoho

    • Next Page