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

    • Zoho People LMS VS Zoho Connect Manuals VS Zoho Learn

      in the past I came accross Zoho WIKI but did not like the platform because it could use a lot of upgrade. Over the time I have noticed Zoho People come out with a LMS module which allows us to created a shared knowledge for our internal team I also came across Zoho Connect which as a knowledge-based for internal team referred to as Manual Now I am seeing Zoho Learn which is a new and fine-tuned version of Zoho Wiki. All of these platforms are very similar but I am wondering what are the differences

    • Marketing Automation Activities in Zoho CRM

      Hello, I've connected Zoho CRM and Marketing Automation, sent a campaign, but no data are displayed in CRM, neither in "campaigns" section inside contact profile. It is possible to display Marketing Automation activities in CRM? Also in CRM Timelines?

    • How do we get a follow up to Experts 22: Scale up your customer support with integrations & extensibility

      Hi, How do we get a followup and answers to the questions we have asked during 'Experts 22: Scale up your customer support with integrations & extensibility'. I have repsonded to the answers but have no way of following up. Thanks Brett

    • Frustrating Email Duplication and Timeline Issues Between Zoho Mail and CRM

      Hi Zoho team, Can someone please help clarify what’s going on here? Here’s what’s happening: I initiate an email to a lead using Zoho Mail. The lead is created in Zoho CRM via the integration, and the email is correctly associated with that lead. Sometimes,

    • Best Strategy to import contacts and when to create leads

      Hi, I'm new to Bigin and looking for a "best" strategy. I had and have the following idea for an use case: 1. Search for websites which I want to contact 2. Create a contact in Bigin with all the required information based on this website (via API if

    • Better implementation of Item Category on Invoices and Estimates

      1) I have added Item Category as a custom field. Honestly, this should be a native part of the item itself, and either required, optional, or not used.  2) When entering an item on an invoice, you have to enter the first character(s) of the item, otherwise

    • Bulk Update (via the 'Accountant' menu)

      Why can't we bulk update Expenses to Owner's Drawings? It always ends in failure with the error "Involved account types are not applicable". If such conversion isn't possible, why make the option available? Better to allow it though.

    • Set Reply_to parameter for "Email an Invoice" API Endpoint

      Is there a way to set "Reply To" email address when using the Email an invoice API endpoint? It doesn't seem to be in documentation, but sometimes there are undocumented parameters. If it doesn't exist, please consider adding it as parameter since all

    • Zoho Books adaptado a la legislación española. ¿Sustitutos?

      Buenas a tod@s No tenemos información sobre la adaptación de Zoho Books a la nueva ley de facturación en España. Me preguntan usuarios de zoho que deberían hacer. Propongo una lista de alternativas, si al final se opta por no desarrollar la funcionalidad

    • A Question about Email Handling (Sending and Receiving)

      Hello! I was looking into setting up Email Aliases for my domain that I purchased a while ago through Zoho Mail. I set up a singular alias already and have it linked with Gmail, and it seems to be working out well. However, I set up another alias today,

    • Kaizen #201 - Answering Your Questions | Webhooks, Functions, and Schedules

      Hello everyone! Welcome back to another post in the Kaizen series! We are incredibly grateful for all the feedback we received, and as promised, we will answer all the queries in this Kaizen series. Last week, in our 200th post, we addressed one of the

    • Zoho Projects Strict Dates for Tasks

      Hi Zoho Projects team, I would love to see a feature to allow Strict Dates for Tasks. Sometimes in projects you have dates which must not move, even if predecessor Task dates change. For example, perhaps you need to book access to a facility to perform

    • No Mark as filed buton in GSTR -3b

      We are filing our GST in GST portal -and want to mark GST in Zoho books as filed. It is possible to mark GSTR 1 as filed through Mark as filed button. But there is no such button in GSTR-3B.  How to mark corrosponding GSTR-3B as filed?

    • ranking by drag the choices instead of rank by number

      is the option of draging the choice is available instead of selecting the ranking number for each choice?

    • TASKS - Dashboard to show ALL Tasks from ALL apps.

      The Unified Tasks View is useless without the other main zoho apps (Desk, Books & even FSM now) We need to see all our tasks under on pane of glass. The Zoho developers are out of touch with real business workflows. So how it was designed they want us

    • Function #13: Transaction level profitability

      In Zoho Books, the Profit & Loss report provides valuable insights into the overall profitability of your business, indicating whether you have made a profit or incurred a loss. However, there may be occasions when you wish to assess whether a specific

    • Zoho CRM with Sap Business One

      I need information about integration CRM with Sap BO, thank.

    • Allow bill to nest multiple projects

      A bill (purchase) is more often than not used across multiple projects and this functionality is missing and very urgently needed for accurate reporting of purchases across projects

    • Amazon invoice in Zoho Books

      I have just made my first few sales on Amazon India. Amazon Seller account generates invoices for the sales made on Amazon. These invoices are sent to customers also. Now when I was only making offline sales, I used to create Invoices in Zoho Book. Now

    • How can I change iOS display setting at night?

      It seems like at night the two light daytime settings themes (white with blue on top or just white) are not available and I am forced to choose among a few other colours that I really don’t want, I.e., black, blue or orange. Is there a way for me to always

    • Celebrate WWW day with Zoho Desk

      Let's recall the times when we learned 'WWW' stands for World Wide Web. Whether you like to call it wuh-wuh-wuh or double-u double-u double-u, or World Wide Web, we all owe a lot to this groundbreaking invention that reshaped how we connect, communicate,

    • The power of camaraderie

      In the days before the internet boom, conversations happened face-to-face or over the phone. Phone calls were precious; every minute counted, and every word mattered. We looked forward to those moments of real connection. Even today, nothing quite matches

    • Message Content is missing/invalid fault

      We cannot create a complete template over zoho. Some features - like button adding - is missing. The templates are created on Zoho and edited on Meta to solve the problem. But still then, some features cannot be used and the message returns this failure

    • Können bereits gesendete Kampagnen im Nachhinein einer Mailing-Liste zugeordnet werden, ohne dass die Kampagne erneut versendet wird?

      Wir haben unsere älteren Kampagnen in Campaigns über Kontaktkategorien versendet. Dann haben wir umgestellt auf Mailing-Listen, damit alte Kampagnen auch in einem Archiv aufgerufen werden können. Jetzt ist die Frage, ob die Kampagnen, die über die Kategorieauswahl

    • Newsletter Templates Are Not Mobile Responsive

      Hi, I've already submitted this request to ZOHO once this morning, but for some reason your system logged me out, wouldn't accept my username/password login, and isn't showing any evidence that I've submitted this issue. So here we go again... I am under the impression that your newsletter templates are supposed to be mobile optimised: https://www.zoho.com/campaigns/blog/responsive-email-marketing.html This is clearly not the case, as the last 2 newsletters I've created in Campaigns look perfect

    • Digest Juillet - Un résumé de ce qui s'est passé le mois dernier sur Community

      Bonjour à toutes et à tous, Zoom sur les nouveautés de juillet dernier au sein de Zoho Community France. Zoho Commerce vous propose une expérience améliorée grâce à sa nouvelle interface ergonomique et à ses fonctionnalités avancées, conçues pour faciliter

    • 100 Rows in a Subform is too limited

      We have a custom Module in CRM called Price Sheets, when we get a PO from a client we add the items from the PO to it and then check with our vendors for pricing and add our margin etc And after it is complete we have setup custom scripts to create a

    • Uninstall unattended agent

      Hello, I'm testing many use case before we purchase assist for our remote support. While we are testing what is the proper way to uninstall agent?  I did uninstall from systray, from windows control panel but still ZohoURservice is running. How can I uninstall client side?

    • Zoho Commerce Down?

      Is anyone else's storefront down at the moment? Ours has been down for at lease an hour.

    • Zoho Projects iOS app update: Dashboard widget on the home screen

      Hello everyone! We are excited to introduce the 'Dashboard' widget in the latest version(v3.10.8) of the Zoho Projects iOS app. Dashboard widgets allow you to view the project progress visually without having to open the app. The widget enables you to

    • Idea: Workflow Rule Trigger Only When Subform Row Is Updated (Thanks to New Inline Row Feature)

      Hi Zoho team and community, With the recent update to Zoho CRM, we can now add or delete rows inside subforms without entering edit mode, using the inline Add row button. This is a fantastic improvement for user experience — seamless, fast, and efficient.

    • Auto add new section based on document choices

      Hi team, I'm wondering if the below is a possilibity within Zoho sign. We have an application process to become a customer of ours, we currently use Zoho Sign to manage this application and this works quite well. However, if the customer indicates 'YES'

    • Change rate after xxxx kilometers

      Is there a way to change the miileage rate after a certain mileage. After 5000 kilometers, we want the rate to automaticly change. Thank !

    • Subform Entry Limit from a Subform Field (A different Subform on the same Form)

      Hi, I would like to be able to use a Subform 1 Field as the Dynamic Entry Limit for Subform 2. Even better would be able to use some code with the values, so for example using the Subform 1 Qty Field as the Max Entry limit for Subform 2, BUT only the

    • Slow Zobot response time

      Hi, We launched the Zobot on our site to sit along with the regular Live Chat but had to take the Zobot down as the response time was very slow. The bot was slow to begin then once the chat had been initiated the response was very slow. The bot typing

    • how to show data of 3 table in pivot

      Based on engineer name i want to get the data from 3 different tables like Service , amc, installation , but Every table contain Engineer name As Common , based that from the service table i want to take service amount , and count of service based on

    • Custom Status for Purchase Orders

      Currently Zoho books has functionality to create custom statuses for Sales Orders. Can this be extended to include custom status for purchase orders as well? It was a great decision to add this functionality to sales orders. Our use case is for tracking

    • Ask the Experts 22: Scale up your customer support with integrations & extensibility

      Hello everyone! The foundation is set. Build the beams. Raise the pillars. Set the walls. The Zoho Desk architecture stands tall. Let's discuss integration within Zoho Desk, extensions from the Marketplace, creating connections between Zoho Desk and other

    • Is there no way to duplicate an entire workflow or even custom function across multiple departments?

      Is there no way to duplicate an entire workflow or even a custom function from one department to other departments, like it is done for field duplication from one department layout to other department layouts?

    • Automated reply on any new ticket raised by customer

      Hi ZohoDesk team, Can we set up an automation so that whenever a new ticket is created against our support email; ZohoDesk immediately sends our standard acknowledgement, including the expected TAT for resolution? If that’s possible, could you share the

    • Next Page