Relationships & globals - how to use for quoting interface?
I have been studying FileMaker and I have successfully created some tables, relationships, scripts, layouts for a small database.
Now I want to do something more complex (trying from scratch) and I am stuck. Please help!
I am attaching a screenshot of my current (test) database.
I am trying to create a quoting interface (layout and calculations).
As you can see in the diagram I have a Hotels, Rooms, Periods and RoomRates table.
These work fairly well with the current relationships, I have a Room Rates layout where I can enter all rates for each hotel.
[a "roomrate" is unique combination of hotel, period (periods differ for each hotel) and room type (room types differ for each hotel, that's why this setup) ]
I have also created for now a Quotes table, with global fields.
Here's where I have trouble:
- where do I put them (in separate Quotes table or in existing table)?
At the moment I don't need to save quotes, just to calculate them and then merge them in letters/emails.
- how do I use them & what further relationships do I need to setup?
What I want to accomplish:
1. User selects hotel (from a drop-down ideally, or a portal)
2. Rooms of that hotel are shown in a portal
3. User selects specific room
4. User enters dates for booking (start/end dates)
5. Total is calculated (based on the room type & period)
(other amounts will be calculated too, but if I get one right, then I can do the calculations for the rest)
How can the user select the hotel & then select the room?
I sense that the solution relies on globals, but I have been trying things I got messed up.
If I have a layout showing records from Quotes, the rooms don't work, if I show from RoomRates, something else doesn't work...
I have looking at this for too long and now it all looks fuzzy!
Please, any help will be appreciated.
(I use FileMaker Pro 9 Advanced, on WinXP)