I've been building a database from scratch for tracking support tickets, and it took me a while to figure out how to get the summary listing portal to be able sort by any of the columns. I'm sharing how I did it for the benefit of others who might not have the patience or level of experience working with FMP to figure it out themselves (including my future self!).
This setup allows you to click any of the column headers (buttons corresponding to fields shown in the portal) to get the portal to sort by that column, and if you click the header again it flips the sort order. It also filters out closed support tickets and allows you to filter them back in with a toggle checkbox.
It's not the prettiest thing, but it does the job, here's a screenshot:
The "Contacts.fmp12" starter solution is an example of doing it with sub-summaries of sub-summaries by putting the "Details" tab into a separate layout, but I prefer to do it in a portal because it keeps everything in one layout. Others might need to be able to do this in a portal or need these detailed instructions to configure their solution. The code for reversing the order I used below is also applicable for doing it with sub summaries. (I didn't think to look at the "Contacts.fmp12" starter solution until after doing all the work and typing this out, and while you only have to update the "Sort list value" value list in that database to be able to sort by more ways, it doesn't toggle between ascending and descending, and either way the complete lack of documentation in the starter solutions make them seem more complicated than they really are. My solution can't show sub summary headings, which might be useful to some.)
Here's how I've configured mine:
1. The first global field
This can all be done within one data table, although you'll need a second instance of the table to create the portal (relationship info in step 7). Here I've done it on my Support Tickets table. You essentially need:
1. A global field that stores the current sorting field
2. A global field that stores whether to sort by A-Z or Z-A
3. A calculation field that pulls from other fields in the table based on what the global sorting field is
- Note: this needs to be updated manually every time you add a field to the table
4. A portal that sorts by the calculation field
5. A script that sets the global variable field
6. Buttons on the layout to act as column headers which trigger the script
7. Addendum: hiding inactive items
1. The first global field is set to auto-enter a calculated value of "Ticket Number" so that the portal has a default sorting. Validation is not necessary. Make sure to set storage to Global.
- For simplicity, the values that I use (though validation is not required) is equivalent to the sorted fields' names, which is equivalent to the column headers. These are: Ticket Number, Priority, Status, Date opened, Date due, Platform, Summary of Problem, Next Steps.
2. The second global field is set to auto-enter "A" (with quotes)
- Note: I use a script to toggle these fields, and the fields themselves don't display on the layout, so a value list is not necessary.
3. The calculation field is the most complicated part, but also the only part that needs to be changed if you add new sorting columns. The full calculation is at the bottom of this message, but it is essentially in this format:
a. Use the Let function to set an internal variable where the value is the value of the field that corresponds to the field specified in the Global field, e.g. "If ( Global:SortTicketListBy = "Next Steps" ; Next Steps ; Ticket Number )
b. Make a second internal variable (txt*), equal to Upper(Order)
c. Make a third internal variable (reverse*), that substitutes A-Z-0-9 with 9-0-Z-A
(* Based on the suggestion in Vaughan's fmforums post)
d. After setting variables, if the Global A-Z field is set to Z, use the values from "reverse", otherwise use the values from "txt"
- Since this field is only used by the portal sorting,
- If some fields are long, you might want to limit the number of characters drawn from each field
- In my calculation I've enabled secondary sort orders by adding the second field to the one drawn, e.g. If ( Global:SortTicketListBy = "Next Steps" ; Next Steps & Ticket Number ; Ticket Number ). I've done this manually, so the secondary field is fixed based on the primary, but there are ways you could program a secondary sort field into it.
- I've also had to get the calculation to add zeros to the Ticket Number so that it doesn't put 11 immediately after 1 (since the calculation field is stored as text instead of a number)
- I suppose you could pull only a portion of the called fields using the Left() command if some of the called fields are long and that slows down the processing.
4. The portal simply needs to be set to sort by the calculation field. The portal needs a name, e.g. "Support Ticket List Portal"
5. The script is very simple. It just requires a script parameter of the sorting value ( = column headers = field names, in the way I've set up my database) supplied by the button, and it sets the global variable to that script parameter. If the global value is already set to the script parameter, then it also toggles the A-Z global field. After refreshing the portal, the calculation field and portal take care of everything else.
- Set Variable [ $~Sort ; Value: Get(ScriptParameter) ]
- If [ $~Sort = Support Tickets::Global:SortTicketListBy ]
Set Field [ Support Tickets::Global:SortTicketListAorZ ; If ( Support Tickets::Global:SortTicketListAorZ = "A" ; "Z" ; "A" ) ]
- End If
- Set Field [ Support Tickets::Global:SortTicketListBy ; $~Sort ]
- Refresh Portal [ Object Name: "Support Ticket List Portal" ]
6. The buttons just need to call the script with the script parameter of the sorting value, e.g. "Next Steps". They can be conditionally formatted to highlight when the Global:SortTicketListBy field is equal to the sorting value.
7. Show or hide closed tickets in the table by configuring the relationship between the two table instances like so:
- A calculation field, Support Tickets::Inactive, is set to If ( Status = "Closed" ; 1 ; 0 )
- A global number field, Support Tickets::Global:ShowInactiveTickets can be set to 0 or 1
- There is a button above the table that performs a single step to set the Global field like so (actually it's a checkbox with a button trigger so that it looks to the user like they're checking and unchecking the checkbox):
If ( Support Tickets::Global:ShowClosedTickets = 1 ; 0 ; 1 )
- The relationship has Support Tickets::Global:ShowInactiveTickets connected to Support Tickets 2::Inactive with a ≥ sign
There may be slightly easier ways of doing elements of this, and so far my table doesn't have too many entries, but it's relatively simple in that once you've set it up, the only thing you have to change when adding new columns to the table is the Case() step within the calculation field (aside from the button, its script parameter, conditional formatting, and label, which you'd have to edit anyway).
Here's the full calculation from that field, which I'm sure I'll refer to again in the future when I want that substitution table:
[ Order = Case (
IsEmpty ( Global:SortTicketListBy ) or (Global:SortTicketListBy = "Ticket Number" ) ;
Case ( Length ( Ticket Number ) = 1 ; "0000" ; Length ( Ticket Number ) = 2 ; "000" ; Length ( Ticket Number ) = "3" ; "00" ; Length ( Ticket Number ) = "4" ; "0" ) & Ticket Number;
Global:SortTicketListBy = "Priority" ; Priority & " " & Date due ;
Global:SortTicketListBy = "Status" ; Status & Priority & " " & Date due;
Global:SortTicketListBy = "Date opened" ; Date opened & " " & Priority;
Global:SortTicketListBy = "Date due" ; Date due & " " & Priority;
Global:SortTicketListBy = "Platform" ; Platform & " " & Priority;
Global:SortTicketListBy = "Summary of Problem" ; Summary of Problem ;
Global:SortTicketListBy = "Next Steps" ; Next Steps
txt = Upper( Order ) ;
reverse = Substitute( txt ;
[ "Z" ; "0" ] ;
[ "Y" ; "1" ] ;
[ "X" ; "2" ] ;
[ "W" ; "3" ] ;
[ "V" ; "4" ] ;
[ "U" ; "5" ] ;
[ "T" ; "6" ] ;
[ "S" ; "7" ] ;
[ "R" ; "8" ] ;
[ "Q" ; "9" ] ;
[ "P" ; "A" ] ;
[ "O" ; "B" ] ;
[ "N" ; "C" ] ;
[ "M" ; "D" ] ;
[ "L" ; "E" ] ;
[ "K" ; "F" ] ;
[ "J" ; "G" ] ;
[ "I" ; "H" ] ;
[ "H" ; "I" ] ;
[ "G" ; "J" ] ;
[ "F" ; "K" ] ;
[ "E" ; "L" ] ;
[ "D" ; "M" ] ;
[ "C" ; "N" ] ;
[ "B" ; "O" ] ;
[ "A" ; "P" ] ;
[ "9" ; "Q" ] ;
[ "8" ; "R" ] ;
[ "7" ; "S" ] ;
[ "6" ; "T" ] ;
[ "5" ; "U" ] ;
[ "4" ; "V" ] ;
[ "3" ; "W" ] ;
[ "2" ; "X" ] ;
[ "1" ; "Y" ] ;
[ "0" ; "Z" ]
If ( Global:SortTicketListAorZ = "Z" ; reverse ; txt )