Dynamic Portal Sorting

Dynamic Portal Sorting

Developers often place column labels above portals, and one of the first things we've seen users do with a newly minted database is click those ever-so-tempting column headers expecting them to sort. As discussed previously in this chapter, you can sort portals either at the portal level or at the relationship level. One of the more common requests we get as developers is to allow users to sort by whatever column they wish. Unfortunately, there's no way to programmatically define by which field a portal sorts. There is, however, a method for dynamically sorting a portal.

Using a calculation field, you provide FileMaker with the data by which you want a portal sorted. You need to create two new fields for your database: a control field, gSortPref , to hold the name of the field by which you want to sort, and a field for the actual sorting, sortCalc . We suggest you place sortCalc in the same table in which the rest of your portal fields sit. Your control field serves as a mechanism for choosing sort order. There are multiple ways to allow the user to change the values in it: You can use a value list, set by script when a user clicks on a column header, or perhaps a script available in the Scripts menu. However this field is managed, it's the end result that is important. Your sortCalc field depends on it. Here's an example of how sortCalc might be defined:

Case (

     gSortPref = "First Name" ; Name_first;

     gSortPref = "Last Name"; Name_last;

     gSortPref = "Email"; Email;

     "error"

)

If you set a portal to sort by sortCalc , notice that depending on what choice someone makes of gSortPref , the calculation returns the data by which the user expects to sort. If gSortPref = "First Name" , the related portal data from sortCalc might look like

• Alex

• Beth

• Coleen

If gSortPref = "Email" , sortCalc 's data would change to be

• beth@email.com

• gibson_alex@email.com

• neffy@email.com

There are a few more steps to completely flesh out this technique, but these are the basics.

One remaining task to be done is managing screen refresh. Your user may change gSortPref , and sortCalc updates accordingly , but your portal doesn't actually resort until the user changes layouts or modes, or performs one of a range of other possible actions. You could write a script to take the user into Preview mode and back into Browse mode, but to force the screen to refresh with a minimum of screen flashing, reset the key that controls the front of the portal relationship. Use a Set Field () script step, and set the key field to itself. This forces the portal to refresh ”because you've just altered one of the sides of its relationship ”without requiring the user to navigate or change modes.

To establish sort buttons at the top of column headers, simply create as many buttons as there are fields in your portal, then attach a script that, using Set Field , controls what parameter is passed to gSortPref .

An alternative technique would be to have gSortPref hold the actual name of the field by which you want to sort, and then instead of a case statement, use a GetField function to populate sortCalc . This works well for cases where all your fields are of the same type; however, both techniques fail when you are dealing with multiple field types ”for example, NameFirst , NameLast , BirthDate , and Age . You still need a case statement, as explained in a later section.

Multiple Field Type Portal Sorting

If you are using the technique just described, sortCalc needs to be a calculation that returns text, and so numbers and dates sort by the rules that govern text. Unfortunately, it's not possible to dynamically control what data type a calculation returns, so the following data ”1, 8, 9, 12, 82 ”sorts like so

1

12

8

82

9

To get numbers to sort properly as text, you need to ensure that all of your numbers contain an equal number of digits. 01, 03, and 10 sort properly where 1, 3, and 10 would not.

Dates in your text calculation, likewise, sort like so:

1/12/2004

10/1/2004

10/10/2004

10/2/2004

3/1/2004

Remember that FileMaker stores dates internally as integers. This is the key to solving the puzzle. The idea here is that if the integer representations of dates are compared, then the sort works properly. sortCalc needs to be set as the following, assuming you have three fields that you want to display in your portal ( myNumber , myText , and myDate ):

Case (

     gSortPref = "Number" ; Right ( "000000000000000" & myNumber; 15 );

     gSortPref = "Text"; myText;

     gSortPref = "Date"; GetAsNumber( myDate );

     "error"

)

This calculation converts all your numbers into 15-digit numbers. It concatenates 15 zeros with whatever number has been entered into myNumber , then truncates the result to 15 characters . This ensures that 1, 3, 10, and 999 respectively return 000000000000001, 000000000000003, 000000000000010, 000000000000999.

Quite likely, the integer representation of your date field already uses the same number of digits. Remember that dates are stored in FileMaker as integers. 4/1/2004 is 731672. To drop below or above six digits, you'll need to be working with dates approximately prior to 274 A.D. or after 2738 A.D. Most databases are a safe bet at six digits, but if you'recalculating dates for a sci-fi novel or are dealing with ancient times, feel free to use an identical approach to add digits.

Descending Dynamic Portal Sorting

It's possible to extend the technique discussed in the previous section so that the portal can be sorted in either ascending or descending order. To accomplish this function, you need to sort by two fields ”one ascending and one descending ”instead of just one.

Recall how sorting by multiple fields works: FileMaker Pro sorts all like values from the first field in a sort request together, and then orders records with identical values in that first field by a second field. Table 16.2 shows an example where a user sorted by last name, then first name.

Table 16.2. Contacts Ascending

Case (

     gSortPref = "number-ascending" ;

        Right ( "000000000000000" & myNumber; 15 );

     gSortPref = "text-ascending"; myText;

     gSortPref = "date-ascending"; GetAsNumber( myDate );

     1

)

Case (

     gSortPref = "number-descending" ;

        Right ( "000000000000000" & myNumber; 15 );

     gSortPref = "text-descending"; myText;

     gSortPref = "date-descending"; GetAsNumber( myDate );

     1

)