1 2 Previous Next 22 Replies Latest reply on Jun 1, 2012 7:17 AM by RobWood

    Many to Many Relationship issue

    nick_1

      Title

      Many to Many Relationship issue

      Post

       Hi All,

      I am new to filemaker pro but have done some database work in school. I am having an issue with being able to relate a many to many database. I would like to link many projects with multiple images vice versa. I thought a checked box would work, but I was getting a <value list missing>.

      For each project there is a unique ID and this ID is associated with the other tables through the ID. For examlple: image1 is associated with project ID 2, and project 2 is associated with image1. I would like to have multiple project ID's associated with image1. I understand that many to many relationsips  aren't best practice. Any advice will be greatly appreciated. Thanks in Advance! I can provide additional info or screen shots if my situation was difficult to understandSmile

      Would I need a intermediate table to make this possible ( join )?

      nick

      manytomany.PNG

        • 1. Re: Many to Many Relationship issue
          philmodjunk

          I understand that many to many relationsips  aren't best practice.

          Who told you that? Many to many relationships are a fact of life in relational database design.

          Here's the standard and most flexible way to implement a many to many relationship:

          Icons---<Icon_Project>---Projects

          Icon::IconID = Icon_Project::IconID
          Projects::Project ID = Icon_Project::Project ID

          Each time you want to link a specific Icons record to a specific Proejcts record, you create a record in Icon_Project with  values that match the IconId and Project ID values of the two records to be linked. Often, this is done using a portal to Icon_Project--the join table. If you enable "allow creation of records via this relationship" for the join table, you can format the other ID field that matches to the other table as a drop down list. Selecting a value in the drop down list then creates a record in the join table that links the two records.

          Here's a demo file you can examine. Feel free to ask questions if you don't understnad how wome part of it works: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

          It's also possible to use a field that stores a return separated list of values (such as that created by a check box group) instead of the join table, but it is a much less flexible approach to use.

          • 2. Re: Many to Many Relationship issue
            nick_1

             Phil,

            Thank you for the quick response! You have explained it very well. I will implement it as you said to see if it works.

            nick

            • 3. Re: Many to Many Relationship issue
              nick_1

               Phil,

              That worked great. I created that join table with the fields you suggested and linked them. I also used that portal you suggested and added the container so that I could view the icon. This was an easy fix! Thanks again. Have a great week!

              nick

              • 4. Re: Many to Many Relationship issue
                RobWood

                Hey! I am so new to FileMaker Pro that I'm actually just in the trial period. This is actually my father's screen name. He doesn't know what he's doing, so he's hired me to figure everything out and build his database... Anyway, I think I have a similar problem and don't understand anything that you suggested Nick try to fix it and I can't open the link to the demo file you posted. The database I'm trying to create is to help my dad keep track of his invoices, proposals, customers, general contractors, suppliers, etc. for each individual job he does with his home roofing company. I have a table for customers, general contractors, suppliers, and sub contractors and each has their own unique ID so I can form a relationship to those ID's in the "Jobs" table. (Each individual "Job" is defined by a street address where he's doing whatever work that needs to be done there). Since there's alot of different work that he may be doing at one individual job, there is most likely going to be more than one supplier and more than one sub contractor at each individual "Job". SO each record (or job) in my jobs table is most likely going to need to have more than one sub contractor ID and more than one supplier ID. How can I do that? Or is there a way to simplify my tables to where there's only one possible answer in each field for each record? I'm sorry if this is such an amateur question. I don't want to be a bother, but I would greatly appreciate your help since I can't call support without paying a fee and I don't know anyone who uses FileMaker Pro. Thank you!

                • 5. Re: Many to Many Relationship issue
                  philmodjunk

                  I think I have a similar problem and don't understand anything that you suggested

                  That doesn't give me anything to go on for responding. If you post a specific question, then I can provide an answer.

                  I wonder if drop box was having a problem earlier. This is the second report I've had of trouble downloading from a link that I've posted. I just clicked the link, downloaded the file and opened it with no trouble so I suggest trying to download it again.

                  (Note: it's hard to read your post due to it being one long paragraph...)

                  for each individual job he does with his home roofing company. I have a table for customers, general contractors, suppliers, and sub contractors and each has their own unique ID so I can form a relationship to those ID's in the "Jobs" table. (Each individual "Job" is defined by a street address where he's doing whatever work that needs to be done there)

                  In Manage | Database | Tables

                  Create a new table named Job_Supplier

                  Click the fields tab and define these fields:

                  JobID
                  SupplierID

                  Click relationships and drag from Supplier::SupplierID to Job_Supplier::SupplierID and from Job::JobID to Job_SupplierID so that they are linked in a relationship where the join table, Job_Supplier, forms a "bridge" between the two tables.

                  Say you have two suppliers, with SupplierID's 30 and 45 that you need to link to a Job with JobID of 233. You would create two records in Job_Supplier, both with 233 in the JobID field, but one would have 30 in the SupplierID field and the other would have 45 in that field.

                  The simplest way to do that in FileMaker is to place a portal to Job_Supplier on the Job and/or Supplier layouts. If you enable "allow creation of records via this relationship for Job_Supplier in the Job to Job_Supplier relationship (Double click the line connecting them to open a dialog where you'll find that option), you can place a portal on the Job layout, put the Job_Supplier::JobID field in the portal row and set it up with a drop down list or pop up menu of Supplier ID's from the Supplier ID field. You include a name field from the Supplier field in this value list to make it easier to select the correct supplier. This allows you to select suppliers for the current job record by making selections in this field in the portal to the join table.

                  You'll find this as the "basic setup" in the demo file.

                  I can't call support without paying a fee and I don't know anyone who uses FileMaker Pro.

                  I strongly recommend that you invest in a book and/or training tutorials on FileMaker (There are even videos on YouTube). We'll be glad to help all we can here, but a forum like this is best suited for answering specific questions where the books and training materials will provide you with a more broad based understanding of how fileMaker and relational databases work--and coincidentally it will help you better understand the posts you find here in the forum.

                  • 6. Re: Many to Many Relationship issue
                    GuyStevens

                    Hey Rob. Maybe it would be a good idea to start a new thread for you so we can focus on your problem.

                    What you need is join tables. If you need to link muliple contractors to one job. The idea is simple:

                    Job Table:
                    ID  - A number field with Auto enter serial number option.
                    JobDate
                    ...

                    Contractor Table
                    ID  - A number field with Auto enter serial number option.
                    ContractorName
                    ...

                    JoinTable (let's call it Join_Job_Contractor
                    ID  - A number field with Auto enter serial number option.
                    JobIdFk - a number field
                    ContractorIdFk - a number field

                    Basically what you note in this table is for example:

                    JobIdFk  -  ContractorIdFk
                    1              1
                    1              2
                    1              3
                    2              1
                    2              4
                    3              5
                    3              6
                    3              7

                    You create relationships bethween the ID and the corresponding IDFK fields.

                    Jobs::ID-------Join_Job_Contractor::JobIdFk
                    Contractor::ID------Join_Job_Contractor::ContractorIdFk

                    You set the "Allow creation of records trough this relationship" on the side of the Join table for the relationship between the Jobs table and the Join table.

                    Then on a layout based on the Job table you create a portal based on the Join table that displays the ContractorIdFk field.

                    And you make that field a dropdown list that gets it's values from the Contractor table. Make sure the first field is the ContractorId, the second field can be the contractor name.

                    • 7. Re: Many to Many Relationship issue
                      RobWood

                      Thanks guys! My understanding of relational databases and SQL stems from what I know from taking a college computer course a few years ago that included Access. I understood it very well and have actually taught alot of people how to create databases on Access. But my dad has a Mac and I couldn't get Access for him, therefore I'm trying out FileMaker and trying to use my fleating knowledge about Access to help me out with understanding FileMaker. 

                      Phil, the link does work and I was able to download the demo, but I get a pop-up window telling me that Safari can't open it because there's no available application to view it and "No default application specified to open the document"... Again, my dad doesn't understand much about his computer and I'm used to working on my PC, so I don't know what to do about this, but that's not your problem.

                      Between the two of you, this all makes alot of sense now and I'm going to try it, but I've tried the Auto Enter Serial Number option in my Customer table for CustomerID, but it's not working in the Table View when I'm entering information into the fields. I've also used the Auto Enter Data option in the same table for the "State" field, and it's not working.

                      DaSaint- what is this "Fk" you're putting after everything?

                      Another thing I've noticed that doesn't pertain to my original problem but I figured you might know is that I can't access pre-designed templates through Starter Solutions. The window pops up but it's just all black with nothing there and the "choose" button is greyed out. Does this have to do with me just having the trial? 

                      • 8. Re: Many to Many Relationship issue
                        philmodjunk

                        I would guess that you are using FileMaker 12. This is a file in the older .fp7 format used by files from Filemaker 7 through 11. Launch Filemaker 12 and use Open from the file menu to open the file. FileMaker will convert the file to .fmp12 format for you and then open it.

                        "it's not working" doesn't tell us HOW it's not working for you. An auto-enter setting will automatically enter data into the field each time you create a new record. It won't enter data into existing records so this might be why it doesn't appear to work for you.

                        If you used subforms in access, it may help to understand that a "portal" in FileMaker is much the same thing.

                        FK stands for "foreignkey" it's a commonly used naming convention for fields used as foreign keys in filemaker.

                        • 9. Re: Many to Many Relationship issue
                          GuyStevens

                          FK means Foreign Key.
                          PK means Primary Key

                          Say you have a table of Jobs.

                          To identify each job uniquely you create a Unique serial number in the field "ID". This is the Primary Key.

                          That way every job gets one number and there are no two jobs with the same number.

                          Then, if you want to refer to a certain job in another table you just note the Job ID in a field. That ID then becomes a Foreign key.

                          And you would call that field JobIdFk so you know that that id in that field is the foreign ID from the Job table.

                          Then you create a link between the PK and the FK in the other table you you have a relational database. In your second table you can now see fields from the Jobs table without the need to duplicate data.

                          • 10. Re: Many to Many Relationship issue
                            GuyStevens

                            Wow, looks like we are nicely doubling up on information here :)

                            Posting only minutes apart with the same information :)

                            If you don't get it now, you never will :)

                            • 11. Re: Many to Many Relationship issue
                              philmodjunk

                              More complimentary than "doubled" I think. Makes for a pretty good "tag team" here. Wink

                              • 12. Re: Many to Many Relationship issue
                                RobWood

                                Yes, yall are a very good tag team- Phil criticizes my formatting and phrasing and introduces answers and DaSaint follows through with details that fill in the holes and then smiley faces to make me feel better Wink

                                Yes, Phil, I am using FileMaker 12.

                                Ok, yes I remember all about Primary and Secondary keys from Access, so thanks for clarifying- I understand completely now. I just wasn't sure what it stood for.

                                • 13. Re: Many to Many Relationship issue
                                  GuyStevens

                                  It's like good cop - bad cop Laughing

                                  I'm always the nice guy Wink

                                  • 14. Re: Many to Many Relationship issue
                                    philmodjunk

                                    Apologies for criticizing your formatting, but I've noticed that certain posts are more likely to not get responses. Those that are hard to read are one of those types of posts so I was making a suggestion intended to help you get the help you need should you make any future posts.

                                    1 2 Previous Next