FileMaker Inspirations

This article describes a script-less technique for using dynamic relationships and portals to search values in a related table using a single global field. It is a part of a series on Advanced Search Interfaces.

This technique produces an interface similar to the Google-like Search described in previous articles in this series, but instead of using a script and find mode, it uses calculated fields and relationships. This can be quite useful in portal views and requires no scripting.

Place a global search field and button above a portal for Google-like search functionality through a portal.

To create the filter, we will build a relationship between a table with a global search field (we’ll call this the base table) and the table in which we want to view filtered records (we’ll call this the target table). This relationship will be based on three calculated fields: two in the base table and one in the target table.

Step One: Create a Global Search Field

Create a text field, GlobalSearch, in the base table. In the field options, set the new field to use global storage as shown in the figure below.

Under the storage tab in the field options, select the "Use global storage (one value for all records)" check box.

Step Two: Create Calculated Fields For The Relationship

Field 1: FilterStart

In the base table, we will create a calculated field, FilterStart. This field will contain a calculation that will produce the ‘lowest’ value in the possible set of desired results.

The FilterStart calculated field will produce a Text result of a single underscore(_) when GlobalSearch is empty, otherwise it will simply be the value of the GlobalSearch field. In a sort, this value would always appear at the beginning of the desired search results.

• The FilterStart calculation, as shown in the image above, will be as follows: Case ( Isempty ( GlobalSearch ) ; "_" ; GlobalSearch )
• The calculation result is Text
• The “Do not evaluate …” check box should be checked if you want to show nothing when the search field is empty, unchecked if you want to show all records when the search field is empty.

Field 2: Filter End

Also in the base table, we will create another calculated field, FilterEnd. This field will contain a calculation that will produce the ‘highest’ value in the possible set of desired results.

The FilterEnd calculated field will produce a Text result of the contents of GlobalSearch followed by the repeated letter z. In a sort, this value would always appear at the end of the desired search results.

• The FilterEnd calculation, as shown in the image above, will be as follows: GlobalSearch & "zzz"

• The calculation result is Text
• The “Do not evaluate …” check box should be checked if you want to show nothing when the search field is empty, unchecked if you want to show all records when the search field is empty.

Field 3: FilterTarget

In the target table, we will create a calculated field, FilterTarget, which will contain a value list of values from each of the fields we wish to search on. For instance, if we have the two fields, Name and Notes, and we want search on each of these, our calculation would be as follows:

• The FilterTarget calculation will be as follows: Name & ¶ & Notes

• The calculation result is Text

Step Three: Build The Relationship

Add a new table occurrence of the target table to the relationship graph.

Define a relationship between the base table and the new occurrence of the target table.

The relationship will be based on two criteria:

• FilterStart in the base table is less than or equal to FilterTarget in the target table.
• FilterEnd in the base table is greater than or equal to FilterTarget in the target table.

The result in the relationship graph should look something like this:

Step Four: Add Layout Objects

You can then place the global field and a search button which simply performs a commit.

In the Button Setup Dialog for the search button, simply select Commit Records/Requests. No script necessary.

Below the search field, add a portal to the new table occurrence you just created in the relationship graph.

That’s it! Now the user can simply enter search terms in the search field and view their results in the portal.

Limitations

One limitation of this method is that it will only match on the first word or words of the target table’s fields. For instance, typing “Doe” in the search field will not match a record with Name “John Doe”. One possible solution would be to substitute spaces in the FilterTarget calculation with carriage returns:

Substitute ( Name ; " " ; ¶ ) & ¶ & Substitute ( Notes ; " " ; ¶ )

This allows searching on any word. However, this introduces a new limitation; You may now only match on single search terms. Any muliple word searches will result in no matches. A possible solution to this would be to perform the same substitution in the FilterStart and FilterEnd fields. The problem with this solution is that our results will be based on an OR search rather than an AND search. In other words, if “John Doe” is typed as the search term, the resulting set will be all records with “John” and all records with “Doe”.

To achieve a true Google-like search showing all records that contain a search phrase within a field, we would either have to introduce some scripting, perhaps using triggers, or produce calculations with much greater complexity which could greatly increase file size (due to indexing) and goes beyond the scope of this particular article.

So, use this method when matching from the beginning of the target field or matching on a single term is sufficient.

For a much more robust and streamlined solution that eliminates all of these limitations using the Portal Filtering feature introduced in FileMaker 11, see Dynamic Portal Filtering While You Type.

If you have any questions or comments feel free to post a comment below or contact me directly.