9 Replies Latest reply on Mar 16, 2009 11:16 AM by philmodjunk

    Many to Many Relationships

    dinora

      Title

      Many to Many Relationships

      Post

      I know this question has been posted but I am still not getting it. 

       

      I need to link airports to cities, airports can serve many cities and cities can be served by many airports.

       

      I have then created a joint table where I can assign an airport to a city

       

      In summary

      Country ---<Airport

      Country---< City

       

      Joint Table City to Airport

      I have an AutoID for this table as well as the Airport and City

       

      So I have linked City ID from the city table to City field on the join table one to many

       

      But when I am trying to link the airport ID to the airport field on the join table it wants to do a Table occurrance and create a duplicate table.

       

      Can you help as to why this is happening

       

      Country

      City

      Airport

      Joint City to Airport

       

      These are my tables and ideally then I want to have a portal where I can assign airports from the city table

       

      I have a value list that displays airports by country which means on the portal if I am in Buenos Aires it should only display those airports that belong to Argentina

       

      Thanks 

       

       

       

       

       

       

        • 1. Re: Many to Many Relationships
          TSGal

          Dinora:

           

          Thank you for your post.

           

          Pull down the File menu and select "Manage -> Database..."

           

          Click on the Relationships tab.

           

          Make sure the City field in "Joint City to Airport" table is connected to the City field in the "City" table.  Then, when you select a City, a list of airports for that Country will appear in the Airport portal.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Many to Many Relationships
            philmodjunk
              

            Dinora,

             

            You appear to be describing the "traditional" table design approach to building a many to many relationship. If I am reading you correctly, you have three tables. Table one lists cities, Table 2 lists airports and a third "join" table lists both cities and airports. Relationships then link from table 1 to table 3 and from table 3 to table 2 (This puts the Join table in the "middle").

             

            This works, but many experienced FMP developers are aware of another approach that works quite well for many implementations of a many to many relationship. 1) In table 1, define a text field  called "airportList" 2) store a list of airport names separated by carriage returns in this field. 3) Use manage database to link the AirportList field directly to the airport field in table 2. Table 3 need not exist. With this approach, a portal using this relationship will list all table 2 records with matching airport names listed in table 1's airport list field and a Go To related record script step can create a found set of those same records. In many cases, the contents of AirportList can be controlled by formatting the field to display checkboxes from a value list. (Clicking multiple items in the list will add them to the field with the needed carriage return list separator while "unchecking" a box will remove an item.)

             

            Not the traditional approach you'll find in a standard database design textbook but a pretty handy gimmick when your using filemaker Pro.

             

            Phil Caulkins

             

            • 3. Re: Many to Many Relationships
              dinora
                

              Hi TSGal,

              I know you are correct on what you are saying but is not working - I have a link between my city id field in the city table with the city name in the joint table.

               

              My problem is that the airport table has to have a relation with the country and when I try to do the relation with the country and a relation with the joint table is not allowing me but it wants to create a second table (copy of airports table)

               

              Here is the dilemma again

              Four tables

              Country

              City

              Airport

              Joint City to Airport

               

              Country ID is related to a Country field in the Airport Table

              Country ID is related to a Country field in the City Table

               

              If I am thinking correctly then an airport can only be in one Country and on the same concept a city can only be in one Country so you have One to Many relations respectively

               

              Now I want to be able to allocate airports to cities For Example United Kingdom is the country in the UK you have cities such as London, Dover, Southampton

              Each of this cities can use London Heathrow and/or London Gatwick as gateways So I should be able to build a  many to many by doing my joint table where City ID field is linked to City name in the joint table like you say and the airport ID should be linked to airport name in the Joint table

               

              Here is where I get stuck because I already have the relation between country and airport is not letting me establish the relation Airport to joint table without creating a duplicate table.

               

              I hope this makes sense

              Thanks

              Dinora

              • 4. Re: Many to Many Relationships
                dinora
                  

                Hi Phil

                 

                Thanks - I am trying to understand your concept but I dont get it. 

                Can you ellaborate on your explanation - I have Country Table, Airport Table, and a city Table

                 

                The idea is a city belongs to one country, and an airport belongs to one country, but then you have cities that can be served by many airport within that country and on the same many airports serve the city

                For example

                UK - Cities are London, Dover -  Both these cities use London Heathrow and London Gatwick as a gateway

                 

                I am not sure waht you mean by store a list of airport names separated by carriage returns - Do you mean a value list??

                 

                 

                • 5. Re: Many to Many Relationships
                  philmodjunk
                    

                  Dinora asks: "I am not sure what you mean by store a list of airport names separated by carriage returns - Do you mean a value list??"

                   

                  Let's keep this as simple as possible. Define a text field in your city table. I named it "AirportList" in my previous post. Put this field on a layout, make it several lines of text in height and format it to use a scroll bar. Enter an airport name in the field, press return, enter a second name, press return... Etc until you have entered all the airports for the city named in the current record. Repeat for each record in your table. Once you can manually create these lists in a text field and get this relationship to work, you can experiment with a number of easier methods for populating this field. You can write scripts, format the field to display check boxes, even define calculations that use a value list. Which of these methods work best for you will depend on what you want to do and how you design your data entry layouts.

                   

                  Now define a link matching AirportList to the Airport name field in your airport table. Filemakers logic for this type of relationship is "match any records where airport name matches the first item in AirportList or the next item in airport list or..." This will give you many to many matching without using the Join table that most database systems require.

                   

                  With this relationship, you can create a layout that shows records from your city table. If you place a portal on that layout that refers to the above relationship, you can display all the matching records from your airport table for the current record. You can also create a layout of your airport records and put in a portal that uses this same relationship to display records from your city table. Thus it's a many to many relationship, but without a join table.

                   

                  Hope that helps. I've spotted enough of your posts here and there to realize that you are new to all this. This trick is a well known one that many experienced developers use to avoid using a join table. Like any other technique there are trade offs and it should also be possible to use the standard Join table to do what you want.

                   

                  Looking at some of your earilier posts I wonder if you may be having problems understanding how the relationships graph works. It's hard for us forum readers to tell because we can't see your graph and may not be accurately picturing it when we read your description. Hang in there, keep trying and either of the two approaches listed in this thread should work for you.

                  • 6. Re: Many to Many Relationships
                    TSGal

                    Dinora:

                     

                    I have a simple database.

                     

                    Country

                       Country (Text)

                     

                    Airports

                       Airport City (Text)

                       Country (Text) 

                     

                    Cities

                       City Name (Text)

                       Country (Text)

                     

                    Joint City To Airport

                       City Name (Text)

                     

                    My relationships are as follows:

                     

                    Airports >- Country (via Country fields)

                    Cities >- Country (via Country fields)

                    Joint City To Airport >- Cities

                     

                    As far as records, this is what I have:

                     

                    Country

                       United States

                       Canada

                     

                    Cities

                       (Country - City Name)

                       United States - Los Angeles

                       United States - New York

                       United States - Chicago

                       Canada - Calgary

                       Canada - Ottawa 

                     

                    Airports

                       (Country - Airport City)

                       United States - Denver

                       United States - Los Angeles International

                       United States - New York LaGuardia

                       United States - New York Kennedy

                       Canada - Vancouver International

                       Canada - Calgary International

                     

                    On my layout with "Joint City To Airport", I have included a portal into Airports.  Whenever I select a city in the United States, it shows all the airports in the United States in the portal.  If I select "Calgary", it displays the Canadian airports.

                     

                    Let me know what I am doing differently than you.

                     

                    TSGal

                    FileMaker, Inc. 

                     

                    • 7. Re: Many to Many Relationships
                      dinora
                        

                      Hi TS Gal,

                       

                      Thank you for your suggestion.  Phil from another thread solved my problem.  But in looking at your solution My problem is that you say the portal needs to come from the airport -, but in reality the portal is into the Joint City to Airport -  So that I can add records from the port into the Joint table.  By establishing the relationships the way you explained - I can make all airports display from each city but all airports all over the world .

                       

                      But what I want is to be able from the city table to have a portal that only allocates airports to the joint table - To create a new airport I need to add the record in the airport table.

                       

                      Look at Phil's answer below it is interesting and in my case it works because my airport table had only one field so I have eliminated the extra step of having a many to many.  In the Country table I have a text field where I enter the airports for that country -  Then just establish a relation between the city table and the Joint table by city -  It works!!

                       

                      If my airport table had other fields then I will probably have to have the many to many -  In this case what needs to happen is a duplicate table gets created for the airport table and establish a relation from country field in the airport table to Country table. 

                       

                      This also works!

                       

                      Thanks you again for your help

                      • 8. Re: Many to Many Relationships
                        dinora
                           Thank you Phil - It worked very nicely :smileywink:
                        • 9. Re: Many to Many Relationships
                          philmodjunk
                            

                          Your Welcome,

                           

                          TSGal's solution also works. You just have to set up the right relationships and refer to them correctly when setting up your portals.

                           

                          Technically, the multi-value key approach that I described is still a many to many relationship.

                           

                          Phil