Title

Relationship issues / auto feed info from one table to another

Post

Situation:
I have an archive of one artists works, each work is a record with fields like: Title, Date, Image, Dimensions etc. and one field is called Provenance. Provenance is a text field with information about which exhibitions the work was shown in in the past.
ie
Title: Flower
Provenance: Solo Show, MoMA Toronto, 2013
First light, Art Museum Denver, 2008
Gordian Knot, Fine art Gallery San Francisco, 1997
etc.

Problem:

Now i have another table in the same filemaker file called: Exhibitions
In Exhibitions are several fields, listing which works have been exhibited where & when. One entry would be like this

Show title: First Light
Venue: Art Museum Denver
Date: 2008

Works by artist X in the show: Flower
Rock
Diptych

I would like to establish a relationship between the two tables which would automatically feed the info from the Exhibitions table to the works archive table. So that the info: First Light, Art Museum Denver, 2008 would appear in the Provenance field of the records Flower, Rock and Diptych in the works archive.

I can link Show title, Venue and Date from the table Exhibitions to Provenance in the table Works archive, but i do not know how to tell filemakern which works are concerned.

in the Exhibitions table i have multiple fields called title 1, title 2, title 3, etc. for all the works that were shown in that particular exhibition.
In the works archive i have records for all these works. What i do not know is how to tell filemaker
If title 1, title 2, title 3 etc (in exhibitions) matches a title (in Works Archive) then fill in provenance in these records automatically with (Show title, date, venue).

Combining multiple items of data (Show name, date, location), for multiple shows all in one field does not look like the best design for storing this data. A related table of records with one record for each such show linked by an ID field (not a name) to a table of art works, that are then linked to a table of artists would seem a much better data model. And the fact that a work can be shown in multiple shows at multiple venues and each show can include multiple art works also complicates the needed relationship.

If all artworks have a single artist, your basic data model might look like this:

Thanks for posting this answer. I, too, am having a bit of portal trouble with a music database I'm trying to get started with. It currently has 1 artist, 1 album and 17 tracks. There are 4 tables (Artist, Album, Track_Show, and Tracks.) The portal, drawn in the Track_Show table, is not showing any tracks. I don't know if it's a primary key issue in each of the Tracks' records, or something else I may have missed. Please see the included Relationships. Any and all help greatly appreciated. Thanks in advance.

I've changed it, but still no data showing the portal. The tracks values in the _pkTracksID are all auto-entered with values corresponding to the track numbers. I am baffled. What else could I have missed?

If the relationships are correct but no related records appear, the next thing to check is whether the _fk fields are correctly receiving the needed values to match to the _pk field of the related parent record.

I did not list those details as it depends on how you design your database--particularly your layouts.

If you place a portal to Track_Show 2 on your Album layout and have enabled "allow creation of records via this relationship" for Track_Show 2 in the Album to Track_Show 2 relationship, you can then format _fkTracksID as a drop down list of values from _pkTracksID with TrackTitle set up in the value list definition as "field 2". That then enables you to select existing Tracks records to link them to your current Album record on your Album layout.