Zoho Creator: Sort sub-form records in both Main Forms and Views/Reports

Zoho Creator is a great system for quickly creating simple cloud applications. I've run into a problem with sub-forms, though: currently, Zoho Creator does not provide functionality for sorting sub-form records by a specified column. Instead, it sorts records in the order in which they were added.

My sub-form is a Creator Form that's linked to another Creator Form (basically, 2 different tables). The forms are linked with a bi-directional lookup relationship.

I've seen and tried implementing these "hacks", but none of them work for my situation:

[Zoho Forums, "Subforms sorting rows"][1]
[Zoho Forums, "Hack to sort rows of a subform and pre-populate row fields that I want to preset"][2]

I also called Zoho tech support, and after looking at my application, they said that sorting sub-form records is not currently possible.

Any other ideas?

Solution

My tested solution is still a hack, but until Zoho implements a method to sort sub-form records via the GUI, this will have to do.

First, create a function that you can call from anywhere (e.g. when a new sub-form record is added or changed)--for details on that, go here: http://www.zoho.com/creator/help/script/functions.html

This function will first duplicate the sub-form records by the parent record ID (sorting by the appropriate column) and then delete all sub-form records that were inserted before the script started:

int SubFormRecords_SortByAnything_ReturnCount(int ParentRecordID)
{
    scriptStartTime = zoho.currenttime;
    for each rSubFormRecord in SubFormRecords  [ParentFieldName = input.ParentRecordID] sort by FieldName1, FieldName3, FieldName2
    {
        NewSubFormRecordID = insert into SubFormRecords
        [
            FieldName1 = rSubFormRecord.FieldName1
            FieldName2 = rSubFormRecord.FieldName2
            FieldName3 = rSubFormRecord.FieldName3
        ];
    }
    delete from SubFormRecords[ (Series == input.ParentRecordID && Added_Time < scriptStartTime) ];
    return SubFormRecords[ParentFieldName == input.EventID].count();
}

Once the above sorting function is in place (customized for your application), call it when appropriate. I call it when adding a record associated with the sub-form, or when I change the sorting column values.

That works well, and as long as you don't have complex logic associated with adding and deleting records, it should have minimal impact on application performance.

Please let me know whether that works for you, and if you have any better ideas.

Caveat: This solution is not suitable for forms containing additional sub-form records because deleting the records will delete linked sub-form values.

Thanks.