"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below!



Beginner, Intermediate and Advanced Video Training


Recent Blogs:

Becoming Certified
Becoming Certified

Logically Speaking
Logically Speaking

Efficiency
Efficiency

Keeping the Faith
Keeping the Faith

Planning
Planning




$10.00 Beginner Video Training


Quick Tip:

IsEmpty
I have seen many people, even expert FileMaker developers, check for an empty field by comparing it to to double quotes (""). While this method may work some of the time, it can fail in some situations. Always use the IsEmpty function.



Tips & Tricks Videos


Fun Stuff:

The Wedge
Claris Corporation and the FileMaker, Inc. have been housed in a building called the wedge in Santa Clara, California since they spun off from Apple, Inc. The building is nicknamed the wedge because it looks like a wedge. For some years they had an adjacent building called the interstitial but it was let go when the company downsized to focus on FileMaker products.

The Wedge



RSS Feed
In Depth Index
Level: Intermediate
Category: Scripting
Tuesday, February 14, 2017
Indexes make finds fast! But, indexes also support a bevy of other features such as relationships, value lists and validation. This blog entry will explore the definition of an index from a FileMaker point of view as well how it is used throughout the application.

In Depth Index

The best way to define indexing is by way of analogy. Let’s say you're reading a technical handbook on FileMaker and want to find the sections mentioning global fields. You could flip through each page scanning for the word “global” or save yourself a ton of time and visit the index of the book. The index will list all the pages where "global" can be found. This is exactly how a word index functions in FileMaker. Instead of searching every single record, each unique word is added to the index with references to every record where it occurs. This makes searching incredibly fast since it can now search through a much smaller list of words.

You can quickly view the index of a field by clicking into it and choosing From Index... from the Insert menu. There are actually two types of indexes possible for a text field. The first is represented when the "show individual words" option is unchecked. It shows how the index looks for a text field when a find is performed.

In Depth Index

When the option is unchecked, the index is displayed as a relationship would view it. Relationships consider the entire entry for the match so word separators are ignored. The only exception is a return character but multi-keys are too advanced to cover at this point.

In Depth Index

The index for a date, number, time or timestamp field doesn't have the option for "show individual words" since these field types can only be searched as a complete value.

In Depth Index

To confuse matters even more, FileMaker defines indexing in Manage Database as Minimal and All. Minimal means only one index type has been created while All means two index types have been created. The two types of indexes are Word and Value. A Word index is used for finds on a text field while a Value index is used for searches on number, date, time and timestamp fields, relationships and validation. Only text fields can have both index types since only text fields can be searched by word (Word index) and be used in a relationship (Value index). Up to 100 characters of each word or value are indexed.

Words are defined most often by spaces but can also be determined by commas, periods, colons and other characters. It’s best to test the index by entering values into a field and viewing it, in order to gain a better understanding. For example, "john.osborne" is considered one word and is indexed as such. Add a space after the period and FileMaker now considers the period as the end of a sentence and indexes "john" and "osborne" separately without the period.

