portal filtering of child records based on multi-join table
I'm developing a database with a built-in reference manager for academic sources. I have a three-way join table that connects the following tables: 'items', 'creators' and 'creator_types'. Each item can have more than one creator, and each creator can have produced more than one item. In addition, each creator can be of different types for different items; for instance, a creator may be the author of a chapter in an edited book, or may be the editor of a book, and many creators are both authors and editors for different items. (There are also different 'item_types' but that does not matter to my question below.) Depending on item_type, my data entry forms have portals for entering and displaying authors or editors or both.
My initial approach was to set up the relationships as in the image below. In the layouts I then have an author and/or editor portal. This works well for data entry, in that it fills in 'Join_Items_creators::creator_type_ID' based on the respective global fields, but of course the portals do not display the correct data; rather, both the author and editor portals (using the 'Creators_Authors' and 'Creators_Editors' TOs) display exactly the same data regardless of creator_types. I thought I could remedy this with portal filtering based on 'Join_Items_creators::creator_type_ID' but this has led to some strange behavior where all creators are shown in one portal but not the other despite the fact that the underlying 'Creators' table contains the correct data. I have not been able to figure out why.
This whole episode made me realize that I don't understand portals as well as I should, and I wonder whether there is a simpler way to achieve the functionality I'm looking for. I would appreciate any advice on how to use portals here for both data entry and display.