Sorting A FileMaker Relationship

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

In the bottom third of the define relationship operator box, you can see options for each table occurrence in that relationship. These settings include ... "Allow creation of records in this table via this relationship", "Delete related records in this table when a record is deleted in the other table" and Sort Records. The settings are on both sides because FileMaker relationships are bidirectional.

For the purpose of this discussion, we are looking at the "Sort Records" setting. This setting can work for you in at least two ways but many only think of the portal method.

The sort option when defining a relationship is used to have related records recognized in a defined order. Relationships sorting can be useful for ...

- defining what related information appears when you put a related field on a layout

- defining what related information is used in calculations using relationships

- defining what related record FileMaker will go to in a GTRR situation

- what order related records appear in a portal ... if the portal does not have it's own sort order

The sort dialog box for the related records is practically the same as the sort dialog box for records. The settings include ascending, descending or custom based on a value list for each related field you want to sort. This sort records setting can work for your portal or you can setup your portal settings individually. FYI ... before FileMaker 7, portals did not have their own sort options. They had to use the sort order defined in the relationship.

Relationship sorting is a feature to consider carefully before implementing. By default, a FileMaker relationship is sorted by the related records creation order. That means the related records that were created first are seen before any more recently created records. Choosing the sorting option can reverse this order or allow you to set your own order. Sounds pretty cool and this option can be very useful. The problem is ... as normally is the case ... when the databases start growing in size. When you go from record to record, you have to wait for the related records to sort. This can be very uncomfortable on large databases that are networked with many users.

REAL WORLD EXAMPLE: As a developer, you may be working with a copy of the FileMaker solution on your individual hard drive. You turn the relationship sorting option on and it doesn't appear to be too slow. After you have completed your design changes, you upload the files in the FileMaker solution to the main FileMaker server. The next day you begin getting complaints about how slow the database is behaving. This is because every user on the networked file is encountering the same default sorting routine. Speed is one thing when a database solution is run by one person on a local hard drive. Speed can be another thing when a database is being used by 50 people over an already stressed network connection.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 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.