Some FileMaker Record Sorting Potential Issues

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

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

For the most part, sorting records is very easy and trouble free. There are a few gotchas that can arise, so I thought I’d address them here.

Text Fields Used For Number Data - When numbers are in a text field, they are going to sort a character at a time and not a value. So a 66 would sort before 7. This is because the first character in 66 is 6. Since 6 is lower than 7, it would come first. So if your sort results for numeric data is off, check to make sure the field is saved as a number field.

Adding New Records After The Sort - When you add new records after a sort operation, only the existing records at the time of the sort are sorted. The new records will appear in the order they were created. This condition is referred to as semi-sorted.

Related Data Sorting - When you are sorting a found set with a related field within the sort order, you will be sorting by the first related value for that relationship. The related fields are sorted in creation order, unless you defined a sort order for that relationship. In that case, your first related value is dependent upon the sort order for that relationship. This multiple level sorting may cause some sluggish response in some of your sort routines.

Empty Field Woes - On occasion, you will see a sort that looks wrong. In some cases, their will be blank records. This is because the record was created and the sort value wasn’t filled in. This can be the case when importing data from another resource.

Data Entry Woes - Some people may enter in the same data in totally different ways, making FileMaker think they are different. Say that one user enters a name in as Jon Doe and another Doe, Jon. Because the data was entered in differently, the two records will show up in difference places when sorting by the name field. So you might need to tighten down your database when you see a list or report that looks erratic. One good way to do this is via field validation settings.

You Cannot Sort Container Fields - Container fields cannot be used in the sort order dialog box. One way around this is to create an additional text field that describes what is in the container field and use that description field for sorting.

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