Rodger's helpful scripting tips #2 - Subforms

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:

  1. 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:

  1. for each record in l_orderedProducts
  2. {
  3.    if record.get("Product").equals(<productId>)
  4.    {
  5.       m_record = Map();
  6.       m_record.put("Description",,New Description>);
  7.       updateRecord = zoho.crm.updateRecord("Ordered_Products",record.get("id"),m_record);
  8.    }
  9. }
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

  1. l_orderedProducts = zoho.crm.getRecords("Ordered_Products",<page>,200);
  2. for each record in l_orderedProducts
  3. {
  4. }

each "record" is a map and since it's a map, we can write data to it directly. So what we can do it

  1. r_record = zoho.crm.getRecordbyId("Orders",v_id);
  2. for each line in r_record.get("Ordered_Products")
  3. {
  4.    line.put("Description",<New Description>);
  5. }
  6. m_record = Map();
  7. 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)

  2. r_draftOrer = zoho.crm.getRecordById("Draft_Orders",v_id);
  3. l_lines = List();
  4. for each  line in r_draftOrer.get("Custom_Products")
  5. {
  6. l_lines.add(line.get("Proof").get("name"));
  7. }
  8. l_lines = l_lines.sort();
  9. for each  item in r_draftOrer.get("Custom_Products")
  10. {
  11. item.put("LinkingModule18_Serial_Number",l_lines.indexOf(item.get("Proof").get("name")) + 1);
  12. info item.get("LinkingModule18_Serial_Number");
  13. }
  14. update = zoho.crm.updateRecord("Draft_Orders",v_id,{"Custom_Products":r_draftOrer.get("Custom_Products")});
  15. 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

            Bootcamp
            Community
            Pricing
            Webinars
            What's New
            Integrations
            Contact Support
            Contact Sales

              Zoho Survey Resources
                Features
                Survey Templates
                Integrations
                Community
                iOS Mobile Surveys
                Pricing
                Android Mobile Surveys
                Insurvey Blogs
                Insight Blogs
                Tips & Tricks





                              Latest Feature Updates


                                  Zoho Desk Resources

                                  • Desk Community Learning Series


                                  • Digest


                                  • Functions


                                  • Meetups


                                  • Kbase


                                  • Resources


                                  • Glossary


                                  • Desk Marketplace


                                  • MVP Corner


                                  • Word of the Day




                                      Zoho Marketing Automation
                                        Zoho SalesIQ Resources


                                              Manage your brands on social media
                                              Access Zoho Social

                                                Zoho Pagesense Resources


                                                    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 Campaigns Resources


                                                                          Zoho CRM Resources

                                                                          • CRM Community Learning Series

                                                                            CRM Community Learning Series


                                                                          • Kaizen

                                                                            Kaizen

                                                                          • Functions

                                                                            Functions

                                                                          • Meetups

                                                                            Meetups

                                                                          • Kbase

                                                                            Kbase

                                                                          • Resources

                                                                            Resources

                                                                          • Digest

                                                                            Digest

                                                                          • CRM Marketplace

                                                                            CRM Marketplace

                                                                          • MVP Corner

                                                                            MVP Corner





                                                                              Design. Discuss. Deliver.

                                                                              Create visually engaging stories with Zoho Show.

                                                                              Get Started Now