Some of the details of your current set up are not fully clear. You may need to adjust what I am about to suggest to get it to fit your actual setup:
It appears that you have many to many relationship. A given exhibit can have contributions by many artists. A given artist can exhibit their work in many exhibitions.
Start with these relationships:
ExhibitionDetail::__pkExhibtDetailIDID = ExhibitionDetail_Artist::_fkExhibtDetailIDID
Artists::__pkArtistIDID = ExhibitionDetail_Artist::_fkArtistIDID
You can place a portal to ExhibitionDetail_Artist on the ExhibitionDetail layout to list and select a Artists record for each given ExhibitionDetail record. Fields from Artists can be included in the Portal to show additional info about each selected Artists record and the _fkArtistIDID field can be set up with a value list for selecting Artists records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Ah that seems like exactly the kind of thing I want.
I have my Exhibitions Table (playing the role of your ExhibitionDetail) and a Contacts Table (Artists), so do I just need to create the ExhibitionDetail_Artist table and create the ids to link them up?
This seems very simple but as I have all of the contacts and exhibitions already inputted it seems hard to get my head around (I started using filemaker a few weeks ago so step by step child-like descriptions would be greatly appreciated!)
Thanks for your help
You'll need to add that "join" table and populate it with data in order to link an exhibition record to the artists that contributed to the exhibition.
Here's a demo file that matches up contacts to events (think artists to exhibitions) that may be helpful to look at: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7
Assuming that you are using FileMaker 12, open this file from the File menu to get a copy converted to the .fmp12 format.
Perfect, I've got it working! Thanks Phil. Now on to trickier things:
Is there a way I can have a portal where when one begins to enter the name of a contact or exhibition it autocompletes based on the database and fills out all the other info?
Eg. If I was viewing Exhibition 1 and wanted to add 'Jon' I could start typing J and it would give the option to autocomplete and fill out Jon's number, email and website.
Likewise, if I was looking at Jon's record, I could start typing 'Exhibi-' and Exhibition 1 would pop up.
Is this easy to implement?
Here are some demo files that demonstrate different auto-complete style methods for looking up data. All require scripting as well as relationships to implement. For files with .fp7, you can open them from FileMaker 12's File menu to get a copy converted to the .fmp12 format:
A "name look up" drop down list where auto-complete works but the ID value is looked up and used to link records instead of the name:
FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7
"Search portals" that update with each keystroke to only list matching related records with a button to click to select that item as well as two more auto-complete enabled drop downs: