Rodger's helpful scripting tips #2 - Subforms
Subforms are very useful but also poorly managed. So here are some helpful tips for when you're managing subform data.
1. Subforms are actually a hidden module within Zoho and you can get data out of subforms for all records. If you've ever tried to create a subform in a module and been met with a duplicate name error, that's why. When you create a subform, you're actually creating a hidden module, and module names have to be unique. Sadly, this module is not searchable, but you can iterate, or loop through your records. i.e Let's say you have a subform called "Ordered Products". You can use:
- l_orderedProducts = zoho.crm.getRecords("Ordered_Products",<page>,200);
And this will give you a list of all (or the first 200) records within that subform. You can then loop through the records by indexing the <page> up by 1 each time. This works for relatively small numbers of records, but not great for tens of thousands of records.
Within the subform record is the record it belongs to, listed as "Parent_Id". Now you can bulk update data within subforms across multiple records. So, let's say you wanted to update the Description of the line item of a particular product within a subform. You could use a bit of code like this:
- for each record in l_orderedProducts
- {
- if record.get("Product").equals(<productId>)
- {
- m_record = Map();
- m_record.put("Description",,New Description>);
- updateRecord = zoho.crm.updateRecord("Ordered_Products",record.get("id"),m_record);
- }
- }
This is just an example code but in this hypothetical situation, you would have just updated the Description on all lines in all subforms in all records for 1 specific product. Sure beats editing each individual record! Remember to use a bulk update if you've got many records to update.
2. Updating a subform entry is easy in deluge, but some people tent to re-write subform data rather than simply updating data. One way of updating a subform is as follows:
Step 1 - Get the subform entries
Step 2 - Create a Map and copy all the subform data into a new map
Step 3 - Add the map to a list
Step 4 - Add the new list to the record map
It's actually way simpler than that. Firstly, you have to understand that when you do a For Each on a list, the "each" is actually a map. This is something that is overlooked by some people. I.E
- l_orderedProducts = zoho.crm.getRecords("Ordered_Products",<page>,200);
- for each record in l_orderedProducts
- {
- }
each "record" is a map and since it's a map, we can write data to it directly. So what we can do it
- r_record = zoho.crm.getRecordbyId("Orders",v_id);
- for each line in r_record.get("Ordered_Products")
- {
- line.put("Description",<New Description>);
- }
- m_record = Map();
- m_record.put("Ordered_Products",r_record.get("Ordered_Products"));
So what's happened here? Well, we've updated the Description on each line item with a new value. This is now stored within the map "line", which is within the list "Ordered_Products", which is within the record (map) "r_record". We can now read the updated list out of r_record.
We don't need to create a new list.
We don't need to create new maps
We don't need to re-create the entire subform of data just to update a few values. You only need to do this if you have a need to preserve the old data and new data within your function which is rarely the case for simple record updates.
3. Re-order a subform without destroying data.
The accepted wisdom when re-ordering a subform is to re-order your data, re-create the subform list, and then re-write that list to the record. This not only adds extra lines of code, but destroys data. It's rare, but if you're using the subform record ID in another place, you can't simply go and re-write your data because the original record will be deleted. So what do we do?
Well, to re-order a subform, you simply need to give it a new S.No order. That's it! You need to have the S.No visible on the record layout for this technique to work, but it will simply re-order you subform lines without re-writing or deleting any data (apart from the S.No that is)
- r_draftOrer = zoho.crm.getRecordById("Draft_Orders",v_id);
- l_lines = List();
- for each line in r_draftOrer.get("Custom_Products")
- {
- l_lines.add(line.get("Proof").get("name"));
- }
- l_lines = l_lines.sort();
- for each item in r_draftOrer.get("Custom_Products")
- {
- item.put("LinkingModule18_Serial_Number",l_lines.indexOf(item.get("Proof").get("name")) + 1);
- info item.get("LinkingModule18_Serial_Number");
- }
- update = zoho.crm.updateRecord("Draft_Orders",v_id,{"Custom_Products":r_draftOrer.get("Custom_Products")});
- return "";
So what's going on here?
1. First we get the record with the subform and we create a blank list
2. Into this blank list we're going to add the sorting key you want to use. In this case, we're wanting to sort by the record name
3. Then we sort the list either ascending or descending
4. The last for each re-orders the S.No. For each line in the subform we get the index of the name in l_lines. What we're doing is asking the code "I have this data, what position is it in "that" list?", and it returns the position, or index. Since all indexes start from 0, but the S.No starts from 1, we need to add 1 to the value returned.
5. We're going to put that new S.No directly into the subform record, just like we did further up the page
6. Finally, we update the record with the updated subform. All that has changed in the subform is the S.No has been updated with a new order.
Now when you re-load your record, Zoho will sort the subform in ascending order based on S.No.
Hope this helps people with writing nice tight efficient code. My next post will be how to turn any Workdrive folder into a drop folder and have new files automatically added to CRM records.
Access your files securely from anywhere
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
Rodger Brehaut
Vigneshwaran K
dkvagency
Ishwarya SG
Sticky Posts
Accessibility in Zoho CRM: Not just a feature—a way to empower
For instructions on setting up these controls, please check this help document: Configuring accessibility controls. Hello everyone, Today (December 3, 2024), on the International Day of Persons with Disabilities, we begin our journey towards a CRM thatAct on your customers' voices in Zoho CRM
Dear Customers, We hope you're well! We are super excited to present a pivotal addition to VoC in Zoho CRM—the ability to act! Customer feedback is directly proportional to customer experience, and in this customer-driven market, that feedback has a powerfulFocus Group Webinar - Streamline Record Creation with Wizards
Hi there, With all the time your users spend on record creation, do you feel like they have the most seamless experience while doing it? For different types of records or those with a LOT of fields, do your users spend time navigating to enter data repeatedly?Notes and Attachments visibility can now be restricted based on profiles
Dear All, We hope you're well! We are here with a quick update about Notes and Attachments profile permissions. In the past, a record's Notes and Attachments were visible by default to all users with record access. However, as notes and attachments canIdentify and prioritize profitable deals: Zoho CRM Forecasts help users focus on revenue-driving opportunities
Post moderated on: 7th November: Release update: This feature is now available for users in all DCs. Dear Customers, We hope you're well! At any given time, a sales rep handles multiple deals, each progressing at its own pace. But not all deals will succeed—some
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho DataPrep 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
How to Retrieve Serial Numbers of Items in Zoho Inventory via API?
Hello, I am currently working with the Zoho Inventory API and need to retrieve the serial numbers associated with specific items in our inventory. After reviewing the documentation, I couldn’t find an endpoint dedicated to fetching serial numbers formerging email accounts
previously I was using 5 mail pop mailboxes within VO , 2 of them are becoming obsolete so I was thinking about deleting the obsolete ones and merge the remainders into my main account mailbox within VO , is this possible ? thnx in advance.Queries filtered by current page/record
I have been trying to use the new queries feature, and I can filter the query, but I'm coming unstuck because I don't understand how to make the query dynamically include the filter of the current record. ie if I'm on a deal, to filter all the recordsZohoPeople API - Retrieve leave type IDs
Hi All, I have created a leave type in Zoho People UI. Now I need to fetch the Leave Type ID of it. As per the documentation[1] I used the curl request [2]. But I ended up with the error response from the API. {"response":{"message":"Error occurred","errors":{"message":"Server Error Occured","code":7031},"status":1,"uri":"/api/leave/getLeaveTypes"}} The new API[3] does not tell anything on how to retrieve the Leave Type ID. Have you done any changes to the API recently. If so please let me knowInadequate Customer Support
Hello & Greetings! I have been a pro Zoho user since the last 2 years and I would admit that the apps that are being offered are good, however the support we receive has a lot more to achieve. This being a design issue rather than a staff issue. BeingIncorrect Closing Stock Amount value
Act as Zoho Inventory Expert. We are a construction company, OVAL Projects Engineering Limited. We started using Zoho Inventory for Stock Management.I have multiple warehouses. I have encountered a problem while generating custom warehouse wise inventoryThe Next Chapter for CRM for Everyone: Moving from Early Access to Phased Rollout for Customers
#CRM25Q1 Hello Everyone, Until now, CRM for Everyone has been available in early access mode exclusively for users who opted to try the new version. We are now transitioning to a phased release, starting with the basic edition. We are thrilled to announceZoho vault uses only password to unlock not a TouchID
TouchID works when normally openning app but when called from keyboard while browsing or trying to log to another app it shows only password option to unlock. This behaviour is only on iPad Mini witch latest 18.2OSUnable to add Guest Members
We are having issues adding Guest Members to our Cliq account. We have sent out a number of request but it seems that only some people are able to access the platform. Others have received a message stating that they need to be granted access from anWebhook when estimate is refused is not firing
Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I createdNew Leave Type: Compensatory off
Hi, there is a new Leave Type: Compensatory off. Can someone tell me how to use it, because it sounds it could work for overtime compensation for our techs. Thanks AndreasInvoice status on write-off is "Paid" - how do I change this to "Written off"
HI guys, I want to write off a couple of outstanding invoices, but when I do this, the status of the invoices shows as "Paid". Clearly this is not the case and I need to be able to see that they are written off in the customer's history. Is there a wayCreate a custom button to modify custom fields in zoho Inventory
I am needing a script for two buttons, 1. Button will add todays date to a custom field named cf_sent_to_sov 2. Button will mark a checkbox or unmark a checkbox field named cf_parts_ordered I have been trying to figure out deluge but have not got anywhereHow to add a record for a different report
I have one form and it has two reports I need to programmatically add records to both reports For example one report is draft and other is processed After the user performs some action on the draft report I want to create a new report in Processed andCreate custom rollup summary fields in Zoho CRM
Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions wereHow do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.
How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.Webhook 'when estimate is refused' is not firing
Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I createdAmazon Integration
Hi, I am seller on Amazon , & I would like to sign up for Zoho books. However my question is can we automate/integrate invoicing, charges and returns in amazon with Zoho using API? Do you have a developer for this? I did take a look at zapier however it just has a create Invoice function nothing else.Link project tasks to tasks in CRM and/or other modules.
Hello, I have created and configured a project in Zoho Projects with a set of tasks. I would now like to link these tasks (I imagine according to the ID of each one) to actions in the CRM: meetings, tasks, analytics). The aim is to link project tasksZoho CRM API Credits & Limits for Workflow
Hi Team, Just wanted to clarify how the API credits work for Zoho CRM and workflows with custom functions. API Credits are based on your subscription and are set at the account level. You can buy additional credits if needed. For Enterprise customers,Count the NUMBER of Contacts for an Account automatically
Hello. Is there any way Zoho can count the number of CONTACTS for a particular ACCOUNT and have a field in the ACCOUNT module update itself automatically? Currently we use Zoho to administer our language school and the Contacts represent students and Accounts represent Grupos (Classes). It would be very useful for us to have a feature like this enabled, and I can see other similar applications requiring something like this. The solution would be even better if the Contacts met a specified criteria,How to use Twilio to send appointment notification and reminder SMS in Zoho Bookings
Hit no-shows out of the ballpark by combining Zoho Bookings and SMS providers. SMS notifications help you remind customers of their appointments and reduce no-shows by reaching out where they are. In this guide, we'll configure an SMS provider calledgeographic search filter in map view
Hi, I have a recruiting and timesheet system built in Creator. The client wants to enhance the search for candidates based on their location and filter by job skills - currently they look on the Map View which uses the geo location or post code of theAnnouncement: Upcoming changes to the permission grant flow for OAuth apps
This announcement is intended for app developers who use the Zoho API console. We're going to implement an important update to the way users grant permission for the OAuth apps created through the API console. What’s changing? Currently, users can grant"Copy Field Values from one Module to another" how to use?
Hi everyone! I'm sorry if this question was already asked, I didn't find it! So let me explain: In my Tickets module, we have the custome field "customer type" where we indicate if it's a lead, user, etc... In the Contact module I used the "type" field with exactely the same entries. I would like when in the tickets module an operator choose an option that it automatically update it in the contact module. I found the "Copy Field Values from one Module to another" custom function which seems perfectAdd Google Workspace Module to Zoho Flow
Dear Zoho Flow Team, I hope this message finds you well. We’d like to request the addition of a dedicated Google Workspace module in Zoho Flow. Currently, there are no triggers or actions for Google Workspace, which limits our ability to integrate andHow do i remove the Powered by Zoho logo from my careers page
Can I remove this? Totals on Pivot Table
Is there a way to change the way the subtotal calculates? In this example I have a formula to give me the average monthly attendance ....but I want the grand total of the month to be the sum of all the average attendances...any ideas? Thank you!@Team Inbox is not working AGAIN
I like Team Inbox in general. It makes using a collaborative inbox easy - when it works. The problem is that it doesn't work at times - and it seems to not work, a lot. It's not catastrophic failure, it's little things. Unable to send messages UnableHOW TO USE ZOHO
IDKZoho account sign in with passkey
Hello, I am trying to sign in using passkey, but the option doesn't show up in the web and is disabled in Oneauth on mobile, saying the admin has restricted the use. On the Admin page in Security MFA I can find no option for passkey. Help would be greatlyPivot table with Text values - "Matrix Report"
User Story - As a user, I would like the ability to display textual data in a two-way table, matrix format (text datatypes, not numerical datatypes displayed as a dimension) One major feature missing from the Pivot tables in Zoho Analytics is the abilityCanvas View in Zoho Recruit
Is it possible or would it be possible to have the new 'Canvas View' in Zoho Recruit?Can Wisestamp email signature be use with Zoho mail?
Does a Wisestamp email signature work with Zoho mail?WiseStamp
WiseStamp is an excellent social media signature tool. It integrated seamlessly into Gmail and Thunderbird, plus a few more. Are there any plans to get this incredible app integrated into Zoho. check it out here: http://www.wisestamp.com/ thanks TimPower BI connector (Zoho Creator) to Zoho Projects
How can i connect power bi to Zoho Projects? domain is zoho.com How can i find workspace name, application link name and Report link name?Update your Google connection with Zoho TeamInbox
Dear all, Wishing you a Happy New Year! Google has recently updated its security policy to enhance user protection, requiring all third-party apps and services to use OAuth authentication and password-less login methods. This update impacts users whoPossible to backdate Stage shifts in Deals module?
I want to enter my organisation's historical dealflow data into the CRM and it's important that the Stage history is showing the correct dates in the past of when it happened. Is there a way for me to modify the "Modified Time" in the Stage History RelatedEasy way to delete attachments
I've reached my data limit and would like to run a view/report, and mass delete attachments. Is there an easy, fast way to do this? Moderation Update: Post Summary: There are two features the post discusses a) Easy way to remove Email attachments WillSites Speed and Performance Grades
I noticed that there are no recent inquiries or complaints about load speed or performance issues with Zoho Sites websites. However, I wanted to understand what Zoho has done to ensure that speed remains optimized, images are compressed and lazy loaded,Next Page