4 Replies Latest reply on Jan 11, 2014 9:32 AM by philmodjunk

    Create Conditional Value List With Self Realtionship?



      Create Conditional Value List With Self Realtionship?


           So here is my trouble,

           I have a list of records (locations) with a location type field consisting of Type1, Type2, and Type3.  Each record will also have a location based on its type. Currently my value list shows every record. I need to filter it to only show applicable locations for each location type.

           If Type1 list Type1 

           If Type2 list Type1

           If Type3 list Type1 and Type2.

           I'm trying to avoid separate tables for each location type, since all fields are identical, and many other tables reference this table for their location.

           Any thoughts would be greatly appreciated.

        • 1. Re: Create Conditional Value List With Self Realtionship?

               Conditional value lists can use a relationship to control what values appear in the value list. A self join can be used as that relationship.

               Here are some links on conditional value lists that may help:

               There's a lot of overlap between the first two links 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 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.

               Forum Tutorial: Custom Value List?

               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.

          • 2. Re: Create Conditional Value List With Self Realtionship?

                 I had looked at the demo previously and created my own capable of filtering the Value List to show only records with a matching type to the current record.

                 But i can not recreate even that much functionality in the table with the self join.  The type field does not reference another table, it is a simple value list.  The portal will only show what Location i have selected and not a list of all locations with the same type.

            • 3. Re: Create Conditional Value List With Self Realtionship?


                   If I was going to create a Value List (VL) for this, I'd create a table for "Type_Locations", with two fields (text), Type and Location.


                   You'd then create a record for each Location, and also put in the type(s). For Type1, you'd "1". For Type2 also put "1". For Type3 you'd enter BOTH, as two lines, i.e.:




                   That way either value would be matched by a relationship to it. You then add a Relationship Graph table occurrence of this table, from a table you want the VL to show on; create VL, use that relationship, choose that table you're on as the: Include only related values starting from: "that table's name on the graph". 


                   For a portal, you're talking about a different relationship (whether its the same table or not). You'd need to recreate the above, but add a table occurrence to the graph, from the portal's "point of view", and use that view to the "Type_Locations" table for that similar (but not the same) VL; unless the relation BACK to the "main" table would work to get the same; but that does not seem likely, as it sounds as if each portal row is getting a specific value itself (?); and the connection is solid (i.e., not a global or unstored field).


                   [One other use of the above is that the users (more like managers) could go to a layout of the "Type_Locations" and add a "new" Location record. If they are messing about with them however, it could cause a little missing up of data. You could make it safer by using an ID for the value, with the VL still showing as the "location"]


                   There may be another method (or this one is not totally correct; happens). It also could be explained in more detail. I also sometimes find Value Lists to be a little tricky to think about, until they work, and then seem simple :-/

              • 4. Re: Create Conditional Value List With Self Realtionship?

                     A self join should work just like a relationship between two different tables. In your relationship graph, either way requires a relationship between two Tutorial: What are Table Occurrences? boxes. You base your layout on one of the two by selecting its name in Layout Setup | Show Records from. Then your value list lists values from the other table occurrence and you select your first table occurrence in the "Include only related values, starting from" option.