by Lorne Walton, ISO Contributor, <lornew@wimsey.com>
The Penultimate FileMaker Pro Template
Avoid reinvention of the wheel with each new project.
The purpose of a database template is to store features that you, the database programmer, use often so as to avoid reinvention of the wheel with each new project. Everyone's ideal template will be different from that of his neighbor, and everyone's template will be a work in evolution. You will continue to alter your template as you discover new tricks and techniques that you want to have at your fingertips.
The bonus file "TEMPLATE.FP3" provided with this issue of the ISO FileMaker Magazine is only a starting point. Undoubtedly many of you will have ideas as to what I've left out. Please feel free to email me with your suggestions. Some of those may become part of future Penultimate FMPro Template releases.
The Design
The template is plain vanilla. Design is a very personal matter, and if you are one who prefers to make all of your databases conform to a similar design, then you'll want your template to incorporate your design features. What you see with our template is a medium-grey background with darker grey buttons and a navigational panel to the side. You may prefer a horizontal navigation panel.
To see how the buttons are defined, go into Layout Mode (command-L) and double-click on any button. You'll see that most of them call a script... see below.
The template has data entered in three records to illustrate what you might make out of it without little or no extra work: a basic contacts database that allows for mailing labels, form letters, envelopes, and a list of contacts. The real template, of course, would contain no data... unless you have a reason for making it otherwise.
The Fields
The first thing FMPro asks for when you're creating a new database is field definitions. Go to the field definition dialog by pressing command-shift-D. That should be control-shift-D on a Windows machine. The template contains a number of fields which again take it beyond mere template toward a flat-file contacts database. Your template need not contain any of the name, address and phone fields, which are provided for illustrative purposes only.
Every database record should have a unique identifier. This is especially important when relational considerations are taken into account, but the concept has merit even with flat-file databases. One way to do this is to define a serial number field. Here it's named Serial Number (how original!) and it's defined as type TEXT, not number. Why? It's a bit beyond the scope of this beginner's article, but let me say that it works better this way because of the tricks that FMPro lets you play with many-to-one relationships, where using a number field could restrict severely the size of the "many" side of the equation.
It is a difficult exercise in self-discipline, but I try to not use spaces in my field names. A "sticky-space" is advantageous when using fields in calculations: when the field name is double-clicked, the whole name is selected instead of one word of the name. To enter a sticky-space, type option-space on a Mac, or alt-space on the PC side.
After defining the field, click "Options" and you'll see that the field is defined as Serial Number, with the next new record to have serial number C00004. That's because I started out with Serial number C00001 and added three sample records. It helps avoid confusion if you prefix the serial number with an identifier that reminds you which file you're working with, in a multi-file database solution. For example, your customer database could have serial numbers that start with "C", your parts file with "P", and your invoices with "I".
How 'Bout a Date?
The other thing that every record should have is a creation date and a modification date. This is especially useful when you're looking for data entry errors.
Not surprisingly, these are defined as DATE fields. Double click on those field definitions if you're unsure how they're set up. For your own purposes, you might even want creation time and modification time. Make sure they are defined with type TIME in the field definitions dialog.
Note that the serial number and date fields need not appear on your main layout. They are more important for what they can do behind the scenes than for what they show Ms. User.
Mark My Records
It is helpful to have a "mark" field associated with each record. In this case, it's defined as NUMBER and resides behind the scenes...almost. You may notice a not-quite invisible number in the lower right corner of the Data Entry layout. The reason for this will become evident, but in some cases, you'll want your Mark to be a visible checkbox that the user can employ to select manually the records of interest for updating, viewing, printing etc.
Set Size is a text field which is defined as a calculation indicating, at the bottom of the main layout, how many records are currently active. Double click the field name to see how it was defined, and click the "Storage Options" button as well. (You're still in Define Fields mode, right?) Yes, this could have been a global, but as such its value would have had to be updated from a script. As an unstored calculation, its value is reset automatically whenever a change takes place. This "unstored calculation" notion is one that will surface repeatedly as your databases become more sophisticated. Set Size is entered as a merge field into a text field at the bottom of the Date Entry Layout. Look at Layout Mode to see how.
Global Warming
The more I use FMPro 3, the more I warm up to the concept of global fields. These are variables. They may contain text, numbers, dates, times, and even "container" objects like graphics and sounds. A container global would be the place to put your company's logo which is to be visible on every invoice, or a sound which is to be played each time the user performs a particular function.
I try to keep my global variables separated from the regular fields physically at the bottom of the field definition dialog, and I also differentiate them by prefixing a "g" to the name. There three dedicated variables, gBookmark, gBackupDate and gOne. There are also two "scratch" or reusable globals, gText and gNumber. Your purposes may require the use of several globals. None of the mentioned globals appear on any of the layouts, but there may well be situations where your purposes will require them to be visible.
There is a sixth global field, "___Globals___" which is used only as a header for the Define Fields dialog. There's no reason why it could not be used for storing data.
The Layouts
Probably every database needs a Data Entry layout. This may be the only layout to which Ms. Jo Average User has direct access. Also in our template are layouts named List, Labels, Letter, Envelope and All Fields. These are accessed by Ms. User via the buttons across the bottom of the Data Entry layout, except for "All Fields", which Ms. User will never see, unless she's the curious type, and has a master password! (The template includes no password protection, which will not be the case for your distributed solution, of course.)
The All Fields layout is useful when debugging and when performing finds, copies and pastes involving fields which are not present on the main layout. Hopefully the purpose and construction of the other layouts is self-evident.
A "Get Info" function was a late addition. Check out the layout, button and script definitions. Note that the info, which appears to be text, is actually a graphic. This is one way of getting around FMPro's habit of replacing the double-slash with the current date, and it also assures that users who do not have the same font will have the text appear the same.
The Scripts
Open ScriptMaker™ under the Scripts menu. I insist that all my databases contain as their first script, one named Go Main Layout or Go Data Entry. This must be marked in the ScriptMaker dialog as "Include in Menu" and moved to the top of the ScriptMaker dialog so that whenever you (or Ms. User) are lost, command-1 revisits your main screen. As a further enhancement within a multi-file database, consider placing a script at the top of each file's ScriptMaker dialog, which does nothing more than:
Perform Script(External, "Main DB") [script: Go Main Layout]
Four Navigational scripts, one corresponding to each of the navigational arrow buttons, are next. These are invisible to the non-privileged user, invoked only via button-click. Also present is the Bookmark script, which uses a self-relation to mark the desired record to which Jo User wants to return. The Random Record script asks her whether she wants to jump to any record in the current set, or to any record in the whole database.
Under the heading of "- Layouts -" you'll find a number of scripts which go to a layout and then call subscripts. The pattern repeats: the first subscript call is either a Sort (where multiple records are to be displayed) or an Isolate (where it is assumed the user wants just a single envelope or letter printed.) The next step selects a Page Setup, the following calls a script which puts the Preview onto the screen in WYSIWYG form, and the final script step calls the Go Main Layout script to take Ms. User back where she wants to be.
The four Page Setup scripts are identical! Confusing? It took me a while as a beginner, to understand this important part of FMPro's structure. As you close any of those scripts, you are asked whether you want to keep the information that was saved as part of that script. The only clue you will have as to what setup was saved with each script is to give each script a meaningful name, like "PS US Letter Small Portrait." To change the setup, make a manual change in Page Setup (under the file menu) then go back to ScriptMaker, open the script, close it, and select "Replace" instead of "Keep". These page setups were defined for an Apple LaserWriter. You may need to redefine them.
Look at the "Print This?" script. It tidies up the Preview of which ever layout you're viewing, and then after pausing, asks Ms. User whether she wants to print what she sees (reminding her first whether it's to be a single page or more than one) and then calls the Print() step if the answer is "Yes". Remember that the () specifies that the user's own printer dialog is to appear before the print happens.
The same logic applies to the two Sort scripts, as it did to the Page Setup scripts: they appear identical but store hidden information about the sort order. The "Find Sort & Stay" script is an important part of any database where navigational buttons are to be used. For that idea to make sense, an "order" is essential. The order you're going to use may be alphabetical (as in the template,) by ID, by date, or some combination of these and other criteria. The script simply makes a temporary bookmark to the current record (different from the user-defined bookmark) using a second self-relation so it knows which record to return to after finding all records and sorting.
"Isolate This Record" makes sense if you stare at it long enough. It finds all records, omits the current one, and then Find Omitted makes the unwanted records disappear while bringing back the one you want. Note carefully the critical difference between "Omit" and "Delete".
The housekeeping scripts take care of setting up the database the way you want the user to find it on opening and to leave it on closing. "Opening" also defines the global gOne as "1" for our Restore Marked relation, in case something has happened to alter gOne's value... unlikely, but why take a chance? The Closing script calls a Save Backup Copy script if the current date is more than 7 days past the latest backup date. User is also permitted to save a backup manually, since Save Backup Copy is available at the bottom of the scripts menu. Set Preferences under the Edit menu to specify which scripts you want automatically to run at opening and closing. If this were the master file of a multi-file database, you'd want the Closing script to close the associated files as well.
Two more scripts in the Scripts Menu allow a found set to be marked and restored. The reason for placing the Mark field in the lower right corner of the Data Entry screen is now evident: the Replace command won't work without it being present... although a work-around would have been to do a behind-the-scenes "Go To Layout 'All Fields'" to accomplish the same thing. You can make the Mark field totally invisible by reducing it to a single dot, making the text color the same as the background color, and specifying under Field Format "Do not allow data entry".
The late-added Info script calls the above scripts to mark a current set and restores it after restricting the database to a single record in the found set. The Info text will display as many times as there are records in the current set, and we only need to read a single copy!
Relations
As a beginner, you may think of relations as a way of sharing data between files which make up a multi-file database solution. You're right, but here's another use. The self relations described above are defined via the Define Relationships menu Item, under "File". These three are as simple as they come, but provide some of the potential we've come to expect from FileMaker Pro 3.0.
What Will You Add?
You may want a button that invokes a script that does a data integrity check. For a contacts database, that could mean checking that every record has a name, surname, at least one phone number, and no carriage returns in those fields. Your script would display the faulty records, if any, or a "Congratulations" message if none.
How about a button whose script find records created/modified since (or before) a user-entered date? Give the user the choice whether creation or modification date is desired, using the Show Message script step.
Your template may require a button to reserialize the Serial Number Field using the Replace script step. All of this and more is possible with FileMaker Pro 3.0. Any and all contributions and comments are welcome!
This article is part of a series aimed at those who are not yet experienced with FileMaker Pro. If you have a beginner-level request that you'd like to see addressed, contact the author at lornew@wimsey.com
## END ##