"No Holding Back FileMaker Blogging"


Navigation:


Support this site by clicking on a sponsor below or becoming a patron!



Document Management Videos



Become a patron of this FREE web site!


Recent Blogs:

Window Locking
Window Locking

Everything Changes
Everything Changes

Subsummary Unique Count
Subsummary Unique Count

Scripted Change Log
Scripted Change Log

Abstracted Log
Abstracted Log


Meta-Consulting
Meta-consulting is a service Database Pros offers to help other developers over programming hurdles via GoToMeeting. Email John Mark Osborne or call John Mark at (909) 393-4664 to find out more about this service.


Create a Complete Contact Manager


Quick Tip:

Global Naming
Most FileMaker developers begin the name their global fields with a "g" (i.e. gMyField). This differentiates global fields, at a glance, from regular fields. In addition, it groups global fields together, so they can then be more easily located in Manage Database and in other field listing dialogs. Unfortunately, this grouping places the global fields in the middle of an alphabetical listing. A better naming convention is to begin global field names with an "x", "z" or "zz" (my preference is "x"). This will group your global fields at the end of an alphabetical listing and differentiate them better from regular fields in a long list of fields (i.e. xMyField).



FileMaker 20 Video Tutorials


Fun Stuff:

Claris and FileMaker CEOs
Bill Campbell  1987 to 1995
Guerrino De Luca  1995 to 1997
Dominique Goupil  1997 to 2019
Brad Freitag  2019 to Present



Beginner, Intermediate and Advanced Video Training







RSS Feed
In Depth Index
Level: Intermediate
Version: FileMaker 15
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 or becoming a patron. Thanks so much!

Comments:

Pavel 04/05/2021
  Thank you for this post! Very informative!

Any information on how indexes are stored? Word indexes and value indexes are stored in one record if a word matches a value? Or, for the word index, a separate entry is created even if the word with the value matches? And is there any way to prevent word indexes from being created if I'm going to only use the field for relationships?
Response by:   John Mark Osborne 04/06/2021
The only access to how indexes are stored is shown in the Insert>From Index... feature. Indexes are not stored on each record. They are stored in a centralized location so they can be accessed quickly. Think of an index in a book. Only one entry for every keyword is stored with references to the page numbers. An index in FileMaker works the same way in that each word is stored once with a reference to what records you can find it on. You can prevent indexes by turning them off in Manage Database>Options. In fact, I'd recommend turning off the index for any field that won't be searched in order to improve the speed of a database. In other words, if the field is not being used in a search or a relationship, there's no need to slow down the database indexing unnecessary fields.
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 04/10/2017
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 03/10/2017
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!