skingjack2 Posted December 25, 2008 Posted December 25, 2008 Hi, Back again with another question...hopefully this the right forum for this query. I have 1 DB with 2 tables named bookings & clients. When I add a new client entry in the bookings table (i.e. a client books a tour), there typically is a traveling companion(s). When completing the booking in the bookings table, I can add the traveling companions names that in turn via a portal will add the traveling companion(s) name(s) to the clients table. The question is how to check for/prevent a duplicate travel companion name(s) entry into the clients table? The 2 tables are related via clients ID. Any quick thoughts to point me in the right direction on how to proceed? TIA. Jack P.S. Happy Holidays!
bcooney Posted December 26, 2008 Posted December 26, 2008 I'd change how you add traveling companions. I wouldn't allow direct entry in the traveling companions portal. I'd have the Add Companions button start a Find in Clients. Then, if not found, offer to create new Client and add as companion.
RalphL Posted December 26, 2008 Posted December 26, 2008 My wife & I take a lot of tours. I made a database to tract with whom we had traveled. I ended up with 3 tables, Tours, Members (clients in your case) and a join table MemTours since this was a many to many relationship. My procedure is to first enter the Tour data, then enter the Members. I start with a layout that has portal that displays all members in the database and a global field that I use to enter the last name. I filter the portal with the global field using a technique called "TypeAhead" from John Mark Osborn's site. If the name appears in the portal, I have a button in the portal that takes me to that record. If not I have a button that creates a new record and transfers the contents of the global field to the last name. When in the record I have a portal that shows all tours the person has been on with us. I have a button which creates a new record in the join table and a dropdown of tours. I select a tour from this list to complete my data entry. To add a companion would be a little more complex. I think I would record companions in the join file.
skingjack2 Posted January 1, 2009 Author Posted January 1, 2009 Bcooney, Thanks for the tidbit; I was uncertain about the best way to perform this...I've now implemented your method in my DB with one problem arising...how to add to the travel companion to the original booking? Any insight would be appreciated? Jack "I'd change how you add traveling companions. I wouldn't allow direct entry in the traveling companions portal. I'd have the Add Companions button start a Find in Clients. Then, if not found, offer to create new Client and add as companion.
skingjack2 Posted January 1, 2009 Author Posted January 1, 2009 Ralphl, Haven't progressed that far into developing the database (still working on the basics) but certainly have a lot of interest in how your solution works. Could you possibly send me screenshot of the main page of your solution via PM so that I can get a better idea of how you put it altogether. Much appreciated if you are able to! Jack
RalphL Posted January 1, 2009 Posted January 1, 2009 Here are 2 screenshots. First is initial screen with enough letters entered to limit portal to my name. The second is the detail screen which shows the member details and a portal showing all trips. Initial_Screen.pdf Detail_Screen.pdf
bcooney Posted January 1, 2009 Posted January 1, 2009 (edited) This is a challenging data model or I'm overthinking the whole thing. I want Clients related to Tours, obviously. Since it's a many-to-many, I'd put a join table btw them; Bookings. However, the Traveling Companions has me stumped. I want to select TCs from Clients. I would like to view a client record and see all his TCs. So, if John is traveling with Betty, I'd see her from John's record, and him from Betty's record. However, their relationship as TCs is also linked to a specific booking. If he went on the same Tour next year, it'd be a different booking, with a different set of TCs. Any help? Here's the start of a demo. Tours.fp7.zip Edited January 1, 2009 by Guest
comment Posted January 1, 2009 Posted January 1, 2009 I think the term "traveling companion" may be misleading. Suppose a client books a tour for his parents - but he himself is not going. Who's the traveling companion here? I believe there should be a separate table of all people actually taking the tour (which could be joined to a general table of Contacts, but doesn't have to be). Alternatively, there could be a general join table of all people related to a booking, with their role specified in the join.
skingjack2 Posted January 1, 2009 Author Posted January 1, 2009 Bcooney, Thanks for the demo file... just starting to digest how this works. Fyi... I note that upon opening the file a & viewing the client layout, it indicates a table is missing. Jack
skingjack2 Posted January 1, 2009 Author Posted January 1, 2009 Comment, Bcooney, Ralphl, For *hopeful* clarity, a traveling companion can be a spouse,children, friend etc. and comprises the actual traveling participants. Whomever (say a son in your example) pays for the trip is also a client and the purchase would somehow have to reflect in the sons client profile. In my currently crude db, I just make a notation that the participants booking that the trip was paid by client "x'. FYI, a tour can comprise anywhere from 1-150 participants. HTH. Jack
comment Posted January 1, 2009 Posted January 1, 2009 Whomever (say a son in your example) pays for the trip is also a client and the purchase would somehow have to reflect in the sons client profile. No, it should be reflected either in the booking record or in the record joining the booking to Clients. Otherwise, when the same son books another tour for himself and his wife, his record in Clients is going to be "tainted" with the details of a previous booking. I think the important questions here are what details of the "traveling companions" (perhaps "passengers" would be a better term?) do you need to record, and is the fact that a passenger took another tour in the past significant in any way.
bcooney Posted January 1, 2009 Posted January 1, 2009 (edited) Thanks for chiming in on this, comment. Guess I was stumped for a good reason. I see traveling companions as smaller groups within the Tour roster. I was imagining that it's helpful to know that Bob (who paid) is with Carol, Ted and Alice. This may lead to seating arrangements at dinner... Ted paid for himself and Alice. However, when we market to Ted, we want to also market to Bob and mention that their group is invited on another Tour. I'm having a good time with this, huh? So maybe that's it. Groups? A way to assign group membership to the Tour roster? Edited January 1, 2009 by Guest
bcooney Posted January 1, 2009 Posted January 1, 2009 Jack, This was a seriously raw demo file. I added tables, deleted tables, playing with the client/booking/companion relationships. I hope it's not more confusing than it is helpful.
comment Posted January 2, 2009 Posted January 2, 2009 Let's not get carried away. My point is that there are two sets here, Clients and Passengers, with no implied cross-reference. Some passengers may be spouses, minors or even pets: you don't want to automatically include any of those as potential clients.
bcooney Posted January 2, 2009 Posted January 2, 2009 Understood. Clients aren't necessarily passengers, and passengers aren't Clients, if you define a Client as a person that has paid for a Tour. So, I'd suggest a People table. Then an Invoice, InvLI table. When, a person books a tour, you generate an Invoice for them. If a person has a relationship to an Invoice, then they're a Client. Then InvLI table is the "booking" info, with a TourID and relevant dates. Separately, on a Tour form, you'd add People to the Tour as Passengers. You could also include the "grouping" function that I mentioned above, if you want to get fancy. However, I would find a way to define Tours so that repeating tours to Greece, for example, can be reported. Perhaps a tour categoryID?
comment Posted January 2, 2009 Posted January 2, 2009 There are many possible variations here, depending on the business rules (of which we know very little).
skingjack2 Posted January 3, 2009 Author Posted January 3, 2009 and the court seems to be getting bigger by the moment =) I thought this would a relatively easy problem to resolve; apparently not. I was originally thinking that the group/tour aspect would be addressed at future date once I have other issues addressed (i.e. invoicing) but if we can tackle both at one time it would be beneficial. Thanks to both of you for bringing up potential issues with how this DB is to designed... Here are my thoughts after some more reflection (feel free to comment) In the initial example, I overlooked adding a line... in this example the son who has purchased the trip for his parents ("the passengers") is to be tagged in the client table as a prospect rather than client but somehow still reflect in his record in the client table (typically this prospect will become a client at some point in time in the future). IMO, a booking could be subset of a tour/group roster Addressing each of your queries/comments: "is the fact the passenger took another tour is the past significant?"- the answer is yes. bcooney- re: post 313000- this is very important particularly with respect to cruises. More info to come later once I'm able to digest the info you've provided so far...remembering I'm a relative beginner (working my way through book "FM, the missing manual" as we speak) Feel free to ask more questions... Jack
comment Posted January 3, 2009 Posted January 3, 2009 "is the fact the passenger took another tour is the past significant?"- the answer is yes. OK, then clearly passengers must be entered as full-fledged records in your Contacts table. This eliminates one of the three possibilities I have outlined in my ERD. I am also getting the impression (please confirm) that it's possible to have more than one person who is somehow related to a booking, without being passengers themselves. If so, I believe this leaves only one possible model (the bottom drawing in my ERD) Contacts -< Participants >- Bookings >- Tours where each Participant is tagged with his/her role in the booking. Note that a single contact could be a multiple participant in the same booking, under different roles. I'd be careful about booking groups as such. Group memberships are fluid: of the 50 employees of Acme, three are not going, two cancel at the last moment, and five leave the company after the tour. So who was actually on board? It's alright to have groups for your contacts, but IMHO the act of booking an entire group should really be a scripted assignment of each individual group member as a participant.
skingjack2 Posted January 3, 2009 Author Posted January 3, 2009 OK, then clearly passengers must be entered as full-fledged records in your Contacts table. This eliminates one of the three possibilities I have outlined in my ERD. * I only noted 2 erd diagrams and my thoughts were that the upper one would be applicable = ) I am also getting the impression (please confirm) that it's possible to have more than one person who is somehow related to a booking, without being passengers themselves. *this is possible... 1 booking for 2 participating individuals, where for illustration purposes, their respective parents are paying for each individual separately...example would be grad present for each participant If so, I believe this leaves only one possible model (the bottom drawing in my ERD) Contacts -< Participants >- Bookings >- Tours where each Participant is tagged with his/her role in the booking. Note that a single contact could be a multiple participant in the same booking, under different roles. *yes, a contact can be both the payer & participant i.e. 1 contact pays for all participants via credit card and the other participants reimburse the credit card holder at a later date/time (which is not of my concern) I'd be careful about booking groups as such. Group memberships are fluid: of the 50 employees of Acme, three are not going, two cancel at the last moment, and five leave the company after the tour. So who was actually on board? It's alright to have groups for your contacts, but IMHO the act of booking an entire group should really be a scripted assignment of each individual group member as a participant. *not sure I follow though bear with me... if 3 employees are not going they would be of little concern- they are not participating. Of the ones that cancel, they would be removed from the tour roster (however any monies they forfeit for cancelling would be retained and have to be accounted for). The participants that quit post tour is not a concern...I'd still have them noted as clients in my contacts table & would be able to continue to market to them. Not sure if this is helpful but note that 1 couple doesn't necessarily mean a group however 2 couples traveling together could be considered a group. FWIW, the only difference between a group and a tour is that a tour capacity is usually finite (limited to say... 50 participants). There maybe be many groups in a tour but never exceeding the tour capacity. I hope this helps answer your query and if I've messed up terminology as I'm wont to do, please accept my apologies. Jack
comment Posted January 3, 2009 Posted January 3, 2009 The upper diagram is actually two - connecting the dotted line or not makes a big difference. I think what you call a group is actually a booking. My concern was with the possibility of assigning a group of contacts as a participant in a booking - without breaking them up into individual participant records. note that 1 couple doesn't necessarily mean a group however 2 couples traveling together could be considered a group. I really cannot see a difference between the two. There is a booking - a unit that represents a business transaction, if you like. The purpose of this transaction is to book any number of participants for a specific tour. One person or one hundred are treated the same way.
skingjack2 Posted January 3, 2009 Author Posted January 3, 2009 I think what you call a group is actually a booking. My concern was with the possibility of assigning a group of contacts as a participant in a booking - without breaking them up into individual participant records. The group will definitely need to have individual participant records note that 1 couple doesn't necessarily mean a group however 2 couples traveling together could be considered a group. I really cannot see a difference between the two. There is a booking - a unit that represents a business transaction, if you like. The purpose of this transaction is to book any number of participants for a specific tour. One person or one hundred are treated the same way.
bcooney Posted January 3, 2009 Posted January 3, 2009 Two things: I suggest defining groups within Participants, not Contacts. Also, I'm not seeing what you mean by "role" of a participant in a Booking, Michael. Perhaps if I saw how you'd relate an Invoice to a Booking I'd understand.
comment Posted January 3, 2009 Posted January 3, 2009 I'm not seeing what you mean by "role" of a participant in a Booking A participant could be a client (i.e. the one who pays for the booking, either fully or in part), or a passenger, or a coordinator (the one to call when there is a change), or any other category that might be needed. IOW, role is the field that enables you to filter a portal (or sub-summarize a report*) so that a booking can be viewed as: --------------------- [big]Booking # 123[/big] Tour Details: ... Paid for by: • Adam Smith (75%) • Betty Jones (25%) Passengers: • Adam Smith • Cecily Smith • David Jones [special diet] • Eve Jones In case of emergency, call: • Betty Jones etc. --------------------- (*) For this, there must be a unique participant record for each Contact/Booking/Role combination. I suggest defining groups within Participants, not Contacts. I'm not sure I see why this would be needed. how you'd relate an Invoice to a Booking It's quite possible a booking IS the invoice - or very close to it. I think we are now entering a phase where workflow plays the dominant part and our usefulness as remote advisers is diminishing.
skingjack2 Posted January 4, 2009 Author Posted January 4, 2009 Hi, You were asking about the current workflow; it involves the following procedures: 1) determine if person exists in the contacts table. If not, a script creates a new record and then appropriate information is inputted into the the record. If record exists, skip to next step 2) create a new booking in the booking table (done via script). Pertinent trip information (type of booking, destination, airline, cruise line, travel companions ...just via std edit field, resort/hotel payments etc) is entered the booking table. 3) required forms (eg. airline reservation form) is generated via different layouts based on the info in the booking table 4) appropriate trip data (names, payments, description etc) is transferred manually to another filemaker solution for invoicing (unable to tie the invoice solution to main Db due to developer constraints at present. Yes, this method is not ideal & cumbersome but currently works. My wishlist of the main DB is to bring the invoicing capabilities internally (priority 1) as well as develop the group/tour recording capabilities (priority 2). Way down the road I'd like to extend this to include some additional CRM capabilities. FYI, I'm currently looking at a demo file ("InvoiceDemo") suggested to another member by Bcooney and seeing if I can reverse engineer and incorporate into the current DB. The question I have in regards to this demo file is what is the proper workflow with respect to my db from an invoicing standpoint: Do I enter the client info, create an invoice in the invoice table (which in turn transfers the info to booking table) or vis versa... letting the invoice table data be pulled from the booking table i.e. the booking table is the "line items" table so to speak. Hopefully the above info will be informative. Jack P.S.If you are starting to get a sense of my frustration, you would be correct (you don't earn income when you are not out selling =) However, please let me assure that your patience, time and efforts are very much appreciated.
comment Posted January 4, 2009 Posted January 4, 2009 You were asking about the current workflow Uhm... no, not really. I was just saying that we are at point where it's difficult to advise further without spending some time at your place of business and learning the ropes. There are so many details (what needs to be scripted, how often does billing occur, where do prices from, and so on) that only you can decide how to solve them to fit your workflow. A note about invoicing: I believe that a participant record whose role is "Client" has all the necessary information to produce an invoice: it has a single related record in Contacts as the person to bill, and it has a single related record in Bookings, which should provide all the necessary information about the sums billed. In a very basic solution one would only need to fill a DateInvoiced field to turn such record into an invoice. Or, to take it up a notch, one would click a button to create a related record in Invoices (with a one-to-one relationship to Participants), so that Invoices have their own sequential numbering. BTW, no matter how simple I make it sound, this is quite an ambitious project - so you better resolve yourself to spending the necessary time.
skingjack2 Posted January 7, 2009 Author Posted January 7, 2009 Comment & Bcooney, After some serious reading ( FM- missing manual) & work, I'm happy to report that I was able to implement the invoicing function with apparent success (more testing still required though). Once the testing is completed it's on to the travel companion/passenger implementation. Based on your combined feedback, this should hopefully come easy. Thanks to both of you for your input and feedback. Jack
Recommended Posts
This topic is 5798 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now