Relationship issues / auto feed info from one table to another
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.
Provenance: Solo Show, MoMA Toronto, 2013
First light, Art Museum Denver, 2008
Gordian Knot, Fine art Gallery San Francisco, 1997
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
Works by artist X in the show: Flower
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).
I hope this makes sense and somebody can help!
all the best Kim