Excel-style sorting

[Matthew F]

I have a table that I'd like to be able to sort according to columns similar to the behavior in MS Excel.  I'm wondering if someone already has an example of this that they could share, or suggestions on how to go about it.

 

In other words, sorting columns sequentially would not clear the prior sort order, but would apply the prior order as a secondary sort criteria.   Also, sorting by the same column twice should reverse the primary sort order.   This would be done with sort buttons in the column headers, without bringing up the sort dialog. 

 

Thanks. 

[Ocean West]

Hi Matt,

 

Unfortunately there isn't an elegant way to programmatically, change the sort order without hard coding all the possible permutations of the given columns.

 

Given that fact i have done it with a VirtualList so that I can control the fieldNames used for the columns and there was a finite number of columns but it ads overhead

and the data being sorted is at least one relationship away, adding a performance hit.

 

However there is one clever trick using a Web Viewer with JQuery and SQL functions to display the results in columns & rows allowing for user interaction to inverse sort on columns - i am sure some programing you could allow the clicks to the column headers to append the sort order as it does in Excel.

 

http://www.seedcode.com/filemaker-sql-explorer/

[Matthew F]

 

 

there isn't an elegant way to programmatically, change the sort order without hard coding all the possible permutations of the given columns

Eeek.  That's what I would hope to avoid. 

 

Thanks for the tip on SQL explorer.  I've shied away from learning SQL, but this might make things less painful.  

[LaRetta]

Hi Matthew 

 

You might also like to explore this file by our Jeremy Bante http://www.modularfilemaker.org/2013/07/virtual-sort/

[Matthew F]

Thanks LaRetta, that's brilliant. 

[LaRetta]

I do feel the need to caution a bit ... these techniques, from my understanding and I could be wrong, are unstored.  Jeremy's process will be a life-saver when you really need to multiple-sort but keep it in reins.

 

I have not speed-tested any of these various options across WAN.  I would probably consider using Perform Script On Server to find and then sort and just give me a list of sorted record IDs (no not unique IDs but record Ids; they are much faster) and use them in virtual portal in their already-sorted state.  But so far I've dodged the multiple-sort-bullet by other tricks, smoke and mirrors, and a LOT of luck.

[Matthew F]

Well, it is certainly a clever solution nonetheless.  I was able to come up with my own ad hoc method with a script, and it suffers from the same limitation.  Namely, sub-summaries parts do not display when sorted by the corresponding column header.  

[comment]

Note that some of this functionality is already built-in into Table View - see:

http://www.filemaker.com/13help/en/html/add_view_data.4.8.html#1045713

 

It's far from perfect, but it just might be better then reinventing the wheel. Especially since the new wheel is not quite round...

[Matthew F]

Wow.  I'm embarrassed to admit that I did not know that one can sort by two fields at once in Table view by Command-clicking multiple column headers.  It ain't perfect, but its still good to know about.