Displaying related records through a portal in a table occurrence
I've been hired to modify a database that was built to archive the work of a visual artist. She has a number of works that are related to each other (studies for a piece, or variations on a work). The primary metadata for her works are stored in a table called "inventory", for which the unique ID (and match field) is titled inventory_id. I need to figure out a way to relate various works in the "inventory" table to each other. In order to do so, I created a table occurrence of "inventory" called "inventory_2", and then set up a join table between "inventory" and inventory_2" called "related_records" with these two fields:
I then related "inventory" and "inventory_2" like this (with the ability to create and delete records from either table):
inventory::inventory_id = related_records::inventory_id
inventory_2::inventory_id = related_records::inventory_id_2
So far, so good - all of this works just fine. The problem that I'm running into is that when I display these related records in a portal, I am only able to see one side of this relationship. I realize that this is because I can only display one side of the related record. In other words, when I relate a work to another work, I can see that relationship in the portal from the record where I created it, but the relationship does not display in the portal from the page of the related record. (I have the portal set up to show related records from the "related_records" table. The portal contains the following fields: related_records::inventory_id_2, inventory_2::title, inventory_2::image). I can see all of this info from the record that I have added this info to, but when I look at the portal from a related record, the info isn't there. I realize that this has to do with how the portal is set up (alternately, when I look at the "related_records" layout, I'm able to see both records at once). I'm just having trouble figuring out how to set the portal up so that the relationship can be displayed in both records. Any suggestions/insights would be immensely helpful.