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.
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:
| 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
I have tried to adhere to the `standard' FileMaker Pro terminology. There may, however, be some exceptions.
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.
If and Exit Loop If
script steps)
NumToText and TextToNum.
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).
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:
Records are created, inspected, modified, and deleted in Browse mode, either manually or through script steps. For example, a new record is created by:
New Record,
Duplicate Record,
A file is created by File-<New.
Import from or Export to a specified file;
New, Open, Close,
Save-a-Copy-as, or Recover steps
involving a specified file.
Perform AppleScript, where the AppleScript involves
other files.
Browse, Find, Layout, or
Preview commands from the Mode menu,
Select->Modify Last Find command,
Browse, Find, Layout,
Preview, or Modify Last Find
script steps, or
Browse-Find-Layout-Preview pop-up menu
(bottom-left in the file's window).
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:
Go to Layout script step
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:
Tab, Shift Tab, and the layout's tab order
Go to Field, Go to Next Field, and
Go to Previous Field scripts steps
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.
The found set is determined through:
Find All, Find Omitted, Omit, and
Omit Multiple commands
from the Select menu in Browse mode,
Find All, Find Omitted, Omit, and
Omit Multiple script steps, and
Go to Related Record script step
using a relationship and option
`Show only related records'.
Next and Previous record.
Goto Record and Goto Related Record
script steps.
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:
Mode->Sort command in Browse mode.
Sort script step.
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.
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:
Find button or Select->Perform Find command
in Find mode.
Calculations can be associated with fields or with some script steps. Various circumstances can trigger a calculation for (re)computation in various ways:
Auto Enter: calculated value and looked-up value;
Validation: validation by calculation.
Mode->Replace command applied to a field
in Browse mode.
Loop steps and global fields, or
Script menu).
Each script has a name and consists of a list of
script steps.
Scripts can be performed
ScriptMaker through the Perform button,
Script menu (when included),
Edit->Preferences->Document...->General).
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.
A relationship can be used to:
Go to Related Record script step),
as current record in the related file,
and optionally as found set
(`Show only related records').
Go to Related Record will go to that particular
related record).
The portal can also serve to create and delete related records.
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:
Edit->Paste Special->From Index....
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:
This relationship will not work because the field ``::GlobalAnchor'' cannot be indexed. Proceed anyway?The relationship will work to access global fields.
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.:
Plug-in solution: Azium Variables Plug-in (also see TechInfo: 105927 - Azium Variables Plugin)
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.
- `Stored CurrentTime' (Time, Stored) = Status(CurrentTime)
- `Unstored CurrentTime' (Time, Unstored) = Status(CurrentTime)
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 Stored Unstored Constraints cannot reference:
- Unstored calculation fields
- Summary fields
- Global fields
- Related fields
cannot be indexed Memory usage in file temporarily 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 updatewhen value is needed,
either for displaying,
or in another calculationPropagation 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 updatewhen used fields were unchanged
when computation is duplicated
How all fields depend on, use, or reference each other can be captured in a dependence graph:
G
|\
| |
|/
F
|\
| \
| \
D E
|\ |\
| \ | \
| \| \
A B C
Notes about the structure of the dependence graph:
Unstored calculation fields
| |
| |
Stored calculation fields |
| Global, summary, related fields
|
Standard fields
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.
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.
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.
Suggestions to FileMaker Inc.:
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 would seem that the following auto-enter options are equivalent:
Data, which enters a constant and
Calculated value with that same constant
(instead of a more general calculation).
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.:
Calculated value should be supplemented with
"Do not evaluate if some referenced field is empty".
Calculated value is not performed
if its target field is not empty (which may happen for duplicated records).
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:
Set Field["MemorizedRecord", "Key"].
Go to Related Record["Memorized"].
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.:
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.:
Status(CurrentFile), DataBaseNames,
RelationInfo,
which return only the name but not the location, i.e. volume and folder).
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:
- 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.
- 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.:
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.
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:
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.:
Save-a-Copy-As command is not appropriate.
Also see Renaming or moving a file in a database and Sharing global fields among 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.:
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:
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);
However, there are several pitfalls in this approach:
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:
Perform external script: "Go to layout X", in file Bar.
Go to Layout: X.
Suggestions to FileMaker Inc.:
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.:
In Layout mode, the attributes of a field occurrence on a layout can be modified
Format menu,
Specify Field dialog.
Specify Button dialog.
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.:
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.
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:
Edit->Copy) data from the field.
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.:
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.:
Impossible, but desirable.
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:
Mode->Sort command
in Browse mode.
ScriptMaker, create a script
(recommended name: "Sort on <mnemonic>")
consisting of just one Sort step,
checking both "Restore sort order" and "Perform without dialog".
Mode->Sort command in Browse mode
to inspect or edit the sort request.
ScriptMaker, edit the script.
NOTE: It is recommended to useSortsteps 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:
- 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.
- It is much harder to maintain a sort request that is duplicated in various scripts.
Suggestions to FileMaker Inc.:
Sort script step that restores
a stored sort request, indeed makes this sort request available for
inspection and editing through Mode->Sort.
Suggestions to FileMaker Inc.:
Also see Combining find requests.
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:
Reverse Order command:
Reverse Order command:
Unsort the records,
then invoke Reverse Order.
Reverse Order command:
Reverse Order command:
Reverse Order.
Suggestions to FileMaker Inc.:
Reverse Order command
to reverse the presentation order of records:
the last record coming first.
Ascending becomes descending, descending becomes ascending.
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.:
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:
Solution: Script three sort requests, one for each key, using the current order as default. Attach them to three buttons. To sort equal Names on Zip Code, first click `Sort on Zip Code' and then `Sort on Name'.
Without the option of using the current order as default order,
the data base designer needs to define sort requests for all relevant
key combinations for ordering records.
With two keys, there are only two possible ways to combine them,
but with three keys, there are already six such combinations.
In general, with N keys,
there are N factorial (N!=1*2*...*(N-1)*N) combinations.
Taking into account that each key can be sorted in two ways (ascending
or descending), you end up with N!*2N possible sort requests.
Without the option, you would need as many buttons,
complicating both the implementation and maintenance of a data base.
With the option, you can do with 2*N buttons;
if there is also a Reverse Order command,
you need only N+1 buttons.
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:
Suggestions to FileMaker Inc.:
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.
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:
Suggestions to FileMaker Inc.:
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:
Mode->Find command.
ScriptMaker, create a script
(recommended name: "Find <mnemonic>")
consisting of just one Perform Find step,
checking "Restore find requests".
Select->Modify Last Find command in Browse mode
to inspect or edit the find requests.
ScriptMaker, edit the script.
NOTE: It is recommended to usePerform Findsteps 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:
- 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.
- 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:
Enter Find Mode
Set Field[field,calc] for each field condition
New Record/Request for each additional find request
Perform Find[], i.e. without restoring
Suggestions to FileMaker Inc.:
Also see Combining sort 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.
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.:
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).
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:
Select->Find Omitted to make the omitted records available for manipulation.
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.
Select->Find Omitted again to make all omitted records available as new found set.
Also see Combining find requests.
To construct a found set consisting of only the current record (i.e. to omit all others) do
Find All
Omit
Find Omitted
Go to Related Record[Show, "Self"] script step,
showing only related records.
Suggestions to FileMaker Inc.:
Find All
leaves the choice of current record invariant.
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.
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
- Date(D) = the number of days elapsed from 1-Jan-1904 to D,
- Time(T) = ( (Hour(T)*24 + Minute(T))*60 + Seconds(T) ) / (24*60*60).
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:
EndTime - StartTime
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.
EndDate - StartDate
Year(EndDate - StartDate) - 1but 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
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:
Suggestions to FileMaker Inc.:
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.
Impossible, but very desirable. Use global fields instead. No concurrency or re-entry.
Also see: Sharing global fields among files.
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.:
ElseIf script step to allow for nicely
cascaded (instead of nested) conditional execution.
Too complicated: use a global field `FileB4' and special script with several steps for each file to return to. Or cascade scripts.
In particular through a portal/relationship, without `losing' the record as selected, forcing an update of the sort order.
An activated script S can be suspended or paused, and later resumed:
Perform Script[T],
it is (temporarily) suspended and script T is activated.
Script S is resumed when T exits
(either when T gets to its end, or T executes an Exit Script step,
or T is forced to exit through the Exit option of Perform Script from a button).
Pause/Resume Script,
it is (temporarily) suspended and (limited) control is given back to the user.
We also say that the script is paused.
Script S is resumed when the user clicks
Resume Script,
Perform Script with option Resume, and the performed
script exits.
Perform Script[T]:
Resume Script:
Resumes the most recently paused script, canceling the open pause.
Suggestions to FileMaker Inc.:
Perform Script options for buttons.
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:
Entity Set +------------+ | Attribute | +------------+ | ... | +------------+ | Attribute | +------------+
---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):
NOTES:
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).
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.File1 File2 +--------+ +--------+ | FieldA |-*-Relationship--<| FieldX | +--------+ +--------+ | ... | | ... | +--------+ +--------+ | FieldC | | FieldZ | +--------+ +--------+
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:
Consider the following database:
Go to Related Record script step).
F >*-R--- G >*-S--- H (many-one, many-one)
F -*-R--< G -*-S--< H (one-many, one-many)
F -*-R--< G >*-S--- H (one-many, many-one; e.g. realizing many-many)
F >*-R--- G -*-S--< H (many-one, one-many; uncommon composition and not further treated)
Here is a more specific example. Assume you have
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:
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:
Go to Related Record script steps:
Go to Related Record, using relationship ResidesIn.
Activate `Show only related records' if you want the found set
in Regions to consist of the single related record.
Go to Related Record, using relationship IsPlacedBy
(no need to activate `Show only related records').
Perform Script [external: `Go on to Region' in Customers].
Go to Related Record script step (possibly directly linked to a button)
in file Orders.
Also see: 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).
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.
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):
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:
Go to Related Record script steps:
Go to Related Record, using relationship Product.
Activate `Show only related records' if you want the found set
in Product to consist of the single chosen related record
(not all related records).
Go to Related Record, using relationship Items
(no need to activate `Show only related records').
Perform Script [external: `Go on to Product' in Item].
To be supplied: setting found set to all related records.
For relationships, there is an option to `Allow creation of related records'. Selecting this option has several consequences:
Suggestions to FileMaker Inc.:
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:
(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.)
The effect of a button appears to depend on the button's location (inside a portal or not). In particular, a button doing
Go to Related Record:
Delete Record/Request, or Delete Portal Row:
Delete Record/Request deletes the master record,
and Delete Portal Row deletes the record in the selected portal row (if any).
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.:
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.:
Poorly documented. In particular, how to force re-sorting while keeping the same portal row selected?
Not documented; possible to some extent; desirable.
Impossible, but desirable.
Impossible, but very desirable.
Also see: Sharing global fields among files in a database.
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.)
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:
the only practical use of access privileges in FileMaker Pro is to have exactly two passwords, namelyIn that case,
- a master password (chosen carefully) with full access privileges,
- a default password (e.g. `no password') with limited access privileges.
The solution to the problems mentioned above is well known:
Suggestions to FileMaker Inc.:
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.:
Stored versus unstored calculations. Indexing.
Mostly in the documentation:
Through the File->Print... command,
FileMaker Pro can produce a printout of some of the definitions in a file:
Obviously missing are printouts for
Suggestions to FileMaker Inc.:
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.
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
Date(12, 31, 1999)
Date(12; 31; 1999)
Date(12 , 31 , 1999)
[that is, with a blank between the number and the separating comma]
Date(Int(12), Int(31), Int(1999))
Impossible, but desirable.
Note that in scripts you can use the Comment
script step.
Third-party solution: Analyzer from Waves in Motion.
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:
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:
A good book that also provides a (light) introduction to the theory of database design.
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.
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.