1 Reply Latest reply on Jul 2, 2014 8:45 AM by philmodjunk

    Limit value list from field, by the value of a second field?



      Limit value list from field, by the value of a second field?


           I have a table of people (fields for names, gender, etc.), and I have a value list that is defined from this table's "names" field. This list shows all of the names, regardless of gender. Is it possible to create a value list that only contains the names for Male people, or Female people, but not both? I cannot seem to get the "Include only related values starting from" feature for the lists to work in this way. This feature does not seem to limit the list by a pre-defined value. Instead, if I am on a Female's record then the list will grab the "Female" value and show all females in the list, and likewise for if the record is a Male then the same list will only show the males, but I want the lists to be separate and only show the "Male" and "Female" people regardless of the record I am on.

        • 1. Re: Limit value list from field, by the value of a second field?

                    This feature does not seem to limit the list by a pre-defined value.

               It can, but it would require two relationships with different constant value match fields and two different value list set ups.

               You can define a calculation field, constMale and define it to return the text "Male". Then use it as a match field in a relationship to an occurrence of your table of people.

               Do the same with constFemale, but use "Female" and match it to a different occurrence of your people table.

               Now define two value lists, one based on the occurrence of people linked to constMale and one for the occurrence linked to constFemale.

               But you might instead use a different conditional value list as identified as "option 1" in the following link:

               Forum Tutorial: Custom Value List?

               It uses a calculation field, but no relationships and added Tutorial: What are Table Occurrences? are needed.

               Other links on conditional value lists:

               There's a lot of overlap between the forum tutorial and knowledegebase article, so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

               The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

               Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

               Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

               Hierarchical Conditional Value lists: Conditional Value List Question

               Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

               Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.