Indexing FileMaker Pro indexes every word in a text field so that finds can be performed quickly. Think of the analogy of a book. Rather than trying to locate something by thumbing through each page, you look in the index and find all the pages numbers at once. FileMaker works in the same way. In order to make finds as fast as possible, the first 100 characters of every word is indexed. If you click into a field and type Command-I (Macintosh) or Ctrl-I (Windows), you can view the index of a field. Most characters are indexed but you may be surprised what characters designate a new word other than a space. The index does take up space, so if a field is not going to be searched on or used as a match field in a relationship, set the Storage Options in Manage Database to never index that field.
Level: Intermediate Category: Scripting Tuesday, January 30, 2018
Imagining a database without found sets is like picturing a bird without feathers. It just doesn't make sense. Found sets are the essence of a database. If there were no found sets, you would need to sort your entire list of records and scroll to find a record. To say the least, this would be an inefficient process... especially if you have more than a thousand records. Found sets are what make a database a database. Not only do finds allow you to locate a specific record but they also enable you to perform actions on a subset of records like importing, exporting, printing, sorting and looping, to name just a few. With such a far-reaching effect on the processes in FileMaker, found sets often need to be preserved and/or reloaded. "Save" is a good word to describe what I'm going to talk about in this article but there are all kinds of saves. Some saves are temporary and other are more permanent. Being able to work with different save techniques is crucial to applying the best method to the task at hand. In this first of a series of articles, we'll start with temporary saves.
SIDE TIP: The one big exception to found sets are relationships. Relationships disregard found sets and for good reason. Imagine trying to show a portal of related records but not all of them show cause there is a found set in the related table.
Why do I need to Preserve a Found Set You might be scratching your head, wondering why would you need to preserve a found set. Sure, it makes sense why you need to create a found set but why would you need to save it? The most common reason is scripting. During an automated process, scripts often perform finds thus destroying the preexisting found set. For example, let's say a user of your solution created a found set using multiple requests and manual omits. Then, the user decides to click on a button you scripted and during the process of the script operating, it performs a find. If you perform the find in the current window, the user's hard work is wiped out. That user is probably going to pretty mad or at the very least, flabbergasted.
A specific example might help you understand better. Let's say you need to preview the current record in a script you are writing? When entering preview mode, FileMaker shows all the records in the found set, starting with the first record in the found set. If the user is on the tenth record, they will likely have to navigate a few pages into the preview to find the record they want to view. This is not a satisfactory result and often makes preview unusable.
Fortunately, there's a nifty little workaround that isolates a record using three script steps in the order shown below:
Show All Records Omit Record Show Omitted Only
This simple series of steps creates a found set of one, containing the current record. It works because the Show All Records step stays on the current record after displaying all records. This allows the Omit Record step to remove the current record, leaving a found set of everything but the current record. The Show Omitted Only step then reverses the found set, leaving a found set of just the current record. Follow these three steps with an Enter Preview Mode step and you are now previewing the current record on the first page. The only problem is, you've broken the users found set and likely disgruntled them.
New Window Preservation All you have to do to fix the problem, described above, is create a new window before isolating the record. When the user is done with preview mode, let the user close the window manually and the user still has the same found set. This technique can also be used if you want to export just a single record. In this case, you would automatically close the window with the script so the user never sees it. There are many examples that could covered here but these should be sufficient to help you grasp the issue at hand.
SIDE TIP: In high volume multi-user solutions where many users are adding many records, it's possible for the isolation technique to fail. That's because new records are added to the omitted set. If user A adds a record at the point where user B omits a record with a script, it's possible the show omitted step could display a second or even a third record in the found set. This conflict is an unlikely scenario in most solutions since records are added at a more reasonable rate. A screen shot of this solution is shown below but will not be discussed. Refer to the comments in the script for more information on how this issue can be overcome. Credit for this script goes to Soliant Consulting.
In my development practice, I preserve the current found set using the new window technique in just about every solution I design. Since each solution I design is unique to my client's needs, the reason for preserving the found set is unique as well. Therefore, it would be difficult to describe every scenario in a paragraph or even two. Hopefully, though, the light bulb is going off and you are realizing all the places where you could save a found set temporarily with a new window in order to save a user some grief.
Additional Possibilities Not only does creating a new window save a found set, it also preserves the current record, selected tab in a Tab Control object, active portal row and selected field. Rather than switching layouts in the same window, use the new window trick to keep the current layout context and replace complicated scripts aimed at restoring these objects. Just open a new window, switch layouts, script what you need to do on the other layout and then close the window. Everything will be the same as the user left it. There's a little bit of flash that occurs but it can be minimized by spawning your new window off screen.
New Window [Style: Document; Top: -10000; Left: -10000]
Spawning the window off-screen limits the flashing to the title bar and toolbar so it's best to hide the toolbar for the least amount of flash. Fast "New Window" operations may not flash the screen at all so first test your solution without spawning the new window off screen. The screen flash is also more noticeable on versions of FileMaker earlier than 16. In fact, it seems to have disappeared in almost every scenario in FileMaker 16. But, no matter what you do, screen flash is noticeable on the Windows operating systems because of the way the screen is redrawn.
Table Occurrence Preservation Another method for preserving a found set uses Table Occurrences, Layouts and Go to Related Record. This method is more complicated to program and requires more infrastructure than spawning a new window but has the advantage of working within the same window. There are just times when you can't create a new window such as when a find is being performed. Rather than repeating myself, I cover this technique in an article called Ultimate Find. You should read the entire article but the specific technique is show towards the end of the article in the section titled "Restoring the Found Set".
Additional Methods In the coming weeks, I will cover two other methods for saving a found set which are more permanent. The techniques covered in this article only save the found set for the duration of the window existence. What I want to cover next are methods for saving found sets for future retrieval for a permanent solution. Both temporary and permanent found set saving are important tools in building a professional solution.