Volume Number: 21 (2005)
Issue Number: 4
Column Tag: Review

Focus Review

by William Porter

What's New for Developers in FileMaker 7?

Short Answer: A Lot!

Most FileMaker developers are clever do-it-yourselfers who like FileMaker because the features they want are all within easy reach. But over the last decade, a growing number of serious developers have been willing to stretch themselves to get to the harder-to-reach features that give FileMaker a surprising amount of power. And with FileMaker 7, the serious developers have much more to reach for. In this article I want to describe some of the problems in earlier versions of FileMaker that made it an awkward environment for applications development, and then in each case, explain how FileMaker 7 has mitigated these old problems, and in some cases actually eliminated them. I'm going to cover a lot of ground here, and some of it will be covered in a hurry. I want to demonstrate that FileMaker 7 hasn't just improved in a few specific areas, but rather that is really does provide a broadly better set of tools to developers.

Four preliminary clarifications

First, I will assume a little familiarity with some version of FileMaker, but not much. I certainly do not assume that you are an expert. I imagine that the reader who will get the most from this article is someone who has used FileMaker in the past but chafed at its limitations, someone who has not upgraded yet to FileMaker 7.

Second, I am talking about what can be done with FileMaker natively. Most of the time, I am thinking about solutions that use a user-interface designed entirely by the developer, in other words, I assume that you do not use FileMaker's built-in commands for creating new records, performing finds, and so on. But, I assume at all times that this solution does not make use of FileMaker 7 plug-ins.

Third, I must note that, while I'm going to focus on FileMaker Developer 7, because of a peculiarity in the FileMaker product line, much of what I'm going to say applies to FileMaker Pro as well. FileMaker does not have a thin client, so end users who wish to use a FileMaker solution run FileMaker Pro. Nevertheless, FileMaker Pro is a development application, too. A different product, FileMaker Developer 7, contains everything that FileMaker Pro 7 contains, plus a bit more. Ninety-percent of what I do every day as a developer could be done in Pro as well as Developer, but the ten percent that Developer adds to the toolset is really important. If you want to do anything even moderately serious in FileMaker, you should get Developer. In what follows I will generally use the term FileMaker Developer only to describe features that are exclusive to Developer; otherwise, I'll simply speak about "FileMaker".

Finally, I'm not going to talk about the script debugger or the Database Design Report (DDR). For one thing, these tools are not new. They were introduced in 2001 when FileMaker Developer became a distinct application (they were what made it distinct from FileMaker Pro). And besides, these features don't really make anything possible that wouldn't be possible without them. I'd hate to work without the debugger (Figure 1), but it doesn't allow me to build solutions that are different and better. And the DDR is inferior to any of the several third-party design reporting utilities available (Metadata Magic, Analyzer, Brushfire).

Figure 1

The Big News in 7: Many Tables in a Single File

In FileMaker 6 and earlier, the distinction between files, data tables and windows was largely technical, because a single file contained only a single table and could have only one window (displaying a single layout). In 1995 when FileMaker Pro 3 was released, it became possible to relate files to one another so they could share data, but it was not possible to combine them in a single file. This had several consequences, most of them undesirable. Scripts and relationships were strictly limited in scope to the file in which they were defined. By default, fields and value lists were also limited in scope to the file in which they were defined, but this was not so inflexible. File B could access data in a field in file A, provided the files were related. And even without a relationship, file B could "borrow" a value list defined in file A.

In FileMaker 7, the file = table = window rule has been broken (Figure 2). I'll get to how this affects relationships and windows shortly, but let's talk first about files and tables. A single file can now contain many tables (a million, says FileMaker, Inc.). So the scope of, say, a script, although still limited to the file in which it is defined, may now extend to every table in a solution. The change has several really exciting consequences, although there's a bit of cost in each case, too.

Figure 2

Pro: Broader scope for application globals

FileMaker has never had (and still does not have) procedurally defined variables, that is, you can't define a variable in a script on the fly, like this:

Set Variable [ theAccount; ACCOUNTS::NAME ]

In lieu of variables, FileMaker offers global fields. The scope of a global field is the file in which it is defined, and this has not changed in FileMaker 7. Nevertheless, the scope of globals in 7 is, or can be, broader than before because a single file can now contain many tables.

