I'm new to Filemaker, busy building a music-database, and I have some questions. Hopefully you can help me.
There is a table with Artists, each with a ArtistID_pk, and here is a table with Titles, each with a ArtistID_fk. ArtistID_pk, and ArtistID_fk are related in the Relationship Graph.
I would like to construct this: each time a new record (with artist and title) is entered, I want FMPro to look up if the artist already exists in the Artist-tabel. If that's the case: the Artist doesn't need to be added to the Artists-table. The only thing necessary is that the (found) ArtistID_pk is copied into the ArtistID_fk-field in the Title-table, together with the new title.
If the new record consists an Artist that does NOT already exists in the Artist Table, it needs to be added to the table with a new ArtistIDpk.
How do I achieve the look-up and copying? By calculating a field and/or a script. Any suggestions are welcome
Thanks for your hulp and advice.