4 Replies Latest reply on Mar 1, 2014 8:47 PM by philmodjunk

    Add found set to join table

    ChadGist

      Title

      Add found set to join table

      Post

           Instead of adding one record at a time to a join table through a portal, I'd like to be able to add multiple records derived from a found set. I started working on a script to do so, and it successfully adds one selected record at a time. What do I need to add to have the script add all records in the found set?

           Thanks for the help!

      add_found_set.png

        • 1. Re: Add found set to join table
          philmodjunk

               You could put those steps inside a loop that loops through the records of your found set with Go to Record/request [next ; exit after last ] as the script step to go to the next record and also to exit the loop after reaching the last record But you'll need to set up the script to switch back and forth between two layouts, one based on the table where you have your found set and one based on the join table were you create new records and assign the values of your variables to the fields of the new record. And then your script has to switch back to the other layout before tying to go to the next record and loop...

          • 2. Re: Add found set to join table
            philmodjunk

                 Note that it's also possible to use Import Records to move such a block of data followed by a Replace Field contents action to assign the correct value to link it to the ID of the record from the "other table" of your many to many relationship.

            • 3. Re: Add found set to join table
              ChadGist

                   Thank you, after messing around with this for a while, I think the following script is working well and doing what I want. If another user already added a found set to the join table, do you have any ideas on how duplicate values could be prevented or deleted?

                    

              • 4. Re: Add found set to join table
                philmodjunk

                     Duplicate Joint table records are usually records where the foreign key fields (the match fields) to the two other tables are both the same. A quick validation field option is to define an added text field with an auto-entered calculation that combines the two values:

                     ID_SAM & " " & ID_SHOW

                     You can set a Unique Values validation on this field.

                     You can then use this in your script:

                     New Record/Request
                     Set Field
                     Set Field
                     Set Error Capture[on]
                     Commit Records
                     If [ Get ( LastError ) //duplicate record ]
                        Revert Record
                     End IF