[Advanced]...by Darren Terry/Claris Corporation
[Editor's Note - Mr. Terry has created an amazingly powerful tool using the information in this article. If you are of a level of anything less than advanced then please READ THIS ARTICLE. While you may not understand directly how it works it is of great importance to those users that want to save stored sets. I only wish I had thought of it.]
Saving What You Have Found
The Problem
You may occasionally find yourself in the situation where you need to perform a find, and then save the resulting found set for future reference. At first, this may seem to be one of those non-problems that suggests its own solution. Why not just perform the find again, to collect those same records together? The answer is that some times this is impractical.
For instance, suppose you need to keep track of all the part numbers that go into making a particular kind of engine, when each part is a separate record in the database. You want to be able to pull up all the parts that make up each of three different engines. How would you do that?
If you perform a find for all records that contain those three engines, you can then sort the database by engine name, and view the report in a sub summary layout by engine name, but this is a time consuming method (requiring first the find, and then the sort, and then going to Preview Mode before you can look at your results). Wouldn't it be nice if you could simply perform a find on a particular engine, hit a button, and permanently save all the records that had parts that pertain to that particular engine? That way they would be permanently grouped together, ready for easy retrieval in the future. But how do you accomplish this?
The Solution
In the past (with earlier versions of FileMaker Pro), you would be out of luck if you needed to save a particular found set of records. Your only recourse would be to script each set of Find criteria, and use different buttons to “flip” back and forth between the different found sets--a very time consuming proposition.
Luckily, however, we are now in the age of FileMaker Pro 3.0! Thanks to a new ScriptMaker step, and a little known feature of how FileMaker relates two files, saving a found set is as easy as one-two-three. As in our earlier example, suppose you had a file called Parts, which contains all the separate parts that make up different engines. Each part is uniquely identified by a Part Number field.
One. Define a new database with one field in it called Part Number, to be used as a Trigger Field with the Parts file. For our purposes, let's call this new file “Found Sets”.
Two. Define a Relationship between the new Found Sets file and your main file, basing the relationship on the Part Number field. Now, create a blank layout in the Found Sets file, and place the Part Number field on it. Then place a Portal on the layout, and put Related Fields into the Portal from the primary file showing whatever information you need to display about the found set that you want to save (for instance, Part Name, Description, Price, etc).
Three. Create a script in the Found Sets File called “Save Set” that has three steps:
New Record/Request
Paste [Select; Part Number]
Goto Field []
For those who are not very familiar with ScriptMaker, this script simply makes a new record in the Found Sets file and pastes the contents of the Clipboard into the Trigger Field. The Goto Field step, when there is no field specified, has exactly the same effect as if the end user had hit the Enter key on the keyboard -- in other words, it deselects the current record.
Now we need to do some minor work in the Parts file. Make a new, blank layout, and put only the Trigger field in it. Let's call this new layout “Match Layout”. Then, create a script called “Save Found Set” as follows:
Goto Layout [Match Layout]
Copy All Records
Perform Script [External: “Found Sets”] - (specify Save Set)
What this script does is take the user to the Match Layout (the one with ONLY the Part Number field in it), and then performs a Copy All Records script step. This is the new ScriptMaker step in 3.0 that saves the day.
Copy All Records will take every field on the current layout, and copy the values in those fields to the Clipboard (separated by Tabs). Then it goes to the next record, and does the same, separating each record with a Return character, and each field with a Tab character. In other words, it basically creates a Tab-Separated text file on the Clipboard.
The Explanation
Now here's where the magic happens. Since the current layout has nothing but the Trigger Field on it, this script will create on the Clipboard a list of all the Trigger values for the current found set, separated by returns. If you were to paste the contents of the Clipboard into a SimpleText document, you would find a list of all the part numbers (or invoice numbers, or customer ID numbers, or whatever) from the current found set in a column, separated by Returns.
After the Save Found Set script has copied these values to the Clipboard, it calls the Save Set script in the Found Sets file, which makes a new record and pastes the contents of the Clipboard into the Part Number field in that record.
Now, if you were to try and paste several part numbers into the Part Number field in one record, you would expect that no matching records would show up in the Portal, since there is no single record in the Parts file that has all of those part numbers in it. But, FileMaker Pro 3.0 is smart enough to know that when a series of trigger values are entered into a trigger field, separated by returns, the user actually wants to find ALL of the matching records for that entire set of trigger values. And that is exactly what it does.
Suddenly, in the Portal, there is your found set from the Parts file! And what's more, the found set is permanently collected in the Found Sets file, since the part numbers are pasted into a record in that file.
Using this technique, it is possible to store an almost unlimited number of found sets for future retrieval. Hopefully this solution will make it much easier for you to keep track of data when you need to save the found set.
[Editor - this is a poorly documented "feature" of FileMaker 3.0. A relation can be based on multiple keys. (e.g. if you want to see customer 101,102,103 & 104 in a portal, then just put in all of those values separated by a return between each of them in the trigger field. An added tip to this most powerful technique is to prompt the user to give the stored set a name and then use that name field as a value list for a popup menu. Imagine being able to select from a popup menu and have a listing of stored sets of data! VERY COOL!!! Thank you Mr. Terry for enlightening us.)
- END -