FM 13: Pseudo Indexing

If you’ve spent much time working with FileMaker Pro, you are probably familiar with the ability to view a field’s index.

To view a field index, put your cursor inside any indexed field and press Cmd-i (on the Mac) or Ctrl-i (on the PC) to see a list of unique (non-duplicated) entries for that field.

It’s a great feature, but it has some limitations:

1. Field indexes are not found set aware — you get unique entries for all records
2. The field must be on the layout (and enterable)
3. The field must be indexed
4. When viewing a field index, it’s possible to accidentally insert data into the underlying field
5. You can’t copy what you see — it’s view only
6. If you’re doing a screen share, and zoom the database up to a higher magnification, the index window does not correspondingly zoom

Well today we’re going to look at a technique to circumvent these limitations, and I invite you to follow along in today’s demo file, FM 13 Pseudo Indexing, if you are so inclined.

As per item #1 on the above list, one of the main benefits of this technique is being able to see unique entries within the current found set. For example in this found set we have 28 transactions but 17 unique payees.

To see how this is done, let’s take a look behind the scenes. Here’s our transactions table…

…with a summary list field defined like so:

At run time, the summary list field populates a global text field (zz_g_mlk — the “mlk” stands for multiline key), which is related to the primary key (ID) of a second occurrence of the transactions table (see last month’s Global Multiline Key As Relational Predicate article for more on this).

Next we are going to define a separate value list for each pseudo index…

…and note that these are field-based value lists, defined like so:

Our next step is attach each icon button to the “pseudo index” script with the corresponding value list name as a parameter.

When the button is clicked, this script is invoked:

Line 2 of the script pushes the summary list into the global field, line 3 immediately overwrites the contents of the global with the values that will be displayed, and the remainder of the script is dedicated to displaying the Pseudo Index window nicely sized and centered with relation to the background window.

And finally, here’s the star of the show, the Pseudo Index layout.

Okay, we’ve seen that the technique is found-set aware… what about item #3 on the list of View Index limitations (“the field must be indexed”)? First let’s demonstrate the problem. If you go to the Chart of Accounts, you’ll see that you can view the index on the first two fields, but not on the third one.

And when we try to define the value list for the pseudo index, sure enough, we get a message saying it won’t work.

But it turns out it will work as long as the “primary” side of the relationship is global or unstored.

(To learn more about this phenomenon, read this article: Magic Value Lists.)

With regards to items 5 and 6 on the View Index limitations list…

5. You can’t copy what you see — it’s view only
6. If you’re doing a screen share, and zoom the database up to a higher magnification, the index window does not correspondingly zoom

…as per this screen shot, pseudo indexing does not suffer from these limitations.

A final thought: as I was about to hit the publish button for this article, it occurred to me that it would be nice to see the count of unique entries in the title bar of the pseudo index window, so I made a couple changes to the pseudo index script.

There… that’s more like it.