Log in
Hot & Trending on FMT

FileMaker 13 Pseudo Indexing

Featured FileMaker 13 Pseudo Indexing

If you spend much time using FileMaker Pro (and if you’re reading this you probably do) 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, 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 just for 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 of the script 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…

  1. You can’t copy what you see — it’s view only
  2. 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, with pseudo indexing they are not a problem:

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.

Kevin Frank

FMT Staff Note: IF you would like any of Kevin's tip or techniques incorprated into a solution or project contact him at his website below.

Hello and welcome. I’m Kevin Frank, and I’ve been using FileMaker Pro since the late ’80s… professionally since 1995. The expression “hack” has both positive and negative connotations. Here it is defined as “a tip, trick or technique that helps you solve a problem,” and the best hacks are the ones that can be re-used and modified to meet a variety of challenges. Or, put another way, as I read recently in Street Fighting Mathematics, “A tool is a trick I use twice.”

Kevin Frank and Associates provides custom FileMaker database solutions for business, government, education and non-profit clients. With over 20 years experience, we are certified FileMaker Developers with a solid reputation for high quality results. We invite you to call us today for a no-charge, no-obligation phone consultation.

To find many more in depth articles from Kevin and some must have tools and services visit his site at the link below:

Website: www.filemakerhacks.com