4 Replies Latest reply on Sep 12, 2011 6:17 PM by JoeKantoski

    Need help A Copy Script

    JoeKantoski

      Title

      Need help A Copy Script

      Post

      I have been at it for several days now and can't figure out the best way to do what I want to so I reaching out for a little help. I also know that this might be a little bit of a data duplication but it necessary becuase it is for medical forms and I need to duplicate data.

       

      What I am failing to accomplish is create a script that will copy  all of the records based on a fk and a pk out of one table and paste them into another table. Basically i set the main table up to have a patient number and this also creates a auto generated key because there could be more than one patient number.

      I want to copy all of a certain patient and paste in a second table.  I would prefer not to use actual copy and paste commands. I was not sure if needed a loop with the set fields or something along those lines. If someone could guide me in the right direction that would be great,

        • 1. Re: Need help A Copy Script
          philmodjunk

          A loop or Import Records are both options here for moving data from one table to another, but your duplication of records task does indeed sound like something that may be unecessary.

          What kind of information do these records represent?

          Why do they need to be copied to another table?

          • 2. Re: Need help A Copy Script
            JoeKantoski

            The basic reason they need to be copied is becuase the table they are coming from is a legal document and cannot be changed. They form the are going to is something that is used and altered on a regular basis. The table the records are in will have signatures and other information associated with it so they will never be allowed to be alter once entered after the signute is added.

            Unfortunately as much as I do not like it I have to follow certain guidelines and rules within the information to protect authenticity which means I must create seperate copies and records is a second place within the database to preserve the originals. When building the full record for the "patient" information must be able to be transferred and altered as time goes on. So that is why i am looking for something to copy it. I only want to do it one patient at a time so would an import or would that copy the whole table.  I only want certain records to copy at certain times.

            • 3. Re: Need help A Copy Script
              philmodjunk

              I'm not clear from your description whether this "to be copied" information is the info in the form that can't be changed or if it also can be edited once it is copied into the other table.

              Such a transfer, if it needs to be edited after transfer is a perfectly acceptable set up for a relational database. If this text cannot be changed on the form to which it is being transferred, then you may be able to simply display this data on the form, but  with it locked down to disallow editing in the fields that display this data.

              Assuming that you really do need to copy the data, here is an outline of two general approaches you can use:

              1) Perform a find in the source table to pull up the records to be copied into a found set. Under some circumstances, Go To Related records can be used to produce this found set.

              Then use Import records to import this data from the source table into the target table. Unlike doing this by hand, you can set up this step without first selecting the layout of the target table, though you will likely want to do this anyway in order to display the results of this import.

              2) Use a looping script to move the data one record at a time. You can define a relationship between a foreign key field in the target table and the primary key field in the source table, then define looked up value settings to copy the data from the matching fields in the source table into the target table. Your script finds all the records to be copied, then loops through them. Each loop of the script does this:

              Use set variable to copy the primary key of the current record in the source table to the variable
              Switches to a layout for the target table, creates a new record and uses set field to copy the value from the variable to the foreign key field in newly created record. Looked up value settings on fields in the target table that have corresponding fields in the source table then auto-enter the matching data for that record.

              Then the script returns to the source table layout and uses go to Record/request/page [next ; exit after last] to go to the next record and exit the loop once it has done so with the last record.

              • 4. Re: Need help A Copy Script
                JoeKantoski

                Thank you I went with your second suggestion. After some tweaking of the script it works perfectly. I wasn't able to use the set field, I used a variable for each field as it changed. It was a bit cumbersome but it worked for now. I will most likely go back and alter at a later dater but thanks for the help.