AnsweredAssumed Answered

Price query

Question asked by arnojansen1 on Aug 1, 2014
Latest reply on Aug 6, 2014 by 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!