2 Replies Latest reply on Jul 25, 2015 9:48 AM by MauriceG

    Need help with script - 2nd attempt

    MauriceG

      Title

      Need help with script - 2nd attempt

      Post

      Good Day,

      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:

      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

      Thanks.