Filemaker Conditional List

There is often a need to limit the lists that is available for selection in an input field. Normally we would use a list that is based on a related table. User either selects a value from the first table or key in a value in the first field. That input value is linked to a related table. On a second field it uses drop down list and the list is taken from the related table. The list will only show the related data based on the relation.

Now there is this condition that requires multiple selection with conditional lists. Lets say a world wide company wants to know a particular group of sales person's performance from a particular state in a particular country in a particular region. Normally it would means that the user chooses a religion like Asia first. Then from the list of Asian countries, choose the country. Then the user narrow down further by choosing a state from the country before he can get a list of sales person's performance.

The first try is to create the fields to do the different selection. Therefore, there will be three input fields 

1. Region
2. Country
3. State

Using the method described in the first paragraph above, It will be easy to choose Region from a unrelated table (sales table). The country field list will then be obtained by joining the Sales table to the input table linked by Region. The question will then be how to get States list based on the Region and Country chosen. Well, you guess it right. It is by joining the sales table with another instance of the sales table linking by Country. Normally this setup will be perfect because Country will be unique. There will not be two Countries that has the same name. However, in real life, there will be instances of duplication.

For example, we want to categorize hardware based on its configuration and make. So we have Brand, Processor, Screen Size, HDD, Ram. Now Brand is unique but Processor might not be. Neither did the rest of fields. Using the Sales method mentioned above, it will be impossible to choose Screen size based on Processor selection. It looks logical to self join the tables like Product::Brand->Product 2::Processor->Product 3::ScreenSize... In actual fact, by the time you choose ScreenSize list, The list is only based on Processor and not by Brand and Processor. It could be a Filemaker bug but that is what I found out.

In order to avoid such confusion, there is a need to revert back to the first paragraph method by just a simple join between two tables. First we have a list from Product::Brand without relation. The Processor input field will then have a relation using "Brand" and the list is taken based on the input table::Brand relation.

The ScreenSize input field then requires a separate product table instance that is joined to the input table by Brand and Processor. The HDD input field list will then have yet another instance of product table that is joined by Brand, Processor and ScreenSize. Finally the Ram input list will be based on a third instance of product table joined by Brand, Processor, ScreenSize, HDD.

By doing this method of joins, user can be assured that the result is exactly as what was defined in the various input fields.