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
Thanks again for your help here, I am working on the script to match my fields and try to use it in a portal.
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.
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 …
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.
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.