Cascading Delete Self-join or Same-Table relationships are very useful (two table occurrences from the source table related to each other), but be careful! Don't ever turn on the option to allow deletion of related records! If you do, whenever you delete a parent record you may delete more than you want. What happens is that since your related records are also your parent records, deleting records can cascade out of control. I have seen the deletion of a single record cause the deletion of all records in all tables.
Jaymo David Knight (aka Speedy) started calling me JoMo years ago, because of my initials, and it kinda stuck. Over the years, it's evolved to the current Jaymo but you can call me anything as long as you are friendly. Other nicknames include Hoss, n2 and Wiz.
Found sets are what make a database a database. Without found sets, a database wouldn't be much more than a relational spreadsheet. Found sets determine which records print, sort, export and preview. Practically all features in FileMaker are structured by the found set. The found set, along with the current record and the sort order, determine the look and feel of a database for a user. Make sure you treat it with respect!
Sometimes you have to break the found set, current record or sort order, unbeknownst to the user. Maybe the user is running a script that does something very complex that requires destroying one of these elements. The user doesn't care how difficult it is to program a feature. The user just wants a smooth experience. If you have to change the found set, current record or sort order, you had better restore it. The only person who should modify the records showing is the user himself.
As covered in the first article in this series, Preserving Found Sets, temporarily saving a found set can be easily overcome with the New Window feature. There is also a table occurrence trick that can be employed for other temporary scenarios as shown in the Ultimate Find article. However, preserving a found set for future retrieval requires substantial scripting using Multi-Keys, the Go to Related Record step and Serial Numbers. In this article, we will cover the original method for saving found sets and then explore other methods in future writings.
The History of Saving Sets The grandfather of found set saving is Darren Terry. I know cause I worked with Darren in the Claris Technical Support department when he published an article on the subject in TechInfo (now referred to as Knowledge Base on the filemaker.com web site). So, I think it's appropriate to start off with the original method he published. It still has merit to this day but does have a few drawbacks, which will be noted in the followup article.
Copy All Records The Copy Record and Copy All Records features were introduced to facilitate movement of data between FileMaker and a spreadsheet, like Excel. Copying and pasting is a lot easier than exporting and importing. All you have to do is commit the current record before you choose Copy from the Edit menu and it copies all field data from the current record on the current layout to the clipboard. The data is stored in tab-separated format which can be simply pasted into an Excel spreadsheet. Each field will go into a separate column. Very convenient for transferring data to be charted, before FileMaker had a charting feature of course.
SIDE TIP: You can manually commit a record by typing the Enter key on the keypad, clicking on a record where there are no objects, changing records, changing layouts, closing the window and quitting FileMaker.
If you hold down the Option (Mac) or Shift (Win) key while copying, all the records in the found set are copied. True to tab-separated format, each record is distinguished with a carriage return. In order to implement Darren's original saving feature, you need to combine this feature with a layout containing just one field. This single field should be an auto-enter serial number. Since there is only one field on the layout, you get a return-separated list of values that uniquely identify every record in the found set.
Go to Layout ["Serial Number"] Copy All Records/Requests Go to Layout [original layout] Paste [Select; MyTable::xglobal]
Let's start out simple and only preserve one found set at a time. We'll add the ability to save and restore unlimited found sets. But, let's not muddy the waters this early on and just focus on the core functionality of this technique. Therefore, a global field will be sufficient for storing the values. Since the unique value are in the clipboard, simply paste the contents of the clipboard into a global text field.
SIDE TIP: If you don't want your users to see the global field, place it to the right of the layout width indicator. That's the gray line at the right of layout mode that separates the workable area from the gray portion that doesn't display in browse mode. Even though your users won't see the global field, you'll still be able to paste into it with a script.
Combining Features Grabbing a set of values that identify records is just the first part of this old school technique. The next step is to restore the found set of records identified by these unique values. This can be done by combining a relationship and the Go to Related Record script step (GTRR). Start by creating a relationship from the global field to the serial number field. In this simple case, there is only one table for a basic contact manager so the relationship is a self-join relationship, connecting to itself. We'll find out later how to connect to a table of representing multiple found sets. For now, we don't need anything more complicated cause we are only saving a single found set at a time.
It's very important that the global field is the primary key. In other words, the table occurrence from the layout you are using to display records should also be the table occurrence connecting the global to the serial number field. I've seen many students in my classes reverse the serial number and global field. What they don't realize is global fields create one way relationships. You can see the stop sign indicator on the relationship line indicating related values can only travel one direction. Since the Go to Related Record script step will be initiated from a layout showing records from the global side of the relationship, the global field must be configured properly on the correct side of the relationship.
Once you have the relationship established, a simple Go to Related Record script will restore the found set stored in the global field.
This works because of a feature called a multi-key. Typically, one value is stored in a primary or foreign key. If multiple values separated by a return are entered, FileMaker interprets each value separately. Since a FileMaker text field can store up to 10 million characters, an almost limitless number of serial numbers can be stored.
It's also important to note that the "Show only related records" option is required. Without this option, the found set would not be restored. All FileMaker wants to do is select the first record in the relationship. If the current found set already holds that first record, the found set won't change at all. If the first record is outside the found set, it will show all records. I emphasis this point not just to justify the use of the "Show only related records" option but to help the reader understand the behavior of FileMaker. Understanding how FileMaker works allows you to tame this beast.
SIDE TIP: FileMaker views records through a relationship in creation order. The only way to change the creation order, for all features referencing the relationship, is to sort the relationship. Sorting a portal only changes the relationship on that layout. Sorting a relationship is schema level.
Saving Multiple Sets Of course, everyone wants to save more than a single found set. Otherwise, why go through all the trouble of combiningg all these features. It would be easier to just employ the New Window feature to preserve a found set. Not only can global fields only store a single set of values at a time, the values are only temporarily stored in a multi-user scenario. Once a user session is ended by quitting the solution, all global values are erased.
In order to permanently save a found set, we're going to need a new table to store multiple found sets on separate records. This table will contain a minimum of two text fields to store serial numbers and a name for each set. To save the serial number values into the new table, quite a bit of scripting will need to be added but it's fairly straightforward.
SIDE TIP: Every table should contain housekeeping fields. These include a creation timestamp, modification timestamp, creation account and modification account. I would also include a primary key in your standard housekeeping fields even if you don't plan on relating a table. Unique values can be used in many scripting operations.
Let's start with the relationship. Two new table occurrences need to be added. Take note of how the table occurrences are connected in the graph below. A new global field named xname connects the CONTACTS to the SETS table. The purpose of this relationship is to locate the found set corresponding to a name selected from a global field and will be come clearer when the script is shown below. The second relationship connects SETS back to CONTACTS in order to restore the found set of records.
The first script provides an interface for naming a found set. There are many ways to provide an interface but the easiest, in this case, is to use a Show Custom Dialog script step. There are a couple reasons why the custom dialog appears at the beginning of the script. First, it prevents the rest of the script from running if the user decides to cancel the dialog. Second, there is no need to freeze the window if the custom dialog redraws the SETS layout in the background.
Once the name of the set is stored in a global field, the script copies all the records in the found set using the special serial number layout discussed previously. Then, the script navigates to the SETS layout and places the name and serial numbers on a new record. Since the xname field is global, no relationship is required to access the information contained within it. Therefore, the global field could really be stored in any table. Often, developers create a GLOBALS table to organize their global fields in one place, uncluttering other tables and making it easy to find globals.
Restoring found sets from the SETS table is more about interface than anything else. In this case, I chose a Popover for the easiest implementation. I couldn't go with another custom dialog because they don't support popup menus. I could have used a Window Card but it would have required more work. All I needed to display was a single popup menu and a button. Think about which features you employ. They all have their time and place. In this case, I didn't want to create a new layout just to support a Window Card. If I felt like thousands of found sets were going to be stored, I might have thought a popup menu was insufficient and gone for the Window Card advantage that allows for the display of a found set of records. Like I said, it's all about what you need for a particular solution.
The script contained in the Popover restores the found set almost as easily as the simple method discussed at the beginning of this article. It starts with the Close Popover script step not only to close the popover but also to commit the relationship. If the xname field attached to the popup menu isn't committed then the Go to Related Record step will not function properly because the primary key isn't up to date.
It's important to notice how the Go to Related Record step works with this relational model. Instead of using two Go to Related Records steps, one to locate the found set and another to restore the found set, one relationship is all that is needed. For example, take a look at the relationship graph below.
With this relational paradigm, one Go to Related Record script would find the selected found set by the name in the global field by navigating from CONTACTS to SETS. A second Go to Related Record would then restore the found set from the SETS record that was selected from the first Go to Related Record by navigating from SETS 2 to CONTACTS. The script might look like the following.
While this script does indeed work, it is inefficient. While it uses the same number of table occurrences as the first relational model, it requires two GTRR scripts. I wouldn't call this the biggest mistake you can make in relational design but it can make you think differently. Small inefficiencies can compile to make a solution that isn't quite a snappy as you would like.
SIDE TIP: A student once pointed this relational improvement out during class and I have never forgotten the lesson. It just goes to show you never know where or from whom you will learn something new.
Duplicate Names If you don't check for duplicate names, when saving a found set, only the first duplicate will show in the popup menu used in the interface for restoring a found set. Even if you decided to go with a Window Card picker, it would be confusing to see two found sets with the same name. Checking for duplicates is best done through a looping script. What you do is keep looping until a unique name is entered.
SIDE TIP: Duplicates don't display in a popup menu based on the contents of a field because value lists are based on the index. An index is best know for efficiently performing finds, much like the index of a book. Each word is stored only once with record numbers next to hit so FileMaker has a shorter list to search.
For your convenience, the entire script for saving a found set is shown. However, the only new steps are steps 3 through 6. These include the loop construct, an Exit Loop If and Show Custom Dialog. The Show Custom Dialog simply repeats the original message from step 2 but adds the message that the found set name is not unique. The Exit Loop If step is where all the logic is contained. First, it tests the existing relationship from CONTACTS to SETS to see if there are any matches based on the name entered into the custom dialog. It also checks to see if the cancel button was clicked in the Show Custom Dialog step using the Get(LastMessageChoice) step. If either are true, it exits the loop, having been satisfied that the user wants to continue and the name entered is unique. In addition, only if the OK button was clicked in the custom dialog will the script make it into the If statement following the loop.
What's Next? In my next article, I want to discuss why serial numbers are the best record identifiers as well as the variety of ways to gather serial numbers other than with Copy All Records. Comparing advantages and disadvantages is one of the best ways to get the most efficient script for the job at hand. There's not just one best method in all scenarios. Besides, you'll learn stuff about FileMaker you can use in unrelated situations.