1 of 1 people found this helpful
It sounds like you're on the right track. This generic data model might help you a little: Travel Agencies Data Model..
In this model, you could list all services together in a portal via the Services TO, and overlay the fields specific to each service type (airlines, restaurants, cars, hotels in the linked example). A portal filter would let them see just the services they wanted. Or, you could use a virtual list technique. Or, multiple tabs with a portal on each like the good old days.
The "save it" step that you refer to would be the record in the Service Bookings table, which forms the backbone of your itinerary and agenda -- these seem to be different reports based on the same data. Note that if they are on a prolonged tour you might see the same hotel (for example) multiple times on the trip, e.g. they check in, stay 2 days, check out, go for a bus tour, then check in again at the end of the tour. This hotel would have 2 service booking records.
I imagine the process would look a little like:
1. Enter customer info
2. Select destination
3. Create Booking with status: In Progress/Pending/whatever
4. Build your itinerary by selecting services, which would create Service Booking records
5. Show the itinerary to the client
6. Get feedback from client
7. repeat steps 4-6 until client approves
8. Confirm each Service Booking with the actual services
9. Change Booking Status to Confirmed
and so on.
I hope this helps, and I hope I wasn't too presumptuous in the level of detail I gave.
Many thanks for your valuable feedback! I really appreciate your input in this matter. It is definitely helpful.
You are describing perfectly the system that I am trying to create.
Regarding the Services listing, do you think it would be optimal to have 1 database for all, or 1 database for each type of service (i.e. hotel, guide, etc.)? Given that the records are quite different, wouldn't it be easier to have them in separate databases, and to have a portal searching the various databases?
I am quite new at FM, and I have to say that I am not certain how to go about with point number 4: "Build your itinerary by selecting services, which would create Service Booking records". I think that you refer to the part that I previously described:
* Building an itinerary quoting system, that begins with a unique client ID, then pulls out from the databases the required information to build an itinerary. I am thinking: begin with connecting the client ID with the current quote. Begin the quote: a first drop down would list the locations offered. A second dropdown would list the categories to choose from (hotels, transportation, guides).
-> Based on the previous selection, a third dropdown would list the service providers from the category selected in the location selected, the products that they offer and the price associated with each one.
Do you have any suggestion on how to structure and script this part?
Once again, thank you very much!!
1 of 1 people found this helpful
I'm sorry if I threw too much information at you at once, given that you are new to FileMaker.
Yes, each of your different service types are stored in their own table. Airlines in Airlines, Hotels in Hotels, etc.
The Service table is more like a common point of entry for all services. In each of the individual service tables (Airlines, Hotels, etc) you would have a field fk_Service_ID.
Every record in the Service table would have a corresponding record in *one* of the services tables.
By "selecting services" I meant the very broad idea of clicking on a service in a portal which would run a script that would:
1. capture the Booking ID (from the record you're viewing)
2. capture the Service ID (from the portal)
3. Create a record in Service_Bookings
4. Set Service_Bookings::fk_Booking_ID to Booking ID
5. Set Service_Bookings::fk_Service_ID to Service ID
Service_Bookings is what's known as a "join table" because it literally joins two tables together.
I hope this makes sense. There are a lot of concepts about relationships that you have to grasp in order to go much further with this, especially when it comes to understanding why you would have a Service table between the individual services and the Service_Booking table.
EDIT: Here is a link to a quick 'n' dirty demo that I hope will illustrate some of the concepts: Dropbox - TravelAgencyTest.fmp12.zip
The demo is obviously incomplete. Only Bookings, Services, Service_Bookings,Hotels and Restaurants.
To add Services, go to the Services layout. If the service is a hotel, enter details in the left-hand fields. If a restaurant, enter data in the right-hand fields. These fields are actually in the Hotels and Restaurants tables, respectively, and will create related records in there automatically. Under normal circumstances you will prevent users from entering Hotel *and* Restaurant data in the same Service record.
I hope this helps!
Once again, thank you very much for your help!
I have been studying table relationships this past week. You were right, it has helped me making good progress on the design of the booking system.
I am seeing your edit now with the link to the demo. I will review it over the weekend and let you know iI have any questions!
Thank you !!
I'm glad that you found my response helpful. Bear in mind that this is only one way to do things; I only tried to present a basic example.
All the best,
Hope you are well !
I have made good progress with table structure and relationships, thanks to your help. I am also able to create records that are assigned to specific type of service (i.e. Hotel, Transportation, Restaurant, etc.).
At this point, I am trying to display the Services in the Bookings table, before I can select them to create various records in the Bookings table, and therefore building an itinerary.
From the Bookings table, I created a portal that displays the Services by destinations, without too much trouble. However I am getting stuck when it comes to showing the actual Service Details, that belong to each service provider. These are located in the tables "Service | Hotels | List" and "Service | Ground | List" in my example.
If I select "Chicago" in the destination list, it shows all the service providers in Chicago. If I select one of them, I would like to have the details of the service (i.e. the type of rooms, the price, or the price of transfers for the Ground Transp. providers, etc.) displayed in another portal on the side. I have been trying to create a TO of Services that would be linked to a "Selected Field" in Bookings, but I cant seem to get it straight.
Then, a script in the "+ Add" button will add the record to the Bookings, as in your example.
Do you have any suggestions?
Thanks in advance!!
Sorry for the late reply -- I've been fighting a respiratory infection and I'm running at half-jets.
I haven't had a chance to thoroughly look at your example. At first glance I'm not sure why you have TOs like "Service | Hotels" joining "Services" and "Service | Hotels | List"... are there multiple records in the List TOs for each record in the Hotels TO (for example)?
If you haven't already, may I suggest that you look through the FileMaker Training Series: Basics for a primer on how you can get your relationships to work for you.
Very sorry to hear about your health issue.. I hope that it gets resolved soon!! And please don't use your remaining fuel on helping me with this project... although your help is very much appreciated!
I set up Services like a portal, where I can enter the details of each hotel, restaurant etc.. Much like you did in your example. However there is a second layer of data that I need to enter, which is the data of all rooms that the hotel sell with each price, the data of all the transfers that each Ground Transp. provide at different price and with different types of vehicles, or the various types of tours that the guides offer, etc...
For that second layer of data, it seemed evident that I needed a second database.. i.e. "Service | Hotel | List" or "Service | Ground | List". Isn't it the case?
I did review the Basics of Filemaker Series and also the Relational Database Design, which were very helpful!
Thank you Peter, be well!