6 Replies Latest reply on Apr 8, 2009 3:25 PM by TSGal

    Pricing and Portals

    dinora

      Title

      Pricing and Portals

      Post

      I am working with 4 tables

       

      Suppliers -----<Join Table (Suppliers to Tours)>---------Tours

       

      So I have a portal in the suppliers table where I can allocated tours to be supplied by supplier A

       

      Each Supplier may have different prices for the same tour which brings me to my next table Pricing

       

      So Pricing table has supplier id to be chosen tour to be chosen and then we enter the price -  The price has a validity date range Start date - End date

       

      The question as follow

       

      From the Suppliers Layout - I have a portal which displays pricing for all the tours that are allocated to Supplier A - regardless of whether the tour has pricing or not this is just to keep track of the tours allocated to supplier A

       

      So in my portal I see -

       

      Tour A

      Tour B

      Tour C

       

      All three tours have been allocated to Supplier A and therefore display - This portal is coming from the Joint table where Suppliers and tours meet.  From here I can enter the pricing for each tour

       

      What I want to do is set up a preliminary step in this portal where from a drop down menu I can choose the years and the portal displays data based on this

       

      So for example

       

      Jan 09  to Dec 09

      Jan 10  to Dec 10

       

      The portal should allow me to choose either of these two options

      Choosing Jan 09 to Dec 09

       

      Tour A $20

      Tour B $40

      Tour C (no price as it is a new tour) so I could add price if I needed to

       

      Then Choosing Jan 10 to Dec 10

      Tour A

      Tour B

      Tour C

       

      All blank pending me to enter the info

       

      Can I have a dynamic portal

        • 1. Re: Pricing and Portals
          TSGal

          Dinora:

           

          I'm not quite sure what you are asking, but this may give you some ideas.

           

          If I understand correctly, Supplier 1 may have three Tours (Tour A, Tour B and Tour C).  However, each tour can be offered more than once.  Therefore, you would have a one to many relationship between Tours and Tour Dates.  Therefore, there would be an additional table that connects Tours to Tour Dates.

           

          Based upon a Tour, you can set up a pop-up (or drop-down) based upon the values in the Tour Dates table.  If the Tour has a set price, then that price would reside in the Tours table.  However, if the Tour price changes (based upon seasons, weekends, etc.), then the price would reside in the Tour Dates table.

           

          So, in your Join table, you can select a Supplier, a drop down list could list the possible tours for that Supplier, and then another drop down list would allow you to select the Dates.

           

          Is this what you are trying to accomplish?

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Pricing and Portals
            dinora
              

            Hi TSGal,

             

            No.  On my suppliers table I have a portal which allows me to display all the tours this supplier offers -  Keep in mind this tour can be operated by different suppliers so the portal is a joint table where Suppliers and Tours are matched up.

             

            So now if I go to Supplier A layout

             

            A portal displays that supplier A Offers

             

            Tour 1

            Tour 2

            Tour 3

             

            And I can allocate Tour 4 if I wanted to so on...

             

            For each supplier I have a rate year meaning a validity of rates for the season so I have a table called rate years which contain two fields start date and end date this rate year is assigned to each supplier - On the supplier table I have a portal showing the rate years lets say

            Jan 1 2009 Dec 31 2009

            Jan 1 2010  Dec 31 2010

             

            So far I have the following Supplier-------<JointSupplierTour>----------Tours

            Then Suppliers--------<Rate Years

             

            Now I have built up a pricing table in here I select a record from teh Jointsuppliertour table and I say that the price is $20 for the rate year of

            Jan 1 09 to Dec 31 09  - In this table I am obviously selecting the record from the Jointsuppliertourtable, I am also selecting the rate year and I am entering the pricing.

             

            So now I know that Tour 1 operated by Supplier A costs me $20 valid this year

             

            Rather than going through this pricing table and entering one record at a time.  I have a portal as mentioned above where I display the tours that have been allocated to Supplier A -  The idea is to also be able to enter the pricing on a portal from the Supplier Table and enter the validity rate year for all records on the portal at once. 

             

            Something like this

             

            I am on the supplier A Layout - I go to a pricing tab - I select the rate year I am looking for from a drop down - I select Jan 1 to Dec 31 09 and below displays a portal with all the tours assigned to this supplier

             

            Tour 1

            Tour 2

            Tour 3 

             

            All three tours need pricing so I enter them $20, 40 and 60 respectively but the field for the validity rate is already pre-filled as I already have selected the rate year above.

             

            I hope is clearer now.  

             

            • 3. Re: Pricing and Portals
              dinora
                

              TS Gal,

               

              I want the tour date to reside in its own table and be assigned at the supplier level rather than the tour level -  All tours operated by the same supplier have the same validity dates

               

               

              • 4. Re: Pricing and Portals
                TSGal

                Dinora:

                 

                Thank you for the clarification.

                 

                You are still going to have to enter the rates for the tours.  If each supplier has different rates for tours, then they will have to be entered in a Join table.  That is, Tours to Suppliers.

                 

                Otherwise, you can keep the price in the Tours table.  However, it looks like you have the Rate Years applied to the Supplier.  That means, all Tours are priced the same, which is contrary to what you told me in the first post.  You can set up a script to create records for the next year's pricing for a Supplier for each tour.  Your join table would then include date range which you can use as part of the relationship when selecting tour dates.

                 

                TSGal

                FileMaker, Inc.

                • 5. Re: Pricing and Portals
                  dinora
                    

                  Hi TSGal,

                   

                  I have been looking at this for hours now - I have reworked the database so that I enter pricing on the join table being the Tours to Suppliers. 

                   

                  You are correct The rate years I have applied them to the supplier - Now my question is how do I allocate several rate years to the same record that is located on the joint table

                   

                  My joint table display a an supplier operates Tour A - How do I enter pricing here based on a rate year?

                   

                  • 6. Re: Pricing and Portals
                    TSGal

                    Dinora:

                     

                    How many table occurrences do you have of Suppliers?  Do you have one just based on Supplier ID?  Or, do you have it based on Supplier ID and Date?  If one, then you maybe want to have one for each.  That way, with the second table occurrence based on Supplier ID and Date, you will be able to find just those records for the date range where you can enter the rates for one particular year.  Otherwise, you can show all tours for each year and enter the rates for each year.

                     

                    TSGal

                    FileMaker, Inc.