Need help with script - 2nd attempt
As I explained in an earlier post this week, I'm converting my library DB from flat to relational. My flat version contains only a Books table (which includes a field called Authors). 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. As mentioned in my earlier post, 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 authors of books that have only one author but the 2nd, 3rd, 4th etc. authors of books with more than one author were indeed 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:
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]
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]