Filemaker multi-table search

Graham Lauren shows how to search across multiple tables in Filemaker 7 and 8.

FileMaker's great merit is that it can enable almost anyone to construct simple, usable databases. The same benefit can undo entry-level users when first exposed to the multi-table structure and relationship potentials first presented in version 7, and now in 8. However, whereas earlier, single-table solutions provided a simple frame for searching, attempting to do so across multiple tables is undoubtedly more complex.

In answer to this problem, the following is a demonstration of how to build a simple, multi-table search routine, which you can embellish as you wish. As usual, there are many different ways to skin the FileMaker cat, but I have found that this, with minor variations around the edges, depending on the required outcome, works consistently for me.

To begin, we will assume that there are two different tables in the database, one labelled Friends and the other Contacts, or home and work, if you like, which will be presented as lists to be searched. In this example, the principal difference is that one will have as its primary focus individuals, and the other organisations. Other fields created, such as those for phone numbers, email and other address details might be largely the same.

To add to this, we'll add two more tables:

1. Globals which comprises utility fields providing services to the search.
2. SearchReturn, which is where the 'found' records turn up.

On top of these is a script routine, which interrogates each of the tables to be searched.

FileMaker displays table data in 'layouts' which principally 'belong' to a specific table. In simple terms, in the solution I outline here:

1. The user enters a search string into a search field.
2. If this search string is found in a specified field in one of the tables to be searched, this match returns a boolean positive result of '1' in another (calculated) belonging to the record in which it is found.
3. Through scripting, the records containing the '1's are then retrieved from their respective tables and imported into the SearchReturn table, whose purpose is to store them temporarily until the user decides which record they wish to work on, at which point;
4. Clicking a button on the desired record returns the user to that record in the table from whence it came.

   Ok, so here's the execution.

   Execution: Setting up the Tables
   
   

   In Filemaker's table-creation function, create the four necessary tables, as seen beneath. I give each their 'T*.' prefix only to ensure they will sort and display alphanumerically in my preferred order.