In earlier versions of FileMaker, it was hard to get data moved from one file (table) to another. For example, in a library database, to create a new title record for an author, it's necessary to capture the ID of the author record. If other title records existed already, you could use a go-to-related-records script or button to jump from the Authors file to the Titles file, viewing only the related titles; then capture the author ID from one of those existing title records to a global in the titles file; create a new record; and finally, set the new record's IDAuthor field to the value in the global. But what about creating the first title record? If you didn't want to use the clipboard, well, it was a pain in the neck, possibly involving scripts in two different files and a passive holding field in a third file (a one-record hub to which every data file had a constant utility relationship).

In FileMaker 7, by contrast, you can create that note record, and move that ID from the Accounts table to the Notes table in just a few steps, in a single script:

# assumes you start with an Authors record selected
Set Field [g::idauthor; AUTHORS::ID]
Go to Layout [BOOKS Form okeditfindprint]
New Record/Request
Set Field [TITLES::IDAuthor; g::idauthor]
Go to Field [BOOKS::Title]

There are two important things to remember about globals used as variables:

First, the scope of a global is the file, whether you like it or not. I sometimes use generic globals (for example, g::tempnum01) as local variables in scripts. I never use a generic global field to pass a value from one script to another; the risk of confusion is too great. But a global used in this way is local only because I say so, but only because that is the way I use it. Technically, a global is a global.

And that leads to the second thing to remember. In FileMaker, you do not need to declare globals; after all, the globals have already been defined, named and typed in the define-fields phase of development. Nevertheless, it's a good idea to initialize globals when you use them in scripts, and to clear them when you are done with them.

Pro: Generic scripting

The scope of scripts in FileMaker, like the scope of global fields, is the file in which the scripts are defined. If you wish to define a consistent user-interface in a multi-file (multi-table) solution in FileMaker 6, with commands like "View List Layout" and "View Form Layout," "Print" and "Find" available everywhere, you usually needed a large number of scripts that did basically the same thing, but were stored redundantly in each file; this was required because a "View List Layout" script in Accounts.fp5 affected that file only; you had to define a similar script in Invoices, Customers, and other files where this action was wanted. Even if you were lucky, this meant a lot of one or two-step scripts that did the same thing in different files. But usually you weren't lucky, because, if you were the kind of developer who reads MacTech, even simple actions involved a degree of error checking. A quite ordinary print script in FileMaker might look like this, for example:

Set Error Capture [On]
If [Status (CurrentFoundCount) = 0]
   Show Message ["No records to print!"]
   # buttons: OK
Else
   If [Patterncount (Status (CurrentLayoutName); "Form")]
      Go to Layout ["Print Form"]
   Else
      Go to Layout ["Print List"]
   End If
   Perform Script ["Page Setup US Portrait"]
   Print []
   # show dialog to let user select printer, page options
   Go to Layout [Original Layout]
End If

Now remember, you would have to duplicate that script in each file that a user might wish to print records from. If you got the script right in one file, you could import it into the others and tweak it, if necessary, so that the go-to-layout steps went to the right layout in each file. But if, after inserting this script into each file in your twenty-five file solution, you decided to add a step to the procedure, say, to capture an error after the Print step and respond to it, well, you would have to make that change manually in this script in each file to which it had been copied.

Not in FileMaker 7, not if you put all the tables in a single file. You can now write a more generic script, something like this perhaps:

Set Error Capture [On]
Set Field [g::haltscriptflag; 0]
# ------------------------------ exit if printing not ok from current layout:
# print only if these conditions are met:
# 1) layout name's last word starts with "ok"
# 2) layout name's permissions include "print"
# 3) active window is in browse mode (windowmode = 0), 
# 4) there is at least 1 record in the found set
Set Field [g::permissions; RightWords (Get (LayoutName); 1)]
If [   Left (g::permissions; 2) <> "ok" or 
   PatternCount (g::permissions; "print") = 0 or
   Get (WindowMode) > 0 or
   Get (FoundCount) = 0
]
   Set Field [g::haltscriptflag; 1] 
   Exit Script
End If
# ------------------------------ otherwise, proceed:
Set Field [g::layoutname;-
   LeftWords (Get (LayoutName); WordCount (Get (LayoutName))-1)]
# next step uses "layout name by calculation" parameter
Go to Layout [g::layoutname & " " & "Print"]
Perform Script ["Print Setup US Portrait"]
Print []
Go to Layout [original layout]

