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

# Price query

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

cheers

Arno

• ###### 1. Re: Price query

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

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

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

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

Hi Erolst,

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

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

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