2 Replies Latest reply on Nov 23, 2011 10:33 AM by EdwardBatt

    Script for inserting field contents from one table to another

    EdwardBatt

      Title

      Script for inserting field contents from one table to another

      Post

      I have two tables with three fields in common. 

      Table one is Inspection Plan. Each inspection plan has it's own unique identifier (QIPID).

      Table two is Inspection Results

      The relationship is 1 - many from Inspection plan to Inspection results

      The duplicate fields are "item" and "description"

      I need to setup a script that I can trigger using a button on the inspection results form that will retrieve the entries for QStepID, item and description from the inspection plan, and insert them as new records into the related fields in inspection results. The filter used to select the which fields to retrieve will be QIPID. mentioned above. 

      I realise that this duplicates data within my database however, without going into length details, this is actually necessary. 

      I have tried as best I could to look through the reference manual etc. and online, but I cannot find an example to copy. I would really appreciate if anyone could help me out with a script for this. 

       

      Regards,

        • 1. Re: Script for inserting field contents from one table to another
          philmodjunk

          I realise that this duplicates data within my database however, without going into length details, this is actually necessary.

          Having discussed this design with you, I don't agree. I see no need to duplicate this data. Since you have a relationship in place, you can add fields from the QIP Steps table to your Inspection Results table and they will display the needed data without any need to store the same item and description data a second time. All you need is a looping script that loops through the QIP Steps table, to use their QStepID number to create matching records in Inspection Results for the new inspection.

          It would help to include a link to your previous thread when you start a new one like this. Then people interested in helping can refer back to it to get the full picture of your design set up. I am assuming from the reference to a QSTEPID field that you are attempting to implement the table structure I recommended in that thread, which involves more than the two tables you've mentioned here.

          To copy data, such as QStepID from one layout to another you can use a script similar to this:

          #This is a partial script, steps to capture the desired Report ID in $ReportID and Performing a find or using Go To Related Records
          #to pull up the needed found set of QIP Step records should precede these steps...

          #Perform this loop on QIP Steps layout after pulling up needed set of step records for new inspection
          Go To Record/request/page [first]
          Loop
             Set Variable [$ID ; QIPSteps::QStepID ]
             Go to Layout [Inspection Results]
             New Record/Request
             Set Field [Inspection Results::QStepID ; $ID ]
             Set Field [Inspection Results::ReportID ; $ReportID]
             Go To Layout [QIP Steps]
             Go To Record/Request/Page [next ; exit after last]
          End Loop

          • 2. Re: Script for inserting field contents from one table to another
            EdwardBatt

            Thanks Phil, having reread yesterdays and your post above, the lightbulb finally went off regarding QstepID. (I warned you I'm a little slow on the uptake). I actually reformatted my tables and relationships exactly as you proposed. When I typed the post I did not have the correct name handy. 

             

            Once again thanks for your help and support, if I have any more problems with this I will repost on this thread. 

             

            Cheers,