Anchor/Buoy question

[Daniel Shanahan]

I am working on a solution and am trying to create it using the Anchor/Buoy method. The solution is for a tour operator. Clients sign up for a tour over the internet. I have the following tables: Reservation (the "Anchor" or "Squid Head"), Pick Up Location, Van, and Tour. Most of the information is viewed from the Tour layout via a portal. This may also be helpful:

The match field between Reservation and Tour is a date field. My question is how do I relate the drivers? It seems like it could be a many-to-many relationship, so I could do something like Van to Date (join table) to Driver. But are many-to-many relationships part of a Anchor/Buoy or Squid method? I'm really not sure how to assign drivers.

Unfortunately, I do not yet have a way to create .zip files. I am happy to email this solution (approx. 130 k), if someone would like to view it.

Thanks.

dan

[Fitch]

To create a zip file in OS X, right-click or control-click on a file, then choose "create archive." (Archive in this case means a zip file.)

[Daniel Shanahan]

So Leopard comes out this October and I am still discovering Panther!

Thanks for the OS X lesson!

Attached is a copy of the file I refer to in the beginning of this thread.

dan

Platypus7.fp7.zip

[Fenton]

Is there only 1 van per tour? Or can you add more? Is there 1 driver per van?

[Daniel Shanahan]

At this point, there is only one van per tour. When a van goes out, it goes out for the entire day; there are no "half-day" tours where a van (and driver) have a morning tour and an afternoon tour.

I will mention that it was suggested that the Tour table include a Tour # field. This would be distinct from the Tour ID (the primary key). However, since there is only one van per tour I thought that meant a one-to-one relationship and I was not (and am not) clear on the need for a Tour # field.

Regarding the drivers, there is one driver per van per tour. That is, a driver does not "split" the tour, where one driver works in the morning and another driver relieves her/him in the afternoon for the same tour. However, a driver does not always operate the same vehicle. For example, one, Joe may drive van ##1 and the next day Martha might drive van #1, etc.

[Fenton]

If 1 Tour = 1 Van = 1 Driver, then Van ID and Driver ID are foreign keys in Tour, not Reservation. Just move them out of the portal (shrink the portal). The Van Capacity is just the Van::Capacity, not Sum(). Or it could be an auto-enter into Tour.

[Daniel Shanahan]

Thanks Fenton. However, I'm not sure I follow. Would I then have two new portals: a Van protal and a Driver portal? This does not work because if does not assign a particular party to a van. Multiple parties can take a tour on the same day and several parties can share a van. But each van does not necessarily follow the same itinerary.

I don't see how this would work without the van and driver in a portal because each tour can theoretically have an unlimited amount of people on the tour (the client is willing to rent vans to accomadate his customers). See zip file "Platypus7_2".

I created a relationship between the Driver T.O. and the Van T.O. (still trying to operate out of the Anchor/Buoy system [a system I do not fully understand]). This seems to work, except that it assigns the same driver on all of the records in the Tour layout. See zip file "Platypus7_1".

I do appreciate your suggestions. However, I am still lost. Any clarifications or other thoughts?

dan

Platypus7_2.fp7.zip

Platypus7_1.fp7.zip

[Fenton]

Yes, you do certainly need a join table. I created one, calling it TourVan (for lack of imagination). It would have a record for each unique combo of:

Tour id

Reservation id

Van id

Driver id

So a particular Van might have 2 or 3 entries per Tour, depending on # in party and capacity. I added a self-relationship to TourVan to count the current load of a van, and to show in red if overloaded.

Platypus7_2.fp7.zip

[Daniel Shanahan]

Thanks for working on this Fenton. I appreciate the time and effort you put into it. I like what you have done. I did notice that a driver is not restricted to a particular van on a particular day. In other words, two drivers can be assigned to the same van on the same day - something that the client does not want.

This project has been a bit of a challenge for me, to say the least. There is a hidden complexity in it, at least for me. I have handed the project off to another developer. However, I have learned several important things: First, Anchor/Buoy or Squid models still need join tables. I don't know why I thought they handled many-to-many relationships differently, but I did. I am new to this model and still not entirely clear on how to execute it. Second, I learned of the importance of understanding the solution before starting work in FMP. I will need to spend more time with ER diagrams. I did this for this project, but I kept going back to it and changing it. I'm sure modifications to diagrams are normal. I think I was doing something more than "modifying" - an indication that I was not (and am not) clear on the structure of the database.

[Fenton]

You can filter the Drivers to only those NOT already driving on a particular date. But it gets a little more complex. But I don't really hold with the idea of "1 tour per date" and "reservation link is date". Maybe that is your situation, but I would use the Tour ID in Reservation as the key and link. That's more "normal" (if there is such a thing).

[Daniel Shanahan]

Can you explain the filtered Drivers part?

[Daniel Shanahan]

I spent a fair amount of time searching through the FMForum and stumbled across the Dwindling value list thread. I downloaded a couple of example files and was able to make it work. Also, I watched Matt Petrowsky's video on ISO ("Auto-Reducing Value List" - I think Lee recommended this). While it is informative, it seems that the technique is much easier in FM 8.5 (maybe 7 & 8 too) than in FM6.

[Fenton]

Once again, it is not entirely necessary to filter the drivers unless there can be more than 1 Tour per date; because it's visually apparent; but it's a nice touch. And if you can have more than 1 Tour per date, it becomes more complex to filter Drivers. So here's my take on it. In which case Reservation must be based on an ID, not the date.

Yes, it's much easier to do this stuff in 8.5, because of the List() function. It would also be easier if you looked up the Tour Date into a field in the TourVan table. But then you'd have to Relookup or otherwise update it if you changed the date of a Tour. Well, you'd have to check the vans and driver assignments again anyway in that case.

Platypus7_2.fp7.zip