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:
- Globals which comprises utility fields providing services to the search.
- 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:
- The user enters a search string into a search field.
- 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.
- 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;
- 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.
Global table fields
In the Globals table, define the fields illustrated here:
Recapping, the fields in Globals provide services to the search, and hold values only temporarily. This table itself contains only one record.
- cLayout is a calculation which enables the display of the name of each table’s layout based on the relationships between Globals and the other tables. See beneath in Execution: Setting up the Relationships.
- gC (an abbreviation of ‘global constant’) holds a constant ‘global’ value of 1 whose purpose is to create the relationship with the field ‘C’ (an abbreviation of constant)found in each of the other tables, in which is auto-entered the value of 1, as specified in the Define Database>Fields module, as illustrated above.
- gIDCatcher ‘catches’ the ID of the found record and helps steer the user to the record they wish to view.
- gLayoutCatcher ‘catches’ the layout name of that same record and helps steer the user to the table in which it will be found.
- gSearch contains, temporarily, the text string to be searched on.
- Search contains, as demonstrated beneath, the text which initially populates gSearch.
Contacts table fields
Through Define Database>Fields, define the following fields in Contacts. As the only difference between its structure and that for the Friends table is the presence of the Organisation field, after setting this up, just copy this table and call the copy ‘Friends’, and then delete its Organisation field, if you wish.
I will explain only the purpose of those fields which require it whose definition is not clear from the illustration beneath.
The purpose of the search is to find a match between the text string in gSearch and any relevant field in Contacts, but we can make this easier if we construct a simple ‘composite’ field of those fields most likely to contain the result. Thus:
- cCompContact is that field, a ‘text calculation’ which contains the Organisation, NameFirst, NameLast and location fields in a single string.
- cIfMatchComposite is a calculation which returns a ‘1’ if the PatternCount – that is, the text within the gSearch field – is matched within the target cCompContact field, a straightforward Boolean result.
- cIfMatchgIDCatcher performs a similar function, which we will come to later.
- IDcontact is a simple, auto-entered, unique serial number for each record.
- TableName auto-enters ‘T3.Contacts’ into every record in its table.
Search Return Table
Finally, the search return table contains only text fields, whose contents are only ever the result of importing data from the Friends and Contacts tables to narrow the user’s search; thus for my own purposes, I prefix these with ‘i’ (an abbreviation of ‘imported’).
Execution: Setting up the Relationships
In the relationship graph, gC within the Globals table must be related to C in each of the other tables, as shown.
Execution: Setting up the Layouts
Now set up the following layouts for each table.
- For Globals, from the Layout menu, select Standard form and create a layout with two fields, Search and gSearch. Call this layout, ‘Entry’.
- For Friends, from the Layout menu, select Columnar list/report and follow the instructions to create a layout display in the fields: IDfriend, cIfMatchComposite, cIfMatchgIDcatcher, NameFirst, NameLast, Location and cCompFriend. By dragging from the ‘Part’ menu at (1) as shown beneath, create a footer for this layout and then drag into it from the field menu at (2) a field as shown by the greyed-out field with the handles at (3), defining this in the Field/Control Setup menu by selecting the Globals table and then selecting gIDCatcher under (4).
- Do for Contacts as for Friends, but include Organisation and cCompContact in place of cCompFriend.
- Finally, for SearchReturn, do as for Contacts and Friends, but this time your list structure will contain only iID, iComp and iTable. And just as in the instruction for friends, follow the same routine to add gSearch from the Globals table to the ‘Header’ of your layout, as shown beneath.
Execution: Setting up the Scripts and actions
Ok, now assuming that your Friends and Contacts tables are populated with data on which to search (and you might wish at this point to enter some dummy data to test these), we can build the scripted routines, through the Scripts menu, which identify and return the required values. The following are the steps and what you should see on the screen as the result of each action.
First, go to: Scripts> ScriptMaker> New, then follow these steps, using the menu on the left. Note that items in bold text prefixed by hash (#) symbols are inserted as instructions (or ‘Comments’, as found in the menu) to myself. (The complete search instruction is included in the appendix of this piece).
Start the search routine
- Go to Layout and Go to Field take you to the entry text field, ‘Search’, whereupon the script pauses until the user enters the text string in the Search field, and reactivates it with the ‘continue’ button
- The text string is then ‘set’ in the gSearch field, whose value, being a global field, unlike a text field, is retained and available for use within both ‘Browse’ and ‘Find’ modes. As there is no need to use the Search field until the next new search, this can then be cleared of its value.
- Set Error Capture [On] enables the script to accept errors (that is, at allows for an inability to find appropriate records) without throwing up error messages prior to the conclusion of the entire script.
- Finally, at the conclusion of this part of the script, by imposing the Pause/Resume Script [Indefinitely] step, the result beneath will be returned so you can check your work. Once you are happy everything is working as intended, all such Pause steps can be removed safely.
You can see here that because of the pattern match on the text string ‘rich’ in gSearch, the records found are returning the value ‘1’ in the field cIfMatchComposite.
Import these records into the SearchReturn table
These records need next to be imported into the SearchReturn table, matched against the fields beneath. The Import Records dialog box will guide you through the matching of tables and fields, as shown beneath.
Next, go to find in Contacts
This step essentially replicates what was done in finding the appropriate records within the Friends table, and, then imports them into the SearchReturn table.
Viewing the returned records in SearchReturn
Finally, the result in the SearchReturn table is this.
Returning to the chosen record in its native table
To view the chosen record in the table from which it came, you need to set up the following script. In simple terms, the steps for this comprise:
- Setting the global fields to fulfil the ‘return’ search.
- Finding and deleting the found records in the SearchReturn table, as these are no longer of value, once you have decided which record you wish to view.
- Navigating to the correct layout (and table) by its designation in gLayoutCatcher.
- Finding the right record in that layout by the match on gIDcatcher with the record’s ID, to create the occurrence of ‘1’ in cIfMatchgIDcatcher in the ‘home’ table.
- Clearing the global fields of their values as a matter of good housekeeping in preparation for the next search.
These steps are illustrated exactly in the script definition beneath.
Finally, to execute this return search, you may wish to make the ‘view’ text shown above on the ReturnSearch layout into a clickable button, which you can do as shown beneath by selecting the text in Layout Mode, then using Insert>button and following the steps to attach it to the script above.
Depending on your purposes and the size and structure of your database, there are myriad embellishments you may wish to make to this solution. Nevertheless, its main value is that of isolating, through the use of the temporary set of values in the SearchReturn table, the discrete set of records which meet your specific interest.
And finally, here is the entire first script, as described above.
#Start the search routine Go to Layout [ -Entry" (T1.Globals) ] Go to Field [ T1.Globals::Search ] Pause/Resume Script [ Indefinitely ] #Enter search string Set Field [ T1.Globals::gSearch; T1.Globals::Search ] Clear [ T1.Globals::Search ] [ Select ] #Then go to find in Friends Set Error Capture [ On ] Go to Layout [ -Friends" (T2.Friends) ] Perform Find [ Specified Find Requests: Find Records; Criteria: T2.Friends::cIfMatchComposite: -1" ] [ Restore ] #Import these records into the SearchReturn table Go to Layout [ -SearchReturn" (T4.SearchReturn) ] Import Records [ Source: -file:Builder.fp7"; Target: -T4.SearchReturn"; Method: Add; Character Set: -Windows ANSI"; Field Mapping: Source field 1 import to T4.SearchReturn::iID Source field 6 import to T4.SearchReturn::iComp Source field 8 import to T4.SearchReturn::iTable ] [ No dialog ] #Next, go to find in Contacts Go to Layout [ -Contacts" (T3.Contacts) ] Perform Find [ Specified Find Requests: Find Records; Criteria: T3.Contacts::cIfMatchComposite: -=1" ] [ Restore ] Pause/Resume Script [ Indefinitely ] #Import these records into the SearchReturn table Go to Layout [ -SearchReturn" (T4.SearchReturn) ] Import Records [ Source: -file:Builder.fp7"; Target: -T4.SearchReturn"; Method: Add; Character Set: -Windows ANSI"; Field Mapping: Source field 1 import to T4.SearchReturn::iID Source field 7 import to T4.SearchReturn::iComp Source field 9 import to T4.SearchReturn::iTable ] [ No dialog ] Pause/Resume Script [ Indefinitely ] #Then PassSerial Show All Records If [ Get(FoundCount)=0 ] Go to Layout [ -SearchReturn" (T4.SearchReturn) ] Show Custom Dialog [ Title: "Nothing Found"; Message: "No records found"; Buttons: -OK", -Cancel" ] Exit Script [ ] End If Sort Records [ Specified Sort Order: T4.SearchReturn::iTable; ascending ] [ Restore; No dialog ]