A Value index is used for searching number, time and date fields as well as relationship key field matching. Up to 100 characters of each return-separated value are indexed (returns are used to create multi-keys but won't be discussed here). While searching can be done on partial text in a Word index, only complete values can be searched for in a Value index. When you think about it, why would you want to search for a partial number or date. In addition, Value indexes are used for relationships to match parent and child on the entire value. While relationships are bidirectional, if you are only use the relationship from one point-of-view, only the match field from the ending point needs to be indexed. In other words, when adding a portal to a layout, only the key field on the portal side needs to be indexed.

In the screen shot below, FileMaker automatically turned on the index for the "notes" text field a find was performed due to the option to "automatically create indexes when needed". The Minimal option was utilized since only a Word index is required to search a text field.

In Depth Index

As soon as a feature requiring a Value index is setup for a field, like a relationship, the "All" option is automatically changed, as long as the option "automatically create indexes when needed" has not been unchecked.

In Depth Index

The next screen shot shows a date field. Notice the "Minimal" option is dimmed since number, date, time and timestamp fields can't have Word indexes. Searching and relationships are both based on the Value index.

In Depth Index

One thing you should notice about a word index is that it does not differentiate between uppercase and lowercase. Searching for “FRED” also finds “fred”, and vice versa, unless you change the language to Unicode. Unicode is commonly employed as an indexing type to enable finds on email addresses since the at sign (@) is a reserved word. As long as the email addresses are all in the same case, this works perfectly. If you are grabbing email addresses from the internet or emails sent to you, people often use uppercase cause it doesn’t matter to the internet. If you turn on Unicode indexing, you may be able to search on full email addresses but you’ll have to get the case correct. I would suggest using standard indexing al the time and simply train users how to search for email addresses and other tricky search criteria using find criteria surrounded by quotes (e.g. "jmo@filemakerpros.com").

Indexes are also utilized in a variety of other features such as value lists based on the contents of a field. This enables FileMaker to quickly build a list for a pop-up menu based on the contents of a field across thousands of records. The downside is that only unique values are placed in the value list simply because that is how an index functions. In most cases this doesn’t cause a problem but consider yourself warned. Indexes are also used in the validation option to determine uniqueness or existence but not many other places. A common misconception is that sorts use the index but this is simply not true.

The downside of indexing is speed and size. It takes time to create an index and space to store it. One index isn’t going to be noticeable but if you index every field, you may have speed issues or file bloat, especially if you index a notes field. It’s important to turn off the indexing for fields that will never be searched by unchecking the option to “automatically create indexes as needed”.

In Depth Index

In addition, prevent access to these fields during search by turning off the Inspector option to allow access in find mode. You can even hide fields with the Hide Object feature but I’m getting ahead of myself. The key here is turning off indexes once the development process is over. While you may search fields during the development process, turn off indexing as needed when the solution is deployed.

Author:
John Mark Osborne
jmo@filemakerpros.com
www.databasepros.com

This blog is completely free. Please support it by clicking on one of the advertisers at the left side of the window. Thanks so much!

Comments:

Sarah 04/10/2017
  Thank you for the helpful response - I'm glad you have the right connections with the FileMaker engine head developers!
Sarah 04/10/2017
  I found this blog posting via Twitter and am really glad I found it. The link just indicated it was about indexing and I was surprised & thankful to discover it was your blog. I just was made aware recently that I didn't understand how indexes work. Your explanation was very helpful.

Can you affirm that any existing indexes (created during development) are deleted when you change indexing to "None" in field definitions? (I went to test it with "Insert from Index" & because I had the option on to create it automatically, it seems to have been re-created again. If I had the option to create it automatically turned off, however, the option to "Insert from Index" was no longer available, but that doesn't mean the previously created index was deleted.)
Response by:   John Mark Osborne
Glad you enjoyed the article. I asked one of the head developers for the FileMaker engine and here is exactly what he said: "Yes, it will delete the index. Make sure to turn off “create index if needed” if you want to make sure it never comes back. Also, after a big delete like that there may still be quite of few partial pages laying around so if you really want to squeeze out all the free areas you should also use “Save a Copy As” with the “compacted copy (smaller)” option. One more note, FileMaker files never shrink in size until they are closed. So if they delete the index and don’t close the file when you look at it in the Explorer/Finder, it won’t change in size until the file is closed."
David 03/10/2017
  Wow. I'll be honest, although I've been developing in Filemaker for a long time, I had the suspicion I did not really grasp how indexing really works and 'To Index, Or Not to Index, That Is The Question...'.

This succinct, brilliant elucidation now fills that gap for me. Am looking forward to mining gold on JMO's blog!

Very many thanks.
Response by:   John Mark Osborne
I wasn't totally clear until I wrote the blog either. Cheers!

Add Comment:

First Name: *
Last Name:
Email: *
Web Site:
Comment: *
 Email Addresses will not be shared on the web site!