Sorting Records Using Your Own Custom Ordering

You may be familiar with the Sort Records dialog in FileMaker which allows you to display records sorted by one or more fields. What you may be less familiar with is the ability to more explicitly define a sort order with the assistance of a value list. This article gives an introduction to this often overlooked feature, and provides a few examples along the way.

Sorting Records

Sorting of records in FileMaker is achieved using the Sort Records dialog.

The dialog is available via Sort->Records in the file menu, and as a script step. You can also sort records by right-clicking while in browse mode somewhere on the layout, and choosing "Sort Records". The shortcut key ctrl+S (command+S on mac) will also bring up the sort dialog.

Move fields from the left side to the right side in order to sort by those fields. You can choose to sort by one or more fields, in either ascending or descending order. For most cases this is enough, and these sort options give you what you want.

There are however some situations where you wish to sort by a field, but the resulting sort is not what you want. This occurs when the sort order is to be custom, and not alphabetical.

As an Example:

An example of this situation may be sorting records by month name. If you were to simply sort your records by the month name, the resulting order would be:

• April
• August
• December
• February
• January

.....

Months are being sorted alphabetically, which is meaningless as a sort order. Ideally your sort order should be in correct chronological order, so how is this achieved?

The answer is to use the Custom Order Based on Value List option that is available when sorting:

Here, I have specified to reorder based on a new value list called "Month List", lets take a look at that value list:

The value list is setup as a custom value list, and in the correct chronological order. It is this order that the record sort will use. Any matching value found in our sort field will be placed in the order as specified by the value list.

An Important Thing to Note Here:

One of the downsides of value lists I find is that when constructing a value list using values in a field, or from a related field or table, any sort order is lost and the values will display in the value list in alphabetical order. If you were to use this method in constructing your value list, and then apply it to a sort as your order, you may find that you lose the sort order you want given the fact the value list is alphabetical.

This method of reordering sorted records based on a value list is most suited when using a custom values value list. When using this option, you can specify the exact order you want, and the value list will retain your order.

As always there is a downside - you must explicitly define these values within the value list itself, meaning the value list contents cannot be stored in a field. However, in most situations this is acceptable. For example, the months case is fine because there are only ever 12 months, and their order never changes.

Back to the Example - Sorting Job Statuses

For this example I have constructed a simple database containing 10 jobs. Each job has some basic details such as customer, date raised, month raised, job status and priority.

In this solution, I wish for the user to be able to sort jobs by their status. Job statuses however have a very specific order to them - not alphabetical. The status order of jobs has been defined earlier by the owner of this database to be:

• Pending
• In Progress
• In Invoicing
• Completed

No problem! All that is required is to construct a value list of job statuses in that order. In fact, it is very likely that the value list would already exist as a method for selecting a jobs status.

You will note that a sort field has a little icon that appears beside it. The icon is either the standard for ascending or descending order. However if you choose a custom order, the icon appears different:

This icon indicates that a custom sort order has been used that is neither ascending nor descending.

As you can see in the above picture, the records have been sorted by job status, and are now displayed not in alphabetical order, but a custom defined order as specified by the value list.

A Final Example, Sorting by Job Priority

Up until this point, when we have reordered our sort by value list, the exact order of the value list itself has been used.

Jobs also have a job priority. A jobs priority from lowest to highest is:

• Low
• Medium
• High
• Urgent
• Critical

A value list has been set up of job priorities in this order, and has been used elsewhere in the solution as the value list used when selecting a jobs status. It is in this order because from a data-entry perspective, the user of this database has requested it from lowest to highest.

However, when jobs are displayed sorted by their priority, it would make sense to show the highest priority jobs at the top of the list. This will ensure that high priority jobs are not missed at the bottom of the list. Having them at the top makes them clearly visible to users. Lower priority jobs can appear further down the list given that they are not as important.

Here is the sort order defined for this example. Records are sorted by job priority, and the custom order based on value list has been used.

The jobs are sorted however from lowest priority to highest, which is not what we want, but is what we would expect given the order of the value list.

So, is there any way to reverse the order? The answer is yes, but unfortunately there is no nice option to reverse the order.

The only way to achieve the opposite order is to create a new value list of job priorities in reverse order, and reorder using that list instead. The Sort dialog does not allow you to order by value list in that value lists reversed order.

Here I have specified the order based on a new value list called Job Priorities Reversed

End result is jobs sorted by priority in order from highest priority to lowest priority.

Multiple Sort Order Fields using Custom Orders

You can extend this further to have a sort which uses more than one sort order, and have each use a custom order.

Consider the following example:

Here I have chosen to sort my jobs by month name, using the custom month order defined in the value list. The second order is job priorities, where I have chosen to sort using the custom value list of job priorities from highest to lowest, the end result is:

Looking at the months first, you can see they are sorted in chronological order from July thru December. Within each month, the jobs are then sorted by priority from highest to lowest. Looking at December as an example jobs are shown from Critical thru Urgent thru to Low.

Not Just for Sorting Records on a Layout:

The option to reorder based on a value list is available wherever records can be sorted, this extends to:

• Records on a Layout (as shown in this article)
• Sorting a Relationship
• Sorting a Portal

In these three situations, the reorder option behaves just like as described in the article.

Final Thoughts:

The option to reorder by value list is an often overlooked sort feature, but a very powerful one. It excels when the field(s) being sorted contain relatively few possible values, such as month names, statuses or priorities. The reason being that a custom value list must be used, where explicitly entered values are put directly into the value list. This method would not work quite as well if the field contained many hundred possible values - unless you decided to create a value list containing every possible value in its correct order!

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article.

Download Example File