AnsweredAssumed Answered

Using script to create records in join table

Question asked by MauriceG on Jul 28, 2015
Latest reply on Jul 28, 2015 by MauriceG

Title

Using script to create records in join table

Post

Good Day,

I am sorry I have to post my question again. I posted it last Saturday July 25, got an initial question from PhilModJunk, answered his question, and haven't heard anything since then. And now it looks like my initial post is no longer on the forum. Wonder what's happened. 

In any case, I'm converting my library DB from flat to relational. My flat version contains only a Books table (which includes a field called Authors in which the authors are listed one below the other separated by a carriage return). I have now created an Authors table with the names of all the authors and their ID. I have also now created a join table called Books_Authors. I found on this forum a thread from August 2013 (http://forums.filemaker.com/posts/c549dca215) where, in the same context, PhilModJunk provided a script to be used to create the appropriate records in the join table but said the script was appropriate only for books that had only one author. It did indeed create all the records in my join table for my books that have only one author but all books with more than one author were left out.

Can anyone help me make the changes that are required to that script so that it creates the join table records also for books that have more than one author? Here is an excerpt from the 2013 thread:

Define the following relationships:

Authors 2-----<Library---<LIbrary_Author>-----Authors

Library::AuthorName = Author 2::AuthorName
Library::__pkBookID = Library_Author::_fkBookID
Authors::__pkAuthorID = Library_Author::_fkAuthorID

Authors and Authors 2 are two Tutorial: What are Table Occurrences? with the same data source table. Enable "Allow Creation of Records via this Relationship" for Authors 2 in the Library to Authors 2 relationship. Enable the same option for Library_Author in the Library to Library_Author relationship.

For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

Go to Layout ["LIbrary" (Library) ]
Show All Records
Go to Record/Request/page [first]
Loop
    Set Field [ Authors 2::AuthorName ; Libary::AuthorName ] ---> this step only works if "allow creation" is correctly enabled.
    Set Field [ Library_Author::_fkAuthorID ; value: Authors 2::__pkAuthorID] ---also requires "allow creation" to work
    Go to Record/Request/page [Next ; Exit after last]
End Loop

Outcomes