3 Replies Latest reply on Aug 9, 2013 12:00 PM by whardy7

    Create related record through script



      Create related record through script


           I have searched on this and found quite a bit, but cannot quite find my particular solution...

           I am converting a library from flat to relational. The two tables are Library (4000 records) and Authors (0 records). Later, I will make a join table for a many-to-many relationship. But, for now, I simply need to create one entry in the Authors table for each unique author in the Library. I script will do this in seconds, but I have spent hours trying to CREATE the script.

           Can someone help me get on the right page?

           Thanks, whardy7


        • 1. Re: Create related record through script

               I'd consider setting up the join table right off as a book can have multiple authors and an author can write multiple books. The same script can create both the author records and the join table records at the same time.

               What data do you have in the Library table for listing the author(s) for each book? That's a key detail as your script will need to take that data and create a new record in authors if such a record does not already exist. Here's simplified method that assumes that you have unique author names and that there is only one author per book. Odds are very good that your final version will need to be more sophisticated than this.

               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]
                   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

               Note that both set field steps create related records in the referenced tables. Duplicate author records will not be created as long as your author names are accurate in the Library table as these steps create a new record only if a matching related record does not already exist.

          • 2. Re: Create related record through script

                 Thanks so much! I will study this now.

                 So does the Set Field create the related record automatically?

                 Never mind - you said that - sorry! I think I'm getting it now.

            • 3. Re: Create related record through script

                   That is so cool! Thanks, Phil!