Unanswered: Sorting a Many-to-Many Relational Field
I had a ‘sorting’ question. There is a Many-to-Many Relational field in my project and it contains two columns – a ‘name’ column and a ‘total’ column. Under the ‘Configure Appearance’ button for this Many-to-Many Relational Field, I specified that the ‘sort’ should be by the ‘name’. This works fine but there are circumstances in which the user may wish to sort this by the largest ‘total’ (Z-A). I realize that I can accomplish this by adding a second Many-to-Many Relational Field that is already sorted in this fashion but that seems to be extremely wasteful.
Ideally, a button would appear next to the populated Many-to-Many Relational Field for when the user wanted to sort the already present list in from the largest ‘total’ to the smallest. I have searched and cannot find any information on how to incorporate scripting to change the sorting of a Many-to-Many Relational Field. Am I bound to using only one, unchangeable sorting method or is there a way to accomplish dual sorting when desired?
Would a duplicate (copy/paste for identical style but change sort order) of your M2M field overlaped but visibility rule controlled by a global which would be set by a "sort button/s" to 1 or 0 work?
May have use a "cludge" to get BD to refreash display - one way is a history "previous-next" move to record action.
That is what I was hoping to avoid. I already have a total of four other Many-to-Many Relational Field Forms within that particular sheet – each displays a different result from a different analysis. The visibility of these are controlled by rules. Essentially, if it is not possible to sort ‘after the fact’, I would have to double that to eight. I would really like to not have to add four more forms because it is already cumbersome to navigate on that form with just the original four Many-to-Many Relational Field Forms.
Wow, this seems to be an oversight by Brilliant Database. I would have been pretty sure this could be done using the traditional methods, but nope, there's no function to switch the type of sort set for a many to many.
After juxtaposing the ability to see the analysis in both fashions against having not-so-many Many-to-Many Relational Fields, I made an executive decision. Both methods of sorting have considerable merit individually and one does not necessarily outweigh the other. Because there is considerable value in seeing the information sorted in the two fashions, I have added the additional Many-to-Many Relational Fields with their visibility controlled as David suggested.
I just have to be especially careful if I ever have to reposition those forms as they have been known to completely disappear off of the entire form. Luckily, James told me how to go 'fishing' for them in the event that happens again (which it already has!).
Just a wild idea (with thanks to James) - what if your created a HTML field in your M2M form and populated it with a choice of other fields (with a script) and then sort on this "changeable" field. Have not yet tried but did comfirm BD will sort a HTML field and James's little HTML snippet he gave me allows any value to be inserted at will (including text which is a game changer for me). Would need a record set based on M2M and loop on this set to change all related lines. So you would always be sorting same field but its contents change.
Hope you follow me as just had this idea while having breakfast and not fully developed yet
Update This won't work even though BD does include HTML in sort list, it is not an actual value in database - display only.
A text field might work but we come back to fragmentation problems and numbers sorted as text are
a problem. Does not look like any alternative to multiple M2M.