In all the discussions of multitable systems in Chapters 5 and 6 and so far in this chapter, we've assumed that all the tables you want to work with live within a single FileMaker file. The capability to have many tables in a single physical file is, after all, one of the more convenient features of FileMaker. But there are still many reasons to build systems that are multifile, in addition to being multitable. This section reviews the mechanics of working with several files at once, and then discusses different design strategies that use a multifile structure.
So far we've looked just at relationships between tables within the same file. But it's also possible to build relationships between tables in different files. As an example, suppose that you're (again!) being called on to build an invoicing system of some kind. Naturally you'll want to start with some set of customer data and a place to hold it. Well, it may happen that a FileMaker file already exists with a bunch of tables of customer information: a table of companies, a table of individual contacts at each company, and a table of company addresses, to name a few. There's no need to redo all that workall you need is the data. But let's assume that the designer of the Customer file is not eager to have you in there adding things to her file. She'd rather you kept the invoicing tables separate, in their own file.
Well, you might find her lack of faith disturbing, but it won't create any actual technical hurdles for you. To reference her customer file from your new invoicing file, you only need to create a file reference to her file. You can then use that file reference to create new table occurrences for her customer tables, inside your invoicing file.
Creating a File Reference
File references are an extremely important topic in FileMaker. In a number of places in FileMaker, you might want to refer to or work with another file. Here are some of the things you can do with other files in FileMaker:
To do any of these things, you first need to create a reference to the other file. A file reference simply tells FileMaker where and how to find another file. FileMaker is capable of working with external files that are present on a local hard drive, that are present on a shared network volume, or that are present on an available FileMaker Server. You can also specify multiple search locations for a file, and the priority in which they should be searched. You can, for instance, create a file reference that says, "First search for the invoicing file on the FileMaker server at 192.168.100.2. If you don't find it there, look on the FileMaker server at 10.11.1.5. If you don't find it there, give up."
Previous versions of FileMaker used file references as well. But these earlier versions kept track of these references behind the scenes, and didn't let you alter the order in which FileMaker searched for a given file. Problems with file references were harder to spot in previous versions, and could occasionally give rise to a problem called crosstalk, in which the wrong copies of files could be accessed by mistake.
For more on the concept of crosstalk and its relationship to file references, see "Crosstalk," p. 526.
In FileMaker 8, each physical file maintains its own list of file references. You can work with these references centrally, and also create them on the fly as needed. Let's see how this works in practice.
Going back to the invoicing example, say you have your own invoicing file, called InvoicingSeparate. The aforementioned untrusting colleague has a file called CustomerSeparate, with a Customer table. You want to use that Customer table from the CustomerSeparate file in InvoicingSeparate.
Your first step is to define a file reference to the external file. To do this, choose File, Define, File References. Click the New button on the next screen, and you'll see the Edit File Reference dialog, shown in Figure 7.26.
Figure 7.26. You can add search paths to a file reference by typing them manually, or by using the Add File button to choose a specific file.
A file reference is a more complex object than it sounds at first. A file reference is really a name given to a series of file paths. (See Figure 7.28 for an example of a file path with multiple entries.) FileMaker resolves the file reference by searching through the path list in the specified order. In general, the point is to use a file reference to tell FileMaker the best (or perhaps the only) place for FileMaker to look for a file.
In general, all the different file paths in the path list point to the same file; that is, a file with the same name and contents. In theory, you could also use a single path list to point to a number of different files, indicating that the later ones should be used if the earlier ones can't be found. You could perhaps use this feature to fall back to other versions of a file or system if necessary.
In the case of the external Customer file to which you want to relate, say that file is being hosted under FileMaker Server. One way to build the file reference is simply to click the Add File button in the Edit File Reference dialog. This brings you to a standard Open File dialog, from which you can click the Remote button to look for servers on your network. When you find the server hosting the Customer file, you can select and open the Customer file. After you do this, FileMaker adds an element to the file path list that looks something like fmnet:/192.168.101.66/Customer, as shown in Figure 7.27.
Figure 7.27. File references tell FileMaker where to look to find externally referenced files.
There's nothing magical about the text FileMaker added to create this file path. The file path list is a free text area, so you're free to add entries to the list yourself by hand, separating each one with a carriage return. Again, the list order is the order in which FileMaker looks for the file.
Suppose that your colleague had also given you a copy of the file to use "offline" while you were doing your development work. You'd want the file reference to resolve to a copy on your local disk instead. But after the file was hosted on FileMaker Server, you'd want to avoid that local copy being found by accident. One way to do this is to add another element to the file path list, possibly by using the Add File button again and navigating to the copy of CustomerSeparate that's on your local hard drive. This places the new path element second in the list, but you can cut and paste it in front of the first path (see Figure 7.28).
Figure 7.28. A search path can contain multiple search locations. FileMaker scans them in the specified order.
If you've inserted a local reference to the file to aid offline development, you need to remember to remove that reference later, or perhaps move it lower on the list. Otherwise, FileMaker continues to search your local drive first, which is probably not desirable.
The file reference displayed in Figure 7.28 instructs FileMaker first to look for the file locally and then, if it can't be found locally, to look for it on the server at 192.168.101.66. If FileMaker searches the entire file path and can't find a file at any of the specified points, it presents a dialog telling you that it's failed to find the file, and then throws up an Open File dialog inviting you to find the file by hand. If you find and specify a file by hand, that file is used for the current external file operation. Figure 7.29 shows the warning you see when a file reference can't be resolved.
Figure 7.29. If FileMaker cannot find a referenced file after searching the specified search path, it displays an error dialog.
In previous versions of FileMaker, when an external file reference failed, FileMaker would also prompt the user to find the referenced file by hand. But in previous versions, FileMaker would remember this choice and add it into the internally stored file path list. This was not always the right thing to do; the path could easily be altered or reset by a flustered user just trying to close the ominous dialogs on her screen. When FileMaker 8 fails to resolve a file reference, and prompts the user to find the file, any selected file choice is valid for the current action, but FileMaker doesn't alter the stored file reference in any way based on this choice.
Adding an External Table to the Relationships Graph
If you've followed along with the example to this point, you've now built a file reference pointing to the CustomerSeparate file that looks for it first on your local hard drive, and next on a networked FileMaker Server. You can now use this reference to add tables from the external file to the Relationships Graph in your InvoicingSeparate file. If you open the Relationships Graph and click the Add Table Occurrence icon, you'll notice something we didn't highlight before. In the resulting Specify Table dialog, there's a menu that lets you choose which file you want to browse for table choices. This menu always includes the current file, and also includes any file references you have defined using the techniques covered in the previous sections of this chapter. Figure 7.30 illustrates this point.
Figure 7.30. When adding a table occurrence to the Relationships Graph, you can base the new occurrence on a table in another file.
With the file reference in place, you can choose the Customer file reference (remember that the name of the file reference can be different from the name of the file to which it resolves, much as the name of a table occurrence can differ from the name of the underlying source table), and from the resulting table list you can choose the Customer table. The table is then added to the Relationships Graph, much as all the other tables we've seen, and you can create the usual relationship between the Customer table and the Invoice table based on CustomerID. The result is shown in Figure 7.31. There's one subtle visual indication that the Customer table occurrence is based on a table from another file: The table occurrence name for Customer is italicized. Otherwise it's just as though you were working with a table in the same file.
Figure 7.31. In this Relationships Graph, the italicized title of the Customer table occurrence shows that the source table exists in an external file.
So that's all there is to using external tables in your Relationships Graph. Add a file reference that points to the appropriate file, and use that reference to pull in tables from the external file.
File referenceslike tables, table occurrences, fields, layouts, and scriptscan benefit from a consistent naming scheme. Here, as elsewhere, the naming scheme you choose is less important than the consistency with which you apply it. By default, FileMaker names a file reference after the actual file to which it points. You may feel, though, that you want some added information in the namean "Ext" prefix or the like, for example, to show that the file is external. This may be even more helpful when naming table occurrences from external files. In the Relationships Graph, the only clue that a table occurrence is external is the fact that the name is italicized. If an occurrence of an Employee table from an external file is named ExtEmployee, this may be a helpful clarification.
Part I: Getting Started with FileMaker 8
Using FileMaker Pro
Defining and Working with Fields
Working with Layouts
Part II: Developing Solutions with FileMaker
Relational Database Design
Working with Multiple Tables
Working with Relationships
Getting Started with Calculations
Getting Started with Scripting
Getting Started with Reporting
Part III: Developer Techniques
Developing for Multiuser Deployment
Advanced Interface Techniques
Advanced Calculation Techniques
Advanced Scripting Techniques
Advanced Portal Techniques
Debugging and Troubleshooting
Converting Systems from Previous Versions of FileMaker Pro
Part IV: Data Integration and Publishing
Importing Data into FileMaker Pro
Exporting Data from FileMaker
Instant Web Publishing
FileMaker and Web Services
Custom Web Publishing
Part V: Deploying a FileMaker Solution
Deploying and Extending FileMaker
FileMaker Server and Server Advanced
Documenting Your FileMaker Solutions