Tom's FileMaker Pro Questions and Suggestions

[ Contents | Latest update: 16 March 2004 | Cross-reference ]

FileMaker Pro (FMP) is a relational database program from FileMaker Inc. (FMI). FileMaker Pro is a single application with a graphical user interface for developing frameworks of databases and for manipulating the data in databases. FileMaker Pro software is available for MacOS and Windows; the FMP databases are cross-platform compatible, can be shared over a network, and can easily be published on the web. There are special developer and server editions.

I have compiled the following list of questions and suggestions while developing several larger FileMaker Pro (v4.0) databases. On one hand, I explain how to accomplish a number of commonly desired effects that I could not (easily) find in the user manual. On the other hand, I point out some shortcomings of FileMaker Pro and suggest improvements that FileMaker Inc. could possibly incorporate in future versions. I have submitted my suggestions to FileMaker Inc., but I do not intend to act as a(nother) feedback collector for FMI.

I encourage you to submit your suggestions to FileMaker Inc. as well
(duplicate requests will hopefully increase their priority):
http://www.filemaker.com/about/suggestions.html

Let this long list of questions not discourage you from using FileMaker Pro. I have, obviously, not found it necessary to include the (even longer) list of things that are very easy to accomplish in FileMaker Pro. I would not have taken the trouble to compile this list if I did not find FileMaker Pro a useful tool.

This material is mainly addressed to developers. It is not intended to grow into a complete FAQ, though it may serve as a FAQ to some extent. Feel free to link to this page.


FileMaker Pro 7 (added 16 March 2004)

It seems that the newly released FileMaker Pro 7 (finally) addresses most of the concerns expressed in this document. I have not yet investigated this myself, but read for yourself:


Disclaimer, Feedback, Copyright

All the information on this page is offered "As Is" and comes without any guarantees whatsoever.
As always, be sure to make backups before experimenting with a database.

Feedback about this page is welcome.

© 1999, Tom Verhoeff


Contents


TERMINOLOGY

I have tried to adhere to the `standard' FileMaker Pro terminology. There may, however, be some exceptions.

Value: An abstract thing, somehow distinguishable from other values (hard to define :-).
FileMaker Pro groups values by their type. Values are communicated with the user in some external format (notation). For instance, in FileMaker Pro, 123 and 0123 denote the same, indistinguishable, number value (viz. one hundred and twenty-three), whereas "123" and "0123" denote distinct text values. Note that input and output formats need not be the same. For example, the date `July 4, 2000', can be input as 7-4-2000 (depending on your system preference for dates) but not as 4-Jul-2000. By proper formatting options, it can be output also as 4 July 2000.

Values are stored and manipulated by FileMaker Pro in an internal format (e.g. 64-bit floating point for numbers). However, number, date, and time values are also stored in the notation they were entered. For example, the date `July 4, 2000', when entered as either 07-04-00 (near the year 2000) or 7-4-2000, is stored internally as the number 730305. However, the date can still be shown back to the user in the way it was entered.

Type: A set of values and associated operators.
FileMaker Pro distinghuishes the following `self-explanatory' types: There are type conversion functions to convert a value of one type to a value of another type, for example, NumToText and TextToNum.

Name: A mnemonic tag with which the user designates various FileMaker Pro objects.
The following FileMaker Pro objects have names: FileMaker Pro does not use object names to record references between objects. Instead, FileMaker Pro uses internally-generated identifiers that are concealed from the user

For instance, assume field F refers to value list V named "Titles" (to be used for validation) , and field G refers to value list W named "Sizes". If the name of value list V is changed to "Whatsits" and the name of value list W to "Titles", then F still refers to value list V (even though list W is now named "Titles").

Here is another noteworthy naming phenomenon. Assume object X refers to object Y named N. When object Y is deleted, the reference from object X to object Y becomes invalid. If later a new object Z (of the same kind as Y) is created and it is also named N, then this does not automatically restore the reference from object X to object Z. The reference from object X to object Z must be explicitly re-introduced, in spite of Z having the same name as Y formerly had.

Note that files get their names through the Operating System. These names do act as identifier, that is, links to files are affected by name changes (also see Renaming or moving files within a database).

Field: An elementary storage location in a record. Resembles a column in database theory.
A field is defined in a file. Each field has a name, a type, and some options. A standard field in a record stores a value of the appropriate type. Values for standard fields are entered and modified in Browse mode, either manually or through scripts steps.

Container fields of type container support a much more restricted set of operations than other standard fields.

The following kinds of fields are non-standard and do not behave like standard fields at all:

Do not confuse

Record: A collection of values, structured into fields. Resembles a row in database theory.
All records in a file have the same structure, i.e., consist of the same fields. Each field of a record holds a value of the appropriate type. The values in the same field may vary from record to record (except for global fields). Records are stored in the file.

Records are created, inspected, modified, and deleted in Browse mode, either manually or through script steps. For example, a new record is created by:

File: A collection of isomorphic records, and associated fields, layouts, scripts, relationships, value lists, indexes, and access privileges. Resembles a table in database theory.
All records in a file have the same fields (though the stored values may differ, except for global fields). Note that a file contains both user-defined data and user-defined functionality (capturing what can be done with the data).

A file is created by File-<New.

Database: A collection of linked files.
A file can refer to other files through various kinds of file links:

Mode: A context for user interaction with a file.
FileMaker Pro provides four ways in which a user can interact with a file: At any moment, one mode in each file is active. It is called the current mode of that file. FMP always starts in Browse Mode (however, see opening script). The current mode is changed through: The set of menu commands available to the user varies with the mode.

Layout: A (graphical) presentation of the field values in the records of a file and of related files.
A layout determines the user view of the records in the found set. In particular, it determines which fields are shown where on the screen or print-out, and in what format. A field can occur zero or more times on the same layout. Each field occurrence has its own formatting options.

At any moment, one layout in each file is active. It is called the current layout of that file. The current layout is determined through:

At any moment, one of the fields on the current layout is selected. It is called the current field. FMP always starts with the first field of the current layout's tab order as current field. The current field is determined through:

With Select->View as Form, only the current record is viewed. With Select->View as List, all selected records are viewed in a scrollable list (the current record being marked). The Form-versus-List view state is remembered for each defined layout.

Fields from records in related files are displayed on a layout either singly, or as a vertical list in a portal. A layout can also contain graphics objects, and buttons to perform actions.

Layouts are defined in Layout mode. A layout consist of various parts, whose appearance can depend on the sort order and the current mode.

Found Set: The selected set of records in a file.
At any moment, one subset of the records in each file is selected. It is called the found set of that file. The records of the found set can be traversed and manipulated; the omitted records are temporarily unavailable. By default, the found set includes all records.

The found set is determined through:

Current Record: The selected record in the found set of a file.
At any moment, one record in the found set of each file (provided it is not empty) acts as the current record. It is called the current record. Its fields can be manipulated. FMP always starts at the first record of the current found set (however, see opening script). The current record is determined through:

Sort Request: A prescription of how to sort the records in a file.
FileMaker Pro presents the found set of records in some order, and it numbers these records in that order starting from 1. The Status Area shows The record numbers can also be put on a layout with the Edit->Paste Special->Record Number command (@@). Note that these are relative record numbers: they are recomputed when the found set or sort order changes.

A sort request is defined in Browse mode using the Mode->Sort command. It consists of a list of field names (called sort keys) and associated sort orders (ascending, descending, custom). The field names must appear in the file or in related files. The records are sorted on the values of the first field in the list according to the associated sort order. Records with the same value in that field are further sorted on the remaining fields in the list. When the list is exhausted, records are sorted on their creation order (earliest first).

A sort request is performed through:

The Unsort command and script step can be thought of as performing a sort request with an empty list of sort keys, yielding the creation order.

A relationship can also involve sorting.

Find Request: A prescription of how to select records in a file.
A find request is defined in Find mode (which is also entered by Select->Modify Last Find). It consists of a collection of field conditions. Each find request selects the set of records that satisfy the conjunction (AND, intersection) of all its field conditions. The Omit option selects the complement. Multiple find requests (cf. Mode->New Request command in Find mode) are combined sequentially by disjunction (OR, union).

Find requests are performed through:

Calculation: A prescription of how to compute a value from one record and its related records.
A calculation is specified by a formula built up from literal constants, fields (values taken from the current record), related fields, and various predefined operators and functions. In the case of a calculation field, there are also options concerning indexing and storage to be specified.

Calculations can be associated with fields or with some script steps. Various circumstances can trigger a calculation for (re)computation in various ways:

Computations involving multiple records must be done through

Script: A prescription of how to perform operations on the records of a file.
Scripts are defined using ScriptMaker (see Script menu). Each script has a name and consists of a list of script steps. Scripts can be performed

Button: A layout element that is set up to activate a predefined action when clicked.
Buttons are defined in Layout mode. Each layout element can be set up as a button (Format->Button...). The action performed by a button is a single script step (including Perform Script to do more than one step). Not every script step can be activated from a button.

Relationship: A prescription of how a record in one file determines a set of related records in another file based on match fields.
A relationship is defined by choosing Given a record in the master file with value v in its match field, the relationship selects as related records all those records from the related file that also have value v in their match field. If the master match field holds a list L of values (separated by carriage returns), then the relationship selects as related records all those records from the related file that have any value from L in the related match field (also see TechInfo: 104553 - Relationships: matching on any of multiple values).

