4 Replies Latest reply on Jan 14, 2016 11:31 AM by scubadave112

    relationship portal desperate for help

    scubadave112

      I am super noob. Before yesterday I never heard of Filemaker but have a lot of experience in DB management with other programs.

       

      I have two tables, contacts and Software. I created and ID number for each employee. in the software table I created ten fields (Assigned user1, Assigned user2, Assigned user3, Ect...) When contact is associated with that software I enter their ID into one of the usable fields. (Im sure there is a better way to this and am open to suggestions).

       

      I want a portal in the contact table to display software associated with that contact. I tried to create a relationship but I can't figure out how to pull the record if the employ ID matches any of the 10 "Associated User" fields. Please let me know how I can make this work. I have been learning so much and I literally have to stop everything because I can't grasp my head around a solution, LoL. Thanks in advance.

        • 1. Re: relationship portal desperate for help
          TorstenBernhard

          A cross-relationship table would do here.

           

          Each record of Crosstable holds the IDs related records a foreign keys:

           

          Contacts::ID <-> Crosstable::fk_Contacts

                                     Crosstable::fk_Software <-> Software::ID

           

          Now you can display for a given SW record the related contact data and vice versa.

          • 2. Re: relationship portal desperate for help
            nicolai

            +1 to Torsten. Cross relationship table is also know as a Join Table. I would also recommend this if you coming from a general DBA background. Here is the FileMaker KB link:

             

            Understanding and creating many-to-many relationships in FileMaker Pro | FileMaker

             

            The other approach is FileMaker specific. Instead of Using 10 foreign key fields in Software table to record related contacts you can use a single indexed text field and add contact ids as a value to a list (CR delimited):

             

            122

            2333

            344

             

            If you link to this field e.g. contacts::id  = Software::contacts_IDs, you should be able to see all software records from contacts contents. As far as I remember it is called multi-key field relationship (unless I made it up).

            • 3. Re: relationship portal desperate for help
              scubadave112

              I am a super noob and I understand there is a place to enter in the calculation and information you have posted, but don't have the experience of knowing what to do with the information your provided me. Nicolai also provided you with a 1+ so im going to assume it is correct and I am going to spend the next couple hours watching videos and reading the KB article and hopefully will understand your response very shortly. I will post back about 8pm est to confirm if this helped. Thanks in advanced

              • 4. Re: relationship portal desperate for help
                scubadave112

                Torsten, You are correct I was interested in join table and Since I am still new to this I did not fully understand your answer. if you have time to better explain your answer for noobs like me that would be great because to grasp the concept of join table, I had to watch the attached video about 10 times to fully understand the logic in how relationships and filemaker work to create the desired effect i was looking for. After learning about the join table I now understand your response, So i thank you very much for helping me and I wish I could give nicolai some points because his post was also very helpful by providing the useful kb link.

                 

                The video that helped me solve this problem the most: Click Here.