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

• KarenG 07/02/2022 3:37pm (2 years 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 (5 years ago)

  hi Marco.
  
  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 (5 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?
  
  Thanks!

• 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 (9 years ago)

  Hi Charles,
  
  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 (9 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 (9 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 (9 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 (9 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 (9 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 (9 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 (9 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 &#40;http://www.teamdf.com/files/weetbicks/conditionalvl/SportsValueList.zip&#41; 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 (10 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 (10 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 (11 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 (11 years ago)

  Paul,
  
  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
  
  <pre>
  
  Master IDp x Master IDs
  InConsumer = zc_Zero_n
  
  </pre>
  
  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 (11 years ago)

  Hi Atalanta,
  
  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:
  
  <pre>
  Let ( Trigger = MASTER ID ;
  
  If (
  IsEmpty ( CONSUMER::MASTER ID ) ;
  0 ;
  1
  ) // End If
  
  ) // End Let
  </pre>
  
  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 (11 years ago)

  Paul,
  
  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 (11 years ago)

  Hi Janet,
  
  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 (11 years ago)

  Hi Atalanta,
  
  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 (11 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 (11 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.
  
  Any suggestions?

• Janet 03/01/2013 9:22am (11 years ago)

  Hello Daniel,
  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.
  Thank you
  

• Stephen M 01/01/2013 2:39pm (11 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 (11 years ago)

  Hello Daniel,
  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!

No one has commented on this page yet.