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:
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).
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.
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.
RSS feed for comments on this page | RSS feed for all comments
KarenG 07/02/2022 3:37pm (1 year ago)
you've done it again - an oldie, but a great one. Thank you for your posts and sample files.
Daniel Wood 28/01/2019 10:51am (4 years ago)
thank you for the comment. So the value lists are built entirely from the record data. Using the demo file as an example, we have a single table named "Sport Equipment" and in that each record corresponds to a piece of sport equipment. So the answer to your question is that to modify the value lists to include more equipment it's simply a case of adding more records.
Now, how you actually go about this is entirely up to you. If you want users to have the ability to add entries, you could create a script that the user can run to do this, it might ask the user for the equipment name, then go and create the record for the user.
In the demo file itself it's a very basic example where we are using purely names of things and entering these into our entry fields. Typically conditional value lists have the user select a record ID underneath, and just show them the equipment names.
So if you wanted in this example to change Skates to Skates (Mens) you can do this for the value list by changing the field value on the equipment record, BUT this is not going to change the name across all records that have previously selected "Skates" as its value, you'd have to go and update all the records.
This is why having the user select the underlying ID of that equipment record is usually the more accepted way. Because you are selecting a direct link to the equipment record, changing the name on the equipment record will have the effect of visually showing the new name across all records that are linked to it.
Let me know if you have any further questions.
Marco 26/01/2019 1:04am (4 years ago)
Great tutorial! Thank you!
One question... how can I allow new values entered into either of the three columns to create a new record in the source table, so that new combination of values can be available going forward?
For example, using the sports data above, let's say we previously had just the word "Skates" to choose from, but the user wants to add whether the scates are for men or women like this 'Skates (Mens),' or 'Skates (Women's). How can we set it up so that the user can add these new items, not just to the record, but so that adding it to the record also adds it to the value list for future users to choose from?
Raymond Henry 21/11/2017 1:00pm (6 years ago)
Great refresher artical. When ever I am faced with the need to create conditional lists I invariably forget and have to relearn a crucial step. The article has saved me a couple of hours floundering around. Thank you.
Paul Hutton 21/08/2015 5:31pm (8 years ago)
Following on from Daniel's comment, if the parent and the children are the same table, you can use a self-relationship to get a list of all children or all parents.
Assuming you have two table occurrences of the base table, one labeled <b>Parent</b> and one labeled <b>Child</>, you could create a <b>ParentIDs</b> field as follows (calculated on the Child table):
List ( Parent::ID ; Parent::ParentIDs )
That will give you your parent's ID, and their parent's, and their parent's, etc. Is that the sort of thing you're after?
Listing all children (and children's children, etc.) is a bit trickier. You'd need a <b>ChildIDsPlusMine</b> field (calculated on the Parent table):
List ( ID ; ChildIDs )
and, obviously, a ChildIDs field:
List ( Child::ChildIDsPlusMine )
That should do the trick, no matter how many layers you have!
Daniel Wood 21/08/2015 9:33am (8 years ago)
hi Charles, thanks for the comment. Consider an example where Parent is A and child is B. I'm also assuming here that all your children, grand children, great grand children etc are all just in a single table and you don't have separate physical tables for these (even if you do this should still apply).
The standard case is for a given parent showing all the children. Done with a relationship from A -> B , and your value list shows all related records from B given a starting context of A.
Now consider showing grandchildren. Your relationship structure would be A -> B -> C . Where C is a table occurrence of children who are children of those in B. To list all the grand children for a given parent in A, the value list would show all related records from C with a starting context of A.
Similarly for great grandchildren: A -> B -> C -> D. Value list shows related records from D with a starting context of A.
Without more specific information around your solutions schema I can't offer much more assistance but hopefully that helps.
Charles 21/08/2015 1:50am (8 years ago)
I just stumbled on the Digital Fusion website, and the is discussion on conditional value list is discovered as a blessing. I need to time to study it well and internalised it. However, I currently have a problem with being able to list a generational offsprings of a particular parent table. In parent-child relationship, I am able to list children of a particular parent. What do I do to be able to list grandchildren, grand-grand children, grand-grand- grand...children and so on. Is it possible?
Tim KUzniar 03/12/2014 5:45am (8 years ago)
Hi Daniel..! Many thanks for that info. Yes, all I needed was a sample of the syntax and where to put it. Looks like everything is working fine.
If I run into any other interesting problems as this project progresses I may beg some more help. :)
Again, thanks for your time and patience and I will be sure to check in here frequently to glean whatever information I can from others' situations as well.
Daniel Wood 02/12/2014 1:14pm (8 years ago)
Hi Tim, without going into a huge amount of detail here, the easiest way is to use portal filtering (a setting on a portal setup). This allows you to define a calculation which dictates when records will show in the portal. Portal filtering works in addition to the relationship that is defining the portals records.
Assuming in this case you have the 4 mentioned fields on the table you are displaying records from, it might look something like:
if ( not isempty ( companyFilter ) ; companyFilter = PortalTable::Company Name ; 1 ) and
if ( not isempty ( categoryFilter ) ; categoryFilter = PortalTable::Financial Category ; 1 ) and .....
Essentially if the user has specified a value in a drop-down, the calculation will only show records that match in the appropriate field. If the user has not defined a filter value, then the records will show up. Hope that gets you on the right track. I'd do a google search for FileMaker portal filtering.
Please note - if your solution is deployed over WAN or depending on the number of records in you portal, then portal filtering can have performance impacts given the way it works. There are other ways to filter but this is the simplest and probably best if your solution is just a local offline database.
Tim Kuzniar 02/12/2014 10:31am (8 years ago)
Hi Daniel..! Many thanks for the prompt response. I have the sample DB and have checked it out and the concepts will definitely work in my project. One other question from this newbie -- how would I use the four individual choices in drop lists like this to filter records that are seen in a portal...?
For instance, in my project I need the user to pick entries in the four drop lists to choose 1) 'Company Name' 2) 'Financial Category' 3) 'Financial Product' and 4) 'Financial Vehicle' and then have the appropriate records display in a portal. Any suggestions would be greatly appreciated. Thanks...!
Daniel Wood 02/12/2014 10:12am (8 years ago)
Hi Tim, thanks for the comment. The link should include a /assets/ before the files folder. I've checked the links in the article and they appear to be working and correct, can you try them again? if not the correct URL is: http://www.teamdf.com/assets/files/weetbicks/conditionalvl/SportsValueList.zip cheers!
Tim Kuzniar 02/12/2014 9:56am (8 years ago)
Hi Daniel..! Great blog..! I am new to Filemaker programming and read with interest the discussion on multiple conditional value lists. I noticed you had posted an example file (http://www.teamdf.com/files/weetbicks/conditionalvl/SportsValueList.zip) but when I try to go there I get an error message. This example sounds exactly like what I need to solve a problem I have on a project database -- I need to be able to have the user pick four different values (each one conditional to the choice before it) and then have the appropriate records displayed in a portal. Any help on this would be greatly appreciated. Thanks..!!
Daniel Wood 13/02/2014 9:37am (9 years ago)
Hi Chris, thank you for the comment. I've whipped up a quick example file to try and help you, using your own tables & fields. Hopefully I have the structure correct that resembles your own system.
You can download it here: http://www.sendspace.com/file/a9eyyl
If you have any further questions please let me know.
CHRIS 12/02/2014 5:22pm (9 years ago)
Hi there, and thanks for this blog.. its a great resource.. I am having a heck of a time getting the conditional value list to work for me and I wonder if there is a fundamental problem with my design.. I am using FMP12. I am making an asset management DB, all records were brought in from an excel file.
In the New DB I have 2 tables. 1 called ASSET MANAGEMENT, another called ITEM INVENTORY. The asset Management table is the parent table and contains the auto enter serials (ID_K), and some search globals. the Item inventory Table is related by the ID's and then contains all the inventory Data.
What I am trying to do is have cascading value lists starting with Department, Sub Department, then Device category. I am using the search globals to filter the data. All the data is contained in one table so i have been trying to make another instance of the table to make the relationship to base a value list on.. but still no joy... can you tell me if I am starting from the wrong place to have this function work ? Please let me know If I can elaborate.. thanks again.
Larry Schunk 09/04/2013 9:59am (10 years ago)
The example file you provided for Susan saved me from a long time of head scratching AND taught me several very valuable lessons in the process. Thank you so much.
Atalanta 05/02/2013 12:08pm (10 years ago)
Thank you for the suggestion. Rather than trying to refresh the fields (couple thousand would take too long), I put in a script trigger so that when the Master ID field is filled in Consumers, it flags the Master record accordingly.
However I am going bald trying to get the relationship to work.
If I pull the values from MASTER and then constrain by MASTER_CONSUMER LIMITED it doesn't work.
I have MASTER_CONSUMER LIMITED as
Master IDp x Master IDs
InConsumer = zc_Zero_n
I have tried it from the context of the MASTER table and the CONSUMER table. I have also tried pulling the value list from the relationship.
I think I'm going to stop thinking about it for a few days, go take my FM12 cert exam, and then go back to looking at it again.
Thank you for your help!!!
Paul Hutton 05/02/2013 10:36am (10 years ago)
You are quite right, the IsConsumer field won't work if it is an unstored calculation ? goes to show what happens when you try to do this sort of thing in your head!
This is going to get a little messy. The reason is that you are trying to use relationships to find MASTER records that don't have a relationship (!) to CONSUMER records. So we need to flag those records somehow.
You will need to change the IsConsumer field to a stored value, probably a Number field with an auto-enter calculation:
Let ( Trigger = MASTER ID ;
IsEmpty ( CONSUMER::MASTER ID ) ;
) // End If
) // End Let
This calculation can then be updated by doing a replace on the MASTER table's ID field (just MASTER::MASTER ID = MASTER::MASTER ID). This could be done whenever a new CONSUMER record is created or deleted to ensure the field is always updated. Or it could be done just before the relationship is used if you are worried about it getting out of sync.
If that is done, then the relationships described above will work just fine. Just be careful to ensure the IsConsumer field is refreshed appropriately.
Let me know how that goes!
Atalanta 05/02/2013 9:53am (10 years ago)
Thank you for the suggestion but don't relationships behave wonky with unstored calculations? (I'm in FM12)
I tried your suggestion and it didn't work. I either get all or nothing. For the value list, pulling fields from MASTER and then limiting it with Master_Consumer or just doing the value list with Master_Consumer.
I've tried the relationships for the value list (using your suggestion to create IsConsumer (in Master which does calculate properly) and the ZeroConst (in Consumer) under different permutations.
The value list is being called from a layout based on Consumers.
Am I just missing something totally obvious here????
Paul Hutton 30/01/2013 10:10am (10 years ago)
What you have described sounds perfectly achievable. Send me an e-mail (at teamdf.com, I'm paul) if you like with details about exactly what's going wrong and I'll see what I can do to help.
Paul Hutton 30/01/2013 10:01am (10 years ago)
I'm not sure of your table structure, but I'll make a guess:
You have a MASTER table, with each row having ID and Name fields.
You have a CONSUMER table, with each row having a MASTER ID field. This is the field the value list is attached to.
If this is right, then you will want a relationship from MASTER to CONSUMER based on matching IDs, and a calculation on the MASTER table, something like:
IsConsumer, calculation, number, If ( IsEmpty ( CONSUMER::MASTER ID ) ; 0 ; 1 )
(As an aside, I always use number fields with values of 0 or 1 for fields like these. This is so it is easy to summarise how many CONSUMERs you have in any found set of MASTERs ? just Sum the IsConsumer field.)
You will also need a calculation field on the CONSUMER table:
ConstantZero, calculation, number, 0
Now you will want a relationship that joins the CONSUMER table to, say, NonCONSUMER MASTERs. This will have one cross-product predicate (to show all MASTER records) and then another that links ConstantZero to IsConsumer.
Now, whenever you are on a CONSUMER layout you can look at the MASTER table occurrence to see the record's matching MASTER record, and you can look at the NonCONSUMER MASTERs table occurrence to get a list of all MASTER records that do not yet have a CONSUMER record. This will be the relationship for your value list.
Just be warned that as soon as you select a MASTER from this value list, it will immediately be removed from it! So you will always want to display the name, etc. using the MASTER table occurrence, instead. (See my answer to Stephen M's question for another example of this sort of problem.)
Hope this helps.
Paul Hutton 30/01/2013 9:36am (10 years ago)
Hi Stephen M,
I think the fundamental problem that you are facing is the different purposes the value lists are being put to.
Daniel's initial example provides a tool for the user to select a piece of equipment using parent categories. The important thing to realise is that, once selected, the value lists are no longer important. (The selected item's categories might be, but not all the other options in the list that weren't selected!)
This is so for your case, too. Use the value lists in drop-down lists or pop-up menus to select the Type, by all means. But then display those values using ordinary calculations.
This is reflected by the fields on your join table. It should only store the ID of the Type it is directly related to, never the Category that the Type belongs to. So, how do you select the Category? That can use a value list stored in a field, but that should be seen as a temporary field for input only, and perhaps should use global storage to emphasize that.
You've got this right, in some ways, with your overlaid calculations, but I would be tempted to go one step further and display the value lists only when explicitly selecting the Type. Have the Type's value displayed as a calculation (and the Category, too, if that is useful) somewhere on the layout with a button to edit it alongside that pops up a little window with value lists for selection.
If a Type's Category ever gets changed then the display calculations will remain correct. Whenever you pop up the window you can set the Category ID field (a global?) just before displaying it so the currently correct category will be selected.
That becomes a little trickier if you are using portals to edit the join table records. You will want to rely on overlaid calculations again to display the true value. And you will probably need an onEnter script trigger to set the Category ID on an underlying value list field just as the user clicks into it to select a new value. The key thing to think of is that the value list is just a tool for selection, not a reliable way of displaying the related data.
Give that a go, and if you keep the idea of data for entry separate from data for display you should be OK.
Atalanta 29/01/2013 7:28am (10 years ago)
I have a slightly different problem. I want to omit existing people from the drop down list. I have a MASTER list of people and from that list CONSUMERS are selected. The value list in CONSUMERS has the ID and the Name of the person to be added. I want to omit from the list consumers who have already been added.
I tried it by creating a relationship that has CONSUMER not equal to MASTER based on the ID. All this does is give me an empty list.
Janet 03/01/2013 9:22am (10 years ago)
Thank you so much for sharing your expertise.
I came across your site this morning when searching for help on conditional value lists. I am hoping this could be the solution I am looking for.
I am creating a database for an organization working with volunteers in a numbers of activities.
I have a volunteer table and would like to be able to (through a portal ) enter the activity, sub-activity, and region the volunteers take part in.
From this info I would like to create a report which would show me all volunteers working in each activity.
I have made a few attempts using your example and adding a volunteer table linked to the others but it does not work.
I would really appreciate your help.
Stephen M 01/01/2013 2:39pm (10 years ago)
Oops, looks like the greater than / less than symbols got stripped out from the third para of my response. Hopefully the below will work, but if not, just imagine the * asterisk is either the greater than or less than symbol to denote a "many" relationship:
<pre>Category Table------ < Type ("Name") Table --------- --------Item Table---- < several other relationships (which seem to be working fine so far, knock on wood)</pre>
Category Table ------ * Type ("Name") Table --------- * Home Join Table * --------Item Table---- * several other relationships (which seem to be working fine so far, knock on wood)
Stephen M 01/01/2013 2:34pm (10 years ago)
Thank you very much for the detailed response. To answer your question in the third paragraph, the expected behavior in this case would be option d) the Category on the Home record should change to reflect the Category currently assigned to Name.
Your fifth paragraph has me thinking.... so I'm sharing my relationship diagram, which looks as follows; it's quite possible I may have things set up wrong (or not in an optimal fashion):
Category Table------<Type ("Name") Table-----------------Item Table----<several other relationships (which seem to be working fine so far, knock on wood)
One Category can have many Types ("Names").
One Type ("Name") belongs to only one Category, but it can belong to multiple Items.
One Item can have multiple Types. (And thus, by extension, multiple Categories, but the Category is tied to the Name record.)
That's where the Home Join Table comes in -- linking multiple Types with multiple Items. I believe I have that part correct. Entries in the Home Join Table would be made either directly (from the Home Join Table layout / TO) or (eventually, once I build it out) via a portal from the Item table (since the Item table is essentially the hub of this database).
The Category value list helps narrow down the value list of Type ("Name") entries, so that you aren't scrolling through 100+ records. I'm not sure how to achieve this filtering using just one value list.
I'd been looking at Script Triggers, but didn't notice the OnRecordLoad, which seems to be a great lead for use on the "Home" layout to ensure the Category match field is updated to match the Category set for the Type ("Name"). I can compare the record's assigned Category with the value of a Calc field that updates to reflect the actual Category assigned to a given Type ("Name"). I feel confident I could manage this approach in a few minutes -- but if the Category value does change, my understanding is that I'd have to page through every record to trigger the update.
Am I correct in thinking that I could also use either OnRecordCommit or OnObjectModify on the Type ("Name") table to trigger a scripted update from that end of the relationship as well, i.e., test if the Category for a Type ("Name") has changed, and if so, find all Home Join Table entries that have this Type ("Name") and update the Home record's Category to match the Name's new Category value? If so, what would be the best way to start this -- OnObjectModify (on the Category field), or OnRecordCommit? From there, would it be a scripted Find operation, then loop through the Found set and update the Category field for each found record? Or is there a more efficient way to manage this?
Sorry for the newbie questions -- as I said, I'm just diving back in after a multi-year hiatus (the last FM Pro version I used was version 7), and I seem to have jumped into the deep end of the pool with some of my requirements. Thank you for throwing me the life line; your expertise is greatly appreciated!