Selecting dataset in layout from indirectly related table occurrence?
I have Events which are related to Clients and to Instructors. In some rare cases instructors act as a Team.
Instructors ---< TeamRoster >--- Teams ---< Events >--- Clients
Instructors::pkInstructorID = TeamRoster::pkInstructorID
Teams::pkTeamID = TeamRoster::fkTeamID
Teams::pkTeamID = Events::fkTeamID
Clients::pkClientID = Events::fkClientID
Events can be of different type. For each type there is a different layout based upon Events. In most Event layouts one client has to be assigned and one instructor (in the other ones one client and one team). I do the assignment by means of the field Events::fkTeamID which is bound to a drop-down list. Its value list is built by reference to Teams::pkTeamID. So far so good. However, as there is no directly related fkInstructorID in the Events table I am not able to assign an instructor using the logic described in the previous paragraph. I have to use Teams. My current workaround is to automatically create one team dataset for every instructor dataset with team size = 1. This makes things more complicated and I am looking for a better solution.
Am I missing something here? Is it really not possible to assign the only indirectly related Instructors to the Events?
Any help would be appreciated.