A relationship can be used to:

  1. Access the fields of related records (notation: `RelationshipName::FieldName') to display their value on layouts and to access them in calculations and script steps. Two kinds of access can be distinguished:
    • Inspection (read)
    • Modification (write)
    A field in a related record is called a related field. If there is more than one related record, then the record designated by `RelationshipName::FieldName' depends on the context. If the reference is from a portal row, then that particular record is designated; otherwise, the first record in the associated sort order is designated (creation order if no sort request was specified for the relationship).
  2. Show or select the related records (through Go to Related Record script step), as current record in the related file, and optionally as found set (`Show only related records').

Portal: A layout element to view a list of related records.
Given a relationship, all the related records can be displayed in a vertical list through a portal on a layout for the master file. A portal shows a portal row for each record related (by the portal's relationship) to the current record in the master file. This row can contain various fields of the related record, and also buttons whose behavior then depends on the related record (e.g. a button activating Go to Related Record will go to that particular related record). The portal can also serve to create and delete related records.

Value List: A named list of text values.
A value list is defined in a file, possibly involving the values in a field from another file.

Index: A structure associated with a field to speed up searches on the field's values taken over all records in a file.
An index is either stored in the file or is temporarily constructed on the fly. See Storage options... for the field to turn indexing on/off.

An index is required on a field that acts as related match field of a relationship. An index speeds up the following operations involving the field:

The index of a field can be viewed in Browse mode by selecting an occurrence of the field on a layout and doing Edit->Paste Special->From Index....

Access Privilege: A mechanism for restricting access to certain features present in a file.
For each file, access to its data (records) and functionality (definitions of fields, layouts, scripts, relationships, value lists, and access privileges) can be limited through passwords and groups.


FIELDS


Sharing global fields among files in a database

Global fields in a file are stored only once and are not duplicated per record. These fields are useful to store data that is independent of the records, such as the name of your company, user input, or parameters to a script. FileMaker Pro provides no direct way to access the global fields of one file A from another file B. Such cross-file access of global fields is often desirable in a database.

Global fields in file A can only be accessed from another file B through a relationship from B to A. Any relationship from B to A will do. If there is no relationship naturally available you can define a relationship named, `Globals in A', from any field in B to any indexed field in A. If you don't like this, an (admittedly awkward) alternative solution is:

  1. Define auxiliary global fields named, say, `GlobalAnchor' of type Number in both files.
  2. Give both global fields the same value, say, 1.
  3. Define a relationship, say, `Globals in A' in file B to file A, using GlobalAnchor as match fields in both files. Click OK when the following message appears:
    This relationship will not work because the field ``::GlobalAnchor'' cannot be indexed. Proceed anyway?
    The relationship will work to access global fields.
  4. In file B, access the global fields of file A as `Globals in A::GlobalFieldName'.

At times, it may even be hard to decide in which file of a database to put your global fields, because the fields actually concern data that is global to the database as a whole and they do not `naturally' belong to one of the files in the database. This may also apply to shared value lists, calculations, and scripts.

Suggestions to FileMaker Inc.:

  1. Include a direct way of accessing the global fields in another file. This could be similar to accessing the fields of the current record in another file (see Accessing the current record in another file).
  2. Give a more accurate error message for a relationship to a field that cannot be indexed, viz. that it can (only) be used to access global fields.
  3. Consider the introduction of a database framework file, where you can store database-wide globals and where you indicate which files belong to the database. This framework file need not contain records and can help to implement such features as renaming a file within a database, copying a database, setting up consistent database-wide security privileges.
Also see: Renaming or moving files within a database, Copying a database, and Protecting multiple files for reasons to introduce a database framework file.

Plug-in solution: Azium Variables Plug-in (also see TechInfo: 105927 - Azium Variables Plugin)


Calculation field trade-offs

There are two kinds of calculation fields: Stored and Unstored. The trade-offs between these are not very clear from the FileMaker Pro documentation and are less straightforward than a naive explanation might suggest.

The value of a Stored calculation field is stored in the file and is recomputed from its formula whenever one of the fields referenced by the calculation is updated.

Note that a stored calculation can involve only literal constants, standard fields, and stored calculation fields in the current record (not from related records), combined by predefined operators and functions. It tracks updates in referenced fields only. It does not track updates in referenced Status, Design, or External functions (though it may use them).

For example, define calculation fields

and display them on a layout. Whenever the window for the layout is redrawn, the unstored calculation field is recomputed, showing the actual time at redrawing (it is not automatically updated every second). The stored calculation field still shows the time at creation.

Reasons: Dependence on related, global, summary, or unstored calculation fields could give rise to many unnecessary recomputations (also see below). For instance, consider a calculation field C involving a global field G. When G changes, that possibly affects the value of C in all records. Should they all be recalculated to keep them up to date, even if none of the values are actually needed now?

Furthermore, the value of "dynamic" functions may change asynchronously outside the control of FileMaker Pro (e.g. Status(CurrentUserCount)). Keep in mind that the same file can be modified over a network by another user as well. It would be too costly to update all calculation fields that depend on these functions everytime their values change.

The value of an Unstored calculation field is not stored in the file and is always recomputed from its formula whenever it is needed. Such a need for computation arises when an occurrence of the field is displayed on the current layout, or when the field is referenced in another (unstored!) calculation field that is being computed.

Here is a summary of the differences between stored and unstored calculation fields:

Kind of Calculation Field
StoredUnstored
Constraints cannot reference:
  • Unstored calculation fields
  • Summary fields
  • Global fields
  • Related fields
cannot be indexed
Memory usagein filetemporarily in RAM
Compute moment when defined (affects all records in the file);
when a referenced field is updated (affects current record);
notwhen referenced Status, Design, or External functions update
when value is needed,
either for displaying,
or in another calculation
Propagation toward referencing fields toward referenced fields
Time to get value= time for field access in file= time for computation
Time to compute linearly bounded can be exponential
Wasted computation when the triggering field keeps the same value after its update;
when the new value is not needed before next update
when used fields were unchanged
when computation is duplicated

How to choose the most appropriate kind of calculation field?

  1. Does a constraint apply (see table above)?

  2. Does it have to track a Status, Design, or External function, that is, should its value always reflect the current value of the function when the field is accessed?

  3. Do you want to minimize file size (at the expense of, possibly exponential, compute time; see below)?

  4. Do you want to minimize time spent computing (possibly at the expense of increased file size)?

How do stored and unstored calculation fields work?

How all fields depend on, use, or reference each other can be captured in a dependence graph:

Here is a simple example involving seven fields A through G: Here is the corresponding dependence graph (all arrows point downwards):
      G
      |\
      | |
      |/
      F
      |\
      | \
      |  \
      D   E
      |\  |\
      | \ | \
      |  \|  \
      A   B   C
Notes about the structure of the dependence graph:
  1. It cannot have directed cycles: it is a DAG (Directed Acyclic Graph). FileMaker Pro appears to maintain a Field Dependency Table. See TechInfo: An attempt to introduce a cyclic dependence (a circular definition in FMP terminology: calculate A using B, and B using A) results in a warning and fails.

  2. Arrows from stored calculation fields can only go to standard fields and other stored calculation fields. In the example above, if F is stored, then D, E, and C must also be stored (G could be either stored or unstored).

  3. Arrows to unstored calculation fields can only come from other unstored calculation fields. In the example above, if E is unstored, then F and G must also be unstored (D and C could be either stored or unstored).

  4. The preceding points imply that the fields in the dependence graph can be clustered in four groups with limited arrows between them:
                    Unstored calculation fields
                       |              |
                       |              |
        Stored calculation fields     |
                  |                 Global, summary, related fields
                  |
           Standard fields
    

Notes about how computing activity spreads over the dependence graph:
  1. An update in any (standard or stored calculation) field X triggers (secondary) updates in all referencing stored calculation fields (above X). We say that the updates are propagated upward via arrows from stored calculation fields. Secondary updates can trigger tertiary updates, etc.

  2. An update is always propagated upward, even if the value in the triggering field did not change after its update or if the triggered field is not needed (for display or otherwise).

    In the example above, assume all calculation fields are stored and A=B=C=2 (hence, D=E=4, F=4, G=20). Changing A to 0 will trigger an update for D (new value 2), which in turn triggers an update for F. The value of F remains 4. Nevertheless, FileMaker Pro will also trigger an update for G, which is a waste of time because G will not change. (No updates are triggered for C and E.) Changing B instead of A, triggers updates for D, E, F, and G (not C). FileMaker Pro chooses an efficient update order, avoiding unnecessary computations that might occur through branching. For instance, changing B triggers updates for F and G only once (not twice: once via D and once via E)!

    Another change of B will again trigger updates for D, E, F, and G. If the values of these fields have not been inspected since the previous change, then the previous update was a waste of time.

  3. Computation of any unstored calculation field X triggers (secondary) computations in all referenced unstored calculation fields (below X). We say that the computation propagates downward via arrows to unstored calculation fields. Secondary computations can trigger tertiary computations, etc.

  4. A computation for unstored calculations is always propagated downward, even if the lower fields will have the same value as the previous time they were computed. In the example above, assume all calculations are unstored and A=B=C=2. Whenever G is needed, F, D, E, and C are (re)computed from scratch (A and B are simple retrieved from the file). If A, B, and, Status(CurrentUserCount) have not changed since the previous computation of G, this is a waste of time.

    What is much worse, is that each reference to field X in the formula of an unstored calculation field triggers a separate computation, even though that field X was already computed earlier. In the example, computation of G will trigger the computation for F twice! This is a waste of time and can even cause an exponential avalanche of time wasted on duplicating previous efforts.

  5. In general, calculations have no side-effects (such as creation of records). The only possible exception that I am aware of is the use of External functions, which could affect the global state of the system. On the other hand, the value of a calculation can be state dependent: evaluating the same formula twice in succession may yield distinct results, because the global system state changed (not through the calculation itself, but through other causes). Think of Status(CurrentTime) and Status(CurrentUserCount). A consequence of this is lack of referential transparancy. For instance, the two formulas 2*X and X+X need not yield the same result.

Here are some anomalous calculation examples. Consider 21 fields Ai with 0<=i<=20.

  1. A0: Number field; Ai+1 = 2*Ai.

  2. A0: Number field, Ai+1 = Ai + Ai.
    Note that these calculations result in exactly the same values as the preceding example. The difference is that now each calculation formula contains two field references.

  3. A0: Number field, Ai+i = Ai*(1+Ai).
    Same trouble as preceding example. However, in this case the duplicate field reference in the formula cannot be avoided in FileMaker Pro, for lack of a so-called `let' or `where' construct.

  4. A0, A1: Number fields; Ai+2 = Ai+1 + Ai.
    Same trouble as preceding example. However, in this case the exponential branching is not due to duplicate occurrence of the same field.

  5. A0: Number field; Ai+1 = Max(i,Ai).

Suggestions to FileMaker Inc.:

  1. Introduce a so-called `let' construct for use in formulas: Let(x,E,F) evaluates F with each x replaced by the value of E, where E is evaluated only once, even if x occurs more often in F. See anomalous examples 2 and 3: Let(x,Ai,x+x) and Let(x,Ai,x*(1+x)) would no longer suffer from exponential recomputation.
  2. Use a more efficient way of computing unstored calculations, by exploiting the Field Dependency Table to start at the far end and by keeping intermediate results in RAM. This can also restore referential transparancy to some extent.
  3. Introduce an option for calculation fields to be both stored and evaluated on demand when invalidated because of a change in a referenced field. This avoids unnecessary recomputations on account of changes in referenced fields when the new value is not needed. Note that the value in such a field is marked as invalid whenever a referenced field changes, but the new value is not computed until it is acutally needed.


RECORDS


Execution (order) of auto-enter actions

When a new record is created (New Record, Duplicate Record, or through a relationship that allows creation of related records), FileMaker Pro automatically performs the auto-enter and validation actions of all fields of the new record. The FileMaker Pro documentation does not explain in what order these actions are executed and whether the developer has any control over the order.

The execution order is relevant when there are dependencies among the actions. For example, consider fields A, B, and C with the following actions:

It is important that A is initialized before B, and B before C. In general, FileMaker Pro appears to handle dependencies appropriately, using a Field Depdendency Table. See TechInfo: For instance, attempting to introduce a circular dependence results in a warning and fails. The execution order is certainly not (solely) determined by the creation order of the fields. The option "Do not evaluate if all referenced fields are empty", however, also does not seem to influence the order.

It would seem that the following auto-enter options are equivalent:

There is, however, a subtle difference, which can be noticed with Duplicate Record. The auto-enter Data is entered no matter what, but the auto-enter Calculated value is only entered when the field is empty to start with (again, not documented by FileMaker; however, see [GC98, p.101]).

Suggestions to FileMaker Inc.:

  1. Document the execution order of auto-enter and validation actions and possibly provide some control over it. In particular, the option "Do not evaluate if all referenced fields are empty" with auto-enter Calculated value should be supplemented with "Do not evaluate if some referenced field is empty".
  2. Document the exact conditions for triggering of auto-enter actions. In particular, explain that Calculated value is not performed if its target field is not empty (which may happen for duplicated records).

TechInfo:


Memorizing the current record

There are various circumstances where you might need to memorize which record acts as current record, and then later return to it. For instance, when you perform a sort request the first record after sorting will become the new current record, and FileMaker Pro `forgets' which record was the original current record (also see Sorting while keeping the current record).

Here is a solution:

  1. Make sure your file has a field that uniquely identifies each record (a primary key). Let's say this field is named `Key'.
  2. Define a global field `MemorizedRecord' with the same type as the primary key.
  3. Define a relationship `Memorized' from MemorizedRecord to Key (i.e. related file = master file).
  4. To memorize the current record do: Set Field["MemorizedRecord", "Key"].
  5. To restore the current record from memory do: Go to Related Record["Memorized"].


Accessing the current record in another file

There is no direct way to access the fields of the current record in another file. The only way to access data in another file is via a relationship. However, a relationship can be such that there is more than one related record. In that case, the relationship accesses the related record that corresponds to the selected portal row, or, if no portal row is selected, it accesses the first related record (first: according to the relationship's sort order; or creation order if no sort order was specified). Also see Effects of buttons in portals.

A workaround is to set up data in some global fields of the other file and to use them in an external script in that file (which can access the current record). [To be elaborated]

Suggestions to FileMaker Inc.:

  1. Include a direct way to access the fields of the current record from another file. This could be similar to accessing the global fields of another file (see Sharing global fields among files in a database).


DATABASES


Inspecting file links in a database

A file in a databse may refer to other files through file links. It seems impossible in FileMaker Pro to discover the full paths of the target files in such file links. Only the file name (no path identifying a volume and folder) is shown for linked files. When re-specifying a file link, a file-selection dialog box appears, which opens in the most recently accessed folder and not necessarily in the folder in which the currently specified file resides.

Suggestions to FileMaker Inc.:

  1. Include commands, and status/design functions to return the full paths of file links (cf. Status(CurrentFile), DataBaseNames, RelationInfo, which return only the name but not the location, i.e. volume and folder).

TechInfo:


Renaming or moving files within a database

FileMaker Pro offers no command to rename or move a file in a database, while preserving all relevant file links.

NOTE: FileMaker Pro does provide for consistent renaming of fields, layouts, scripts, relationships, and value lists. In these cases, all relevant references to the renamed object appear to be consistently updated throughout all files in a database. Most likely, this is accomplished through an internal closed naming scheme based on creation order, which is independent of the open names observed by the user.

When a file in a database is renamed (but not moved to another folder), one needs to update all file links to that file (these may reside in other files and in the file itself). When a file in a database is moved (to another folder), one also needs to update all file links emanating from that file.

Updating all file links to and/or from a given file is awkward, since there are various kinds of file links and these are not efficiently identifiable. I see no other solution than carefully going through all scripts, relationships, and value lists, opening each one to see if there is a file link that needs updating.

NOTE: When FileMaker Pro detects that the target file of a file link is missing (e.g. because you renamed or moved the file), it will automatically open a dialog box enabling you to locate the file. This automatic interactive on-demand update mechanism remedies to some extent the problem of updating file links after renaming. However, this mechanism has two drawbacks:
  1. When you rename a file and then want to deliver the database to a customer, you want to make sure that all file links have been updated. It is not clear how to do this convincingly through the mechanism. You still need to activate each file link systematically.
  2. If by chance a file link is traced to the wrong file, instead of a missing file (e.g. because you swapped the names of two files), then FileMaker Pro provides no opportunity to update the link on the fly (although it may detect that something is wrong).

Suggestions to FileMaker Inc.:

  1. Include a File->Rename/Move command to rename or move a file in a database, while preserving all relevant file links. This requires identifying all the files in a database.
  2. Include a command to find all the files that link to a given file. These may need updating when the given file is renamed. Useful, in case you do not want to update all file links.
  3. Include a command to find all the files that a given file links to. This simplifies verification of file links. These may need updating when the given file is moved with respect to the other files in a database.
  4. Consider the introduction of a database framework file.
Also see Sharing global fields among files in a database and Inspecting file links.

TechInfo:


Copying a database

FileMaker Pro offers various options when saving a copy of a file (See File->Save-a-Copy-As command). Here are four reasons to save a copy:

There are two problems when copying a database:
  1. If a database consists of many files, it is inconvenient to save a copy of each file menu-ally (pun intended).
  2. You need to ensure that file links are preserved.

Concerning the first problem: You can script the Save-a-Copy-As command, but it requires a constant file name, e.g. it cannot be taken from a field. Alternatively, you can copy (a folder of) files outside FileMaker Pro, e.g. using the Finder on a Mac.

NOTE: Before copying files outside FileMaker Pro, it is recommended that you first quit the FileMaker Pro application. If you don't, you may get damaged copies when copying files that are currently open, or that recently have been open. When opening such a damaged copy, FileMaker Pro warns you (once) and automatically attempts to repair the file. It is not clear what risks are involved.

Concerning the second problem: It seems that FileMaker Pro stores file links as relative paths and not as absolute paths. Copying a set of related files seems to preserve the embedded file links automatically. However, it is nearly impossible to verify file links (see Inspecting file links). Furthermore, some file links may be internal and others external, e.g. when a database stores some statistics in a fixed global file. That is, you have a problem when copying only some of the linked files and not others.

This problem is similar to that of copying a collection of cells in a spread sheet: cells may refer to other cells; some references need updating, others not.

Suggestions to FileMaker Inc.:

  1. Include a way of copying a database, such that all file links are properly preserved. Note that being internal or external is defined by the set, and not by an individual file. Thus, the Save-a-Copy-As command is not appropriate.
  2. Consider the introduction of a database framework file.

Also see Renaming or moving a file in a database and Sharing global fields among files in a database.

TechInfo:


Organizing the files in a database

A database often consists of several files. If you ever want to bind your database with the FileMaker Pro 4 Developer Edition into a stand-alone solution, then all the files should be in a single `flat' folder. The binder will not bind files that are in subfolders!

Also note that it is not easy to reorganize the files in a database: see Renaming or moving a file in a database.

Suggestions to FileMaker Inc.:

  1. Include a way of binding a database, such that files can be organized in subfolders. This is yet another reason for introducing a database framework file.


Updating the functionality of a database

Each file of an FMP database contains both data and functionality. For many databases, the developer of the functionality and the supplier and user of the data are distinct parties. I will refer to the latter party as customer. It is not uncommon that the same database solution is used by various customers, each in their own environment. These databases have the same functionality, but their data contents differ.

The developer faces the problem of updating the functionality of a database without affecting the customer's data. This happens whenever bugs are fixed or functionality is enhanced (cosmetic changes to layouts, new scripts, etc.).

Unfortunately, data and functionality are tightly married in FMP databases. You cannot simply replace the functionality in an existing FMP database by new functionality, and keep the data as it is.

A naive approach consists of the following steps:

  1. the developer deletes all records from the new database and delivers an empty new database to her customer;
  2. the customer exports all data from all files in his current database; suggestion: for each file do Browse Mode, Find All, Unsort, Export to FMP format, saving the export results in a separate folder (unsorting is necessary to retain the same creation order in the updated database);
  3. the customer deletes the old database (retaining a backup) and replaces it by the empty new database;
  4. the customer imports all his data (exported earlier) into the empty new database
The developer would do well by providing appropriate scripts for these steps.

However, there are several pitfalls in this approach:

Note that the following information is not retained across FMP sessions, and therefore cannot be expected to be maintained through a database functionality update: The bottom line is that a database contains other data than just the field contents of all the records. The developer would like to have complete control over which information is under control of the customer and which is not. This is where access privileges play a role (the developer keeps master access to herself), but there is more to it. The information that is under customer control needs to be retained across functionality updates as much as possible. This is not easy in FMP.


LAYOUTS


Going to a layout in another file

Suppose you want a button in a layout of file Foo that, when clicked, takes you to layout X in file Bar. This can be accomplished by writing two scripts:

  1. In file Foo, write a script "Go to layout X of file Bar", consisting of one script step:
    1. Perform external script: "Go to layout X", in file Bar.
    Do not include any script steps after this one, or else you may not end up in file Bar, but simply return to the current layout of file Foo.
  2. In file Bar, write a script "Go to layout X", consisting of one script step:
    1. Go to Layout: X.
    This script may include additional script steps.
Link the script "Go to layout X in file Bar" to your button in file Foo. This script is needed because buttons cannot be linked directly to external scripts.

Suggestions to FileMaker Inc.:

  1. Include a script step to go to a layout in another file (to avoid the proliferation of small trivial scripts).


Selecting layout tools

There is a preference to `always lock layout tools': see Edit->Preferences->Application->Layout. When not always locked, a layout tool can be selected and locked by double-clicking the tool, instead of single-clicking it.

Suggestions to FileMaker Inc.:

  1. When layout tools are always locked, interpret a double-click selection as a temporary (unlocked) selection. That is, the preference `always lock layout tools' interchanges the meaning of single-click and double-click selection.


Specifying fields formatted as button

In Layout mode, the attributes of a field occurrence on a layout can be modified

The dialog box summoned by double clicking depends on whether or not the field occurrence is also formatted as a button: The Specify Button dialog can always be summoned by Format->Button.... However, the Specify Field dialog can never be summoned through the Format menu. When the field occurrence is formatted as a button, the Specify Field dialog can only be summoned through the contextual menu. Furthermore, Specify Field occurs in the contextual menu only if the field occurrence is formatted as a button.

Suggestions to FileMaker Inc.:

  1. Make access to the Specify Field dialog less confusing. (Double clicking or a contextual menu should never be the only ways to access a dialog box.) Put a Specify Field... item in the Format menu, and possibly show Specify Field... always in the contextual menu.


Read-only fields

Each occurrence of a field on a layout can be formatted (in Layout mode using Format->Field Format) such that the user is or is not allowed entry into the field. Denying entry into a field occurrence has several consequences:

  1. The user cannot modify data in the field (through this occurrence).
  2. The user cannot copy (Edit->Copy) data from the field.
  3. The user cannot scroll the data in the field (even when it is given a scroll bar).
  4. The user cannot search the data cannot be searched in Find mode.
A script can still affect the data in fields that are denied user entrance.

A field can also be protected against modification completely, that is, applying to every occurrence on every layout (via File->Define->Fields...->Options...->Auto Enter, `Prohibit modification of value', provided some value is auto-entered). Such values can be copied, scrolled, and searched, but cannot modified at all, not even by scripts.

In some situations it is desirable to allow a user to copy and scroll the data in a field, and at the same time to prohibit user modification of the data in some layouts but not all layouts. For example, consider a database with help information in a separate file, consisting of records with search keys and read-only help text. The text should be scrollable because it may be longer than the field window. The text should be modifiable for initial entrance and maintenance.

Workaround: To make the information in field `F' accessible (for copying, scrolling, and searching) and read-only (not modifiable), introduce a field `F ReadOnly' that is an Unstored calculation and has the same type and value as `F' (that is, enter F as formula for `F ReadOnly').

Suggestions to FileMaker Inc.:

  1. Include a field formatting option `read-only' that allows entry into a field, but prohibits modification of the field's data (option settable per occurrence). In particular, a read-only field can be copied, scrolled, and searched.


Changing layout options of multiple fields

In Layout mode, it is possible to change attributes of multiple layout elements in one go, by selecting all elements and applying the option. For example, to change the formatting properties of several fields, select all these fields and do Format->Field Format.... However, be warned that you will affect ALL options that are settable through that particular dialog. For instance, if you want to disallow entry into several fields, without affecting whether scroll bars are included or not, then you should modify the attribute of each field individually.

Suggestions to FileMaker Inc.:

  1. When attributes are set for multiple fields in a Layout-mode dialog box, allow certain attributes to remain unaffected (such as in AppleWorks, where a checkbox contains a dash to indicate that it is not affected).


Sharing groups of objects on layouts

Impossible, but desirable.


SORTING


Saving and editing a sort request

The most recently performed sort request can be inspected, modified, and re-performed by the Mode->Sort command in Browse mode. If you need to deal with more than one sort request, it is best to save each sort request in a separate script:

  1. Set up the sort request using the Mode->Sort command in Browse mode.
  2. Using ScriptMaker, create a script (recommended name: "Sort on <mnemonic>") consisting of just one Sort step, checking both "Restore sort order" and "Perform without dialog".
To inspect or edit this scripted sort request:
  1. Perform the sort script (this makes the sort request available for the next step, though this is not documented explicitly by FileMaker).
  2. Use the Mode->Sort command in Browse mode to inspect or edit the sort request.
  3. When the sort request has been changed, the script must be updated:
    1. Using ScriptMaker, edit the script.
    2. Do not change anything, just click OK.
    3. In the subsequent dialog box, click the "Replace sort order" radio button,
    4. Click OK.
NOTE: It is recommended to use Sort steps involving restored sort requests only as the sole step in sort scripts as described above. These sort scripts can be performed as subscripts in other scripts. Reasons:
  1. It is much harder to inspect and edit stored sort requests in arbitrary scripts than in dedicated sort scripts. In order to inspect and edit a stored sort request, it must be performed. This may be difficult to accomplish in view of conditional steps and it involves performing other script steps, which may have undesirable side-effects.
  2. It is much harder to maintain a sort request that is duplicated in various scripts.

Suggestions to FileMaker Inc.:

  1. Confirm that execution of a Sort script step that restores a stored sort request, indeed makes this sort request available for inspection and editing through Mode->Sort.
  2. Introduce script steps to define sort requests in scripts (find requests can be defined in scripts, but sort requests not).

Suggestions to FileMaker Inc.:

  1. Include a more convenient way to modify the sort request stored in a script.

Also see Combining find requests.


Reversing the (sort) order of records

FileMaker Pro has no command to reverse the presentation order of the records in the found set. Here are two situations where a Reverse Order command would come in handy:

  1. Present the records in reverse order of creation (=Unsorted).
    Solution without Reverse Order command:
    Include a field that auto-enters a serial number upon creation, and use this field as a descending sort key in a sort request.
    Solution with Reverse Order command:
    First Unsort the records, then invoke Reverse Order.
  2. Given a scripted sort request, present records in reverse order of that sort.
    Solution without Reverse Order command:
    Create a new sort request derived from the given sort request by reversing the order of each of its sort keys. Maintenance of such related sort requests is a burden, and for custom orders based on a value list you also must separately define the value list in reverse order.
    Solution with Reverse Order command:
    First perform the scripted sort request, then invoke Reverse Order.

Suggestions to FileMaker Inc.:

  1. Include a Reverse Order command to reverse the presentation order of records: the last record coming first. Ascending becomes descending, descending becomes ascending.


Combining sort requests

When FileMaker Pro performs a sort request, it puts records with the same (possibly composite) sort key in the order they were created, and not in the order they happened to be before the sort request was invoked.

A sorting algorithm that preserves the original order for records with equal sort key, is called `stable'. The FileMaker Pro sorting algorithm appears to be stable, but it always starts from the unsorted order when executing a sort request (though I have not seen this explained in the documentation).

Suggestions to FileMaker Inc.:

  1. Ensure that sorting is indeed stable and introduce an option for the initial order (i.e. the default order for records with equal key) of a sort request:
    1. the unsorted (creation) order, or
    2. the current order of the records.
  2. Allow a sort request with an empty list of sort keys. This should be equivalent to setting the default order, that is, to Unsort if the default order is the creation order, and to doing nothing if the default order is the current order.

Here is an application of using the current order as default order:


`Locking' a sort request

When a record is created or changed, the sort status goes from Sorted to Semi-sorted. It is maintained under Delete Record. Note that order of related records in a portal based on a sorted relationship is updated whenever a related record is changed or added.

The only way to mimic locking is to perform the desired sort request everytime that the found set changes in such a way that it becomes Semi-sorted. The two main disadvantages of this workaround are:

  1. the trouble of locating all the places where the sort request needs to be invoked, and
  2. the loss in performance because a complete resort is done where less work would suffice.

Suggestions to FileMaker Inc.:

  1. Introduce an option to lock a sort request, such that the found set remains sorted according to that request even when it changes (New Record, Find Omitted, etc.). E.g. each newly created or changed record is then (re)sorted into the found set, maintaining the sort order. This should work similarly to records displayed in a portal through a sorted relationship.


Sorting while keeping the current record

When FileMaker Pro performs a sort request, the first record (after sorting) will become the new current record, and the identity of the original current record (before sorting) is `forgotten'. To sort the found set while keeping the same record as current record do:

  1. Memorize the current record.
  2. Sort.
  3. Restore the current record from memory.
See Memorizing the current record for memorizing and restoring the current record.

Suggestions to FileMaker Inc.:

  1. Include an option to maintain the current record when sorting (instead of going to the first record, which can easily be accomplished by a script step anyway).


FINDING


Saving and editing find requests

The most recently performed find requests can be inspected, modified, and re-performed by the Select->Modify Last Find command in Browse mode. If you need to deal with more than one set of find requests, it is best to save each set in a separate script:

  1. Set up the find requests using the Mode->Find command.
  2. Using ScriptMaker, create a script (recommended name: "Find <mnemonic>") consisting of just one Perform Find step, checking "Restore find requests".
To inspect or edit this scripted set of find requests:
  1. Perform the find script (this makes the find requests available for the next step, though this is not documented explicitly by FileMaker).
  2. Use the Select->Modify Last Find command in Browse mode to inspect or edit the find requests.
  3. When the find requests have been changed, the script must be updated:
    1. Using ScriptMaker, edit the script.
    2. Do not change anything, just click OK.
    3. In the subsequent dialog box, click the "Replace find requests" radio button,
    4. Click OK.
NOTE: It is recommended to use Perform Find steps involving restored find requests only as the sole step in find scripts as described above. These find scripts can be performed as subscripts in other scripts. Reasons:
  1. It is much harder to inspect and edit stored find requests in arbitrary scripts than in dedicated find scripts. In order to inspect and edit the stored find requests, they must be performed. This may be difficult to accomplish in view of conditional steps and it involves performing other script steps, which may have undesirable side-effects.
  2. It is much harder to maintain find requests that are duplicated in various scripts.

An alternative kind of find script is to embed the definition of the find requests inside the script:

  1. Enter Find Mode
  2. Set Field[field,calc] for each field condition
  3. New Record/Request for each additional find request
  4. Perform Find[], i.e. without restoring

Suggestions to FileMaker Inc.:

  1. Include a more convenient way to modify the set of find requests stored in a script.

Also see Combining sort requests.


Dealing with complex find requests

The field conditions that make up a find request are limited to those expressible in a restricted "language", which includes some forms of pattern matching and order relations. For example, in field f, the expression C1..C2 with constants C1 and C2, expresses the condition C1<=f<=C2.

To select records satisfying a condition that is not directly expressible in field conditions, you need to introduce an appropriate calculation field. For example, given fields x and y, selecting all records with x<y requires the introduction of an auxiliary calculation field, such as ComparisonAux(Number,Unstored)=x<y. A find request can be issued to select all records with ComparisonAux=1.

A more general approach is to introduce an auxiliary field named ComplexFind and to set it through a Replace command with the calculation x<y. The find request can then be based on ComplexFind. This field can be used for other complex find requests as well.


Combining find requests

When FileMaker Pro performs find requests, it always starts from the complete set of records present in the file, and from those it selects all records that satisfy the find requests. It is not possible to combine the result somehow with the found set that was active before the find request.

Suggestions to FileMaker Inc.:

  1. Introduce an option for interaction of find requests with the previous found set:
    1. ignore the previous found set (i.e., new found set = new selected set), or
    2. add the newly selected records to original found set (i.e., new found set = original found set united with new selected set), or
    3. select only the newly selected records that are also in the orginal found set (i.e., new found set = original found set intersected with new selected set)
When several find requests are put together (using Mode->New Request), they are combined according to the second proposed option. In my opinion it seems natural to a user that sequential performance of two find requests combines according to the third option: find some records and within that set find the ones that satisfy a second criterion as well. Note that the first option is equal to the second option with an initially empty found set, and also to the third option with an initially full found set (with all records).


Extending the found set

The found set can easily be made smaller by omitting some records through Select->Omit or Select->Omit Multiple. The found set can be made larger by judicious use of Select->Find Omitted that complements the found set:

  1. Given a found set to be extended.
  2. Select->Find Omitted to make the omitted records available for manipulation.
  3. Select->Omit (or Select->Omit Multiple) those records that you wish to include in the extended found set as well. They are now added to the omitted set, which is the original found set.
  4. Select->Find Omitted again to make all omitted records available as new found set.
When the complement of the original found set is large, it is tempting to use a find request to select the record(s) to be added to the found set. However, this does not work, because executed find requests deliver their result as the found set, thereby destroying the original found set that was to be extended. The only solution that I see is to record the original found set in an auxiliary field, or ...

Also see Combining find requests.


Finding the current record

To construct a found set consisting of only the current record (i.e. to omit all others) do

  1. Find All
  2. Omit
  3. Find Omitted
If you have a field that uniquely identifies each record (a primary key), then you can also do the following:
  1. Define a relationship Self from the primary key to itself, i.e. related file = master file.
  2. Use the Go to Related Record[Show, "Self"] script step, showing only related records.

Suggestions to FileMaker Inc.:

  1. Confirm in the documentation that Find All leaves the choice of current record invariant.


CALCULATIONS


Sharing calculations

Impossible, but desirable (with parameters). Consider, for instance, the Age(StartDate,EndDate) function discussed in Age Computation. You cannot define it yourself in this way, certainly not global to a database.


Date and time functions

A number of obvious date and time functions is missing, such as the number of seconds between two times, or the number of years between two dates (age). Fortunately, some of these functions can be obtained through features not documented in the FMP User's Guide:

Note that this is similar to AppleWorks (formerly ClarisWorks), where it is documented (see `Date and time functions' in the on-line help) that time T on date D is stored as a serial number of the form Date(D)+Time(T), where

One should be careful to use undocumented FMP features. Though it seems unlikely, in future versions they might no longer be supported as described here. Below are some examples.

Another useful feature (documented: see Date function) is that in an expression

Date (Month, Day, Year)
if the date does not exist, any excess is carried over to the next higher term. For instance, the date for the first day of next month can be obtained by
Date (Month(Today)+1, 1, Year(Today))
If necessary, the year is incremented (day/month are also adjusted if the supplied day does not exist in the resulting month). The same holds for any deficit (though this is not explicitly documented): the last day of the current month can be obtained by
Date (Month(Today)+1, 0, Year(Today))
The Time function similarly carries excess seconds and minutes into the next higher unit, and hours can go beyond 24.

Plug-in: Azium Date & Time Plug-in
TechInfo:

Time difference

Problem: Given the start time and end time for your daily run, compute the duration and display this in the format Hours:Seconds. Poor man's solution:
  1. Define fields `StartTime' and `EndTime' of type Time.
  2. Define field `Duration' as a Calculation of type Time:
    EndTime - StartTime
  3. Set the field `Duration' to format time as hhmm.
A complication arises if StartTime > EndTime, because you run at midnight using a 24-hour clock, or you use a 12-hour clock and run at one o'clock. To cover those cases as well you have to add 24*60*60 or 12*60*60 for negative results respectively. This only helps for runs that are no longer than 24 or 12 hours respectively.

It is unfortunate that FileMaker Pro does not have a single type for recording `timestamps' that include both the types Date and Time (as with AppleWorks), that is, a type with good longterm and with good shortterm resolution.

Age computation

Problem: Given a start date (birth) and end date (death), compute the age in days. Solution:
  1. Define fields `StartDate' and `EndDate' of type Date.
  2. Define field `ElapsedDays' as a Calculation of type Number by
    EndDate - StartDate
However, there are still some functions that are hard to define, such as the number of complete years (age) between StartDate and EndDate. For example, one might try to define a calculation field `Age' of type Number by
Year(EndDate - StartDate) - 1
but this is not always correct. (Note that the `minus one' is indeed needed, because the origin (0) was (unfortunately) chosen at date 0-Jan-0001 and not 0-Jan-0000.) The trouble is that the Year function is applied to some meaningless date in an "early" century (derived from the number of days between StartDate and EndDate). In particular, leap years wreak havoc. A case that exhibits the trouble is where the actual difference (age) obviously is 4 years, but where the attempted calculation `Year(EndDate - StartDate) - 1' erroneously yields 3. Also the case with StartData=EndDate is problematic, because EndDate-StartDate=0 and 0 does not convert to a proper date.

Instead, you have to resort to something ugly like Age(Number)=

Year (EndDate) - Year (StartDate) -
  If ((Month (EndDate) < Month (StartDate)) or
        ((Month (EndDate) = Month (StartDate)) and (Day (EndDate) < Day (StartDate))),
      1,
      0
     )
(Unfortunately, the user cannot define such an Age function in general, see Sharing calculations.) The following calculation based on DayOfYear is INCORRECT (because of leap years):
Year (EndDate) - Year (StartDate) -
  If (DayOfYear (EndDate) < DayOfYear (StartDate),
      1,
      0
     )
Counterexample: The age between 1-Mar-1995, and 29-Feb-1996, and also between 2-Mar-1995 and 1-Mar-1996 is just one day just short of one year, even though these days have the same DayOfYear. The solution published by FileMaker Inc. in TechInfo: 101266 - Calculation for Elapsed Years, Months and Days / Person's Age is barely correct, because a slight variation is INCORRECT:
Year (EndDate) - Year (StartDate) -
  If (Date (Month (EndDate), Day (EndDate), Year (StartDate)) < StartDate,
      1,
      0
     )

The distance between two dates can readily be expressed in terms of days or in terms of complete years. However, expressing the date distance in terms of years, months, and days or in terms of complete months is problematic, because of the varying lengths of months.

For instance, what is the distance between 31-Jan-1990 and 1-Mar-1991? Well, one complete year and some more, but how much more? One month? Well, yes, because all of Feb-1991 is between the two dates, and even 1+1=2 further days. On the other hand, one month after 31-Jan-1991 gets you to 31-Feb-1991, and that is not a meaningful date. In fact, FileMaker Pro interprets Date(Feb, 31, 1991) as 2-Mar-1991, falling after 1-Mar-1991. So, maybe there is not a complete month between 31-Jan-1991 and 1-Mar-1991, but just 30 days (1991 not being a leapyear).

And how about the distance between 28-Feb-1983 and 28-Mar-1983? Zero complete years and exactly one month seems right, but zero years, zero months, and 1+27=28 days is also correct.

Mathematically, the problem can be expressed as follows. Consider the operation on date D defined by

D + [m, d, y] = Date ( Month(D)+m, Day(D)+d, Year(D)+y )
Then the order of applying several +[m,d,y] is relevant: This also raises the question in what order the expression Date(m, d, y) is converted to a proper date when parameters m, d, and/or y are out of range. I would assume that first the months are brought into range by adjusting the years, and then the days are brought into range by adjusting the months (which may require another adjustment of years).

Suggestions to FileMaker Inc.:

  1. Document the conversion of times and dates to and from numbers in terms of seconds and days respectively.
  2. Include a function Age(StartDate, EndDate) to compute the age at EndDate with respect to StartDate.
  3. Unify the types Date and Time (as in AppleWorks) to give one type (DateTime?) for timestamping with good longterm and shortterm resolution.


SCRIPTS


Copying (parts of) scripts

Impossible, but very desirable. Try your luck with either of

These utilities offer limited script manipulation.

Reminder: You can print the scripts defined in a file from the print dialog box.


Parameterizing scripts

Impossible, but very desirable. Use global fields instead. No concurrency or re-entry.

Also see: Sharing global fields among files.


Nesting If steps in a script

In calculations, a choice between more than two alternatives can easily be programmed through the logical function Case. In scripts, however, the Case construct is not possible. Instead, one needs to fall back on nested If script steps:

  If[``StartDate < EndDate'']
    ...
  Else
    If[``StartDate > EndDate'']
      ..
    Else
      Comment[``StartDate = EndDate'']
      ...
    EndIf
  EndIf

Suggestions to FileMaker Inc.:

  1. Include an ElseIf script step to allow for nicely cascaded (instead of nested) conditional execution.
Also see: Selecting an action using a value list


Returning from an external script

Too complicated: use a global field `FileB4' and special script with several steps for each file to return to. Or cascade scripts.


Committing a record in a script

In particular through a portal/relationship, without `losing' the record as selected, forcing an update of the sort order.


BUTTONS


Resuming suspended scripts via buttons

An activated script S can be suspended or paused, and later resumed:

Suspension (including pausing) can be recursively nested. Some button actions deal with suspended scripts:

Suggestions to FileMaker Inc.:

  1. Explain the Perform Script options for buttons.


RELATIONSHIPS


Entity-Relationship model and FileMake Pro

In the Entity-Relationship (E/R) model (also see [Har98, GC98]), one distinghuishes entity sets, attributes, and relationships. The latter should not to be confused with FileMaker Pro's relationships. Below I explain how to realize E/R relationships in terms of FMP relationships.

An E/R relationship R from entity set S to entity set T is said to be:

My E/R-like diagramming conventions: For example, in an E/R model of a company, the following entity sets can appear: Among these entities, there can be such E/R relationships as: Here is an E/R-like diagram representing the situation:
           ---is-placed-by-*<          >--appears-in-*<
  Customer  -*-has-placed--<  Order     >*-contains--<  Product
+----------+                +----------+              +-------------+
| Name     |                | Date     |              | Description |
+----------+                +----------+              +-------------+
| Address  |                | Time     |              | Price       |
+----------+                +----------+              +-------------+
How many times a product appears in an order is not captured in this model.

It is fairly straightforward to realize an E/R model in FileMaker Pro (also see TechInfo: 102582 - Going Relational):

For each ... in E/R Model Define in FileMaker Pro a ...
Entity set S File S
Entity Record with a unique-valued field (the primary key).
Attribute A Field A.
One-one relationship S-*-R---T Relationship R relating the key in S to the key in T (one-one).
Often better: Merge the fields of files S and T into one file ST.
Many-one relationship S>*-R---T Fresh field fRT in file S (the foreign key).
For each record in S, make fRT = key of related record in T.
Relationship R relating fRT in S to the key in T (many-one).
One-many relationship S-*-R--<T Fresh field fSR in file T (the foreign key).
For each record in T, make fSR = key of related record in S.
Relationship R relating the key in S to fSR in T (one-many).
Many-many relationship S>*-R--<T Fresh file SRT with fields fS and fT and a record for each related pair.
Relationship RT relating the key in S to fS in SRT (one-many).
Relationship SR relating fT in SRT to the key in T (many-one).
NOTES:
  1. In FileMaker Pro, a primary key must be a single field, in order to use it as match field in an FMP relationship. You can use as primary key a Stored calculation field that concatenates (via &) several other fields.
  2. The FMP relationships introduced through the scheme above always have a unique-valued primary key field as one of the match fields. FileMaker Pro allows relationships between arbitrary (non-key) fields, but it is not recommended to do so.
  3. In FileMaker Pro, a relationship between two files is stored in the master file (with the from-entities) and it can only be used in the master file. To use a relationship in the related file, one needs to define the converse relationship there as well.
I will use diagrams similar to E/R diagrams for illustrating the structure of a FileMaker Pro database:
  File1                       File2
+--------+                  +--------+
| FieldA |-*-Relationship--<| FieldX |
+--------+                  +--------+
| ...    |                  | ...    |
+--------+                  +--------+
| FieldC |                  | FieldZ |
+--------+                  +--------+
The relationship is stored in the from-file, marked with the asterisk (*). The field attached with a single line (-) holds the primary key. The field attached with the fanout symbol (> or <) holds the foreign key.

In FileMaker Pro, the customer-order-product database can be set up as follows:

  Customer                 Order                  Item                    Product
+----------+             +----------+           +---------+             +-------------+
| Number   |-*-Orders    | Number   |-*-Items--<| Order   |         +---| Number      |
+----------+        |    +----------+           +---------+         |   +-------------+
| Name     |        |    | Date     |           | Product |>*-Product   | Description |
+----------+        |    +----------+           +---------+             +-------------+
| Address  |        |    | Time     |           | Count   |             | Price       |
+----------+        |    +----------+           +---------+             +-------------+
                    +---<| Customer |
                         +----------+
Explanation:
  1. Each of the entity sets customer, order, and product is realized in a separate file with a field for each attribute and with a new field Number acting as key. Even though the customer's name, the order's date-time, and the product's description might be unique, it is often more convenient to introduce your own key fields. I have chosen for Number fields here.

  2. The many-one E/R relationship "has placed" from customer to order is realized through the FMP relationship Orders defined in file Customer: it relates the field Number in file Customer to the new field Customer of file Order. With each order you must store the number of the customer who placed the order in the new field Customer.
    [When more complicated models are realized, longer names may be called for, especially if there are more relationships between the same entity sets. Instead of naming the relationship Orders, it could be named HasPlacedOrders; instead of naming the new field Customer, it could be named CustomerHasPlaced.]

  3. The many-many E/R relationship "contains" from order to product is realized through the new file Item and two FMP relationships: Again, longer names may be helpful: e.g. file OrderedProduct instead of Item.

  4. Any converse relations that are needed should be defined separately. For example, to access the customer of an order (cf. "is-placed-by"), you define relationship Customer in file Order relating field Customer in Order to field Number in Customer.

TechInfo:


Chaining relationships

Consider the following database:

From file F you want to access file H via the sequential composition of relationships R and S. How to do that? The answer depends on what you want to do with the composed relationship:
  1. Access fields in H (cf. notation `RelationName::FieldName').
  2. Show or select related records in H (cf. Go to Related Record script step).
And it also depends on the kinds of relationships involved: many-one, one-many. In principle (see Entity-Relationship model and FileMaker Pro), there are four cases to be covered:
  1. F >*-R--- G >*-S--- H (many-one, many-one)
  2. F -*-R--< G -*-S--< H (one-many, one-many)
  3. F -*-R--< G >*-S--- H (one-many, many-one; e.g. realizing many-many)
  4. F >*-R--- G -*-S--< H (many-one, one-many; uncommon composition and not further treated)

Chaining many-one (functional) relationships

Here is a more specific example. Assume you have

Here is an E/R-like diagram representing the situation:
  Regions                   Customers                    Orders
+---------+               +-----------+                +------------+
| Number  |---+           | Number    |---+            | Number     |
+---------+   |           +-----------+   |            +------------+
| Name    |   |           | Name      |   |            | Date       |
+---------+   |           +-----------+   |            +------------+
| Extra   |   ResidesIn-*<| RegionNr  |   IsPlacedBy-*<| CustomerNr |
+---------+               +-----------+                +------------+
The name of the customer who placed an order can be displayed directly on a layout for Orders through IsPlacedBy::Name, i.e., as a single field of a related record.

The problem is to display, on a layout for Orders, the name (and possibly extra info) of the region where the customer resides who placed the order. In terms of the functions realized by the many-one relationships, this name is simply obtained by sequential function composition: first apply IsPlaceBy to the order, and, to the result, then apply ResidesIn. In FileMaker Pro, however, one cannot chain relationships to display, on a layout in Orders, the doubly-related field IsPlacedBy::ResidesIn::Name.

There are two ways to mimic chained many-one relationships for field access in FileMaker Pro:

  1. [Field inspection only] Carry each of the relevant field values from Regions to Customers by means of auxiliary calculation fields using the relationship ResidesIn, and inspect the auxiliary fields through the relationship IsPlacedBy:
    1. Define a calculation field named RegionName in Customers by RegionName=ResidesIn::Name. The calculation field can be Unstored.
    2. Inspect the region's name in Orders through IsPlacedBy::RegionName.
    Repeat these two steps for every field needed from Regions, e.g. define RegionExtra=ResidesIn::Extra in Customers and inspect IsPlaceBy::RegionExtra in Orders.

  2. [Field inspection and modification] Carry the RegionNr from Customers to Orders by means of an auxiliary calculation field and use it through an auxiliary relationship from Orders to Regions:
    1. Define a calculation field named RegionNr in Orders by RegionNr=IsPlacedBy::RegionNr. The calculation field can be Unstored, but also see Chaining one-many relationships.
    2. Define a relationship IsPlacedBy-ResidesIn in Orders, matching its (calculated) RegionNr to Number in Regions.
    3. Access the region's name in Orders through IsPlacedBy-ResidesIn::RegionName.
    Repeat the third step for every field needed from Regions, e.g. access the region's extra info through IsPlaceBy-ResidesIn::Extra in Orders.

The second approach requires an additional relationship, but works with only one additional calculation field in Customers (independent of the number of fields needed from Regions), and can be used both for inspection and modification.

The other use of relationships is to select related records. For example, in a script performed from a button on a layout for file Orders, you may want to go to the (unique) record of the related region in file Regions. There are two ways to mimic chained many-one relationships for related record selection:

  1. Chain Go to Related Record script steps:

  2. Apply the second approach for accessing fields through a chained relationship (see second approch above), and use the newly defined relationship IsPlaycedBy-ResidesIn in a Go to Related Record script step (possibly directly linked to a button) in file Orders.

Also see: Chaining one-many relationships.

Chaining one-many relationships

Consider the situation of the converse relations for the example above, as illustrated in the following E/R-like diagram:

  Regions                      Customers                   Orders
+---------+                  +-----------+               +------------+
| Number  |-*-+              | Number    |-*-+           | Number     |
+---------+   |              +-----------+   |           +------------+
| Name    |   |              | Name      |   |           | Date       |
+---------+   |              +-----------+   |           +------------+
| Extra   |   Accommodates--<| RegionNr  |   HasPlaced--<| CustomerNr |
+---------+                  +-----------+               +------------+
To display, in a portal on a layout for Regions, all customers residing in a region, we use the converse relationship of ResidesIn, say Accommodates, in file Regions. That is, relationship Accommodates relates Number in Regions to RegionNr in Customers, and it is a one-many relationship.

Similarly, the converse relationship of IsPlacedBy, say HasPlaced, is stored in file Customers and it is a one-many relationship. Relationship HasPlaced relates Number in Customers to CustomerNr in Orders. It can be used to display, in a portal on a layout for Customers, all orders placed by a customer.

Now comes the problem of chaining these one-many relationships: How to display, in a portal on a layout in Regions, all orders placed in that region? In FileMaker Pro, one cannot base a portal on a chained relationship such as Accommodates::HasPlaced::.

Here is one way to mimic chained one-many relationships for field access (compare to second approach in Chaining many-one relationships).

  1. [Field inspection and modification] Carry the RegionNr from Customers to Orders by means of an auxiliary calculation field and use it through an auxiliary relationship from Regions to Orders:
    1. Define a calculation field named RegionNr in Orders by RegionNr=IsPlacedBy::RegionNr. The calculation field must be Stored, for otherwise it cannot be indexed and, hence, not the target of a relationship. (If this is not allowed, you can alternatively introduce a regular field and have its value auto-entered via a Lookup.)
    2. Define a relationship Accommodates-HasPlaced in Regions, matching its Number to the (calculated) RegionNr in Orders. Specify an apporpriate sort request to order the related records.
    3. Access all the orders placed from this region in a portal on a layout in file Regions using relationship Accommodates-HasPlaced.
Note that Stored calculation fields are not always automatically updated when their referenced fields change (see Calculation field trade-offs).

The solution to mimic chained one-many relationships for selection of related records is to use the composite relationship for field access (explained above: Accommodates-HasPlaced) in a Go to Related Record.

Chaining one-many and many-one relationships

As explained in Entity-Relationship model and FileMaker Pro, a many-many E/R relationship is realized in FileMaker Pro by an auxiliary file and two relationships, one of them one-many, the other many-one. Here is the E/R-like diagram for the example used above:

  Order                  Item                    Product
+----------+           +---------+             +-------------+
| Number   |-*-Items--<| Order   |         +---| Number      |
+----------+           +---------+         |   +-------------+
| Date     |           | Product |>*-Product   | Description |
+----------+           +---------+             +-------------+
| Time     |           | Count   |             | Price       |
+----------+           +---------+             +-------------+
The E/R relationship "contains" from order to product is realized by means of the file Item and the two FMP relationships Items and Product. The problem is to display, in a portal on a layout for file Order, the list of those product descriptions and prices that appear in the order.

Here is one way to mimic chained one-many--many-one relationships for field access (compare to first approach in Chaining many-one relationships):

  1. [Field inspection only] Carry each of the relevant field values from Product to Item by means of auxiliary calculation fields using the relationship Product, and access the auxiliary fields through the relationship Items:
    1. Define a calculation field named Description in Item by Description=Product::Description. The calculation field can be Unstored.
    2. Inspect the product's description in Order through Item::Description.
    Repeat these two steps for every field needed from Product, e.g. define Price=Product::Price in Item and inspect Item::Price in Order.

Note that this does not allow for modification of the Product records from file Orders. Usually such modification is not desirable anyway.

The solution to mimic chained one-many--many-one relationships for selection of related records is more complicated. Selecting as current record a related record in file Product from a portal on a layout for Orders can be accomplished as follows:

  1. Chain Go to Related Record script steps:

To be supplied: setting found set to all related records.


Automatically creating related records

For relationships, there is an option to `Allow creation of related records'. Selecting this option has several consequences:

  1. ``Typing in a related field that has no corresponding related record creates a related record based on the match field in the master file.'' [Quoted from FMP on-line help.] The related match field of the newly created record is automatically initialized to hold the same value as the master match field (not explicit in FMP documentation).

  2. Every portal based on such a relationship shows an extra portal row that has no corresponding related record (not in FMP documentation). Typing into that portal row creates a new related record (see preceding point).

It is not clear to me how to trigger automatic creation of related records in scripts.

Suggestions to FileMaker Inc.:

  1. Better document the automatic creation of related records.


Hiding fields with keys from the user

A good relational database design involves fields that hold keys (primary or foreign, also see Entity-Relationship model and FileMaker Pro). It is also good practice to protect these keys from direct user manipulation:

  1. Such keys are preferably meaningless to the user and, hence, not conveniently manipulated by the user. [Reason: Keys are used to record relationships between entities. If a key is, for instance, derived from the name of a person, then changing the person's name requires you to make corresponding changes in all records that reference the person (such as records for orders placed by that person). Without such extra changes, you lose the link between a person and its orders.]
  2. The integrity of a database often depends on certain key constraints being preserved. Arbitrary manipulation of keys often destroys the integrity.

(To be eloborated.) Poor man's solutions (that helps only with 1 above): value list of key values and `also display values from' a meaningful field. Better: To be supplied. (Involves various global fields and scripts, and depends on the kind of E/R-relationships present.)


PORTALS


Effects of buttons in portals

The effect of a button appears to depend on the button's location (inside a portal or not). In particular, a button doing

This can be explained as follows. The effect of script steps Go to Related Record, Delete Record/Request, Delete Portal Row, and all field steps modifying a related field (Set Field and Paste ...), depends on whether and, if so, which portal row is currently selected. Furthermore, when a button in a portal row is clicked that row automatically becomes selected (before the button action is done).

Suggestions to FileMaker Inc.:

  1. Document the effects of buttons in portal rows.


Location of `creation row' in portals

In a portal for a relationship that allows creation of related records, there appears an empty portal row. By entering data in that empty row, a related record with that data is automatically created (also see Automatically creating related records). The empty `creation' row always appears at the end of the list.

Suggestions to FileMaker Inc.:

  1. Introduce an option to display the `creation' row in a portal at the beginning of the list.
Note: There appear to be some third-party solutions for this.


Manipulating portal rows

Poorly documented. In particular, how to force re-sorting while keeping the same portal row selected?


VALUE LISTS


Controling the order in field-derived value lists

Not documented; possible to some extent; desirable.


Selecting an action using a value list

Impossible, but desirable.


Sharing value lists among files

Impossible, but very desirable.

Also see: Sharing global fields among files in a database.


INDEXING


IMPORT/EXPORT


Specifying file names via a field

Impossible, but very desirable. Now you need to specify the name of an import or export file in the script step as a constant. (Even worse for printing to file.)


SECURITY


Passwords are also user codes

To protect the contents and functionality of a database (data and definitions), FileMaker Pro provides a password mechanism. Unfortunately, the passwords are used in isolation, that is, without reference to some form of user identification. Or, to put it differently, the FMP passwords also act as user codes. As a consequence, all passwords must be distinct and must be visible to a system administrator. This has some undesirable implications for administration and security.

Consider the following scenario. You wish to set up a database for three (kinds of) users:

In FileMaker Pro you can accomplish this (in part) by defining three passwords, one for each user and giving these the intended privileges. Of course, in order to provide decent protection, passwords should be chosen with care. For instance, the Apprentice should not know, or be able to guess, the Master password. Here are some of the problems with this scheme:
  1. If users change their passwords, then the system administrator may have a hard time keeping track of which password belongs to which person. For instance, suppose the Journeyman and Apprentice independently change their passwords, unknown to the Master. It may then be cumbersome for the Master to update privileges, because it is not immediately clear which password is intended to be used by which person. This is an administration weakness.
  2. If a user attempts to change a password into a password that already exists, then the change attempt fails. Such failure is an indication to that user, that the attempted password possibly already exists. This is a security weakness.
  3. Because all passwords are visible to the Master, other users may accidentally expose their password `strategy'. This is especially the case when users do not know that their passwords are `in the open'. For instance, users may have passwords on various other systems as well. In order to reduce the burden on their memory, users often take identical or related passwords on the various systems they access. This is a privacy weakness.
It seems to me that
the only practical use of access privileges in FileMaker Pro is to have exactly two passwords, namely
In that case, Anything beyond this scheme is going to be a burden under the current method by which FileMaker Pro handles access privileges.

The solution to the problems mentioned above is well known:

Suggestions to FileMaker Inc.:

  1. Link passwords to some stable user identification (user code). User codes must be unique, passwords need not be unique. Access privileges should be associated with the user code, and not solely with the password.
  2. Allow users to change their password but not their user code, unless they have sufficient privileges.
  3. Encrypt passwords and never show decrypted passwords.


Protecting multiple files

When a database consists of multiple files, it soon becomes a hassle to set up, maintain, and use a consistent scheme for access privileges. The administrator (developer) has to set up the access privileges (passwords and groups) in each file separately. Maintaining consistency is cumbersome and error-prone, especially when making changes after initial setup. Furthermore the (other) users of the database will be confronted with a dialog box for each file they access, unless they use the default password set by Edit->Preferences->Document->General->Try default password.

Suggestions to FileMaker Inc.:

  1. Introduce a database framework file, in which to set database-wide privileges. This can help avoid the administrator's problem of setting up and maintaining identical or related access privileges separately in each file. It can also help avoid the user's problem of having to provide a password for each file that is accessed.


PERFORMANCE

Stored versus unstored calculations. Indexing.

TechInfo:


CROSS-PLATFORM

Mostly in the documentation:

  1. Restrict fonts in layouts to platform-common fonts (Helvetica, Times, ...).
  2. Do not use AppleScript.
  3. Restrict contents of container fields to platform-common formats.
  4. Use sufficiently large boxes so that texts are not clipped.

TechInfo:


NETWORKING


WEB SERVER


MISCELLANEOUS


Printing the definitions in a file

Through the File->Print... command, FileMaker Pro can produce a printout of some of the definitions in a file:

Printing to a PDF file is a nice way of obtaining documentation for your design. This is indispensible when reusing (parts of) a script from one file in another file (also see Copying (parts of) scripts).

Obviously missing are printouts for

The latter are by their nature more difficult to produce, but would nevertheless be very helpful in documenting a design, in particular, the buttons and their actions.

Suggestions to FileMaker Inc.:

  1. In the printout for scripts, also print the script name at the TOP of the page.
  2. Include a way to print definitions of relationships, value lists, and layouts.
Also see: Commenting field, layout, calculation, script, relationship, and value list definitions


User input and output

Input/Ouput: Only through separate layouts with global fields and Show Message script steps, or newly created records (possibly in a separate file.

Plug-in: Input-Dialog plug-in.


Separating multiple parameters

The symbol used for separating multiple parameters to a function depends on localization. In the US localization, this symbol is the comma; in localizations where the comman is used as decimal separator (instead of a period), it is a semicolon. However, if you use a US version of FMP under a US version of the OS and choose the comma as decimal separator in the Numbers control panel (Mac), then FileMaker Pro accepts neither of

Workarounds:


Commenting field, layout, calculation, script, relationship, and value list definitions

Impossible, but desirable. Note that in scripts you can use the Comment script step.

Third-party solution: Analyzer from Waves in Motion.


REFERENCES

Not all of the books on FileMaker Pro are useful if you look for answers to the questions posed on this page. Here is an annotated list of material that I have found useful:

[FMPUG]
FileMaker Inc.
FileMaker Pro 4.0 User's Guide.
FileMaker Pro On-Line Help.

Not a tutorial, but still a good source of factual information, in spite of what some people claim. The on-line help is in many ways more complete and better accessible than the printed manual. Additional information can be found at FMI's web site:

[GC98]
Jeff Gagne and Don Crabb.
Database Design and Publishing with FileMaker Pro 4 for Mac and Windows.
M&T Books, 1998.
[See/order this book at Amazon.com]

A good book that also provides a (light) introduction to the theory of database design.

[Har98]
Jan L. Harrington.
Relational Database Design Clearly Explained.
AP Professionals. 1998.
[See/order this book at Amazon.com]

A to-the-point introduction with good motivating examples. The introduction draws you in by exposing a bad design example. Accessible without prior database knowledge, merging well with the FileMaker Pro concepts (though not with FMP terminology). The first part covers database theory, in more depth than [GC98]. The second part deals with practice: SQL, design tools, and three case studies. Aimed at serious database designers who care about data and their users.

[PO98]
Matt Petrowsky and John Mark Osborne.
Scriptology: FileMaker Pro Demystified.
ISO Productions, 1998.
[See/order this book at Amazon.com]

A book mainly about scripts and calculations, but also about relationships, portals, cross-platform issues, and user-interface issues. Includes a CD-ROM with many examples (each technique is illustrated in isolation with its own FMP database) and lots of information about FMP.