1 of 1 people found this helpful
Just an idea, but might be too much of a hit on performance depending on record numbers and network setup... Have you tried sorting by an unstored calculation field that is based on a global field value? The global field value could be selected from a drop-down list of field names, and the unstored calculation could use the selected global field value in an "Evaluate ()" function to evaluate the contents of that field in each row.
I've been working on a module I'm calling "Virtual Sort" that does exactly that. You can download the current prototype from GitHub. (Click on the ZIP icon, not the FileMaker file.) I'm hoping to put together some documentation and better examples this weekend. It's based on a technique demoed by John Ahn at the last DevCon for using ExecuteSQL to come up with the sort. My version adds sorting on multiple fields and builds the SQL for you. Creating and sorting by a value list was an I idea I considered, but I wound up not going with that. I don't remember exactly why.
I've tried this but using SQL to calculate the sort value and performance is fine (on my limited, local dataset). The layout needs a Refresh which always takes time, but it's very quick. I think we are on to a winner.
Well that most certainly works ;-) and there looks to be a lot going on there, I'm sure you're working out sort order (ascending/descending) and all sorts of things.
I did the following…
Set Field (gSort ; get(scriptparameter)
Then a field called Sort has the following calculation…
ExecuteSQL ( "SELECT \"" & gSort & "\" FROM \"Job\" WHERE \"PK_ID\" = " & PK_ID ; "" ; ¶ )
Basically getting the contents of the field selected for the current record and inserting it. The portal is then sorted on the field Sort.
Thanks for the example though, good work.
Though a good solution has been posted I would like to add some more options for sorting of portal records. Please look into the attached file where you can get options for 3 ways to sorting portal rows.
1. Sort Portal rows by Custom Value list
2. Sort Portal rows by Custom Value list (According to Field Name for which you want to sort)
3. Sort Portal rows by respective column's values on click over the column headers
[This is only giving Ascending order sorting, if you want both way you need to just add another condition.]
Software Developer, Mindfire Solutions, India
SortPortal.fmp12.zip 13.6 K
You can also put several copies of a portal in serparate tabs, each with a specific sort specification. The tab control can be made "invisible" with the "Sort by xxx" choices used as the tab captions. You think you are pushing a button, but actually switching tabs. Very fast as the sort can be on stored fields rather than calc.
Andy Gaunt came up with this idea