3 Replies Latest reply on Mar 12, 2014 1:15 PM by philmodjunk

    Many To Many Layout Question

    jkostenbader

      Title

      Many To Many Layout Question

      Post

           Consider the relationship below. I have two tables Accounts and Features set-up in what I believe is a valid many-to-many relationship joined by a table that contains the primary key from each table. I have a portal set-up based on accounts where I can add "many" features and it works just dandy. I'd like to create a layout based on Features now that shows all the accounts that have any one feature. I thought I could do it via this relationship but its not working as anticipated. I'm guessing I have to create another table instance but I'm unsure how to construct it (still struggling with filemakers use of table instances coming from an Access background). 

           Would appreciate any advice 

           John

      Screen_Shot_2014-03-12_at_8.41.42_AM.png

        • 1. Re: Many To Many Layout Question
          philmodjunk

               From a layout based on Features, you can either place a portal to Join Feature Account or to Accounts. I'd use a portal to the join table if I want to use the portal to select more accounts for that feature. If I just want to see a list of accounts, I'd use a portal to Accounts.

          • 2. Re: Many To Many Layout Question
            jkostenbader

                 Ok...as a follow-up question. Assuming I have my many-to-many relationship set-up correctly, I'm not sure I've constructed my portal(s) in such a way as to populate the join table correctly. Consider the following scenario...

                 Acct 1
                 Acct 2
                 Acct 3

                 Feature 1
                 Feature 2
                 Feature 3

                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                                Join PK                     Account_id                     Feature_id
                                1                     1                     1
                                2                     1                     3
                                3                     2                     1
                                4                     3                     1
                                5                     3                     2

                 The join PK is obvious. I would interpret the above as Acct 1 having feature 1 and 3, Acct 2 as having only Feature 1 and Account 3 having feature 1 and 2. Is this the way you would anticipate the join table to look like in a many-to-many relationship?

                 If so when I construct a portal of "features" based on a layout of "accounts" I placed two fields from the "features" table (description and date added). The description field was a drop down  with a value list based on an unrelated table of "master features" This seemed to work well until I looked at the join table and discovered that values in the features column were not repeating as illustrated above. Seemingly I can't use my drop down of master features for feature_id because it must be unique in the features table. I'm doing something wrong in this many-to-many. I feel like I'm close but need a push in the right direction. Assistance with creating the correct portal set-up would be appreciated

                 John

            • 3. Re: Many To Many Layout Question
              philmodjunk
                   

                        Is this the way you would anticipate the join table to look like in a many-to-many relationship?

                   Those are the correct ID field values, but I wouldn't display them like that in a portal intended for selecting either accounts or features.

                   

                        If so when I construct a portal of "features" based on a layout of "accounts" I placed two fields from the "features" table

                   But that should not be a portal to the Features table, it should be a portal to "join feature account". If you read my first post again, I described using a portal to the Join table when you want to use it to select either features for a given account or accounts for a given feature.

                   You would format the join feature account::Feature_Id field as a drop down list or pop up menu of Feature ID's via a value list of Feature IDs and Feature names from the Features table. The account ID need not be in the portal at all. If you have enabled "allow creation of records..." for the Join Feature Account table occurrence in the accounts to Join relationship, simply selecting a feature from that value list in the bottom "add" row of the portal will create a new related record and all three id fields will receive the correct values.

                   You may want to examine this demo file that links Events to Contacts in a many to many relationship: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7 If you are using FileMaker 12 or newer, you can use Open from FileMaker's File menu to open this file and produce a copy converted to the .fmp12 file format used with newer versions.