4 Replies Latest reply on Feb 3, 2009 1:07 PM by kgbtv

    FM10 - Self Join Value Lists not working

    kgbtv

      Summary

      FM10 - Self Join Value Lists not working

      Description of the issue

      FM Advanced 10.0v1, XPSp2Attempting to have one field entry filter a value list for a second field entry.-Table is SHOWS-Choose NETWORK from NETWORKVALUE value list pulldown in Field 1-SHOWVALUE Value list in Field 2 should then only show related records in pulldown -Followed direction in HELP to create self-join. NETWORK=NETWORK fields.-Relationship graph shows two tables SHOWS and SHOWS2 with NETWORK field joined.-Setup two value lists, NETWORKVALUE showing NETWORK values from the SHOWS table-Second value list, SHOWVALUE showing SHOWS values from SHOWS2 table, related records only First value list for NETWORK is fine.  Displays proper values.Second value list shows nothing at all.  Blank. Have tried reversing the value lists, etc.  Self join value list relationship just does not seem to be functioning.

        • 1. Re: FM10 - Self Join Value Lists not working
          TSGal

          kgbtv:

           

          Thank you for your post.

           

          This works for me, so let me show what I did, and hopefully, you'll discover what I did differently.

           

          1. I created a table SHOWS with the following two fields:

           

          Field1 (Text)

          Field2 (Text)

           

          2. In Browse mode, I added the following 9 records:

           

          Field1 - Field2

          CITY - BOSTON

          CITY - PHILADELPHIA

          CITY - BALTIMORE

          GAME - HOPSCOTCH

          GAME - TIC-TAC-TOE

          GAME - CHECKERS

          CAR - TOYOTA

          CAR - FORD

          CAR - BMW 

           

          3. I returned to "Manage -> Database...", clicked on the Relationships tab, and added the SHOWS table.  Now, "SHOWS 2" table is displayed.  I linked "Field1" in SHOWS to "Field1" in SHOWS 2.

           

          4. In Layout Mode, I double-clicked on "Field1", and set it to display as a pop-up menu.  Under "Display Values from", I selected "Manage Value Lists...".

           

          5. I created a new Value List, named it "First Pull Down", and selected the option to Use values from field SHOWS 2:: Field1

           

          6. I clicked OK a few times to return to Layout mode.

           

          7. I double-clicked on "Field2", and set it to display as a pop-up menu.  Under "Display Values from", I selected "Manage Value Lists...".

           

          8. I create a new Value List, named it "Second Field".

           

          9. When I specified "Use values from field", I selected SHOWS 2:: Field2... AND...  I also clicked the option below that to "Include only related values starting from: SHOWS"

           

          10. Click OK a few times to return to the Layout.

           

          11. Pull down the View menu and select "Browse Mode".

           

          12. Add a new record.  Enter CITY, GAMES, or CARS into Field1.  When you select Field2, you will only have the relevant selections for what you entered into Field1.

           

          Let me know if you are still running into difficulty, or if you need clarification for any of the above steps.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: FM10 - Self Join Value Lists not working
            kgbtv
              

            Thanks for taking a look!

            Only three things I am doing differently.

             

            1- I am in a different table than the table containing the Value List data.  So the Value lists are coming from Table 1 to choose what data to fill into Table 2 fields.

             

            2-I was using pulldowns.  I switched to popups but same result.

             

            3- This is a Remote Database on FM Server 10 Advanced rather than a local dbase.

             

            Otherwise, I am spot on with what you described and am still getting <no values defined> in the second pop up menu.

             

            Next guess?  ;)

            • 3. Re: FM10 - Self Join Value Lists not working
              TSGal

              kgbtv:

               

              This should also work with a value list from a different table.  Here is an extension of what I did:

               

              1. I created a new table "EXTERNAL" with two fields:

               

              Field1 (Text)

              Field2 (Text)

               

              2. I created a relationship between SHOWS and EXTERNAL via "Field1".

               

              3. I imported the nine records from SHOWS into EXTERNAL.

               

              4. I changed the pop-ups to pull-downs, and I changed the relationship in the Value Lists from SHOWS 2 to EXTERNAL.

               

              5. Just make sure that for the second pull-down "Field2", you select "Include only related values starting from: SHOWS"

               

              That's it.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: FM10 - Self Join Value Lists not working
                kgbtv
                  

                Ah-ha.  The missing link!

                Had to create a relationship between Table2 and Table1 where the value lists were being pulled from.

                Now both work fine.  Appreciate it!