Sorting Revisted For FileMaker Reporting Needs

From Dwayne Wright PMP
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Many times you will want to have your found set of records in a particular order for reports. By default, FileMaker records are seen in the order they were created. As a FileMaker user, you can override this sort order temporarily. This is done by running a sort command via a menu option or via a script.

The sort order is made up of particular setting for the data in one or more fields. You make a selection of a field or field that you want to sort via, the sort order (ascending, descending or other) from a dialog box and then perform the sort.

Sorting can also show up in other areas than a command for a found set of records. You can have sort orders in relationship definitions, portal views, dynamic value lists and more. You can also have sorts affect calculations and summary totals.

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.

Clear All Button - When you have placed sort order items into the right hand column, you can delete them all via this button. This resets the right hand column to empty.

** 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!

More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

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.