Once upon a time, you could only define one table in each FileMaker file. A system of related tables thus required many different files--each with its own table. FileMaker thus had to support what you describe here from the earliest related version (FileMaker 3) and still does.
Go to Manage | Database | Relationships.
Click the New Occurrence button. (Bottom, far left button).
This opens a dialog where you specify what this new "occurrence box" will refer to for its table of records (the data source table). Select "Add FileMaker Data Source" from the Data Source drop down. This opens a dialog box where you can find and select your second FileMaker file. Select it and you'll get yet another window listing the tables in that file. Select the table you want and you now have a reference to that table in the external file. You can link it in relationships and refer to it just as though the table were defined in the local table.
If you open Manage | external data sources, you'll see a list of such external file references where you can manage these references should you need to change or relocate such an external data source.
There are a few issues you'll need to be aware of with a multi-file solution:
- If you use Manage | Security to define accounts/passwords to control use access, you'll need to define identical Account names and passwords in both files. If you do not, you'll get an extra password log in dialog box the first time you attempt to access data in the second file. Likewise any changes to account names and passwords must be done identically in both files. This can be automated with scripts.
- The first time you bring up a layout that displays data in the second file, run a script that accesses data in the second file, or display data from a calculation that references data in the second file, filemaker will quietly open the second file in a hidden window. If the file cannot be found (say you are on your iPad, it will trigger a file not found error message. In FileMaker Pro, you'll get a dialog box opening asking you to find and open the missing file. Thus, you need to keep such referenced files in the same location and not change their file names unless you also update the external data source references. You may need alternate layouts on your iPad that avoid references to the multi-media file.
- "Run with full access privileges" is a handy script option that enables a script to run as though the file where opened with the [full access] privilege set even when a lower access level user actually has the file open. However, if security settings on the external file will keep the script from doing what it needs to do, Run with full access privileges will not get around that limitation in a script in the main file. You may need to create a script that runs with full access privileges in the secondary file and use a perform script step in the main file to run it in order to get around that limitation.
- Global variables defined in one file cannot be directly accessed by scripts defined in other filemaker files. Global fields in one file can be directly referenced--if a table occurrence to the field's table has been added to that file.