Kaizen #125 Manipulating Multi-Select Lookup fields (MxN) using Zoho CRM APIs
Hello everyone!
Welcome back to another week of Kaizen. In last week's post in the Kaizen series, we discussed how subforms work in Zoho CRM and how to manipulate subform data using Zoho CRM APIs.
In this post, we will discuss how to manipulate a multi-select lookup fields using Zoho CRM APIs.
Multi-select lookup field
The Multi-Select Lookup Field enables you to establish a many-to-many relationship between two different modules in CRM. It allows you to associate multiple records with each other, from two different modules.
These associations are stored in an individual module called "Linking Module". Consider there are two modules, Employees and Skills. The Employees module contains details regarding Zylker's workforce, and the Skills module contains details regarding various skills like Social Media Marketing and Content Marketing.
We want to associate multiple skills to an employee. So, a multi-select lookup can be created in the Employees module. When doing so, similar multi-select lookup field will be automatically created in the Skills module, along with the EmpXSkills linking module by Zoho CRM. The underlying data model is described in the below image.
The above chart shows the data model representation when you create a multi-select lookup field in a module. Here, there are two lookup fields—one pointing to the Employees module and the other pointing to the Skills module. In the linking module, two lookup fields (lookup fields with api names - Employees and Skills) will be created. The lookup fields, one pointing to Employees and the other pointing to Skills from the linking module, establish a connection between the linking module and its associated module.
How to associate an employee's skills while creating an Employee record through the Insert Records API
API names you need before invoking the API
- The API name of the multi-select lookup field in the modules you want to insert data.
- API names of the lookup fields in the linking module. Eg: here the API Name of the linking module is "EmpXSkills" and the corresponding lookup field api names are "Employees" & "Skills". You can use the Fields Metadata API for Employees and Skills to get these details.
Step 1
Know the API name of the multi-select lookup field in the module (In our case, Skills is the multi-select lookup field in the Employees module)
To know the API names of the multi-select lookup fields, make a GET - Fields Metadata API call. Among all the Employee's fields, multi-select lookup field can be identified by the json key data_type with the value multiselectlookup. The corresponding connected module can be found from the json connected_module. Below is the API call & response for such a multi-select lookup field.
Request URL : {api-domain}/crm/v6/settings/fields?module=Employees
Request Method: GET
Sample Response:
The above highlighted keys are the details of the Multi select lookup field. The corresponding keys are explained below:
"multiselectlookup": { "display_label": "Skills", //Display label of the MxN field in the Employees module "linking_module": { "api_name": "EmpXSkills", //API name of the linking module "id": "5725767000002166520" ... "lookup_apiname": "Employees", //API name of the Employee lookup field in the linking module "connected_module": { "api_name": "Skills", //API name of the connected module "id": "5725767000002165263" }, "api_name": "Skills_In_Related_List", //API of the related list of the connected module Skills in the Employees module. "connectedfield_apiname": "Employees", //API Name of the multi-select lookup field in the connected module (Skills) "connectedlookup_apiname": "Skills", //API name of the Skills module lookup field in the linking module. "id": "5725767000002166655" //Related List ID }, ... |
Step 2
Using the api_name of the linking module, make a GET Fields metadata API call to get the list of fields (along with their api_name) present in it. It lists all fields of the linking module in the response.
Sample Request and Response
Search for the "data_type": "lookup" in the response. The lookup fields represent the connected modules in association with the linking module.
For example, in our case, the response will have two lookup fields. One of the lookup fields (with api name Employees) points to the Employees module, and the other one (with api name Skills) points to the Skills module.
Step 3
Associate records via the Multi-select lookup field using the Insert Records API
To associate records via the MxN field, you need to know the IDs of the records in the Skills module. Here is the input body to insert the skills in the Employee module with the multi-select lookup field Skills.
Here is the input body to insert a new Employee record and associate a Skills record to it using the MxN field.
Request URL: {{api-domain}}/crm/v6/Employees
Request Method: POST
Sample Input:
{ "data": [ { "Name": "Patricia", "Email": "patricia@mail.com", "Position": "Marketing Specialist", "Year_of_Experience": 5, "Skills": [ //API name of the multi-select lookup field in Employee module { "Skills": { //API Name of the lookup field pointing to the Skills module in the linking module "name": "Marketing", "id": "5725767000002149427" //Record ID in the Skills module } }, { "Skills": { "name": "Social Media Marketing", "id": "5725767000002149476" } } ] } ] } |
How to disassociate an employee & skills relation while updating an Employee record through the Update Records API
Request URL: {{api-domain}}/crm/v6/Employees
Request Method: PUT
Sample Input:
{ "data": [ { "id": "7890710000097291", "Name": "Patricia", "Email": "patricia@mail.com", "Position": "Marketing Specialist", "Year_of_Experience": 5, "Skills": [ { "_delete": null, //This association in the linking module will be deleted "id": "5725767000008126002" //Record created in the linking module } ] } ] } |
Sending _delete:null will cause delinking of the association.
How to associate an employee's skills via "Linking Module"
You can associate the relationship between Employees and Skills module by creating records in the Linking module (EmpXSkills). Use the API names for the corresponding lookup fields, Employee (API Name: Employees) and Skills (API Name: Skills) in the input body.
Request URL: {{api-domain}}/crm/v6/EmpXSkills
Request Method: POST
Sample Response
The id in the above response is the Primary Key ID of an Employee-Skill association record in the linking module. This ID can later be used to do specific operations like association update or deletion via API.
How to disassociate an employee & skills relationship via "Linking Module"
Use the Delete Records API to delete the record which corresponds to the specific relation between Employee and Skills module in the EmpXSkills module. You can get the record ID for the specific association using the Get Records API for the linking module.
Use the Delete Record API to delete the specific record, thereby deleting the specific association between the Employee and Skills record. Please note that only the association is removed, and not the individual records.
Sample Request and Response
When to use create/update operation in Employees/EmpXSkills module?
Use "Employees" module: When you want to create/update records in the Employees module, and associate the record with a Skills record in a single API call.
Use "EmpXSkills" module: When you want to associate/disassociate the relationship between existing Employees and Skills records.
Retrieve data via COQL API and Bulk Read API
There may be situations where you need to fetch records based upon certain conditions.
For example, Zylker's HR team wants to retrieve the list of employees having more than 4 years of experience and are experts in Social media marketing. In this case, they can use Zoho CRM's COQL API or Bulk Read API. Let's see how to achieve this.
Retrieving MxN data via COQL API
We know that both the Employees and Skills modules' association data is maintained in the linking module. In order to retrieve data from the linking module, query using the API name of the lookup fields in the linking module.
Request URL: {{api-domain}}/crm/v6/coql
Request Method: POST
Sample Input:
{ "select_query" : "select Employees.Name as employee_name, Employees.Year_of_Experience as employee_experience, Skills.Name as skill_name from EmpXSkills where Employees.Year_of_Experience > 4 and Skills.Name like '%Social%'" } |
From the SQL perspective, above COQL can be interpreted as
select emp.Name as employee_name, emp.Year_of_Experience as employee_experience, skill.Name as skill_name from EmpXSkills left join Employees as emp on EmpXSkills.Employees = emp.id left join Skills as ski on EmpXSkills.Skills = ski.id where emp.Year_of_Experience > 4 and ski.Name like '%Social%' |
Sample Response
Retrieving MxN data via 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 linking module records, use its API name.
Request URL: https://www.zohoapis.com/crm/bulk/v6/read
Request Method: POST
Sample input to export linking module's records:
{ "callback": { "method": "post" }, "query": { "module": { "api_name": "EmpXSkills" //API name of the linking module }, "file_type": "csv" } } |
Export linking module records that meet the specified criteria
To export linking module's records based on the given criteria above (similar to the COQL API).
{ "callback": { "method": "post" }, "query": { "module": { "api_name": "EmpXSkills" }, "fields": [ "Employees.Name", "Employees.Year_of_Experience", "Skills.Name" ], "criteria": { "group": [ { "field": { "api_name": "Employees.Year_of_Experience" }, "comparator": "greater_than", "value": "4" }, { "field": { "api_name": "Skills.Name" }, "comparator": "contains", "value": "Social" } ], "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, you will be notified in the callback URL. The records are available in a downloadable CSV file or ICS file (for events). See the Bulk Read API document to know how to view the status of the scheduled job and download the file, along with more sample requests and responses.
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 #124 - Manipulating Subform using Zoho CRM APIs
-------------------------------------------------------------------------------------------------------------------------------
Cheers!
Additional Reading:
Kaizen Posts:
- Kaizen #26 - Linking module and Multi-select Lookup fields
- Kaizen #77 - Multi-select Lookup Fields in API V4
- Kaizen #80 - COQL API - Part I
- Kaizen #81 - COQL API - Part II
API Documentation - Linking Module APIs
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Subramanian K
Andres
Haiku Technical Support
Sticky Posts
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 achievedKaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI
Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over theKaizen #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.
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Prefilled Date fields auto-changed and then locked when using “Edit as new”
If a document out for signature has date fields (not SignedDate fields) that were pre-filled before sending, and then you use “Edit as new” to create a new version of the same document, the value of those date fields gets automatically changed to todayIs there a way to update all the start and end dates of tasks of a project after a calendar change?
Hi! Here's my situation. I've built a complete project planning. All its tasks have start dates and due dates. After completing the planning, I've realized that the project calendar was not the right one. So I changed the project calendar. I now haveAccess Phone Field Components (Country Code) Directly
Hello everyone, I'd like to propose an enhancement for the Phone field in Zoho Creator. The Problem: The Phone field captures the country code and local number separately, but stores them as a single string (e.g., +1234567890). To get the country code,Send mass messages through WhatsApp from the Tickets module
Hi Everyone! Effective communication is key to delivering prompt and reliable customer support. Because WhatsApp is one of the most widely used and familiar messaging platforms, it's an effective channel for agents to reach customers who have submittedLead Owner Signature Merge Field
I want to automatically insert a signature (i.e. contact info usually found at the bottom of an email) into an email template, depending on who the lead owner is. What is the merge code for the Signature from a Users profile? CRM > Settings > Customization > Templates There is a popup near the bottom of the edit screen which says: "You can insert a Signature, which is available as a merge field in the users section." It is also referenced on this page: https://help.zoho.com/portal/en/kb/crm/customize-crm-account/customizing-templates/articles/template-builder#Merge_FieldsHow do I create a time field?
I want a field that only records time. I can only see how to create a date-time field. If I do that and enter a time, without a date, nothing is recorded. If I create a number or decimal field, I cannot use it in time calculations. All I want is a fieldAlternating columns - How to reverse order on mobile - Responsive template
Can the order of alternating columns be reversed on mobile so that image comes before the text? Example: Desktop Row 1 column Left (image) , column right (text) Row 2 column Left (text) , column right (image) Mobile Currently Row 1 Image over text RowInvokeURL butchering JSON for OpenAI API calls
My organization works with mostly educational institutions. We have a custom module called "Schools", which is the user-entered school name they put when using our service (which they enter along with their state and zip code). We want to map this toCustom order for Current Stage (Blueprint field)
Hi! I suggest adding the option to set a custom order in reports for the Blueprint field ‘Current Stage’. Currently, these fields can only be sorted in ascending or descending order. Thanks!CRM E-mail Sync from Outlook
We are exploring Zoho as a possible new solution for our company. We are trying to understand further on how e-mail sync works. We use outlook. Our current CRM logs anytime we e-mail a customer so we can see in the CRM the message sent to the customer.Run your help desk on your schedule
In business, time is of the essence. This is especially true in a function like customer service, where KPIs such as response time and agent availability are the measures of success. Perhaps the most crucial consideration one needs to make about time is your hours of operation. These set expectations for your customer and for employees. To make communicating this information easier, we've revamped the way business hours and holiday lists work in Zoho Desk. In order to accommodate teams that workThe Customer Happiness REST API is broken
1. We are unable to extract the customerHappiness ( https://desk.zoho.com/DeskAPIDocument#CustomerHappiness#CustomerHappiness_ListallcustomerHappiness) object using the ticketNumber criteria. We keep getting HTTP 429 even when we limit to 60 calls perCan the Product Image on the Quote Template be enlarged
Hello, I am editing the Quote Template and added ${Products.Product Image} to the line item and the image comes up but it is very tiny. Is there anyway that you can resize this to be larger? Any help would be great! ThanksAutomatic Department and Employee Sync Between Zoho One and Zoho People
Dear Zoho Support, I'm writing to propose a valuable feature request that would streamline data management and improve user experience within the Zoho ecosystem: automatic synchronization between departments and employees in Zoho One and Zoho People.Zoho Expense and Chase Bank Visa credit cards - Direct Feed?
Our company uses JP Morgan Chase Visa credit cards. We can't be the first to try and use a combination of Zoho Expense + Chase Bank + Visa Credit Cards --- anyone successful with this combination? 1. The direct feed automation isn't working. When I goImpossibile inviare il messaggio;Motivo:554 5.1.8 Indirizzo del mittente bloccato
" Impossibile inviare il messaggio;Motivo:554 5.1.8 Indirizzo del mittente bloccato" Hi need to send email, how can fix this? Thanks, AliceImage inputed in Zoho Form were displayed to small in exported version
Good evening, my name is athallah, i am a trainee in government office Right now, i use Zoho form to digitalize an airfield monitoring form. my problem is, the image were to small to displayed in exported version. it really bad for formal presentationAPI. How do I get responses submitted to the form?
Hi, I'm trying to retrieve a list of form responses via the API. I've created a Self Client application in API Console. What scoup should I enter? What are the endpoints? Is there documentation on this anywhere? I've wasted a lot of time searching. GPTQuestion about Zoho CRM Professional plan users
Hello! I have a question about the Zoho CRM Professional plan. Is the pricing charged per user or per organization? Are there any free users included in this plan, or do I need to pay for each additional user separately? Thank you!Communicating with emojis
On July 17, we celebrate World Emoji Day! We're a bit late 😐 sharing insights about this day. But we just couldn't let it pass without a mention 😊 because emojis have a meaningful connection with customer service 💬 🤝. We do not want to miss out onChanging the Default Search Criteria for Finding Duplicates
Hey everyone, is it possible to adjust the default search criteria for finding and merging duplicate records? Right now, CRM uses some (in my opinion nonsensical) fields as search criteria for duplicate records which do nothing except dilute the results.Is it possible to create a word cloud chart in ZoHo Analystics?
Hi there, I have a volume of transaction text that I would like to analyse using word cloud (or other approcah to detect and present word frequency in a dataset). For example, I have 50,000 records describing menu items in restaurants. I want to be ableHelp integrating Aircall into Zoho CRM
Hi all, We are need to get better AirCall integration into Zoho. We have configured in the Aircall dashboard but we have not done the Zoho side. We cannot see Zoho CRM information on incoming calls. And we calls and text's are not being logged. SpecificallyShopify Extension no longer working in CRM
Zoho CRM and Extension are no longer working properly. We have used Shopify Basic for years. All of the sudden it stopped working, then started working again, and now no longer working again. There are several ways for Personal Information to sync fromHow to keep track of bags, cans, drums of inventory?
We buy and sell products that are packaged in bags 🛍️, cans🥫, drums🛢️, etc. with batch numbers. When we get a shipment of one of the products, how do we track we received (say) 10 cans each of 5L of a product and maybe we received 10 cans of anotherEasy third party collaboration: Zoho Flow Integration
Third-party integrations extend your project functionality with improved workflow, and data processing. With Zoho Flow integration you can include multiple third party actions into your project or task automation. Add Zoho Flow action in Workflow rulesZoho Inventory - How to pay a supplier up front then receive multiple deliveries
How do we manage situations where we pay a supplier up front, then the receive the products in increments? Example Workflow: Create Purchase Order > Receive Bill for full amount > Receive Items 2 or more deliveries. Currently, once a Bill is created againstIntroducing Configure, Price, Quote (CPQ) in Zoho CRM | Public early access 2023
Greetings Customers! We're excited to announce that the CPQ feature will soon be available in Zoho CRM and accessible to all accounts using the Professional, Enterprise, and Ultimate editions. We've opened this feature to select customers, and it'll behow to edit the converted lead records?
so I can fetch the converted leads records using API (COQL), using this endpoint https://www.zohoapis.com/crm/v5/coql and using COQL filter Converted__s=true for some reasons I need to change the value from a field in a converted lead record. When I tryZoho Bookings <> CRM integration
Hello Zoho community! We are enabling our Zoho Bookings <> CRM integration. What is the workflow if the integration detects that the contact already exists in the CRM? Does it create a duplicate record? Overwrite the record? Merge the record? (in thisCannot reject empty expense report
Hello, We are currently having issues with two empty expense reports where if we try to reject them, either manually or through the REST API, we get error 114016, which says some of the expenses have already been billed and must be removed. I'd appreciateUse of Zia within Forms
I have noticed that recently you have added Zia to help create forms from scratch. Would it be possible to add Zia functionality to free text fields for form submissions or if this is already on the roadmap an ETA for this please? We have a lot of fieldDefault in fields on Form B based on the user selection in Form A
Hi Everyone, I have added an action button to a form report to bring up a new form based on user selection, see it indicated in red below: Then when the ne form loads, I want to default in some of the fields based on the record the user was selected on.Auto-sync field of lookup value
This feature has been requested many times in the discussion Field of Lookup Announcement and this post aims to track it separately. At the moment the value of a 'field of lookup' is a snapshot but once the parent lookup field is updated the values diverge.Last activity time is acting like last modified time
When i edit the description or any field in the potential, account, contact and lead, the Last Activity Time is being updated like the Modified Time. This is messing all workflows and reports and we are unable to track real last time of activities likeEnhancements to the formula field in Zoho CRM: Auto-refresh formulas with the "Now" function, stop formula executions based on criteria, and include formulas within formulas
Dear Customers, We hope you're well! By their nature, modern businesses rely every day on computations, whether it's to calculate the price of a product, assess ROI, evaluate the lifetime value of a customer, or even determine the age of a record. WithHEX/RGB Color Input in Visual Editor
Hello Zoho Pagesense Team, We hope you're doing well. We’d like to submit a feature request to improve the color selection options in the Pagesense popup editor. Current Limitation: Currently, to set text colors, users must move the color slider manually.Add Comprehensive Accessibility Features to Zoho Writer
Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 CurrentCRM and Campaigns- tags not integrating?
Hi! I am setting up an automation in zoho campaigns and it says the crm is integrated...but when I want to use a trigger of a certain tag I created for people in the crm to denote those who purchased, it doesnt have that tag available as an option inMaximum limit of rows exceeded
I am trying to add a row to a spreadsheets that has fewer than 60 rows. I keep getting an error message that says I have exceeded the maximum limit of 65,536 rows. Any ideas out there?Next Page