10 Replies Latest reply on Feb 5, 2010 12:22 PM by philmodjunk

    trouble relating through a join table



      trouble relating through a join table



      I've searched through the forums and spent some time in the Knowledge base, but can't seem to figure out this issue.  Apologies.


      I'm using FMP10, on Windows 7.


      My table naming convention is the following:



      T20b   - Role table # is 20, b indicates that it is the 2nd instance of the table

      role     - table that this instance is assigned to

      GAMES - the data source of the Table

      id_game - the field that relates the role table to the Games table



      Tables involved:



      Role (join)

      • id_contact
      • id_game
      • id_role
      • Name_Contact
      • Name_Game           Indexed, Lookup (T20b_role_GAMES|id_game)
      • Name_Role             value list


      Table instances








      From a Games layout, I have a secondary tab with a ROLE portal (T15g_games_ROLES|id_role)

      • joined by role_id
      • Records can be created by this relationship
      • Fields are Name_Role and Name_Contact
      •          Both are value lists from the join table (T15g_games_ROLES|id_role)


      To enter a new row in my portal, I select the Name_Role & Name_Contact from the drop-downs


      When I look at the Role Layout, a new record is created

      All fields are filled in EXCEPT the contact_id


      I need to figure out how to get the contact_id filled in so that the secondary tab with a ROLE portal will show in the Contacts layout.


      I've confused myself with lookup fields and adding another instance of a table to my joins.  Now I've muddied any possible clear solution.


      Any help would be very appreciated!


      Thanks in advance!



        • 1. Re: trouble relating through a join table
             Hi- if my explanation is too detailed, I could ask a mnore vague version that would apply to others more easily....let me know.  Thanks!  :)
          • 2. Re: trouble relating through a join table

            I just saw your post a few minutes ago. Let me make sure I'm decoding your naming conventions:


            T15g_games_ROLE has games for its data source table?

            and you need the contact ID from the related table to be entered when you select a name in Name_Role correct?


            If I am interpreting your naming conventions (g means you have at least 6 other TO's to games?), it seems you should be selecting the contact ID from your value list instead of the name.


            You can use a 2 column value list and column one can be your contact ID while column 2 is your matching name field. Then your name field can simply be a field from the related table or you can set a looked up value in the field definition to copy that data--though that's not the best approach if I've analyzed this correctly.


            Let's try simplifying your description down so that it's easier to figure out your tables, TO's and relationships:


            Try this format:


            TOname::keyFieldName (Datasourcetablename) = TOName::keyfieldName (datasourcetablename)


            To document how you've related just the three? tables in question. Then we might be able to take a better shot at helping you.

            • 3. Re: trouble relating through a join table

              Hi Phil,


              Thanks for your response!


              T15g_games_ROLE has ROLE for its data source table (it connects to games)

              In my naming convention, the datasource is always in caps.  The table names are long because they help sort all the TOs and layouts etc. I'll do away with the complicated naming and simplify. 


              When you say TOname, I think you mean TableOccuranceName.  Is that right? 


              TOname::keyFieldName (Datasourcetablename) = TOName::keyfieldName (datasourcetablename)



              T20::id_contact (ROLE)      =  T20a::id_contact (CONTACT)

              T20::id_game (ROLE)          =  T20b::id_game (GAME)


              T15::id_games (GAME)        =  T15g::id_games (ROLE)

              T05::id_contact (CONTACT) = T05p::id_contact (ROLE)



              In the Games Layout, I have a portal from the T15g TO.


              I enter the Role name and the Contact name - a new record is created in the Role table.  The game name and ID paste into the Role record.  The Contact name pastes into the Role record, but I need to figure out a way to do a lookup or create a relationship that will paste in the contact ID to the Role record.



              I have to mention that I'm using a DB I didn't create- and am trying to figure it out slowly. 


              The relationships use multiple table occurances, instead of direct relationships for example:

              A = A2 -> B


              B = B2 -> A


              When most of the tutorials are talking about joining A->B 


              I've been able to figure that out - but the join table is throwing me off. 


              Hopefully I've clarified things rather than confuse them!

              • 4. Re: trouble relating through a join table



                From what I gather you have a contact table and a game table. Your role table is a join that has id fields linking a specific role to both a game and a contact (player). This is a normal and useful way to set up the tables. The join is powerful and flexible for this purpose.


                When you create a new role record, you seem to say it is done from the context of a game or game layout. So the game id is available from the game record and is copied into the new role record. Your question is a good one. Since you are not in the context of the contact/player you dont have any indication which contact id to specify. Somehow you (probably the user) have to specify which contact id you want. A value list is one option. The value list might be setup as a pop-up menu in the new role record that gives a list of all contacts (or some subset) for you to choose from.


                Hope that helps.



                • 5. Re: trouble relating through a join table

                  Thanks Ken- yes.  You understand my question (regardless of my crazy description).


                  I was thinking there must be a way to create a lookup or maybe a script that is activated upon record creation of a role record.


                  When the role record is created, it has the role id (auto-enter), role name, game name, game id, and contact name. 


                  There is a connection between role and contact.  Could I create a lookup that would fill in the contact id? 

                  Would a script that says: upon role record creation, setvalue for Name_contact, go to Contact Layout, do a search for Name_contact, get the contact id, go back to original record and paste the contact id in it?


                  That script seems a little silly - I'm sure there is something that I'm missing.  Any help would help.


                  Thanks again!


                  • 6. Re: trouble relating through a join table
                       You've got the right idea. You do need something like the script you described. However, searching on the name is not guaranteed to give you a single, unique record, e.g. there could be two Contacts named Joe Smith. This is why keys fields are used. If you have the contact name already, how did you get it. If you got it at some point from a Contact record, that would be the time to get the ContactID_fk. With that you can always get the ContactName through a relationship. Usually that is how we keep track of a Contact or Game, keep the key around somewhere. If you dont already have the ContactID use something like a value list of ContactID_fk/ContactName in a pop-up menu to allow the user to select the correct Contact.
                    • 7. Re: trouble relating through a join table

                      Thanks Ken.


                      I get the contact name by entering it in the portal from a value list.  I guess having a 2 column value list, like Phil suggested, would be the way to go.  I'll have to lookup how to do this & return to the forums to give you my (hopefully) success story.  Thanks very much!

                      • 8. Re: trouble relating through a join table

                        OK, So I added the contact ID field to my value list.  I have a solution that is not ideal, but will work. 

                        When I select the contact from the value list, I see the contact_id with the name.  I then enter that number in the contact_id field and it creates the relationship.


                        The only other way I can think to link these is to create a script button called "link contact" that would grab the name I just entered, go to contact layout, search for that contact, grab the id and paste it in the field.


                        Since I'm creating the DB for my own use to track stuff, manual is fine.  If I ever decide to allow others to enter records, I'll probably have to do the script. 


                        So for now, problem solved!!


                        If anyone has thoughts for automation, let me know though.



                        • 9. Re: trouble relating through a join table
                             You can hide the ID field in the value list. Look in the definition of the value list for an option to "Show Values Only from the Second Field". This allows your value list to have both the ID and the Name, while only displaying the name in the list. To the user it will look like the are picking the name, but behind the scenes you get the ID/key. This will also cause the value list to be sorted by the name, which makes more sense to users than sorting by a weird ID number.
                          • 10. Re: trouble relating through a join table



                            When you hide the ID column, the names in column two once again must be unique. Duplicate names will no longer appear in the list. You either have to set a unique value validation rule on your name field or keep both fields visible to avoid this issue.