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.
21 Comments
Nice technique! I found this searching for whether it’s possible to create dynamic relationships in Filemaker, and this definitely fits the bill. Something puzzles me, though — when I try to create relationships based on calculations from global fields (or any other unstored calculation), I always end up with invalid relationships. I’m going to try to implement your method as closely as can fit to my problem and see if it works this time.
@dp Thanks for the comment. There are circumstances where using unindexed calculations based on global fields will work and others where they will not work. For example, if table A is related via a global field to table B via an indexed field, you will be able to view related records in table B from table A, but not the other way around. To use the language I used in this article, the target table’s fields used in the relationship must be indexed, but the base table fields may be global.
Thanks for the pointer. I was running into trouble because the relationship was a self-join, and I wasn’t clearly separating which fields were on the base or target side (as you put it here) of the relationship. Everything now works as advertised.
Thanks for the technique Danny, interesting and useful. With regards to this:-
“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.”
You could instead make the FilterTarget definition:-
“Substitute ( Name ; ” ” ; ¶ ) & ¶ & Substitute ( Notes ; ” ” ; ¶ )
& ¶ & Name & ¶ & Notes
This allows for single and multiple word searches, with the limitation that multiple words must appear in the same order as they do in the Name, Notes etc fields (eg ‘The Name’ works but not ‘Name The’).
You could also add in another calc field in the target table which checks if the 1st word of Name is ‘The’ and if so, removes it. For example this could be defined:-
Name_Without_The:-
If (
LeftWords(Name; 1)=”The”;
RightWords(Name; WordCount(Name)-1);
Name
)
with this field defined, the full definition for FilterTarget would then be:-
“Substitute ( Name ; ” ” ; ¶ ) & ¶ & Substitute ( Notes ; ” ” ; ¶ )
& ¶ & Name & ¶ & Notes & ¶ &
Name_Without_The
@Rooster – thanks for sharing the great ideas. These are significant improvements to the technique that can be implemented rather easily.
If you have a big db, you’ll need to turn on indexing for FilterTarget to make this work. Look under Storage Options in the Specify Calculation dialog. Uncheck “do not store calculation results” and choose “Index: minimal” or even “All” if minimal doesn’t work.
Note also that the field referenced by FilterTarget needs to be a straightforward Text field, and cannot be a calculation referencing data in a related table. Not sure this is an issue for anyone else, but it was a change I had to implement.
Ok, I have read this several times and it all makes sense, but not sure how to use this in my solution…even though this is exactly what I need. My confusion is “base table” and “target table”. I need examples, as in…I need to search a table of cats (for example) and let’s call this table cats_db. So is the cats_db my base table? See, this is where you lose me…
@RJ – The cats_db table would be your target table in this case. The base table would be the table that the current layout is viewing. The target is the related table that you are searching. (If you’re familiar with FMP 6 or earlier relationships, the base table would be the left side of the relationship and the target would be the right.) So, the field you are typing the search criteria in is a global field in the base table. The table you are getting results from is the target table.
Hi. I’ve also got stuff I need to search within portals but they are usually date ranges so I was wondering if this technique if done “as is”, would be able to recognize a Filemaker date range search function, for example “1 January 2010..3 March 2010″ Cheers everyone!
@Cassian W – Unfortunately, this method would not work for date ranges “as is”. To search by date range in a portal, you might want to consider using portal filters (new in FMP 11) and perhaps have two separate filter fields for start/end date. Take a look at the Dynamic Portal Filtering While You Type article and the follow-up article, Dynamic Portal Filters With Multiple Criteria for ideas.
It’s a good solution, but the problem that I have is, this solution only works with the first words, not with the rest. I mean, if I have a Title “Filemaker user manual” This cannot find this title introducing “user manual”.
@Eric Bueno – If I understand your problem correctly, it is one of the limitations that I outline under “Limitations”. Try the solution I mention in that section. If that doesn’t work, my recent article, “Dynamic Portal Filtering While You Type,” has a much more robust solution. The method in that article requires FileMaker 11.
This is a very nice technique, however, when I publish my database on the web, a guest with only viewing privileges cannot type in and search the portal window because it makes changes to the FilterStart and FilterTarget fields, which I assume Filemaker thinks is making a change to the database. Any ideas of how to get around this?
I solved the issue, you just need to make the individual fields that were created for this search modifiable under the security options for publishing on the web.
Your way of explaination is nice and clear!! But i would like to somehow have my search global field use pattern count for searching large text notes. But my duplicated tables are not giving any related fields.
@robert jesus – Unfortunately, I don’t understand your problem. What do you mean by “my duplicated tables are not giving any related fields”?
any changes in FM12 that has any impact on the above example. the portal is not showing any records. thanks & regards
@marinko – I’m not aware of any changes in FM12 that would affect this. I’ve converted several databases from FM11 to FM12 that used variations of this feature, and all worked well after the conversion. My guess is that there is either a problem with your filter calculation or the relationship.
thanks for the prompt reply. I put up the three ‘filter’ fields on my form so that I can see what happens when I do a search and filtertarget shows one of the potential matches (from a 2 char search), but the portal window remains empty. the portal window points to a copy of the instance of the target table (there’s only 2 fields + filter target). maybe I just need to go do something else for a while
ps. I’ve gone through your other examples and wanted to check as to best approach. I want to populate a field with the full name of a town from a list of 200+ town in another table by the user typing a few characters that is matched against the full list and returns a small number of results from which the user selects. the above seemed the simplest of the examples that you have put up to achieve this.
Thank you. works very well