4 Replies Latest reply on Apr 1, 2009 8:52 PM by dinora

    Complicated Relationship

    dinora

      Title

      Complicated Relationship

      Post

      I have the following set up

       

      Suppliers ----<Tours Suppliers>----------Tours

       

      These tour which can be allocated to different suppliers has pricing that needs to connect to it so in theory I have another table called pricing

       

      which needs to have the data on the pricing table allocated to each tour belonging to each supplier.

       

      I want to have a portal in my suppliers table where I can display all the tours allocated to that supplier and be able to enter the pricing from the portal rather than the table.

       

      Can anyone help?

       

      dinora

       

        • 1. Re: Complicated Relationship
          philmodjunk
            

          First let's check for understanding:

           

          You might have a tour called "Five day Riviera"

           

          If you select the Acme Travel supplier, Five day Riviera costs $2,000.

          If you select the Nadir Travel supplier, Five day Riviera costs $10,000.

           

          That correct?

           

          If so, you need a Join table (sound familiar?)

           

          Create a table Supplier_Tour and define three fields:

          SupplierID

          TourID

          Cost

           

          Define relationships linking Supplier_Tour to the Supplier and Tour tables.

           

          SupplierTO ---- Supplier_Tour-----TourTO

           

          Select SupplierTO as your layout's table.

          Set your portal to refer to Supplier_Tour

           

          Place TourTO::TourName and Supplier_Tour::Cost fields in your portal rows.

          • 2. Re: Complicated Relationship
            dinora
              

            Understood

            but what if I want to have the pricing on a different table that connects to the table supplier tour but needs to display on the supplier table.

             

            The reason is that the price for the Five Day Riviera costs $2000 today for Acme Travel Supplier but it may go up to $5000 for next year and I am trying to keep history.

             

            So I do have the joint table that is connecting my suppliers to my tours but I need a table that serves as depository for prices by tour for each supplier - And this I can accomplish as well by linking the Supplier_tour table to the pricing table.

             

            I have a portal on the suppliers table that allows me to allocate tours to different suppliers -  What I would like next is to see another tab on my suppliers table where displays all the tours allocated to the particular supplier and be able then to enter pricing for each.

             

            i.e.

             

            Supplier A has been allocated to Tour Miami, Tour Fort Lauderdale, Tour Tampa - I am doing already this through a portal

             

            Now comes pricing I want to be able to go to a tab from the suppliers table and see the list of tours allocated to Supplier A so I can enter the price

             

            So I should see

             

            Tour Miami--- Here I enter the price which feeds the pricing table I have created. 

            Tour Ft Lauderdale

            Tour Tampa

             

            So how do I establish this relation

             

            Thanks

            Dinora

            • 3. Re: Complicated Relationship
              philmodjunk
                

              I'd add additional fields to the Join Table. Something like

               

              Effective Date

              Active (text field with "yes" or "no" )

              SupplierKey, a calculation: If(active = "yes",SupplierID,"" )

               

              Link your supplier table to SupplierKey instead of SupplierID.

               

              Now you can have multiple records in your table for the same combination of Supplier and Tour but only the current price is visible in your portal.

               

              You can use a different relationship and portal to display the pricing history for a selected supplier and tour. In this portal you can add a button to

              Set Field [Supplier_TourTO::Active, "No"]

              Set Variable [$SupplierID, Supplier_TourTO::SupplierID]

              Set Variable [$TourID, Supplier_TourTO::TourID]

              Go to Portal Row [Last]

              Set Field [Supplier_TourTO::active, "Yes"]

              Set Field [Supplier_TourTO::SupplierID, $SupplierID]

              Set Field [Supplier_TourTO::TourID, $TourID]

               

              That seems easier then adding a separate table.

               

              • 4. Re: Complicated Relationship
                dinora
                   Yep it works -