"No Holding Back FileMaker Blogging"
|
Navigation:
|
Support this site by clicking on a sponsor below or becoming a patron!
FileMaker 20 Video Tutorials
Become a patron of this FREE web site!
|
|
Recent Blogs:
|
Currency Formatting
|
Gathering Portals
|
Multiple Choice Picker
|
Popups and Pickers
|
Window Locking
|
|
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 FileMaker Calendar
|
Quick Tip:
|
Open Script Disabled If you have a multiple file solution, it is possible for open scripts, specified via File Options, to not run on opening of the file. This can happen when a file is opened hidden by a related field or via a Perform Script step. Only when the hidden file is selected from the Window menu and displayed will the open script perform. The reasoning behind this behavior is efficiency. If you have to wait for an open script to run just to see data in a related field, you will have unhappy users. Also, imagine if the open script shows a dialog and interrupts what the user is looking at on the original file. The only way to guarantee an open script runs is to use the Open script step or launch the file manually. |
|
Tips & Tricks Videos
|
Fun Stuff:
|
DevCon History Here's a list of the FileMaker Developer Conferences* through 2020:
# | | Year | | Location |
23 | | 2020 | | Nashville, Tennessee** |
23 | | 2019 | | Orlando, Florida |
23 | | 2018 | | Grapevine, Texas |
22 | | 2017 | | Phoenix, Arizona |
21 | | 2016 | | Las Vegas, Nevada |
20 | | 2015 | | Las Vegas, Nevada |
19 | | 2014 | | San Antonio, Texas |
18 | | 2013 | | San Diego, California |
17 | | 2012 | | Miami Beach, Florida |
16 | | 2011 | | San Diego, California |
15 | | 2010 | | San Diego, California |
14 | | 2009 | | San Francisco, California |
13 | | 2008 | | Phoenix, Arizona |
12 | | 2007 | | Orlando, Florida |
11 | | 2006 | | Orlando, Florida |
10 | | 2005 | | Phoenix, Arizona |
9 | | 2004 | | Phoenix, Arizona |
8 | | 2003 | | Phoenix, Arizona |
7 | | 2002 | | Palm Desert, California |
6 | | 2001 | | Orlando, Florida |
5 | | 2000 | | Palm Desert, California |
4 | | 1999 | | San Diego, California |
3 | | 1998 | | Monterey, California |
2 | | 1997 | | San Jose, California |
1 | | 1996 | | Santa Clara, California |
* Name changed to Claris Engage in 2020 to match renaming of FileMaker, Inc. to Claris.
**Due to the COVID-19 virus, this conference was changed to a virtual conference. |
|
Document Management Videos
|
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.
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.
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.
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.
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.
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.
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.
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 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!
|
|
|
|
|