What's your expectation: to add multiple Music records, one for each instrument the Artist plays? Or one Music Record with multiple entries for the Artist, one for each instrument he plays on that "Music"? (in that case: would Music be better described as Song or Album?)
As to the searching: if you are on layout based on Artist and you want to search for an Artist then the portal does not come into play, the search happens on Artist fields (portals display related fields).
If you are on a layout based on Music and want to select an Artist then there are number of different ways. The easiest is to use a value list showing all possible artists. That may become unwieldy if you have a lot of them.
Thanks for your reply. Since an artist is a person it's an unique record, but because there can be many artists a value list is not an option. That's why I thought to put the artists in their own table and (re) use them from there. That works ok but only when I add a new artist. I can't find a way to reuse an existing record. So I would appreciate it if you'd tell me about all those "different ways". By the way: it's about classical music, so for instance The violin concerto of Brahms is a specific musical work, played by a specific unique artist. But this artist can have played other pieces as well and in those cases I want to reuse his record.
You are right that Artist belong in their own table. And Music does too. I'm afraid I don't follow this statement:
when I add a new artist. I can't find a way to reuse an existing record.
Where are you when you want to do this? Can you post a screenshot of your layout and perhaps a screenshot of your relationship graph?
It is very likely that you are missing one table to join Artists and Music.
Sorry, I'm afraid I wasn't very clear. I work with the portal, but as you pointed out, this is only to display related fields. But I need a solution where I can use records from a table that is related.
So the names has changed. I start to create an ALBUM in which I can create all tracks in the CD table through the portal tool. But I am not able to add or select a Conducter or Soloist, nor can I add or select a Piece and not to mention a Composer.
There are tables for every unique piece of data and I think the relations are as they have to be. Please tell me where I am wrong.
Wim, What I understand is that he wants not to reuse an existing record, what he wants is to use data "from" another existing table into a portal where he has his music listed. Go from there!
I would not use that approach but since he has it, he has to link the related tables and checkmark the in the proper relation side to allow creation of records with this relation in order to add the artist name to the music table, and the proper set of the field data in the portal.
wimdecorte Please correct me if I am wrong.
From my understanding, You are using the Artist ID for establishing relationship between Artsit and Music.
Artist - Artist Id(Primary Key)
Music - Music Id(My assumption Primary Key)
Artist Id(Foreign Key)
In Portal tool,consider the main table as Artist, and select columns from Music table in Form View. It displays the list of music entries for each of the artist.
Please let me know for further queries.