This one script will print from any layout that is configured to permit the print command, using a print layout that is appropriate for the table being viewed. You can see at a glance that it's more abstract than the FileMaker 6 script shown above. The abstraction is important, but it's equally important for you to have designed the database in such a way that this abstraction is possible. The script shown here assumes that layouts that permit printing have names that end with a string starting with "ok" and including "print". The script also assumes that, if a layout allows printing, there will be another layout with the same name, except that the permissions string at the end will be replaced by the word "Print." So the layout list might look something like this:

• Authors Form okeditfind
• Authors List okfindprint
• Authors List Print
• Titles Form okeditfind
• Titles List okfindprint
• Titles List Print

In this hypothetical database, the two form layouts do not permit printing, while the two list layouts do. If the script is called from one of the list layouts, the script will go to the corresponding print layout, print, then return to the original layout. If the script is called from one of the form layouts, it will abort half way through. Obviously, it's critically important for the layouts to be named consistently, but that's a good lesson to learn quickly. You won't get far in FileMaker 7 if you are sloppy about naming fields, value lists, layouts and scripts.

Now say that you decide later to provide some feedback to the user if the script fails. You could add a few steps that trap more precisely for the kinds of failure (layout doesn't permit printing, or no records in found set, or window is in Find mode) and display a custom dialog explaining the problem and identifying the current layout. And you do it all in one place. Does the solution consist of two dozen base tables? Doesn't matter. This one script will work wherever the context supports it.

Generic scripting is harder. You have to think a bit more abstractly--more like a programmer--to write generic scripts. Doing so means breaking out of the old, very non-generic ways of thinking that FileMaker developers have used for a decade or more. But boy, it's worth it. Generic scripts are a spectacular time saver. I recently rebuilt a small money-management solution from scratch in FileMaker 7. It had taken many weeks to build the original in FileMaker 6. I rebuilt it in 7 from scratch in a day and a half, largely because of generic scripting, and truly "global" global fields.

Con: Loss of the Context-Sensitive Scripts Menu

If you put all your tables, layouts and scripts in a single file, you get the great advantages of generic scripting described in the previous section. But with a cost: the Scripts menu itself--the only menu FileMaker lets the developer control--has become less useful than it used to be.

A conventionally designed multi-file solution in FileMaker 6 had user-interface objects scattered about in every one of the files that users had to access. You could use buttons for common commands and file-to-file navigation, and stick file-specific commands up in the Scripts menu. Each file could have its own proper scripts, so, for example, while working in the Accounts file, the Scripts menu displayed scripts pertinent to accounts, like "Print Mailing List of Accounts"; when working in Invoices, they saw a different list. The Scripts menu, in short, was context-sensitive, with the context being defined by the table (which was the same thing as the file in FileMaker 6).

In FileMaker 7, unfortunately, the Scripts menu is the file, not the table. If you put a script named "Print Invoices" in the Scripts menu, it will be available at all times, whether it's appropriate to the layout the user is looking at or not.

There are basically only two ways to deal with this problem, and one of them isn't a good solution. The first option is to ask your users to get used to a very long Scripts menu--and to begin every one of those scripts by checking to make sure its not being called out of context. But all that context-checking is a pain in the neck, and the long Scripts menu is bad UI practice. In FileMaker 7, you can't gray out a script when it's inappropriate. So that leaves you mainly with the second option: use more buttons, more creatively. What I'm doing is creating a context-sensitive pseudo-menu that is really a value list of command names attached to a global field, with a "Do" button placed nearby (Figure 3).

Figure 3

This is perhaps the place to mention that FileMaker 7 does not require that you put all your tables, layouts and scripts in one file. You are free to build solutions in FileMaker 7 much the same way you did back in 6, by giving each table its own file. But if you do, you will lose an awful lot of the greatest benefits 7 has to offer.

Relationships, Table Occurrences and File References

The new relational model

If you know anything about FileMaker 7, you know that it has a new GUI for managing relationships (Figure 4). It's not the GUI that matters. In fact, the GUI has some weaknesses. But the new relational model has many advantages (and once again, brings with it some new problems).

Figure 4

1. Relationships are now symmetrical and transitive

FileMaker went relational, sort of, in 1995 with version 3. I say "sort of," because FileMaker 3 was really still a flat-file database that simply had acquired the ability for one file to "see" another file's data. Relationships in FileMaker from version 3 through version 6 were neither symmetrical nor transitive, that is, relating A to B did not automatically relate B to A (that would be symmetry), and relating A to B and B to C did not automatically create a relationship between A and C (that would be transitivity). To achieve symmetry, you had to define relationships twice, once in each of the two files being related. To achieve something like transitivity, you had to "pipe" data from files whose relationship to the current file was remote. In other words, if LINE ITEMS is related to INVOICES, and INVOICES is related to ACCOUNTS, and you wanted to display the account name on a layout listing line item records, you had to create a calc field in the INVOICES file with this formula:

accountname_ct = ACCOUNTS::Name

The LINE ITEMS file couldn't "see" anything in the ACCOUNTS file, but it could see this calc field in the INVOICES file, since LINE ITEMS was directly related to INVOICES. I think of it this way: You could borrow a cup of sugar only from your next door neighbors, and if they didn't have any sugar either, you'd have to ask them to go to their next door neighbor to get some for you.

In FileMaker 7, relationships are now both symmetrical and transitive. A relationship between two tables only needs to be defined once. And you no longer need calc fields to get to data in remote tables.

2. Complex relationships are much easier to define.

We might distinguish between two kinds of relationships, structural and utility. A structural relationship is the kind of relationship you generally learn about when you study relational theory. A primary key in one table is matched to a foreign key in another table. Primary keys are generally simple matches, for example, ACCOUNT::ID = INVOICES::IDAccount. In FileMaker 6, all relationships looked like this, because the only operator available to define the match was equals.

In FileMaker 7, there are other operators available: <>, >, < and the Cartesian operator x, which relates all the records in one file with all the records in another file. The < and > operators are especially useful, because they make it really easy in FileMaker 7 to do things like show (in a portal) all related invoices that are unpaid and 30 days old.

And, it's much easier now to create a utility relationship that you can use to filter a portal based on something a user types in a global "find" field. Say the layout displays a list of contacts, and you want to let the user filter the list by typing a couple letters into a global field g::filtertext. In the table that you will be filtering, create two calc fields with the following formulas:

namelastshort_ct = Left ( Name Last; 1 )
namelastlong_ct = Name Last & "zzzzzz"

Finally, relate the table containing the global to the table containing the records to be filtered in this fashion:

g::filter ? PEOPLE::namelastshort_ct and g::filter <= PEOPLE::namelastlong_ct

That's right, the relationship has two criteria and both must be satisfied. Now, typing "Po" will match Poole, Porter, and Potter. No need for the exploded keys required to achieve the same result in FileMaker 6.

Moreover, although you can still indicate the default sort order for related records as part of the definition of the relationship, you don't have to, and you can override this sort order ad hoc in individual portals. In FileMaker 6 and earlier, if we wanted to get the same basic relationship to sort different ways, we had to create multiple instances of the same basic relationship (using the same keys) but change the sort order. No longer.

Tables and Table Occurrences

The new relational model is a quantum leap forward, but it is not without its own idiosyncracies. In FileMaker 7, you do not relate tables directly, you relate "table occurrences". A table occurrence is a logical instance of a table. The table occurrence is defined on the relationships graph. In a very simple relational database, you might not need more than one occurrence of each table; in that case, the distinction between occurrences and base tables is inconsequential. But if your database has a number of tables, and requires even a slightly complicated network of relationships, it is likely that you will end up needing to define multiple occurrences for some of your tables. The relationship graph will not tolerate "cycles," that is, circularity. Between any two related table occurrences, there must be only one path to travel.

Why might you need more than one occurrence of a given table? As a general rule, you may only need one structural relationship between two tables, but in FileMaker, you will typically want to use utility relationships to achieve certain goals. For example, let's assume that ACCOUNTS is related to INVOICES thus: ACCOUNTS::ID = INVOICES::IDAccount. This is the basic, structural relationship between the files. However, you may wish, in the Invoices file, to display accounts that are related in some other way, say, all accounts that are in the same sales region. Even if Sales Region is a valid sort of field to be used as a key, you can't simply draw a relationship line from INVOICES::Sales Region to ACCOUNTS::Sales Region, because that would be circular. (If you tried to do this, FileMaker would assume that you were trying to modify the structural relationship between ACCOUNTS and INVOICES.) What you need to do here is create a second occurrence of the ACCOUNTS table, give it a descriptive name, place it on the other side of Invoices in the graph, and relate Invoices to it. A self-join will also always require a second occurrence of the table.

The Importance of Naming

It has always been a good idea for FileMaker developers to name fields and relationships according to an intelligible, consistent system, but you could get away in earlier versions of FileMaker with a good amount of sloppiness in this regard. If you are sloppy in FileMaker 7, you'll be sorry.

File References and Separating Application from Data files

Unlike more powerful RDBMSs, FileMaker Pro makes no technical distinction between data files and application files. In a conventional FileMaker solution, the user's data is stored in the same file as the developer's layouts and scripts. This commingling is not attractive conceptually and, on a more practical level, it makes performing updates extremely awkward: The data has to be copied from the old files into the new ones. In earlier versions of FileMaker, you could separate data and application to some extent, but by no means fully. It was a huge amount of trouble, and you lost a number of FileMaker's natural advantages in the process.

Complete and true separation still isn't possible in FileMaker 7, but you can get much closer to it than you ever could before, and best of all, now it's really easy. The key to this breakthrough is a new feature called a "file reference". If you want to build a two-file solution, with a front end file A containing all your scripts and layouts, and a backend file B containing nothing but the user's data, you simply define the data tables in B, then tell A where to find B by defining a file reference to B. Once A has been linked to B in this manner, A can "see" and act upon all of B's tables as if it contained them itself. A can't see or use B's relationships or scripts or layouts, but it can access all of its individual tables. You can define the data tables in B and store the user's data there, and put all the application resources such as relationships, layouts and scripts in A, and link the two files with a file reference. The application file A doesn't have to have any tables defined in it at all, while the backend file B doesn't have to have any user-accessible layouts or scripts.

Sticking the data in the backend and the application resources in the front end makes the architecture of complex solutions much clearer. Finally, it's now possible to update a solution without having to copy data from old files into new ones. This is tremendously useful to developers of commercial applications. Got a new v-rev ready? All your licensees need to do is quit the solution and replace the old application file (A) with the new one. It's almost revolutionary.

I have to say almost revolutionary, because there's slightly less here than there seems to be. That backend file isn't really a special data file; it's in every respect a full FileMaker binary file, structured to store scripts and layouts and value lists, even if you don't create any. As a binary file, the backend is more prone to corruption than a plain-text file would be. If you design your solution so that the data is stored in a separate backend file, you will still want to provide for an old-fashioned data-transfer update, so that a damaged data file can be replaced by a pristine clone. The biggest problem is that the structure of the backend data file can't be edited by the front end application file. The application file can't, for example, create new fields in the backend or redefine the options in existing fields. There are ways to deal with these limitations, but not to eliminate them entirely.

Windows

So, in FileMaker 7, we've got tables stored in files, relationships defined between table occurrences, which may or may not point to tables in the file in which the relationship is defined. Are you still with me? If you are, then let's move on to windows. This is where it gets complicated.

In FileMaker 6 and earlier, the rule was, 1 file = 1 table = 1 window. It was limiting, but it had the advantage of being easy to understand. In FileMaker 7, on the other hand, the rule is 1 file = 0, 1 or more than 1 tables = 0, 1 or many windows.

In FileMaker 7, you can create and manage multiple windows procedurally, using the ScriptMaker. For example, say that you want to give users the option of opening a memo window on the right side of the screen so they can take notes while reviewing a data list. Here's one way to do it.

Adjust Window [Maximize]
Move/Resize Window [
   Window = Current Window;
   Width = Int (Get (WindowWidth) * .66) - 3
   ]
New Window []
Move/Resize Window [ 
   Window = Current Window;
   Width = Int ( Get ( WindowWidth ) * .5 ) - 3;
   Distance from left = Get (WindowWidth) + 3
   ]
Go to Layout ["Notes Form"]
Go to Field ["Notes"]

The script starts by maximizing the current window. In Mac OS X, this script step is aware of the Dock, so you don't risk placing the new window underneath the Dock if the Dock is visible on the left or right. The script then shrinks the main window to two-thirds of its original size, less 3 extra pixels just for comfort space. The New Window command always spawns a "clone" of the current window, so the new window initially sits on top of the original window and has the same dimensions. The script takes advantage of that fact by using those dimensions to resize the new window so that it's half again as wide, but moved over to fill the right third of the screen. After that last step, the new window has focus. The last two steps switch the new window to a notes layout and enter a notes field so the user can start typing immediately.

You could also use something similar to the preceding example to compare two different found sets side by size, say, Q1 of this year and Q1 of last year. The new window has the same found set as its original when it's initially created, but you can then do a find in the second window without affecting the found set in the first window.

The Move/Resize Window script step can be used with a loop to animate the movement of a window on screen. For example,

Set Field [g::windowwidth; Get (WindowWidth) - 1]
Loop
   Exit Loop If [g::windowwidth < (Get (ScreenWidth) / 2)]
   Move/Resize Window [
      Window to Adjust = Current Window;
      Width = g::windowwidth
      ]
   Set Field [g::windowwidth; Get ( WindowWidth ) - 1]   
End Loop

That example slowly shrinks the window until it's half the width of the screen. How slowly, depends on the computer's CPU, which is one of the problems with using these effects.

The control FileMaker 7 gives developers over windows is one of the new version's biggest steps forward. It's also one of the trickiest things to manage. It's extremely important to come up with a good naming convention for windows and stick with it, and then test constantly to see which windows are open before you use a New Window or Close Window step in a script, otherwise you might end up with more than one instance of a given layout visible at a time. Keep in mind that giving windows names like "Main" and "Commands" can be risky, because if you have two solutions open simultaneously, and each has a window named "Main", well, a script step that is designed to resize a window named "Main" might resize the window for the wrong solution.

There are a couple other things to keep in mind when working with windows in 7.

In earlier versions of FileMaker, where window = file, the file was either open or closed, and if opened, its window was either visible or hidden. It's more complicated in FileMaker 7. A file can have any number of open windows, and these can be open or hidden; but a file can be open without having any open windows at all. That occurs when the file is opened by another file because the other file needs access to the first file's data. A file's startup script is not triggered until the first window is opened for the file. So if you want to run a startup script in a back end data file that does not otherwise get opened, you must be sure to call that script from the front end file. In addition, don't expect the Close Window command always to close the file. There is now a distinct Close File command. If there is only one window open, Close Window = Close File. But if there are no windows open, or if there is a window open and a window hidden, Close Window may or may not close the file.

Complicating things further, while you can run a script when the file closes, you cannot trap either for the changing of window focus or for the closing of a window that is not the last window open for that file. The example I gave above, opening a notes window on the right side of the screen alongside a window displaying a list of data, illustrates the risks involved in using multiple windows. After finishing a note, the user might click back on the list window and expand it, hiding the note window, then later, the user might type Command-W to close the list window--intending to close the solution--and be surprised to discover that the notes window is still open.

Inside Calculations and Scripts

Notes and Comments

The ability to comment scripts was added a couple years ago. Field notes and calc-formula comments are new in FileMaker 7.

Calculation formulas can now be commented using either or both of two formats, C or C++.

/*A C style comment has an opening and a closing marker but can be more than one line long. */
// C++ style comments at the end of lines cannot contain line breaks.

These can be used in any calc formula, whether it's part of the definition of a calc field, used in a Set Field script step, or in the formula for a custom function.

I find field comments somewhat less useful to me as a developer. I try to give fields self-explanatory names, and if I fear that I might forget why a field was created, I document the field's creation in a separate developer log. But, many developers will find it convenient to be able to explain fields right in the Define Fields dialog.

And you can use field comments as a UI device, if you like. There is a new function that gets the comment (if any) attached to a field:

FieldComment ( fileName ; fieldName )

One way to use this function is in a script that tells the user what the field does. The following little script assumes that the insertion point is in the field about which the user wants more information:

If [ Not IsEmpty (FieldComment (Get (FileName) ; Get (ActiveFieldName))) ]
   Show Custom Dialog [
   Title: "Field: " & Quote (Get (ActiveFieldName)); 
   Message: FieldComment (Get (FileName) ; Get (ActiveFieldName)) ]
End If

In our vertical market application in FileMaker 6, we have literally hundreds of quick-help scripts for individual fields. They're hard to edit and maintain, and every field (or field label) must have a button attached to it. In FileMaker 7, on the other hand, the single script will work with any field in any table in the solution, provided (a) that the field has a comment and (b) that the user is allowed into the field. Once the script is written, the developer really only needs to write field comments.

Variables in Calculations

Look back to the printing example above, and use a variable to store the layout name. This was the script step:

Set Field [g::layoutname; -
      LeftWords (Get (LayoutName); -
      WordCount (Get (LayoutName))-1) ] 

The calc formula here can be rewritten like this, using the Let function:

Let ( 
   [ n = Get (LayoutName); w = WordCount (n)];
   LeftWords (n; w - 1)
)

The scope of the variables here is the calculation formula itself. You can use "n" and "w" with different meanings in every formula in the database and, while you might confuse yourself, you won't confuse FileMaker. But, using variables makes it easier to write and read calc formulas, especially ones like this that would otherwise call for repetition of the same functions.

Script Parameters

A script parameter is a value that may be passed to a script when it is called either by a button or by an earlier script. The script parameter is specified in the "Specify Script" options dialog of the button or the earlier script.

Script parameters help you write more generic scripts and do so more efficiently, by passing values directly without having to store them temporarily in globals. One very simple use of script parameters is in buttons. My "go-to-previous-record" button now calls the same script as its counterpart, "go-to-next-record," but the former passes the script parameter, Get (RecordNumber) - 1, while the latter passes the parameter, Get (RecordNumber) + 1. The script, called ("nav: go to next | prev record"), looks like this:

#Confirm there is a record to go to
If [
   Get (FoundCount) = 0 or
   Get (ScriptParameter) < 1 or 
   Get (ScriptParameter) > Get (FoundCount)]
   Beep
Else
   Go to Record/Request [ No dialog; Get (ScriptParameter) ]
End If

One simple script, good for any layout and any number of records, no global fields, and buttons that differ from one another only in looks--and in the script parameter each passes.

In some other cases, script parameters save you the small trouble of storing a value in a global first. For example, one script can call another, and pass to it the script parameter "Get (LastMessageChoice)". The subscript would then interpret the script parameter and do this or that as appropriate, like this:

If [Get (ScriptParameter) = "1"]
   Do something...
Else
   Do something else...
End If

Another useful way to use script parameters: to display error dialogs to users. As a solution gets complicated, I will sometimes decide that certain scripts are no longer needed. If I have time, I will examine the solution in the DDR, or in one of its superior third-party counterparts, to see where the script is used. But if I don't have time, I'll change the script's name to indicate that it's now supposed to be obsolete, then as the first step in the script, I'll call a subscript called "utility: notify user obsolete script is running," with the script parameter

Get (ScriptName) & "|" & Get (LayoutTableName)

Note that, although a script parameter can only pass a single value, you can nevertheless use a delimiter like the pipe character, then parse out multiple values at the receiving end. The subscript called here would end up showing the user a dialog that identifies the script that is running unexpectedly, and tells the user to notify the developer that this dialog was seen.

Some of the other things that you might think to do with script parameters can be done right in the scripts themselves. For example, you might want your "New Record" script to do a certain validation if the user is working in one table that is not needed if the user is working in other tables. You might think that it would make sense to send the layout name as a script parameter, but every step in a script is interpreted in the context of the current table occurrence, the current record, and the current layout, so you could just as well use a Get (LayoutName) as the first step in the script.

The Evaluate Function

The Evaluate function is one of the most useful, and most powerful new features in FileMaker 7, yet it's quite simple: it evaluates an expression and returns a value. For example, in a calc formula, there is no difference between the following:

ITEMS::Cost + (ITEMS::Cost * PREFS::Tax Rate)
Evaluate (ITEMS::Cost + (ITEMS::Cost * PREFS::Tax Rate))

The Evaluate function takes a single parameter, a text string that represents a calc formula. But since the formula is "packaged" as a string, the formula itself can be generated on the fly, or can be generated by a script and stored in an ordinary text field.

Custom Functions

One of FileMaker Developer 7's new features (not available in FileMaker Pro 7) is the ability to define custom functions. A custom function may be called for whenever you find yourself wanting to process values in the same, somewhat complicated fashion over and over again. Custom functions could be used in association with auto-enter calculations (see below) to provide simple encryption and decryption of data, or to determine if a given piece of text is or is not a palindrome, or to turn numbers ($4.75) into text ("Four dollars and seventy-five cents"), or anything else you can think of. The coolest thing about custom functions, after the fact that they exist at all, is that they can call themselves. In other words, that they support recursion.

Here's a very basic custom function, using recursion (Figure 5).

Figure 5

/* Returns capital letter initials */
Let ( 
    w = WordCount ( Name ) ;
    If ( w = 0 ; "" ; 
      Upper ( Left ( Name; 1 ) & GetInitials ( RightWords ( Name; w-1 ) ) )
    )
)

It turns "Prince" into "P" and "George W. Bush" into "GWB." Unfortunately, it also turns "William F. Buckley, Jr." into "WFBJ," which is probably not what one wants. So, the formula can be improved by filtering out a select number of possible suffixes. And what about names that include prepositions or other little parts of speech, such as Ludwig van Beethoven or Ortega y Gasset?

Custom functions do not have to use recursion, of course. Here is a custom function that formats telephone numbers. The function assumes that, in a prefs table, the user can specify two values for delimiting phone numbers in fields named phonedelimiter1 (possible values might include "()", "/", "-" and ".") and phonedelimiter2 (possible values might include "-" and "."). The function takes one parameter, "Phone", so FormatPhone (Phone) =

/* Declare variables A, B, C and P */
Let ([
A = If (
   Length (g::phonedelimiter1) = 2; 
   Left (g::phonedelimiter1; 1); 
   "");
B = If (
   not IsEmpty(g::phonedelimiter1); 
   Right (g::phonedelimiter1; 1) & If (Length (g::phonedelimiter1) = 2; " "; ""); 
   "-");
C = If (
   not IsEmpty(g::phonedelimiter2); 
   g::phonedelimiter2; 
   "-");
P = Filter (Phone; "1234567890")
];

   
/* Now, perform the actual calculation */
If (
not IsEmpty (P) and Length (P) = 10;
A & Left (P; 3 ) & B & Middle (P; 4; 3 ) & C & Right ( P; 4 );
Phone )
)

This function allows for user preference with regard to the delimiters; if you simply hard coded, say, hyphens as the delimiters, you could cut the formula down to a few lines. In any case, once you've written the function, you can use it anywhere in the solution to format any and all phone numbers. How is the function triggered? You could use it in a script, but in FileMaker 7 there's a much better way: auto-enter calculation.

Auto-Entry Calculations

Auto-enter calc formulas in FileMaker 6 could not reference the field in which the calc result was to be placed; that was regarded as circular. In addition, in FileMaker 6, an auto-enter calc was evaluated only once, when the record was first committed. Both of the old limitations have been removed in FileMaker 7, and as a result, auto-enter calcs are now more useful than ever before. The most obvious use for auto-enter calcs is to format data such as phone numbers and social security numbers in a consistent fashion. Say you defined the custom function described in the previous section for formatting phone numbers. Now you can assign the following auto-enter calculated result calc formula to any phone number field:

FormatPhone (Home Phone)
or
FormatPhone (Fax)

So, the user types "2145552929" and, when the field is exited, the number is reformulated as "214-555-2929" or "(214) 555-2929" or "214.555.2929", depending on your preference with regard to delimiters.

You can't make this formula completely generic. Neither Get (ActiveFieldContents) nor GetField (Get (ActiveFieldName)) will work here, because auto-enter calcs are evaluated when the user exits the field, at which point there is no active field. So you either have to hard code the name of the target field into the calculation, or store the field's name in a global and reference that, and either way, the calc will break if you change the name of the field and forget to update either the formula or the global. Nevertheless, these limitations are minor compared to the benefit provided by the auto-enter calc option, namely, that phone numbers will always be properly formatted.

Auto-enter calculated result has a new option: you can indicate whether you want existing values changed or not. I will never use a lookup again because you can tell FileMaker 7 not to replace an existing auto-entered calc result, but you can't keep it from re-looking up something if a trigger values changes.

Auto-enter calc formulas can do a lot more than format data. You could use an auto-enter calc (with the right field definition options) as an alternative to a conventional calc field, say, to calculate total time elapsed in a record with fields for starting time and ending time.

Conclusion

At the FileMaker Developer Conference in Phoenix in August 2003, we were shown a preview of FileMaker 7. At that time, the product was code-named "FileMaker Next." When it was actually released in March 2004, six months later, the code name had been dropped, and the product was called FileMaker Pro, version 7. Boring--and misleading. FileMaker 7 has a new database engine under the hood, and even the controls in the cockpit have changed so substantially that this deserves to be recognized as a whole new application. True, if you don't want to do much with it, it's as easy as ever, and I suspect FileMaker, Inc. didn't change the name because they didn't want to scare off the do-it-yourselfers who are the lion's share of the FileMaker market. But I want to get the word out to serious professional developers: this new FileMaker is a serious professional development tool.

---

William Porter, Ph.D., is the president of Polytrope LLC, a small software development firm in Dallas, Texas, that focuses on building FileMaker Pro solutions. William is writing a book on FileMaker 7 for No Starch Press (release date: 2005).