From Dwayne Wright PMP
Certified FileMaker Developer
Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here).
The very basics of sorting records starts in browse mode and then choosing sort from under the Records menu. This will bring up the sort dialog box. The dialog box will show all the fields that can be sorted upon on the left side of the dialog box. You click the one you want and click the Move button. This will cause the field to appear in the box on the right, which is the specified sort order.
You can specify options for each of your field sort selections. Highlighting a field in the right hand sort order area will allow you to select these options. You can choose a sort order of ascending, descending or even what a value list sort order may be. You can even override the language assigned to an indexed field with a different language. So as you can see, FileMaker allows you a lot of flexibility in your sorting record needs.
You can repeat these steps to add sorts within the sorts. That is to say that after the first field is sorted, you may have two or more fields with the same information. For example, you might have more than one record that has the state of TX in the state field. You can sort by state and then choose to sort by county. All the TX records will appear before the WA records but within the group of TX records ... they are sorted by their counties.
We covered most of the settings in the sort dialog box but I wanted to make sure we covered all of them completely. The following is a listing of the objects in the sort dialog box with a brief description of what each setting will do.
** Now this is a little tricky to explain but the Clear button and the Move button are the same button. It changes it’s title and behavior when you click on a listed field on the left or the right side of the dialog box.
Clear Button - The Clear button will remove one or more of the selected sort fields in the right hand column.
Move Button - This button appears as an option when you have selected a sort field in the left column. When this is the case, the Move button will move the selected field from the left column into the right hand column to be added to the sort order.
Sort Button - This button becomes active when you have one or more fields in the right hand sort order column. The sort button will perform the sort for the found set of records in the file.
Unsort Button - Will return the found set of records to their default order ( the order the records were created ).
FYI ...The next three options are used to change the sort order of a field in the right hand column. To change a sort order item, click it in the sort list and then click the ascending, descending or by value lists options. You can see the sort order change by looking back at the sort icon next to the field.
Ascending Order Radio Button - Sorts the file so the lowest values for the selected field will appear first in the following found set.
Descending Order Radio Button - Sorts the file so the lowest values for the selected field will appear towards the end in the following found set.
Text values are sorted
A - Z by character when ascending ( alpha before omega )
Z - A by character when descending ( segway before magazines )
Number fields can include negative values and are sorted smaller numbers before larger ones when ascending ( -10 before 10 ) larger numbers before smaller ones when descending ( -10 before -1 )
Date fields can include dates in the future and are sorted the past before the future ascending ( 11/30/2003 before 12/25/2003 ). the future before the past descending ( 12/25/2003 before 11/30/2003 ).
Custom Order Based On Value List - Sorts the file so that items found first in a value list will appear first in the following found set. When the option is selected, It is used primarily for text fields but can be used in other field types as well.
Override Field’s Language For Sort - This is used to override the language settings for a field in the sort order. When chosen, it will sort the field by that language setting.
Reorder Based Up Summary Fields - This option is often overlooked but can be a great asset when doing reports in FileMaker. This feature allows you to export summary fields that are in a sub summary part. This means you can actually save a report that includes subsummary data as it’s own FileMaker file!
BTW: Sorting In Relationships
You can also sort the way records are seen in a defined relationship. This does not actually sort the records in the related table ( often called the child table in a relationship ). The sorting will control what related records information you are viewing when you place a related field on a layout. A more common benefit is to see related records in a desired order when looking at a portal.
Adding a sort order to a relationship is done by selecting the pop up menu above the left hand field column. You choose the relationship you want to use and then select the related fields that it provides. Relationship sorting can slow down a FileMaker solution in some cases. It is most noticeable when the related file has a large number of records and a large number of related records. In many cases, the speed hit pays off well in additional functionality of your relationship but I’d still recommend using relationship sorting only when needed. There are other places where you will find the sort dialog box become available such a portals and sub-summary reports.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2007 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.