3 Replies Latest reply on Aug 12, 2011 8:11 AM by KAmsinger

    Coping a Recored from 1 File to a 2nd File Through Scripting

    KAmsinger

      Title

      Coping a Recored from 1 File to a 2nd File Through Scripting

      Post

      Hi. I am on FMP Adv 11.

      I have a database that is on a shared network that is 2 separate files. The first file called "PERSON" contains all of my contacts records. The second file called "QuestionnaireNotes" I store documents for each contact and both are matched up through relationship tables and external data files.

      What I am trying to do is write a script in the PERSON file that when I am in a recored that is already created and has commited fields I click on a button it takes the NameFirst, NameLast, and _kp_Person (which is a unique serial number for that record) of the record I am currently in in the PERSON file and then goes to the other file QuestionnaireNotes  and creates a new record and pastes into the new record the same NameFirst, NameLast, and _kp_Person. This way I can then be in a person's record in the first file PERSON and click on a button that will take me to that person's documents stored in the second file QuestionnaireNotes. However, the script I have currently setup is not working properly.

      The current script that I have setup is not working because instead of doing what I describe above it is creating a new record in the PERSON file and duplicating the NameFirst, NameLast, and _kp_Person then taking that duplicate record and taking that info in the duplicate record and creating a new record in the QuestionnaireNotes and inserting in the duplicates records information. So when it's done I have 2 duplicate records in the PERSON File that has the NameFirst, NameLast the same names but the _kp_Person id is a new ID. Which is not what I want. I want the script to take the info of the record I am currently in and create a new record in the QuestionnaireNotes File and insert in the same NameFirst, NameLast, and _kp_Person ID. 

      Here is my current script that is in the PERSON file and is linked up to a button in the person's record:

      Go to Layout ["PERSON" (PERSON)]

      #

      Set Variable [$Person; Value:PERSON::_kp_Person]

      Set Variable [$PersonInfo1; Value:PERSON::NameFirst]

      Set Variable [$PersonInfo2; Value:PERSON::NameLast]

      #

      Open File ["PhotoCatalog"] //This is the actual file name that has a table in it called QuestionnaireNotes

      #

      New Record/Request

      Set Field [QuestionnaireNotes::_kp_Person; $Person]

      Set Field [QuestionnaireNotes::NameFirst; $PersonInfo1]

      Set Field [QuestionnaireNotes::NameLast; $PersonInfo2]

      Commit Record/Requests[]#

      Go to Related Record [Show only related records;From table: "QuestionnaireNotes";External; Using Layout "QuestionnaireNotes" (QuestionnaireNotes)]

      (end of script)

      Then for my relationship tables I have in both the PERSON file and the QuestionnaireNotes File I have the same relationship setup and checked the box in each relationship to allow a new recored created via this relationship in the QuestionnaireNotes table.

      PERSON = QuestionnaireNotes

      _kp_Person = _kp_Person

      NameFirst = NameFirst

      NameLast = NameLast

        • 1. Re: Coping a Recored from 1 File to a 2nd File Through Scripting
          philmodjunk

          Variables in one file are not accessible from another. That's why your script fails.

          If you have a table occurrence of the table in "PhotoCatalog" in your current file, You can replace the Open File step with a go to layout step that switches to a layout based on this table occurrence. (You can create table occurrences of tables in external files in Manage | Database | Relationships by clicking the bottom left button, then selecting the file and table from the data source drop down. You'll then need to creat a layout for this new occurrence.)

          If you have a relationship between the two files based on

          Person::_kp_Person = QuestionnairNotes::_fk_Person (don't link by name here)

          You do not need to copy the name from one table to the other, just the primary key.

          PS. I used _fk_Person in the second table as this value should not be a primary key in this table. It's a "foreign key". Wink

          • 2. Re: Coping a Recored from 1 File to a 2nd File Through Scripting
            KAmsinger

            Okay, well then if you say variables from 1 file are not accessible in another file, then how come the script I have created is able to take the 3 fields in the PERSON file in the record I am in and create a new record in a separate file PhotoCatelog with the same info? The only thing that is incorrect with my script is it is not taking the info from the record I am in on the PERSON file to create the new record in the PhotoCatelog file, its duplicating the same record in the PERSON file and then taking that info from the duplicate record and making the new record in the PhotoCatelog file...

            I feel like I am just missing a step or have a step setup incorrectly... Maybe I am not describing my issue clearly or incorrectly because I feel like this should work since it's doing exactly what I want, but just not in the way I want it too, since its duplicating the same record in the same file then taking that info to create the new record in the PhotoCatelog file... 

            • 3. Re: Coping a Recored from 1 File to a 2nd File Through Scripting
              KAmsinger

              Never mind. I actually figured it out and got it to work. I just replaced "Create New Record" with Copy Record and it works perfectly! Thx for your help!