FileMaker Weetbicks

Conditional Value Lists Pt 1 - What Are They & How to Build Them

September 13, 2010 By Daniel

Conditional Value lists may be the most asked about piece of functionality on the FileMaker Cafe. To try and provide a thorough and easy to follow explanation of what they are and how they work, I decided to write an article on the subject and build a small example file to help illustrate the technique.

What is a conditional value list?

A conditional value list is any value list whose contents can dynamically change. The most common method to change a value lists values is to change the contents of another field. In this case, the values that are displayed in the value list are dependent upon the value in the field. Indeed, the terms conditional and dependent can be interchangeable.

Why are they used?

Conditional value lists are a great way to narrow down the size of large value lists by breaking them down into smaller manageable lists. They also help when you need to select values for 2 or more fields. The easiest way to show this is to jump straight in with a straightforward example.

Sport Equipment Example

I have a database that is used for recording a large variety of sporting equipment. I have a sport table that contains the following fields:

• Sport Category
• Sport Name
• Sport Equipment

Each item of equipment can be categorised into a specific sport, and that sport falls into a specific sporting category. Examples of sporting categories might be Ball sports, stick sports, contact sports. A sport name might be Hockey, and for that sport the equipment could be a stick, a puck, a net, or gloves.

Lets imagine now, that the database also acts as a way to hire out sporting equipment. When a piece of equipment is hired out to someone, a record is created in the database, and that item of equipment is captured on the record.

One field, one value list?

The piece of sporting equipment could be recorded into a single field. One of the problems with this is if there are many thousand items of sporting equipment, the value list is very large and may be tedious to select the item.

Consider also, that many sports contain the same type of item. for example Netball, Basketball, and Ice Hockey all have nets, so if you chose "net" as the item of equipment, how would you know what type of net it is ?

Three fields, three value lists

A more clear solution is to define the item of equipment as a category, name, and the item itself - basically use the fields already in the sport equipment table.

The first step is to select a category - there are far fewer sporting categories than there are items of equipment, so selecting a category should be very easy.

With a category chosen, the next step is to choose a sport. There is no value in showing EVERY sport, instead, what you would expect to see is just the sports that are of the category you have chosen. Again this will keep the list of sports much smaller and more manageable.

Lastly, to select an item of equipment, you would expect to only see items of equipment that belong to the sport you have chosen, which itself belongs to the category you have chosen.

Constructing the Value Lists

The category value list is easy. It's just a straight up value list of all sport categories from the table. The "All Values" option is used in the value list.

The sport Name value list is the first of the "conditional" lists. Conditional value lists are built using the "Include only related values starting from" option in the value list setup. Because of this, conditional value lists also require you to create a relationship for the purpose.

In the image above, I have created a relationship between my main table (which contains the fields I am entering values into), and the Sport Equipment table. The condition on the relationship is Sport Category = Sport Category. This means the relationship is setup to only find sport Equipment whose category is the same as the category I have chosen. That is the condition. By building a value list based on this relationship - the value list will also only show sport names which belong to the category I have chosen. Note that relationships & value lists are basically the same thing - the value list is showing records through the relationship you define.

Here, you can see the value list built that is based on the relationship created above. The field the value list is displaying is Sport Name through the relationship. The most important part of the setup is the "Include only related values starting from" option - in this case it is set to "Home" which is the table occurrence on the left hand / source side of the relationship.

The end result is a list of sport names for the chosen category.

Taking this principle to the next step, the Sport Equipment field can have a value list built which shows just equipment for the chosen category AND name. In this case, the condition is that my chosen sport category AND name, matches all Sport equipment records with the same category and equipment.

Here is the relationship created for the second value list construction. The two conditions are specified.

The value list is built in the exact same way as the one for Sport Names, however this time the new relationship is chosen, and sport equipment will be the field in the value list.

(the results of all 3 value lists are shown in the earlier pictures).

Final Thoughts…

That's it, it's just that simple. Conditional value lists are built using relationships. the condition part refers to the fact that the values are dependent upon a value you choose in another field - that field is then used in the relationship to narrow down the records through the relationship - and thus the records in the value list.

Example File

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

Download Example File