Semi-Sorted A database can either be sorted, unsorted or semi-sorted. The first two are pretty obvious, but it is less clear how a database becomes semi-sorted. A database becomes semi-sorted when a new record is added to a currently sorted database and the option to keep records sorted is unchecked. It can also become semi-sorted if a record is edited -- but only if one of the fields used in the sort is edited. Deleting a record will not make a database semi-sorted.
Level: Intermediate Category: Scripting Tuesday, May 23, 2017
In this article, multiple scripts will be created to accomplish the same task. This may seem silly at first but, it's an important exercise to learn the best approach to a problem. If you are new to this concept, you should actually build the different scripts to cement the concept in your mind. I've been using this methodology for a very long time so it just comes naturally. In fact, most of the work occurs in my head. In time, the process will become second nature and you will find yourself building more efficient and capable scripts.
The example in this article is very simple. Write a script to locate records matching the current record, based on a single field match field. That match field is a first name field. In other words, if the record being viewed has "Joe" in the first name field then the script should locate all other records containing "Joe". The specific solution is not as important as the methodology you will learn to apply to your own unique FileMaker problems. Therefore, the goal of the solution has purposely been kept very simple to emphasize the thought process.
Whenever I present this solution in a training class, someone always wonders why. Sometimes students ask why I don't use a portal or the Find Matching Records feature? Sometimes they ask why I don't teach a different lesson. Well, I can't teach everything I know about scripting in a single article or even a year of instruction. However, I can teach you how to think on your own. Early on in my teaching career, I discovered that each student wants different information. I toyed with the idea of offering a class where only questions were asked, but finally opted to teach students how to solve their own problems.
"Give a man a fish and you feed him for a day; teach a man to fish and you feed him for a lifetime." - Maimonides
When you are working through this exercise, focus on the techniques for creating a solid script rather than the actual solutions presented. Concentrate on comparing the advantages and disadvantages of several approaches to a problem. It's not that certain aspects of the scripts presented can't be copied to your own solution. In fact, many of the techniques presented are key foundational concepts that can be applied to many solutions. It's just that the focus of this article is comparing and contrasting methods in order to determine the best solution for the given programming scenario.
I'll go out on a limb and say this is my favorite training exercise because it makes you think outside the box. It makes you consider conceptually rather than about a specific goal. Other articles on this blog will recall this exercise and ask you to compare and contrast several approaches to more complex scripting problems. If you start looking at the advantages and disadvantages of one scripting solution versus another at the beginning of your career, you'll reach a higher level of expertise much sooner. In fact, this technique of weighing the pros and cons applies to calculations, relationships and any other feature you might utilize in FileMaker.
A scripted solution is not necessarily the best solution to the problem of locating similar records. A portal would probably be better to show a list of related records. However, this is an article about scripting so only scripting techniques will be utilized. In addition, scripting is the most open to multiple approaches to the same problem. The most important lesson to learn from these exercises is to solve a problem with several approaches, assess the advantages and disadvantages of each and pick the right one for the job at hand. It is very likely one solution will work better in one situation while another solution works better in a different scenario.
The Simple Approach The easiest approach to scripting a find, for all customers with the same first name, is copy and paste. The clipboard bridges the gap between Browse and Find mode. There is no need to to capture errors for no records found since the script will always find at least one record. In addition, adding the Allow User Abort step just takes focus away from the purpose of the script, which is to analyze just the core steps of a script.
The Copy step destroys the contents of the clipboard, making it a poor choice. In addition, it also requires the target field to be on the layout for both the Cut, Copy and Paste steps to function properly. Clearly this is not a good choice for a solution but was necessary to examine. In fact, Copy and Cut should never be used in a script unless the button says "Copy" or "Cut" on the button label. Knowing the wrong way to program is almost as important as knowing the right way.
Set Field Writing the same script using Set Field, instead of Copy and Paste, solves the problems with the clipboard and the target field. Set Field doesn’t use the clipboard and doesn't require the target field to be on the current layout. Think of the global field as a clipboard which can be accessed in both Browse and Find mode. Divorcing a script from a specific layout allows them to be edited independently without concern for dependencies that might be broken.
The only concern with this approach is the requirement of a global field (“xfind”) to bridge the gap between Browse and Find mode. However, Global fields clutter up Manage Database and should be avoided whenever possible. It's certainly possible to reuse global fields across multiple scripts as long, as a value does not need to be accessed beyond the life of the script. You can even store your global fields in a separate table, in a way uncluttering Manage Database, since no relationship is required to access the data from a global in a script. Still, I try to limit the amount of schema I create in a FileMaker database, whenever I can.
Last Visited The insert from last visited record feature allows FileMaker to grab data from the previously selected record. It is often used instead of the Duplicate Record menu item when only a small portion of the data from a record needs to be replicated.
If you're unfamiliar with this feature, manually perform the following series of steps:
Locate a record that contains a value you wish to duplicate.
Make sure your cursor is blinking in a field (it doesn't matter which field). This activates the last visited record.
Create a new record (or, move to another existing record).
Go to any field and type the keyboard equivalent for Insert from Last Visited Record. On the Macintosh, hold down the Command key and type the apostrophe key. Under the Windows operating system, use the CTRL and apostrophe keys. Or, use the menu item under the Insert menu.
The field that you are in will now contain the same data that was in the previously selected record.
Repeat for as many fields as desired. Add the Shift key to the keyboard equivalent to insert and tab to the next field.
What may not be totally clear is the importance of first selecting a field on the previous record. If you visit a record but don't click into a field, FileMaker doesn't consider that the last record visited. This explains the importance of the Go to Next Field step before entering Find mode, in the script below. Rather than hard coding a specific field using the Go to Field step, Go to Next Field creates a more adaptive script that doesn’t break if the specified field is removed from the layout.
This solution suffers from some of the same problems as the copy and paste approach. The Insert from Last Visited script step requires the field you want to insert to be on the current layout. If you don't want to show this field on the active layout, you'll need to create a special find layout for this script or attempt to hide the field somewhere on the layout. Neither of which is a satisfactory workaround in my opinion.
On the other hand, this technique has a distinct advantage over the previous two solutions. If the goal is changed to include multiple find fields, the insert from last visited record feature starts to shine. Just add as many additional Insert from Last Visited steps as find fields. With the copy and paste method, the script will need to bop back and forth between Browse mode and Find mode using the Modify Last Find step. Set Field will require additional global fields for each find field.
GTRR The Go to Related Record approach requires the least number of script steps but clutters up the relationship graph. If the relationship already exists, no harm, no foul. This is also the fastest script of all based solely on the number of steps required to execute. In reality, relationships are one of the slowest features in FileMaker and should be avoided whenever possible. With just a few thousand related records, speed issues will be negligible. Only when tens of thousands of related records need to be sifted through will translating a relationship to a found set begin to be slower than a find.
Script Parameter Let's take advantage of Script Parameters to program this solution one more way. Script Parameters allow you to pass a variable from one script to another script or from a button to a script, using the Perform Script step. Script parameters are specified via a calculation so you can reference any fields, relationships or functions you desire. Our example simply references the “name_first” field.
When the script is executed, the script parameter attached to the button passes the contents of the “name_first” field to the script. The script enters find mode and places the result of the Set Field formula into the field. The Set Field formula references the Get(ScriptParameter) function which grabs the parameter passed to the script. Script Parameters are kept in memory for the calling script only, effectively moving the find value from Browse mode to Find mode without requiring any structure other than the script. The only downside is a button, script trigger or other script is required since, at some point, the parameter has to be passed to the script.
Set Variable A solution using the Set Variable script step can produce the same results as the Set Field approach but without a global field. Variables are very similar to Global fields but are stored in memory rather than a field, uncluttering Manage Database to my heart’s delight.
Variables come in two flavors: local and global. Local variables are declared using a single dollar sign and global variables with two dollar signs. Otherwise, you can name your variables whatever you desire. Whenever possible, declare variables as local rather than global. Global variables persist beyond the scope of the script where they are declared and end up cluttering the Data Viewer. When the Data Viewer is cluttered, it becomes difficult to effectively utilize the Data Viewer in conjunction with the Script Debugger. Local variables clean themselves up at the end of a script by erasing any stored values and erasing the variable name from memory. Most variables can be declared and utilized within a single script so there are very few reasons to declare a global variable.
Find Matching Set Variable is probably the best all around approach to solving this problem but as you may know, there is a feature specifically designed for this task. The Find Matching Records step finds all records matching the contents of a specified field from the current record, with one simple script step. It even has options for constraining and extended the found set. However, it is inflexible, only accomplishing a single task. While it may do that single task well, it will never perform any other function outside of it’s design.
Conclusion So, why run through all these examples when the solution is so simple? When considered in this manner, it would be stupid not to use the Find Matching Records feature. However, not all problems are the same even when they appear to be the same. A disadvantage for one technique may prove to be an advantage in a particular scenario. It all depends on a variety of factors. Keep each one of the techniques in mind, except for maybe the copy and paste approach, whenever you are programming a simple or complex solution.
When you first start utilizing the comparison technique with FileMaker, create a matrix listing the plusses and minuses of each approach to help you decide the best choice. As you get better, you will be able to visualize the comparison chart in your head, eventually making the best choice in seconds or minutes. In other words, practice makes perfect. Work this methodology into your programming and it will eventually become second nature.