4 Replies Latest reply on Jul 2, 2013 6:56 AM by dougfsm

    Adding a new record to a related table in a script

    dougfsm

      Title

      Adding a new record to a related table in a script

      Post

           Hi,  I want to be able to add a new record to a related table in a script without changing layouts (or at least without the user leaving the layout of the current table). Can this be done? 

           Thank you!

        • 1. Re: Adding a new record to a related table in a script
          philmodjunk

               Freeze Window
               Set Variable [$ID ; ParentTable::PrimaryKey]
               Go to Layout ["Child" (ChildTable) ]
               New record/Request
               Set FIeld [ChildTable::ForeignKey ; $ID ]
               Go To Layout [original layout]

               While the script will briefly change layouts, this will not be visible to the user.

               Be careful of script triggers set on either layout. Sometimes this script fails due to tripping such a trigger which then performs the triggered script to produce an uexpected result.

          • 2. Re: Adding a new record to a related table in a script
            jmw

                 Douglas, the method mentioned  bij PhilModJunk is a secure, clean and simple method. To add to that; It can be done without switching layouts but then there additional things to take care of:

                 In the relationship between the tables check the option 'allow creation of records in this table via this relationship' underneath the chid table. If there is no related record present for the current parent-table-record you can create one by simply setting a datafield of child table. This will create a new record and set the fields which are part of the relationship to the values of the parent table. While this is quick, there is of course a serious risk of overwriting existing data if a related record does exist, something you may have to test for in your script. 

                 Otherwise, if you have a portal on your parent-table-layout which has child-table-records , you will notice that setting the 'allow creation' option creates an empty last row in the portal. Entering data in this row now always creates a new record, setting the relationship fields to the parent's values. The script would be:

                  

                 Go to Object [ Object Name: "ChildTablePortal" ]

                 Go to Portal Row [ Select; Last ]

                 Set Field [ ChildTable::DataFeld; "New child data" ]

                  
                 Drawback of this method might be that users can now too create new records, which you might not want. 
                  
                 So while it is possible to create new records in a related table without changing layouts (I am guessing that ExecuteSql is yet another method), there are additional concerns which do not exist in the method mentioned by PhilModJunk (well, apart from the script trigger issue). 
                  
                 Cheers,
                 Jan Martin Wagenaar

                  

            • 3. Re: Adding a new record to a related table in a script
              philmodjunk

                   ExecuteSQL cannot be used to create new records. It only supports SELECT queries.

              • 4. Re: Adding a new record to a related table in a script
                dougfsm

                PhilModJunk and Jan, thank you both for your responses. Very helpful. PhilModJunk's method worked perfect for my needs but I also appreciate the extra insight given in your post Jan.

                Much appreciated!