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.
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.
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
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 - 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
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??
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.
I have a simple database.
Airport City (Text)
City Name (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 - City Name)
United States - Los Angeles
United States - New York
United States - Chicago
Canada - Calgary
Canada - Ottawa
(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.
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
Thank you Phil - It worked very nicely :smileywink:
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.