Dynamic Portal Sorting

 <  Day Day Up  >  

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

Last Name

First Name

Abrams

Alex

Abrams

Beth

Adams

Steve

Adid

Fereena

Adid

Samir


Recall also that sort fields can be set for ascending or descending behaviors. If you change the first name in Table 16.2 to sort descending, the list would look like Table 16.3.

Table 16.3. Contacts First Name Descending

Last Name

First Name

Abrams

Beth

Abrams

Alex

Adams

Steve

Adid

Samir

Adid

Fereena


You can use FileMaker Pro's capability to properly sort in descending order for your sortCalc field. To toggle between the two behaviors, ensure that the first field always contains identical values when a user wants to have a portal sort by the second field ”in this case set to descending order.

Here are the two fields you need:

sortAscend =

 

 Case (      gSortPref = "number-ascending" ;         Right ( "000000000000000" & myNumber; 15 );      gSortPref = "text-ascending"; myText;      gSortPref = "date-ascending"; GetAsNumber( myDate );      1 ) 

sortDescend =

 

 Case (      gSortPref = "number-descending" ;         Right ( "000000000000000" & myNumber; 15 );      gSortPref = "text-descending"; myText;      gSortPref = "date-descending"; GetAsNumber( myDate );      1 ) 

Notice that in the case that someone chooses one of the descending options, all the values in sortAscend equal 1. If you set up your sort dialog to first sort by sortAscend (ascending) and second by sortDescent (descending), your first field overrides the second when a user chooses one of the ascending options from gSortPref ; otherwise that field is set to all the same values, and by definition the values in sortDescent will apply, happily making use of the descending sort behavior built into FileMaker.

To put the finishing touches on your user interface, you might consider making icons that indicate when a portal column is sorted ascending or descending. The script that sets your gSortPref can also control which images appear in container fields to provide visual feedback to the user.

 <  Day Day Up  >  


QUE CORPORATION - Using Filemaker pro X
QUE CORPORATION - Using Filemaker pro X
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 494

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net