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.

Zoho Desk Resources

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

Sticky Posts
Focus 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 can
Identify 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
Gain control over record sharing with portal users through our new enhancement: criteria-based data exposure
Dear Customers, We hope you're well! Portals is a self-service avenue through which your clients can access and manage their direct and related data in Zoho CRM. This empowers them to be more independent and enables them to experience a sense of transparency
Context and convenience just got better with Zia's email intelligence
Dear Customers, We hope you're well! We are in 2024, and email as a tool is only getting more powerful each day. While it enables seamless daily correspondences, we are here with a set of abilities that will enhance your user experience and save several