6 Replies Latest reply on May 8, 2014 1:20 PM by mmccarty

    Help with setting up a Many to Many relationship

    mmccarty

      Title

      Help with setting up a Many to Many relationship

      Post

           Hello,

           I'm looking for some ideas on how to set up relationships so that the information needed is easily accessible.  I have a table, called Vehicle, that contains a record for each vehicle.  There is also a table, called Comms, that lists all communication systems.  One vehicle can have multiple communication systems.  Thus I connect the two tables using a line item table, LI_VehicleComm.

           I want to be able to look at one vehicle record and see all other vehicles that it can talk to.  Not all communication systems are compatible.  I have a table, called Compatible, that is just a list of each combination of communication systems that can work together.  I.e. Comm1 can talk to Comm1 (one record) and Comm1 can talk to Comm2 (another record).

           Is it possible to set up the relationship so that when looking at one record in Vehicle I can view all other vehicles that it can communicate with?

           My initial set up idea was Vehicle TO LI_VehicleComm TO Comms TO Compatible TO LI_VehicleComm(duplicate) TO Vehicle(duplicate).  While that does allow me to drill to the needed information, it isn't optimal.  I can't view the needed information on one screen.  Plus it is kind of convoluted.

           Any ideas are welcome smiley

           Thanks

        • 1. Re: Help with setting up a Many to Many relationship
          philmodjunk

               With the right match fields you might be able to drop out Comms from this "chain" and link TO LI_VehicleComm to TO Compatible.

               Does a portal to Vehicle(duplicate) list the correct records when placed on a Vehicle layout

               Do you have Filemaker 12 or newer? If so, you could use ExecuteSQL to list this information in a large multi-row text field.

          • 2. Re: Help with setting up a Many to Many relationship
            mmccarty

                 "With the right match fields you might be able to drop out Comms from this "chain" and link TO LI_VehicleComm to TO Compatible."

                 I may not have the right match because I still run into the Many v Many problem.  I did Platform TO LI_VehicleComm (duplicate) TO Compatible TO LI_VehicleComm (duplicate2) TO Platform(duplicate).  The records are pulled through but only for the first communication system.  This is because the chain is (1 v Many) TO (1 v Many) TO (1 v Many) TO (Many v 1). 

                 "Does a portal to Vehicle(duplicate) list the correct records when placed on a Vehicle layout"

                 In my original chain, correct records get pulled through but not ALL of the records.  A portal only pull 1 "line item" amount of data.  I need to figure out a way to make this connection less convoluted.  I was thinking of leaving the original chain and then using calulation fields to set up a dashboard structure.  But I'm not sure how to pull that off either.

                 "Do you have Filemaker 12 or newer? If so, you could use ExecuteSQL to list this information in a large multi-row text field."

                 I'm using FM 13 Pro.  I can look into ExecuteSQL (never used it before).  By backup plan is to just write a script to do the necessary data drilling and create a "dummy" table/layout for display.  That is an ok option but a better structure is still plan A

            • 3. Re: Help with setting up a Many to Many relationship
              philmodjunk

                   What are the match fields that you are using here in these relationships? Perhaps a screen shot of the relevant portion of Manage | Database | Relationships would help to show that.

                   ExecuteSQL allows you to define your relationships as part of the calculation field instead of requiring them to be defined in Manage | Database.

                   But I'm not sure what you are trying to say when you posted:

                   

                        A portal only pull 1 "line item" amount of data.

              • 4. Re: Help with setting up a Many to Many relationship
                mmccarty

                     I've included the picture.  Please note that I have changed the name of table Vehicles to Platform

                     What I mean by "a portal only pulls one lie item of data" is that it is only useful for one Many to Many situation. For example, I can use a portal (pulling records LI_VehicleComm) to display all Comms on my vehicle.  But it can't show all compatible communication systems for each of those Comms because that is an another Many to Many.  The portal would only show the first compatible Comm.

                • 5. Re: Help with setting up a Many to Many relationship
                  philmodjunk
                       

                            What I mean by...

                       That is not the case. What I would expect to see is exactly the list that you are requesting here. That's why I asked if you had tried it or not as this would also confirm the validity of your relationships and data.

                       For some reason, your screen shot appears very small and I cannot read it, nor can I enlarge it to become able to read it.

                  • 6. Re: Help with setting up a Many to Many relationship
                    mmccarty

                         You are right, what I had tried was working.  It was since a vehicle could be compatable due to more than one of its communication systems it appeared that only one record was getting pulled through.  But that is just an artificality of the data.  I created some dummy records to better test and it appears to be working.

                         Thank for your help