8 Replies Latest reply on Mar 22, 2009 8:20 PM by dinora

    Again Many to Many Relationships

    dinora

      Title

      Again Many to Many Relationships

      Post

      I am still having a difficult time making this work -  I have

      Country Table

      City Table

      Tour Table

      Supplier Table

       

      1.- My Suppliers can operate in one country only - So I have a relation between country and Suppliers  Country-----<Suppliers - There can be many suppliers in one country

       

      2.- Each City can only be in one country - So again City--------<Country

       

      3.- My products are tours - So tours can operate in one city  City-----------<Tours - One city can offer many tours

       

      4.- Now I need link the suppliers to the tours as each supplier can operate the same tour - so rather than creating the tour multiple times for each supplier I need to allocate the tour multiple times to multiple suppliers - Many to Many

       

      5.- What I want is on the suppliers table to have a tab where I can allocate the tours

       

      Can you help with the diagram of the many to many - The idea is that when allocation a tour to a supplier I will only display the cities that belong the country where the supplier works and the tours that only belong to the city where I am allocating the tour

       

      For example

       

      Supplier A - Is in Argentina

      Tours tables has two tours in Buenos Aires Tour A and Tour B

       

      I want to go to my supplier table under the tour tab and have a portal where I will choose the ports available to Argentina - In this case Buenos Aires and based on that choose either Tour A or Tour B which are tours available in Buenos Aires

       

      Thanks

      Dinora  

       

        • 1. Re: Again Many to Many Relationships
          comment_1
            

          Well, if you build it this way:

           

          Suppliers >- Countries -< Cities -< Tours

           

          then each supplier "sees" only the tours that are available in the country where they operate.

           

           

          However, I am not sure what you mean by "allocating a tour to a supplier". It seems like you need another table to record the actual interactions between a supplier and a specific tour, e.g.:

           

          Suppliers -< TourInstances >- Tours 2 

          • 2. Re: Again Many to Many Relationships
            dinora
              

            Thank you this is all correct - And this is what I have done so far :smileywink:

             

            The tours 2 table you mention is a table occurrance of the tours table - Correct?  

             

            The issue I am having is that rather than seeing all the tours that are available on the country where the suppliers operate - I want to be able to see all the tours by city within that country

             

            So in my joint table I should be able to choose a supplier then from here select a port starting values with country and by selecting a city for that country then select the tours for that city

             

            I am having problems with this step of selecting a city from a country which comes by selecting the operator. 

             

            I hope I make sense??

             

            Thanks

            • 3. Re: Again Many to Many Relationships
              comment_1
                

              Yes, Tours 2 would be another occurrence of the Tours table.

               


              Dinora wrote:

              rather than seeing all the tours that are available on the country where the suppliers operate - I want to be able to see all the tours by city within that country


              That is possible - but obviously, you must make a selection of a city first somewhere. If you define a value list of Cities to show values from the City field in City (or rather the CityID field, with the City being the "also show" field), showing only related values starting from Suppliers, that list is going to be restricted to cities in the country of the current supplier. Then you need another relationship to restrict the tours to those available only in the previously selected city - and another value list showing only those related values from Tours.

               


              Dinora wrote:

              So in my joint table I should be able to choose a supplier then from here select a port starting values with country and by selecting a city for that country then select the tours for that city


              Not exactly - that is, not if you are doing this from a layout of Suppliers, in a portal to the join table. Because in this situation, the SupplierID will be automatically entered in any record that you create in the portal. You only need to select the city, and then (using the second value list mentioned above) the tour.

               

               

               

               



              • 4. Re: Again Many to Many Relationships
                dinora
                  

                Understood, and I am almost there, but

                 

                You wrote


                That is possible - but obviously, you must make a selection of a city first somewhere. If you define a value list of Cities to show values from the City field in City (or rather the CityID field, with the City being the "also show" field), showing only related values starting from Suppliers, that list is going to be restricted to cities in the country of the current supplier. Then you need another relationship to restrict the tours to those available only in the previously selected city - and another value list showing only those related values from Tours. 

                So what tis the relationship that will restrict the tours to the previously selected city -  I am not seeing this how this relation should work

                 

                I have the country------<Suppliers

                I have Cities---------< Tours

                I have joint table Supplier -------<Suppliers to Tours >---------Tours2

                 

                I have a value list with the cities Displaying from the city table , City ID and City name from the second field

                 

                Now the relation that you are talking about to restrict the tours - Should this be Cities 2 Table to the Tours 2 Table linked by the City ID to City Name ??

                 

                I cannot picture the relation I need here? Sorry but maybe is too late and I need to give it a break and in the morning I will see it clearer

                • 5. Re: Again Many to Many Relationships
                  comment_1
                    

                  The additional relationship would be:

                   

                  Suppliers to Tours::CityID = Tours 3::CityID 

                   

                  and the value list would show only related values from Tours 3, starting with Suppliers to Tours.

                   

                   

                  Note that this relationship exists only to enable the value list, and plays no role in your data structure - unlike the other relationships we have discussed so far. 

                  • 6. Re: Again Many to Many Relationships
                    dinora
                      

                    I am sorry, I am a new with filemaker so this means


                     

                    Suppliers to Tours::CityID = Tours 3::CityID 


                    In addition to the current tables I need to create a third TO - Tours 3 and link the City ID to the City Name on this table. 

                     

                    What do the colons mean?

                     

                    Thanks

                    • 7. Re: Again Many to Many Relationships
                      comment_1
                        

                      Dinora wrote:

                      I need to create a third TO - Tours 3 and link the City ID to the City Name on this table.


                      Yes - except you want to link City ID to City ID, not name.

                       

                        

                      This:

                       

                      TableOccurrence::FieldName

                       

                      is a "fully qualified field name" - a way to refer to a field unambiguously. You'll see it a lot in scripts, and in calculations that reference related fields.


                      • 8. Re: Again Many to Many Relationships
                        dinora
                           It worked