3 Replies Latest reply on Nov 5, 2009 1:02 PM by philmodjunk

    Filtering Value Lists



      Filtering Value Lists



      I would like to create a drop down list that only populates with related values that match certain criteria.

      How can I do this?

      I have looked at the other examples in the forum, but can't figure out how to do this myself!


      I have records that look like:


      FieildID Volume 

      Code1 1

      Code2 0.4

      Code3 0

      Code4 0.5


      I would like to populate a drop down box via a list so that 'only' values in FieldID that have Volumes > 0 in it, i.e. in this case I should only have Code1,2,4 in the drop down box. These fields are already related in a 1-many relationship (as the Codes are unique), but I can use that code many times (until its volume value drops to 0).


      I will then run a script on commit to update the Volume field, such that if I enter 0 it drops off my managed list.



      Any help would be greatly welcomed.




        • 1. Re: Filtering Value Lists

          You should have two tables here, I'll call them MainTable and ValuesTable for this example:


          In MainTable, define a field,ZeroKey and put 0 in it. You can do this with a calculation or a number field set to auto-enter this value.


          Define a relationship:

          MainTable::ZeroKey < ValuesTable::Volume


          Now, for a layout based on MainTable, you can use a value list specifying ValuesTable::Volume with related values starting from MainTable.

          • 2. Re: Filtering Value Lists

            Sorry, I simply don't get it! - (I have tried it, but can't get it working)


            Why do you need two tables?


            Can you explain the logic behind what you suggested?

            Why does the 'related values starting from MainTable' bit filter out all non-zero entries in the drop down list? To me, that makes no sense.


            I feel a little bit dumb here; it just seems overly complicated for such a simple thing to do.......

            (A calculation box here would go a very long way FMP Devs.)






            • 3. Re: Filtering Value Lists

              You don't absolutely need two tables, it's just an easier starting point. You DO need two table occurrences so that you can specify a relationship for the value list. The two Table Occurrences could refer to the same table. Since you didn't describe your table structure, I had to make my own and the two table approach is easier to describe without pictures.


              "Why does the 'related values starting from MainTable' bit filter out all non-zero entries in the drop down list?"

              That's a very good question. Filemaker doesn't make this part of the process obvious. In order to tell filemaker which relationship we want it to use, we really need to tell it three things:

              (1 & 2 ) What tables are used? Filemaker knows that RelatedTable is the first Table Occurrence used because we specified it as the value source at the top of the dialog. The "starting from" designation tells it that MainTable is the second TO. Since every TO points to only one physical table for each TO, it can find the data it needs for the conditional value list.

              (3) What's the rule for matching the values? That's the information we enter into the Edit Relationship Dialog to describe the relationship linking these two table occurrences. In this case we are specifying that all values match IF the number stored in Volume is greater than zero which is what you wanted.



              There is a calculation based method for doing what you want. If you want your values to always be filtered the same way, you can do it this way:

              Define a calculation field: If ( Volume > 0 ; FieldID ; "" )


              Now specify this field in Manage Value lists and no relationship is needed.