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

    Complicated Relationship



      Complicated Relationship


      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?




        • 1. Re: Complicated Relationship

          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:





          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


            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.




            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




            • 3. Re: Complicated Relationship

              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
                   Yep it works -