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.
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.
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
So how do I establish this relation
I'd add additional fields to the Join Table. Something like
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.
Yep it works -