Complex Filtering Using Related Record Information

This article demonstrates how you can filter a relationship to one table, by using information located in a related table. The example we will be using is filtering a relationship to a Customer table, but by searching on a customers related Contacts, which are in an entirely different table.

What are you Talking About?

We are talking about the filtering of a relationship. There are a few various methods as to how this can be done, but we will be using a search field, that will match the contents of a key field generated on the related table.

This article is going to assume you have knowledge about basic concepts behind filtering. If you are unfamiliar with the concepts, there are a number of sites offering techniques on how to achieve this. A quick google search for "FileMaker Relationship Filter" will yield some results, or you can check out this great article on relationship filtering by Danny Kohn of FileMaker Inspirations. This particular article does not use the exact technique mentioned in order to keep the complexity level to a minimum, but the technique mentioned in the article is a great one nonetheless.

Setting up the Example Case

The example we will be using to demonstrate this technique is a simple one using two tables - Customers and Contacts. The relationship is that a Customer can have many Contacts, and so the foreign key field "Customer ID" is placed on the Contacts table.

Both tables contain basic fields you would expect to find in these types of tables. Primary keys are Customer ID and Contact ID respectively.

The zLocatorKey fields found in both tables, are special key fields used to accomplish the filtering of the relationship. While this type of key is not exactly the most efficient in terms of size, it is relatively easy to grasp how it works to achieve the filtering. In order to build this key, we have used a couple of custom functions, which we will not be going into detail about given the scope of this article, suffice to say you can check them out in the example file.

The Goal We are Trying to Achieve:

So, with all of this said, what is it we are actually trying to achieve here? The goal is to produce a portal based on the Customers table, which can be filtered by typing into a search field.

We wish to allow the user to search by customer name, which should be a fairly straightforward task given that it is the customers table being filtered, and so the customers name resides on the table we are filtering.

HOWEVER, we also wish for the user to type in a contacts name, and have matching customers also appear. How would we do this? Contact information is not kept on the Customer table at all. The only association between the two is via the primary/foreign key relationship.

You might think the answer would be to place the related contact information on the Customers table, but stop! Any calculation placed on the Customer table to pull through related contact information is going to be unstored, and thus any relationship based on this calculation would break. The destination side of any relationship must have all predicates stored.

Perhaps the answer then is to store contact information on the Customer table, thus allowing us to filter customers directly. Well, in order to do this, a great deal of extra work and scripting will be required:

• Any change to a contact name would have to be updated on the Customer record
• Any adding/deleting of contacts would have to be updated also
• This would have to be done via script triggers, scripts, server-side scripting etc.
• In addition, you are essentially storing the same data twice.

The Proposed Solution:

To best illustrate the solution to this problem, have a look at the following diagram:

Step one is to carry out the filtering on a Contacts table occurrence. The filtering is setup just as if we were filtering contacts. The users enter a value into the search field, and matching contacts are found. If the relationship yields matching contacts, then we know which customer they belong to by way of their Customer ID foreign key.

Step two involves obtaining the contact results. The important piece of information concerns the customer the results belong to - Customer ID. This information is pulled back to the base context which in this case is "Home". The results are pulled back by using the List function to produce a return-delimited list of Customer IDs.

The final step now uses these resulting customer IDs in the relationship to the customers table. Any customers found through this relationship, we know have contacts that match the entered search term, because the customer IDs were obtained originally from a search for contacts.

That is the basic process in a nutshell. Depending on how you tweak these relationships further, you can achieve your desired behavior.

Example File Explained:

In the example file provided, the context for these relationships is "Home". The Home table contains the following fields:

Filter Search: Global text field the user enters search term into
Filter Key:Match field used in relationship to Customers to drive the portal
Filter Customer IDs: Return delimited list of Customer IDs obtained from the match through the Contacts relationship.

The relationship graph is setup as follows:

The first relationship is between Home & Contacts, and is used to obtain matching contacts based on the search value the user enters:

The relationship is to the Contacts zLocatorKey field. This field has been setup as a basic return delimited list of possible search terms for a contact, mainly just their given, family, and full names.

The second relationship is between Home & Customers, and is used to display the desired results in the portal.

This relationship is from a special key field called Filter Key.

If ( IsEmpty ( Filter Search ) ; "*All*" ; List ( Filter Search ; Filter Customer IDs ) )

If the user enters nothing into the search field, a special keyword "*All*" is used. The purpose of this is purely to determine how the portal will look when the user has not searched for anything. There are two possibilities here, either the portal shows nothing, or the portal shows all records. In this case, we have decided to show all records.

In order for the portal to show all records, we need to ensure the relationship is setup to find all records. In the zLocatorKey field in the Customers table, we have ensured every customer record contains the "*All*" identifier in its key, thus every record will be found through the relationship in this case.

Back to the calculation, if the user HAS in fact entered a search term, the following is used:

 List ( Filter Search ; Filter Customer IDs )

This is a return delimited list of both the users search value, and a return-delimited list of customer IDs obtained via the search on contacts earlier.

The end result is a key which should find all matching customers by both the customer names, AND by contacts that were matched by their names.

The contents of the Filter Customer IDs calculation field is simply a list of customer IDs through the Contacts relationship.

NOTE: About use of List function:

While this is a useful technique, it needs to be used with caution in some situations. This is because of the use of the list function to pull back customer IDs from matching contact records.

Whenever the list function is used, FileMaker downloads the entire record from records being listed, not just the field being listed. Because of this, if you search for "David" for example, and there are hundreds of David contacts in the system, then FileMaker will download all of those records in order to achieve the list of their Customer IDs. Over a local file, or LAN, this might be acceptable. Over WAN however, this may exhibit itself as slow filtering, given all records need to be downloaded.

For this reason, the example file has not been implemented with a live filtering functionality.

Final Thoughts:

This is a useful concept for carrying out complex filtering of relationships. The core idea is that you can filter relationships using other related tables, by pulling the results back to your main context, and then using them in your primary relationship.

As with most techniques, there are always drawbacks, the primary one in this case being the use of the List function to pull back results. This can have implications in solutions distributed over WAN or even LAN depending on the size of the tables concerned.

Example File

Please find attached an example file. This file was used for all the screenshots in this article, and is provided to help you fully understand what is going on in this article, and to let you experiment in FileMaker with this solution.

Download Example File