Hi,

 

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:

 

inventory_id

 

Inventory_id_2

 

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.

 

Thanks

 

If I understand you correctly, you have a One To Many Relationship setup between your inventory table occurances... To resolve your problem, you need a Many-To-Many Relationship setup. This will show the related inventory from any inventory. Create a join table between inventory tables to link your Inventory tables together.

Hi mer,

 

 

I've been hired to modify a database that was built to archive the work of a visual artist. 

Attach a copy of the file so we can see your structure. Here is a link to explain how to do this and to prepare the file. ATTACH FILE

 She has a number of works that are related to each other (studies for a piece, or variations on a work).

 

Very rarely are objects in the same entity set grouped by being related to each other. In the vast majority of cases, they are grouped by belonging to a common parent (what you call a "piece" or a "work"). This type of relationship is much easier to implement than what you seem to be trying to do.