AnsweredAssumed Answered

Combining Information from Three Distantly-Related Tables in One Layout

Question asked by jgetman on Jul 26, 2016
Latest reply on Jul 27, 2016 by philmodjunk

Hello, all.  I have a situation where I have three tables in my database that hold People (PeopleProgram, PeopleEpisode, and PerformersPeople. These need to remain separate tables so that they only relate to specific other tables (in this case Program, Episode, and RecordingSession). 


(If I made them one table, then everyone who is listed in PerformersPeople and PeopleEpisode would show up in one list along with PeopleProgram in, say, a layout for Program, and that is not what I'm looking for.)


In any case, now I need to make a layout in which users can search through all People (from PeopleProgram, PeopleEpisode, and PerformersPeople) in a way that will bring up a list of all People and create connections to their related record in the correct layout (see attachments). The problem, as you might imagine, is that making a layout to do this is problematic because the layout can only be attached to one master table. It's a flaw in my database app that I haven't been able to figure out, either at tables/relationships level or at the user interface level.


I've attached some images that might help--the relationships diagram and all six of the current user layouts. If, for instance, someone searches for Alexander Courage on this fictional "People" page, the page should come up as having records related to all three groups of people, and I want users to be able to follow links to the related records in Programs Browse and Detail, Episodes Browse and Detail, and Master Cue Browse and Detail. Can it be done?


I'd appreciate any thoughts. If it takes reworking the relationships, I'm happy to hear it. If there's some way to make a more general "Google"-like search page, then cool. If it takes scripting...I'm willing to learn. But really, anything that can make this work so that users can search through people on one page and then choose which related records they want to see on which layouts--that will make me happy.


Thanks! ~J