6 Replies Latest reply on Aug 6, 2014 6:09 PM by arnojansen1

    Price query

    arnojansen1

      hihi I am a filemaker pro 13 user on mac. I have a simple database and need some help to create a calculation or find procedure.

       

      My database is one hotel has many rooms, and rooms have many seasonal prices. So what i am looking for is can I find a price in a certain season/ date range and if the date starts in one date range how can i assure the routine finds 3days in one range and shows me 1day in the next for instance

       

      my database looks a bit like this, hotel-A, has Lakeview room, 1Jan2014 to 30Jun2014 the price is 200 dollar and 1July to 31Dec2014 the price is 220

      the fields are room-id, start date, end date, price

      with a relation to hotel, and lakeview room tables

       

      now if somebody wants to book a room from 29Jun2014 to 2July,

      the calculation is total 3 nights = (2nights * 200) + (1 * 220)

       

      as you can imagine the date range will be extended every year as new prices come in so the calculation I am looking for needs to take care of that too. The date range can be 4 or 5 per annum covering for more expensive periods in the year etc. So not just 2 because i could potentially use an if or nested if function. I have a hotel that gave me a range of 6 date ranges already

       

      Thanks for your help!

       

      cheers

      Arno

        • 1. Re: Price query
          erolst

          See if this thread

           

          https://fmdev.filemaker.com/message/150038

           

          can help you.

          1 of 1 people found this helpful
          • 2. Re: Price query
            arnojansen1

            how nice is that, I will have a look at this and the example files provide. looks like this could solve my problem. Thanks so much for your quick respond and help. Cheers

            • 3. Re: Price query
              arnojansen1

              Thanks again for your help here, I am working on the script to match my fields and try to use it in a portal.

               

              Cheers

              Arno

              • 4. Re: Price query
                arnojansen1

                Hi Erolst,

                 

                your answer was very helpful and the script works in my database.

                 

                however I have a new problem as my database was relational between rooms

                and room prices this no longer works.

                 

                What i am looking for is the following. I have a rooms table where i lookup

                room prices (from the room prices table). in the rooms table i have a

                portal where i would like to create bookings so first room is booked for

                2days and calculate the price for that room from the prices table. then the

                next room is booked, different hotel, different room with the prices.

                 

                sorry it becomes technical when i write it down. your price query works

                perfect if you have one product with many prices.

                 

                I look for a booking form that eventually works with an itinerary where

                people book several hotels/ rooms for subsequent days with the correct

                prices. Basically i need the script to now what room number to looks for

                and then perform the script. the room number is a field on my rooms table

                that links into the prices table with a foreign key

                 

                Hope this makes a little sense and thanks again for  your help.

                 

                Cheers

                Arno

                • 5. Re: Price query
                  erolst

                  You can do that with a script which takes a list of the parameters that you now pass in manually (start date and duration) for a specific room, but the rub is probably that …

                  arnojansen1 wrote:

                  to now what room number to looks for

                  and then perform the script.

                  … you need a logic that finds matching rooms per an itinerary.

                   

                  If you think this through from the end (bookings with rooms and calculated prices) to the beginning (an itinerary that maybe is just a text list), you can develop a script that step by step transforms your itinerary list into a set of bookings.

                   

                  I don't know your workflow, but I can imagine that you could create a list of preliminary booking records that have everything but the room number, then start looking for one, based on collision detection etc.. When they have one, calculate the price, etc.

                   

                  Sounds like an interesting problem.

                  • 6. Re: Price query
                    arnojansen1

                    Thank you again Erolst,

                     

                    I might need to change the way I have related tables and have a process where I calculate the price and then copy that to the itinerary. At least that will get me started and once I learn more about the ins and out from filemaker try the perfect it on the way. It sure is in interesting one and keeps me thinking.

                     

                    appreciated

                     

                    cheers

                    Arno