AnsweredAssumed Answered

how to automatically look-up parent-key in same table and copy into foreign-key in another table

Question asked by filemakerbro on Nov 19, 2017
Latest reply on Jan 3, 2019 by darcylydia



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.