Hack to sort rows of a subform and pre-populate row fields that I want to preset

Hack to sort rows of a subform and pre-populate row fields that I want to preset

Not sure if Zoho have finally enabled sorting of rows in subforms.  But the hack below worked for me.

Basically i take note of the random order the rows are sorted.  Create a hidden field in the table being used for the subform called Order_Index.  I then just set the order index to the value it appears when the subform is displayed before implementing this logic. E.g The row I want to appear first might show as row 5 so I set the Order_Index for that record to 1.

I create a map() which has a key of the order index and field name and set the value for that record.field. 

E.g. 

  1. // Setup the base list of product types.   The Product_Types_Form is the table I am using for the subform

  3. productTypes  =  Product_Types_Form  [ID != 0] sort by  ID ;
  4. rowCounter = 1;

  6. /* Create a map and insert all the Product_Type records sorted using the Order_Index field i manually set by viewing the subform random sort order that zoho generates.  But at least it is the same every time so this method works
  7. */
  8. productTypesMap = map();
  9. for each productTypeRecord in productTypes
  10. {
  11.     // create a key in the map for each record based on the Order_Index and Record Field Name
  12.     productTypesMap.put(productTypeRecord.Order_Index + "_Product_Type", productTypeRecord.Product_Type);
  13.     productTypesMap.put(productTypeRecord.Order_Index + "_Default_Supplier", productTypeRecord.Default_Supplier);
  14.     productTypesMap.put(productTypeRecord.Order_Index + "_Default_Model", productTypeRecord.Default_Model);
  15.     productTypesMap.put(productTypeRecord.Order_Index + "_Default_Model_Unit_Cost",           productTypeRecord.Default_Model_Unit_Cost);
  16. }

I preset the number of rows in the subform so there are already rows setup ready to populate in the order i want to.  You can set this in the form builder for the subform field. If you dont do this you will just get a NULL pointer error when you try to loop through the subform rows.  So an obvious limitation is you have to know how many rows the subform will contain.  If anything changes you would have to add new record to the subform table and set the order index correctly and change the preset rows for the subform!!

Then i loop through the rows of the subform and pre-populate the row field values using the a row counter to link with the Order_Index and get the record values which are in the map in the order i want them.  In my case I also wanted to pre-populate 4 of the 8 fields in each row of the subform.  I guess this is why most people want to be able to sort subforms when the parent form is first loaded.  

I know its a hack and has many limitations but it did the job for me instead of waiting months or years for Zoho to add the feature. And after all this I had to create another hack for the form in edit mode because the subform is sorted in a completely different random order, not how it is saved when the record is created.  I think they have fixed this one now.

Below is the code to order the subform rows in the order I put them into the map();

  1. // The row counter creates the link to the Order_Index. I.e. when the rowCounter=1, the record value in map will be record with Order_Index=1 and so on.
  2. rowCounter = 1;

  4. // My subform is called Required_Products, so for each row I set the values contained int he map i created above.

  6. for each productRow in Required_Products
  7. {
  8.     productRow.Product_Type = productTypesMap.get(rowCounter + "_Product_Type");
  9.     productRow.Supplier = productTypesMap.get(rowCounter + "_Default_Supplier");
  10.     defaultModel = productTypesMap.get(rowCounter + "_Default_Model");
  11.     productRow.Model_Lookup = defaultModel;
  12.     defaultModelUnitCostStr = productTypesMap.get(rowCounter + "_Default_Model_Unit_Cost");
  13.     productRow.Unit_Cost = defaultModelUnitCostStr.toDecimal().round(2);
  14.     rowCounter = (rowCounter  +  1);
  15. }


And that's it.  My luck Zoho will implement sorting and ability to pre-populate in the near future anyway.  BUt I thought this might be helpful as it is a common topic/complaint on the forums.