4 Replies Latest reply on Mar 17, 2010 11:00 PM by comment_1

    Date calculations and finding a price

    arnojansen1

      Title

      Date calculations and finding a price

      Post

      Hi, I am working on the following and need some help.

       

      I have a product in my database that has a price of lets say 10, but this price is only valid from 1Jan09 to 31Jul09 and the price for 1Aug09 to 31Dec09 is 12. 

      Product, valid day from 1Jan09 to 31Jul09, 10

      Product, valid day from 1Aug09 to 31Dec09, 12

       

      How can i make a function based on the sale date (variable) to select the correct price. So if i sell the product after 1Aug it will take the 12 as the price.

       

      Cheers, 

        • 1. Re: Date calculations and finding a price
          comment_1
            

          You don't need a calculation for this, only a proper structure: a table of Products, and a table of Prices with fields for:

           

          • ProductID

          • FromDate

          • Price

           

          Then you can lookup the correct price from your sales table using a relationship:

           

          Sales:: ProductID = Prices:: ProductID

          AND

          Sales:: Date ≥ Prices:: FromDate

           

          with the Prices records being sorted by FromDate, descending.

          • 2. Re: Date calculations and finding a price
            arnojansen1
               Thanks for the reply!
            • 3. Re: Date calculations and finding a price
              arnojansen1

              Your solution worked fine, but just ran into a new problem.

               

              I am building a database where you e.g. sell hotel rooms. And prices are only valid for a certain period. So if somebody books a room from 30 March to 3 April and the price changes per 1 April how can I pick up the two prices and calculate 1 day against the old price and the rest against the new price? without having to make two bookings, one for 30 March and from 1 April to 3 April.

               

              I am using filemaker pro 10 on a iMac using MacOS 10.5.8  The database is used on a stand alone machine only, and my filemaker experience lvl is beginner to intermediate.

               

              many thanks

              Arno

               

               

              • 4. Re: Date calculations and finding a price
                comment_1

                Well, you could lookup the price on departure date (using another relationhip) and if it's different from the price at arrival date, do a calculation. This is assuming there can be only one price change during a booking - otherwise it can get very complicated.