TweetFollow Us on Twitter

What's New for Developers in FileMaker 7?

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
   If [Patterncount (Status (CurrentLayoutName); "Form")]
      Go to Layout ["Print Form"]
      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.


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]
   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)]
   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...
   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); 
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)
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.


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).


Community Search:
MacTech Search:

Software Updates via MacUpdate

GraphicConverter 10.5.4 - $39.95
GraphicConverter is an all-purpose image-editing program that can import 200 different graphic-based formats, edit the image, and export it to any of 80 available file formats. The high-end editing... Read more
Dash 4.1.3 - Instant search and offline...
Dash is an API documentation browser and code snippet manager. Dash helps you store snippets of code, as well as instantly search and browse documentation for almost any API you might use (for a full... Read more
Microsoft OneNote 16.9 - Free digital no...
OneNote is your very own digital notebook. With OneNote, you can capture that flash of genius, that moment of inspiration, or that list of errands that's too important to forget. Whether you're at... Read more
DEVONthink Pro 2.9.17 - Knowledge base,...
Save 10% with our exclusive coupon code: MACUPDATE10 DEVONthink Pro is your essential assistant for today's world, where almost everything is digital. From shopping receipts to important research... Read more
OmniGraffle 7.6 - Create diagrams, flow...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
iFinance 4.3.7 - Comprehensively manage...
iFinance allows you to keep track of your income and spending -- from your lunchbreak coffee to your new car -- in the most convenient and fastest way. Clearly arranged transaction lists of all your... Read more
Opera 50.0.2762.58 - High-performance We...
Opera is a fast and secure browser trusted by millions of users. With the intuitive interface, Speed Dial and visual bookmarks for organizing favorite sites, news feature with fresh, relevant content... Read more
Microsoft Office 2016 16.9 - Popular pro...
Microsoft Office 2016 - Unmistakably Office, designed for Mac. The new versions of Word, Excel, PowerPoint, Outlook and OneNote provide the best of both worlds for Mac users - the familiar Office... Read more
SoftRAID 5.6.4 - High-quality RAID manag...
SoftRAID allows you to create and manage disk arrays to increase performance and reliability. SoftRAID allows the user to create and manage RAID 4 and 5 volumes, RAID 1+0, and RAID 1 (Mirror) and... Read more
OmniGraffle Pro 7.6 - Create diagrams, f...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more

Latest Forum Discussions

See All

Around the Empire: What have you missed...
Around this time every week we're going to have a look at the comings and goings on the other sites in Steel Media's pocket-gaming empire. We'll round up the very best content you might have missed, so you're always going to be up to date with the... | Read more »
The 7 best games that came out for iPhon...
Well, it's that time of the week. You know what I mean. You know exactly what I mean. It's the time of the week when we take a look at the best games that have landed on the App Store over the past seven days. And there are some real doozies here... | Read more »
Popular MMO Strategy game Lords Mobile i...
Delve into the crowded halls of the Play Store and you’ll find mobile fantasy strategy MMOs-a-plenty. One that’s kicking off the new year in style however is IGG’s Lords Mobile, which has beaten out the fierce competition to receive Google Play’s... | Read more »
Blocky Racing is a funky and fresh new k...
Blocky Racing has zoomed onto the App Store and Google Play this week, bringing with it plenty of classic kart racing shenanigans that will take you straight back to your childhood. If you’ve found yourself hooked on games like Mario Kart or Crash... | Read more »
Cytus II (Games)
Cytus II 1.0.1 Device: iOS Universal Category: Games Price: $1.99, Version: 1.0.1 (iTunes) Description: "Cytus II" is a music rhythm game created by Rayark Games. It's our fourth rhythm game title, following the footsteps of three... | Read more »
JYDGE (Games)
JYDGE 1.0.0 Device: iOS Universal Category: Games Price: $4.99, Version: 1.0.0 (iTunes) Description: Build your JYDGE. Enter Edenbyrg. Get out alive. JYDGE is a lawful but awful roguehate top-down shooter where you get to build your... | Read more »
Tako Bubble guide - Tips and Tricks to S...
Tako Bubble is a pretty simple and fun puzzler, but the game can get downright devious with its puzzle design. If you insist on not paying for the game and want to manage your lives appropriately, check out these tips so you can avoid getting... | Read more »
Everything about Hero Academy 2 - The co...
It's fair to say we've spent a good deal of time on Hero Academy 2. So much so, that we think we're probably in a really good place to give you some advice about how to get the most out of the game. And in this guide, that's exactly what you're... | Read more »
Everything about Hero Academy 2: Part 3...
In the third part of our Hero Academy 2 guide we're going to take a look at the different modes you can play in the game. We'll explain what you need to do in each of them, and tell you why it's important that you do. [Read more] | Read more »
Everything about Hero Academy 2: Part 2...
In this second part of our guide to Hero Academy 2, we're going to have a look at the different card types that you're going to be using in the game. We'll split them up into different sections too, to make sure you're getting the most information... | Read more »

Price Scanner via

Apple restocked Certified Refurbished 13″ Mac...
Apple has restocked a full line of Certified Refurbished 2017 13″ MacBook Airs starting at $849. An Apple one-year warranty is included with each MacBook, and shipping is free: – 13″ 1.8GHz/8GB/128GB... Read more
How to find the lowest prices on 2017 Apple M...
Apple has Certified Refurbished 13″ and 15″ 2017 MacBook Pros available for $200 to $420 off the cost of new models. Apple’s refurbished prices are the lowest available for each model from any... Read more
The lowest prices anywhere on Apple 12″ MacBo...
Apple has Certified Refurbished 2017 12″ Retina MacBooks available for $200-$240 off the cost of new models. Apple will include a standard one-year warranty with each MacBook, and shipping is free.... Read more
Apple now offering a full line of Certified R...
Apple is now offering Certified Refurbished 2017 10″ and 12″ iPad Pros for $100-$190 off MSRP, depending on the model. An Apple one-year warranty is included with each model, and shipping is free: –... Read more
27″ iMacs on sale for $100-$130 off MSRP, pay...
B&H Photo has 27″ iMacs on sale for $100-$130 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 27″ 3.8GHz iMac (MNED2LL/A): $2199 $100 off MSRP – 27″ 3.... Read more
2.8GHz Mac mini on sale for $899, $100 off MS...
B&H Photo has the 2.8GHz Mac mini (model number MGEQ2LL/A) on sale for $899 including free shipping plus NY & NJ sales tax only. Their price is $100 off MSRP. Read more
Apple offers Certified Refurbished iPad minis...
Apple has Certified Refurbished 128GB iPad minis available today for $339 including free shipping. Apple’s standard one-year warranty is included. Their price is $60 off MSRP. Read more
Amazon offers 13″ 256GB MacBook Air for $1049...
Amazon has the 13″ 1.8GHz/256B #Apple #MacBook Air on sale today for $150 off MSRP including free shipping: – 13″ 1.8GHz/256GB MacBook Air (MQD42LL/A): $1049.99, $150 off MSRP Read more
9.7-inch 2017 WiFi iPads on sale starting at...
B&H Photo has 9.7″ 2017 WiFi #Apple #iPads on sale for $30 off MSRP for a limited time. Shipping is free, and pay sales tax in NY & NJ only: – 32GB iPad WiFi: $299, $30 off – 128GB iPad WiFi... Read more
Wednesday deal: 13″ MacBook Pros for $100-$15...
B&H Photo has 13″ #Apple #MacBook Pros on sale for up to $100-$150 off MSRP. Shipping is free, and B&H charges sales tax for NY & NJ residents only: – 13-inch 2.3GHz/128GB Space Gray... Read more

Jobs Board

*Apple* Store Leader - Retail District Manag...
Job Description: Job Summary As more and more people discover Apple , they visit our retail stores seeking ways to incorporate our products into their lives. It's Read more
Sr. Experience Designer, Today at *Apple* -...
# Sr. Experience Designer, Today at Apple Job Number: 56495251 Santa Clara Valley, California, United States Posted: 18-Jan-2018 Weekly Hours: 40.00 **Job Summary** Read more
Security Applications Engineer, *Apple* Ret...
# Security Applications Engineer, Apple Retail Job Number: 113237456 Santa Clara Valley, California, United States Posted: 17-Jan-2018 Weekly Hours: 40.00 **Job Read more
*Apple* Solutions Consultant - Apple (United...
# Apple Solutions Consultant Job Number: 113384559 Brandon, Florida, United States Posted: 10-Jan-2018 Weekly Hours: 40.00 **Job Summary** Are you passionate about Read more
Art Director, *Apple* Music + Beats1 Market...
# Art Director, Apple Music + Beats1 Marketing Design Job Number: 113258081 Santa Clara Valley, California, United States Posted: 05-Jan-2018 Weekly Hours: 40.00 Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.