Conditional Value Lists Pt 1 - What Are They & How to Build Them
By Daniel Wood, 15 December 2010
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.
Something to say? Post a comment...
Comments
Daniel Wood 01/01/2013 10:35am (12 years ago)
Hi Stephen, that is a tricky one indeed! Firstly, the reason why you stop seeing the sport name in your example and see a number instead, is because that particular value list associated with sport name, no longer contains the value for record ID 5. The reason the value list no longer contains it, is because you've changed that sports category, and thus that particular sport name now belongs to another category - to put it another way:
If a field (sport name) contains an ID that is not in its associated value list, then it can't render the second value list value (the actual name of the sport) and thus you just see the value of the field, in this case 5. Now, you could as you say overlay a field on top of this to display the sport name, but that doesn't really solve the fact that the sport name no longer matches the chosen sport category on the home record.
The question of how you resolve this kind of depends upon which fields on home you expect to change. Taking your example again, you've changed the sport category for a particular sport name, now when you go back to home, do you expect to see a) the sport name cleared because the chosen category doesn't match it, or b) the sport category clear because the sport name doesn't match it, or c) the sport category stay the same but sport name change to a valid one for that category, or d) the sport category change to the correct one for the sport name (eg from 2 to 3)....
So there is no one specific way to fix it, it all depends on what you expect to have happen and you would have to program that accordingly. There is also no 'automatic' way to achieve this, you are likely going to have to write a script, because when you change the values of records on your "value list" tables, it is not going to trigger an update to your home record(s).
Before I give a possible way of solving it, there may be another method that might be more suitable here. What if you didn't use a series of value lists and instead used a single value list? depending on how many overall potential values this might be a viable solution. If you are choosing one record instead of multiple, then if you change that single record then your home value need not change, it should point to the same record and whatever category/name is on that single record...
Anyways, back to multiple value list issue, While it is not very elegant I might start with an onRecordLoad script trigger or a similar trigger to run a script. That script will then look at the IDs on your record and determine if any are no longer valid, the order in which you check these kind of depends on what you expect the behavior to be.
Key parts of the script would include using the ValueListItems function. This function can tell you all values of a particular value list. Another useful function you could use is a function to tell you if a given value is in a list of values - FM has no native function to do this, but you can use the one here: http://www.briandunning.com/cf/1174 (there are also many other variations on that site you could use). You can use these two functions in conjunction to determine if the value in one of your fields is in its associated value list anymore, eg:
ValueExists ( Home::Sport Name ; ValueListItems ( Get ( FileName ) ; "SportNames" ) )
This would return 1 if the sport name is still valid and in its value list, if not then something has changed...
In your original example, if sport name is no longer in its value list because the category of that sport name has changed, then you could use the above to test this, if this is indeed the case, then your choice of action could be to clear sport name, or to set sport category to the correct category for the name, which you could do by going to the sport name layout and searching for that name and finding out what its category is etc....
Hope this helps, basically there is no nice elegant solution that I know of, the solution depends on what you expect to happen, and you will likely have to script this
Stephen M 30/12/2012 2:12pm (12 years ago)
Hello Daniel! Thank you for posting these articles, they have been immensely valuable as a learning tool as I dive back into FileMaker. The solution you posted for Susan on November 30 was great, because I have a similar setup and your example for her helped me as well. I'm stuck on an issue though, don't know how to handle it, and am hoping you can offer your expertise & insight.
Let's say you've filled out all 4 fields on the Home record and everything is great. Then you realize there's a (hypothetical) mistake in an association / entry, so you go to the parent table, say, Sports Name, and change the Sports Category for "Squash" from 2 (Racket), to 3 (Water). Everything else for Squash is still correct as is.
However, when you go back to the "Home" layout, "Sport Name" now just displays its ID value, 5, instead of saying "Squash", and the Sport Category has not updated to accurately reflect "Water", because its underlying ID value is still showing "2" for Racket Sports, even though in the Sport Name layout you changed Squash's Sport Category to "3" (Water).
On my own I've tried everything I could think of -- changing relationships, setting auto-enter on fields, using calculation fields, lookups, script triggers, etc... to try and capture the change in the parent table and somehow force an update in the Home table, but I'm still stumped and feeling rather clueless.
In my DB, "Home" is actually a Join Table (with the Category --> Name conditional coming from the left side of the relationship), so there could potentially be hundreds of "Squash" entries that would suddenly be "broken". In my case I only have 1 conditional ("Name", keeping with the demo / example), so I've contemplated taking the "Category" data out of a separate table and just including it in the "Name" table, but that isn't particularly appealing, as the Category may potentially change and it'd be easier for the changed value to elegantly flow via a relationship to all records that have it.
The best I could manage was using related fields overlaid on top of the menus in the "Home" (join) table / layout, which then display the correct value based on the parent record for Squash (whose ID value is still correct), but the Category field in the Home record still has the original (and now inaccurate) value.
How can one account for this and ensure that data remains "in synch" in the "Home" record when one of the underlying field values has changed in the corresponding parent table?
I've scoured every online resource I could find and am still at a loss for how to address this issue. My brain seems to be too fried to figure out a solution, so I'm wondering if I'm missing something obvious or just have a bad table / relationship design. Is there a best practice for how to handle this?
Thank you in advance for any insight you can offer, and Happy New Year!
Daniel Wood 21/12/2012 10:15am (12 years ago)
Hi Jesse, your relational structure there looks correct. The value list you want to create is going to be setup using "show related values" option. On your graph you will require a relationship between Event and Supervisor (Supervisor = ID) and then from that Supervisor table occurrence to Venues via your other relationship, so it will be a chain of 3 table occurrences (event, supervisor, venue) with 2 relationships joining them.
The value list should show values from Venues table occurrence. Under the other options, choose "Show only related values starting from" and choose the Events table occurrence". The value list should be hooked up to the Supervisor field on the Events layout. If this doesn't work then I"ll need to know a bit more about your graph setup and value list setup to diagnose further, cheers.
Jesse Roberts 21/12/2012 10:10am (12 years ago)
So...
i have a db with a number of tables in it, but we will talk about the Event table, the Supervisor table, and the Venues table. Supervisors have territories, where different Venues are. When i choose a supervisor for an event, i only want the venues under that supervisor's purview to display in the dropdown. right now, Event Records::Supervisor = Supervisor::ID and Supervisor::State = Venues::State. Am i missing something to get the list to filter out?
Susan Stewart 30/11/2012 12:16pm (12 years ago)
You are AWESOME. I went through your example thoroughly and I think it's going to work for us. We even tried it out by creating another table after manufacturer to test our understanding. Can't thank you enough...
Sincerely, Susan.
Daniel Wood 30/11/2012 10:06am (12 years ago)
Hi Marco, thank you for the comment, appreciate it :)
Susan, I have whipped up a sample file for you which hopefully will help you with your scenario. Please download it here - http://www.teamdf.com/files/weetbicks/conditionalvl/SportsValueList.zip
I've used the 4 tables in question and a "Home" table which is where the IDs that you are selecting in your fields/value lists are going. Pay attention to the graph setup for the relationships required, and then how the individual value lists are setup using those relationships.
I have also put some auto-enter calculations on the selection fields too. The purpose to these is that they trigger when a particular popup value is chosen, so that values are cleared. For example if you have all 4 values selected, and then you change the Sports Name, you wouldn't want to leave Sports Equipment & Manufacturer with values, because the values in those fields may no longer be relevant to the new Sports Name you choose, so the auto enters work to clear the values depending on which values are changed.
Cheers!
Susan Stewart 30/11/2012 9:13am (12 years ago)
Hi Daniel,
Thanks for this great example and it works very well in a flat file table instance. My problem is that I've got 4 tables. Sports category, sports name, sports equipment, and manufacturer. Each table has a key field that relates only to the table next to it. So Sport Category Key is stored in sports name, sports name key is stored in sports equipment, and sports equipment key is stored in manufacturer. There is no master table that stores all of these fields in one table.
Hence, I'm having a lot of trouble setting up Conditional Value Lists because setting up multiple occurrences of the table doesn't really make sense to this design.
I can get the sports category and the sports name value list easily but I can't get the sports equipment value list to work properly because it does not directly relate to sports category.
I've been working on this for days and have tried so many different ideas. I'm currently trying some script triggers on the equipment field but they don't seem to have any effect on the drop-down list.
Any and every idea is welcome!!!
Thanks so much.
Marco 30/11/2012 2:33am (12 years ago)
Hi Daniel,
Conditional Value List is not the problem. The problem is how people explain things to confused people.
Kim asked you the same type of question I have in my mind about it. Conditional Value List goes beyond the choices one has to click in the Specify Fields for Value List window in Filemaker. Many places don't talk about that. They just give the explanation on the Filemaker app side. Filemaker Help, VTC and Lynda also do.
<b>You are the first person that talked about the context side.</b>
<blockquote>Thank you for this Daniel! One question, why do you need another occurrence of everything for the portal example? And is this extra occurrence set up in the exact same way as the one you walk through on the example?
Thank you!
Kim</blockquote>
She is right to ask you that because many people, including me, don't get it just by the explanation from the Filemaker side of conditional value list.
I just bookmarked your explanation and part 2, it is a great explanation and will help a lot.
Paulo Felix 05/10/2011 6:10am (13 years ago)
Many thanks for this site. I'm not a professional FileMaker developer but I love to build solutions based on this great software for personal use and limited distribution among my friends and colleagues. This blog helped me a lot.
Robert Moran 08/06/2011 3:03am (14 years ago)
Great site. Daniel has helped me before on numerous occasions on another FMPro environment and no doubt, I will be asking more questions as FM is an ocean of an app most people are unaware of. Also, I too am an avid cyclist of the road variety. Awesome sport my son turned me onto over 20 years ago. :)
Daniel Wood 28/04/2011 5:09pm (14 years ago)
Hi Kim,
The reason why the portal example requires additional table occurrences which are different to the ones used in the first example, is all to do with context. In the first example, the value lists are being used on the layouts context. The sport category/name/equipment fields are all direct on the layout.
However, in the portal example, the fields are now in a portal which is a different table occurrence. As such, if we used the original value lists from the first example, it simply isn't going to work
If you have a look at how the value lists are setup for the portal situation, you'll notice that for each, the "include only related values starting from" option is set to the table occurrence of the portal, whereas in the first situation it is set on the layouts table occurrence.
I'm probably not making this any clearer, and it is a little difficult to explain. In the first example, the context that the value lists are populated through is that of the layouts main context. So when the record changes, the value lists change because the source/left hand side of the relationship is changing.
In the portal case, the layouts record is not actually changing, but it is the portal rows themselves which are the things that are changing. If we used the value lists here from the first example, the values in those value lists would never change, because the value lists are still evaluating from the context of the layout, not the portal. In order for the value lists to work properly in a portal, they must be evaluated from the context of the portal, and obtain related values starting from that portals table occurrence.
Now, to put that all together, consider if you wanted to try and use the existing table occurrences that the first set of value lists are based on, simply by duplicating those value lists and changing the context they evaluate from. Looking at the sport names value list for example, the value list would begin from the portal context, go through the layouts context, and then to the sport name table occurrence:
Home to Home for Portal Example Home to Sport Equipment for Sport Names
If we actually tried to evaluate this, all portal records are going to relate back to the same home record because this is a many to one relationship going backwards to the main table occurrence like this. From there, it continues traversing the relationship from the Home record (1 record for all portal rows) to sport names.... basically the end result is every portal rows sport names value list will end up being exactly the same and not changing on a portal row by portal row basis.
have I made this even more confusing now? In short - the whole thing is a matter of context - what context the value lists begin evaluating from.
Kim 28/04/2011 10:08am (14 years ago)
Thank you for this Daniel! One question, why do you need another occurrence of everything for the portal example? And is this extra occurrence set up in the exact same way as the one you walk through on the example?
Thank you!
Kim
Jon 03/04/2011 6:24pm (14 years ago)
Well I have some good news. Your post consumed me to the point where I did plenty of research and actually managed to pull this off myself (pats self on the back). WIth a combination of your post, my Filemaker Pro Missing Manual and some filemaker forum posts I was able to complete my goal.
I ended up using an If function nested in a Case function to achieve my weird setup.
Don't worry though, I'm sure there are many other advanced stuff I have yet to figure out. Thanks for the blog and I might request your assistance in the future. Thanks for the quick reply.
Cheers.
Daniel Wood 03/04/2011 12:00pm (14 years ago)
Hi Jon, thanks for your comments. Flick me an e-mail and I'll see what I can do to help, I may need to ask a few more questions about your solution, cheers.
Jon 03/04/2011 10:59am (14 years ago)
Want to say thanks for your blog, it's been a breath of fresh air as I try to learn some more complicated things for my database. I do have a quick question though.
Can conditional lists be used with calculations as well? Let me explain.
Currently I am working on a database for managing bands and shows that they play. At the end of the night there are many factors that determine how much money the band gets paid. First of all there is a guarantee amount, say $500. Often times the band will get a percentage of the door funds. Venues will say, in additional to your guarantee, you will receive 20% of the door funds.
Other times the venue will say you get your guarantee or 80% of the door, whichever is more.
I want a conditional value that is allows me to choose where the door funds are in addition or in replacement to the guarantee. This selection will dictate in my calculation I have set up how much money the band gets paid at the end of the night.
I'm sure there is a way to do it but I'm to dumb to figure it out. Any help would be greatly approached and hopefully I explained my situation clearly.
Thanks again.
« previous 1 2
No one has commented on this page yet.
RSS feed for comments on this page | RSS feed for all comments