Sorting Records

Your Rolodex may be limited to an alphabetical (by last name) arrangement, but a FileMaker database has no such limitation. You can sort the records in any order you want, as often as you want. You can even do a sort-within-a-sort, as you'll see later in this section.

The process always begins the same way: Choose Records Sort Records. Youll see the Sort Records dialog box shown in Figure 2-7.

The Sort Records window lists all fields in your current layout on the left. Use the pop-up menu at the top of the list to switch to a list of all the fields in the table that are tied to the current layout. See Section 7.3.5.7 for details on tables and layouts. You tell FileMaker what to sort by moving the field to the list on the right. For example, if you're in need of a short-term loan, you may find it useful to sort your contacts by annual income, so you'd start by clicking the annual income field.

Figure 2-7. The Sort Records dialog box offers a lot of options, but the two lists on top, and the first two radio buttons, are critical to every sort you'll ever do in FileMaker. In a nutshell, you pick the fields you want to sort by and the order they should be sorted. Then click Sort. That's the essence of any sort, from the simple to the most complex.

The steps below, though, show a more common examplesorting by Last Name:

1. Choose Records Sort or press Ctrl+S (-S).

   The Sort Records dialog box (Figure 2-7) appears.

2. Select the Last Name field from the list on the left, and then click Move.

   The field name appears in the Sort Order list on the right. You can save a little mouse-mileage by double-clicking a field on the left instead of selecting it, then clicking Move.

3. Click Sort.

   FileMaker sorts the records in the traditional alphabetical-by-last-name order. You can tell by flipping through the records or switching to Table view.

Each sort field has an order associated with it as well. Right now, you only need to worry about two of these orders: "Ascending order" and "Descending order." You can click one of these radio buttons before you click Move and the field will have the setting by the time it makes it to the Sort Order list. More often than not, though, you'll move the field over before you think about the order. In this case, just click select the field from the Sort Order list and then pick the order. Each field in the Sort Order list shows a little bar-chart icon representing the order assigned to it, which matches the icons next to each radio button.

---

Tip: If you change your mind about one of the fields in the Sort Order list, click it. The Move button changes to say Clear, and a click removes the selected field from the list. If you want to remove every field from the Sort Order list, click Clear All.

---

When you're all done, click Sort. If you change your mind, click Cancel instead and FileMaker will forget everything you've done while in this window. Otherwise, the next time you choose the Sort Records command, the dialog box will show the same settings you used most recently.

The status area lets you know if your records are sorted (no surprise there). Below the Record Count, it says "Sorted" if you've done a sort, and "Unsorted" otherwise. (See the box on Section 2.5 to learn what "Semi-sorted" means.)

2.4.1. Multiple Sort Fields

FileMaker lets you pick more than one field to sort by, which comes in handy when you have lots of records with the same data in some fields. For example, if your People database gets really big, you might have several people with the same last name. If you just sort by last name, there's no telling in which order the like-named people will fall. In this case, it would help to sort by last name and first name.

When you use more than one sort field, the order the fields appear in the Sort Order list is important, because it determines which field FileMaker uses first. If you sort a list of family members by First Name, then Last Name, you might wind up records ordered like those shown at left in Figure 2-8.

If you sort by Last Name, then First Name, you'll get something like the window shown at right instead. In the first example, FileMaker sorts everything by First Name. When two people have the same first name, it then uses Last Name to figure out what order those two should be in. In the second example, the records are first sorted by Last Name instead. First Name is then used to figure out how to sort the folks with the same last name.

Figure 2-8. Both windows show the same list of people, but are sorted differently. The one on the left was sorted by first name, then last name. Notice how all the Carolines are together. The data in the window on the right was sorted by last name, then first name. This time, the Fawcetts stick together.

Since the order of the sort fields is so important, FileMaker provides a convenient way to shuffle them around in the Sort Records dialog box after you've added them to the Sort Order list. Figure 2-9 sheds some light on how it works.

Figure 2-9. Each field in the Sort Order list has a little double-headed arrow before its name (top). To change the order in which they're listed, first point to one of the arrows. Notice that your cursor changes shape (middle). FileMaker is letting you know that you can drag the item up or down in the list. Drag the item to its new position (bottom). If you prefer the keyboard, use the arrow keys to highlight the field you want to move. Then press the Ctrl () key along with the arrow keys to move the selected field. When you're done, and you click Sort, FileMaker will sort your records using each field in order, starting with the top field in the list.

If you do a multi-field sort and then discover you didn't get quite what you expected (because you had the fields in the wrong order), just choose Records images/U2192.jpg border=0> Sort Records again, move the fields in the Sort Order list to the right places, and then click Sort.

FREQUENTLY ASKED QUESTION
Sort of Sorted

When I haven't sorted my records, the status area says "Unsorted." What order is that, exactly? Is it completely random? Also, sometimes it says "Semi-Sorted." What in the world does that mean?

Remain calm. When the records are unsorted, they're in creation order. The first record you ever created (and haven't deleted yet) shows first, followed by the next one you created, and so on. Creation order is FileMaker's natural order for the records. Once you sort the records one way, they stay that way until you sort again or explicitly unsort them. (There's a button to do just that in the Sort dialog box, although why you'd want to use it is up to you.)

Now then: If the records are sorted and you add a new record, FileMaker doesn't put that record in its properly sorted place. Instead, it always goes to the end. So if you add Adam Aaronson to your sorted address list, he winds up after Zorba Zuckerman and the status area then reads "Semi-Sorted." For the most part, the data is sorted, but one or more records are out of place. Even if you then delete Adam Aaronson, it'll still say Semi-Sorted. FileMaker isn't smart enough to realize the data is back in sorted order until you tell it to sort again.