4 Replies Latest reply on Jun 13, 2013 8:32 AM by philmodjunk

    3 tables - relationships



      3 tables - relationships



           Hi everybody,


           I'm currently building a database in Filemaker Pro and I've been facing some challenges when it gets to establish the relationships.


           I have 3 tables.


           1- Distributors

           2- Instruments

           3- Issues.


           So far I have tbl Distributers (dist_ID) linked to tbl Instruments (dist_ID_fk), and same scheme from Instruments to Issues.


           I have a portal to Instruments at the Distributers layout and everything works quite fine.


           My problem is that sometimes I would like to enter a new instrument without having to go to the distributers layout, and I would like to browse trough the distributers and add an instrument there again without leaving the Instruments layout.


           Is this possible?

           In SQL I remember that it wasn't that difficult, but here I'm having a lot of trouble to get it working.


           Thank you for any help you guys can give me.

        • 1. Re: 3 tables - relationships


               You need another table, often called a "join" table by FileMaker developers.  One dsitributor can have many instruments and one instrument can come from many distributors.  Your schema might look something like this:


               The porta on both the distributor and instrument layouts displays the join table.  In the relationship graph you can mark the relationship as allowing the creation of records in the portal.  You'll also need to create a value list of both the distributor ids and instrument ids.

          • 2. Re: 3 tables - relationships

                 If this is not a many to many relationship, any instrument is linked to only one Distributor, but Distributors can be linked to many instruments, you do not need a join table.

                 If you want, you can use New Record from the Records menu when on the Instruments layout and then you can format the dist_ID_fk as a drop down list of distributors in order to link the new Instrument record to an existing distributor.

            • 3. Re: 3 tables - relationships

                   Thank you all for your feedback and I'm sorry for the late reply but it was impossible for me to come here before.

                   About both answers, probably I didn't explain myself well enough.


                   I've attached a picture here that shows what I have so far.

                   What I would like to do is to have on the distributors table, all the info with the distributors, and a portal to the instruments. But once on the Instruments layout and table, I would like to that every time I chose a distributor, it will take me back to those associated records.

                   So for example if I have on the distributors Apple, Microsoft and Dell, I can check their instruments.

                   Once I go to the page with the instrument's profile, where I can also check for the issues related to each one, if I chose Apple, I can immediately  be on the records associated to Apple. The thing is that people might be lazy and they don't want to go to the distributors' screen every time they want to add a new instrument or complaint.


                   I'm not sure if I was clear enough :(.

                   Again if someone has any idea I thank you all in advance.

              • 4. Re: 3 tables - relationships

                     You don't actually need a portal to do this--a portal takes up more space on your layout than a drop down list that could also be used for this purpose, but that portal can be set up and made to work. You just need to modify your relationships a bit and add in a new occurrence of instruments:

                     Instruments>----x-----<Distributors Picklist--------<PickListInstruments

                     Modify the relationship between insturments and Distributors PickList to use the X operator instead of = to get a list of all distributors. Then put a button inside the portal row (or turn all the fields in the portal row into a single button) that uses Go To Related records to pull up the desired set of Instruments records.

                     The GTRR step would look like this:

                     Go To Related Record [Show only related records; From table: PickListInstruments; Using layout: "LayoutWhereYouHaveThePortal" (Instruments)]