Relationships: how many is too many?
I have a bit of a problem with a database I'm working on. The easiest way to explain it is to upload my relationship graph. I'll start the description at the Trip table & TripMain layout. In terms of business rules, a Trip consists of a bunch of people going somewhere, and can contain many Flight Requests, Hotel Requests, and Car Requests. The Trip layout (based on table Trip) contains a portal to TripJoin. TripJoin is a join table which links Person records to Trip records, i.e. which people are going on a particular trip. Along with the 2 foreign key records in each join table record, I store a few attributes that are specific to that person for that trip - which cabin class they've requested, which passport they are using (some have two, you see), and a calculation which looks at which of their passports has been selected, and displays the correct passport number on the layout. Visas (UK, USA, Schengen) are done similarly but auto-guessed based on the destination country/continent for that trip.
The Trip layout contains a button/script which creates a flight request and auto-populates it with all the Trip participants (thanks Phil for teaching me that!). It does this by copying key fields into the FlightReqJoin table.
I have two table occurrences of Flight Request, because in terms of business rules, it is possible to create "standalone" flight requests which are independent of any Trip which is currently occurring. This is so that the client can generate ad-hoc flights not particularly related to (or worthy of creating) a Trip. So a Flight Request links to Persons using FlightReqJoin. And for Flight Requests that are tied to a trip, a 2nd TO called Flight Request 2 allows me to relate a flight request to a trip via the TripID(fk) field. Note that my FlightReqJoin table, unlike the TripJoin table, does *not*, at this time, have fields which store Passport In Use, Visa In Use, etc type information.
My problem is this - on layout Flight Request there is a portal, based on FlightReqJoin, which lists all the people (Person table records) on that Flight Request. I have added a field "Passport #" which is set to show data from the TripJoin::Passport_num_in_use field - which is a calculated field based on which drop-down option (passport 1 or passport 2) was selected on the Trip layout. On the Trip layout this works fine - the correct passport number is shown. On the Flight Request layout, no passport number is shown.
Looking at the relationship graph, I have to wonder - am I demanding too much, too many relationship "jumps" for FileMaker Pro? Or is there just no way for FMP to relate this information - essentially I'm asking it to grab the field Passport_selected field from TripJoin somehow. Looking at the rel graph I think this can only really be done via the 2nd TO of FlightRequest, yes?
So is there a way to achieve that? If I was to change my Flight Request layout to be based on my 2nd TO of Flight Request, it would fix this problem (I think) but would break the portal - it simply lists a mish-mash of people (some of whom are repeated many times), along with the date of birth and resident-permit number of the first person in the Person table. Presumably because FMP can't relate backwards from Flight Request 2 (TO2) all the way to FlightReqJoin without going via Trip, TripJoin, Person and then FlightReqJoin.
Should I just add the cabin_class and passport_selected fields to the FlightReqJoin table also (and update my auto-populate script on Trip, so that it copies that info across) ? Essentially the guts of what I want in terms of functionality, is this:
A. The user can create an ad-hoc flight and select the passport in use, for each passenger, manually.
B. The user can create a flight from a Trip record, in which case the passport in use is already selected, and that info would be copied over to the new Flight Request record.
Hope this makes sense... any ideas??