Sorting Data - File Maker

Sometimes, you’ll want to arrange your records in a particular order (“Sort all records by invoice date, with the oldest first,” or “Let me see all contacts in the found set in alpha order, sorted first by last name, then by first name, with the As first.” FileMaker’s handy built-in sort feature will help you do just that.

Go into Company.fp5 to the List View layout and select Show All Records from the Records menu (if the current found set isn’t already all records). Now right-click /CONTROL-click the CompanyName field of the first record (on the contents of the first record’s field, for example, “ACME Corp.”) and select Sort Ascending from the contextual menu that comes up. Voila! It’s that simple. The records are now sorted A to Z on CompanyName.

From the same menu, you can sort the records in descending order, too, and by value list. The last one will sort the values in the field you’re sorting on based on the order in which values are listed in a predefined value list. For instance, you could use this if your fiscal year starts in July and whenever you sort by the MonthName field you want the sort to have July first and June last. All you have to do is set up a value list that has the months listed in this order and then sort by value list in ascending order from the contextual menu.

The Sort Records Dialog
The above method of sorting is the “simple” way to sort records, but there’s also a more complex way, which allows you to sort on many fields at once. To try this out, go into browse mode in Company.fp5 and select Sort from the Records menu. You’ll see the dialog below. On the left, you can select the fields you want to sort on.

The Sort Records Dialog

Creating a Sort Order
Try this dialog out.

  1. First, select the State field by single-clicking it. Next, select Ascending Order (A to Z, 1 to 100, and so on) using the radio buttons below the left box. Now click the Move button. The beginning of your new sort rder appears in the right-hand box.
  2. Now select the City field at left, select Ascending Order again, and move it also over to the sort order. (Note that you can drag fields up and down in the sort order using the little arrows and that you can change the sort “direction” of a field in the sort order by highlighting it and changing its direction).
  3. The Clear All button clears the selected sort order so you can start over, the Sort button runs the sort on the found set, and the Unsort button unsorts the found set, if it’s already sorted somehow. Click the Sort button. Now the records are sorted A to Z, first by state, then by city. That’s sorting!

Sorting by Subsummary Fields
Say you want to sort some records based on values that are calculated in a summary field. For instance, you might have a summary field that calculates how many orders each contact has placed with your company. Instead of doing a summary report that summarizes the data by contact name, you might want to sort it so that you could see who had placed the most orders. To do so, you would need to sort and summarize on the summary field.

But because FileMaker’s built-in help system covers this so nicely, I’ll refer you to that instead of using up more trees here. Search for “Sorting records by subsummary values.”

Common Sorting Problems
Here are some common sorting problems and how to fix them:

  • Sorting a bunch of numbers gives this: 1, 10, 2, 20, 3, 4, 5, 6 . . . — probably because the numbers are in a text field, where 10 does come after 1, alphanumerically.
  • To solve this problem, switch the field’s definition to a number field or somehow add leading zeros to the numbers so that they’re 01, 02, 03, and so on. Then they’ll sort correctly.

  • If dates in date fields sort oddly, make sure you’re having users enter fourdigit years (or validate for four-digit years in the field’s definition).
  • Remember that repeating fields sort by the first repetition’s value. The rest are ignored.
  • If you create new records after you sort a found set, the found set will show up as “semi-sorted” on the status area. You’ll have to rerun the sort to sort these new records into your sort order properly.
  • Sometimes when you sort a bunch of records on a list view, it seems like the first several records are blank. This is probably because these are empty records, entered in error by users (because null values sort to the top when sorting in ascending order). Tighten down data entry to avoid this and, in the meantime, delete the blank records.
  • If any particular record sorts in an unexpected way in the found set, check the sort field in that record for any stray characters, punctuation, hidden text, or carriage returns that can affect sorts. Also check the language used in the Sort dialog.

All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd Protection Status

File Maker Topics