6 Replies Latest reply on Sep 30, 2011 11:52 AM by wckwong

    In Script - choose source layout during Import Records

    wckwong

      Title

      In Script - choose source layout during Import Records

      Post

      Hello,

      I'm struggling in writing a script that would help me automate the importing of data between a master fp7 file and a few different child fp7 files with their own individual sets of data.  All of the slave contain the same set of fields as the master, and all files have mutliple layouts from related tables.

      I know that by using the import records function I can update the master database with the data from the child files, but I'd have to specify the source layout for each table.  I'd like to automate this and I wrote a script where I choose the source file-path to import from, and run the following script

      Import Records [no dialog; "$$filename";Add; windows ansi] then

      Go to Layout["Layout1"], then repeat for layouts 2, 3 and 4...

      Where $$filename is a previously defined path to the chosen source data (child) file.  However, by doing this script without dialog, only the data from the first layout in the source file is imported.

      Is it possible to specify the source file-layout for the import records script, so each layout from the child can be matched with the master?

      Any comments would be appreciated, thanks

       

      Will

        • 1. Re: In Script - choose source layout during Import Records
          philmodjunk

          You do not need to specify layouts in order for scripted Import Records steps to work like you have to with manual uses of Import Records.

          Inside the Import Records script step is an option to specify the table (actually the table occurrence) of the source file from which the records will be imported. This is what you need to select here when importing records. If the source file is closed when this step executes, you get all the records in the table, but FileMaker insists on asking you to enter the password even if a matching account name and password is defined in both tables. If the file is opened, this import will import the found set of records from the specified table occurrence. Thus it's often useful to perform a script in the source file that finds the records you want to import first, then your next step is to import the records.

          Since you are using a variable to refer to the file, it's usually best to include two file references like this:

          $$filename
          file:Filename.fp7

          The system will refer to the literal file when you are creating the script so this makes it possible to set up field mapping and other import options. The first reference, then supercedes it when the script executes to import from the filepath inside that variable.

          • 2. Re: In Script - choose source layout during Import Records
            wckwong

             Thank you, Phil, I'll try that.

            Is it possible that I can choose to open the source file first, then go to a specific layout, and the import records....all running form a script within my Master database?  Thanks again

            • 3. Re: In Script - choose source layout during Import Records
              philmodjunk

              Why would you "go to a specific layout" for this script? I'm not clear on the reason for doing that.

              You can, it's all a matter of using the script steps available to accomplish that.

              Open File can be used to open the source file.

              Perform script can be used to both open the source file and also to run a script in it. That's often done with such import scripts as a way to set up found sets in the source file so that they can then be imported into the target file.

              • 4. Re: In Script - choose source layout during Import Records
                wckwong

                 I thought I needed to specify the layout in the source file first because I'm importing from mutliple tables to mutliple tables in the Master database. 

                I was using a variable before to let me choose a filename and path to the source data file, but without opening it, when i import the data only the data from the first layout was imported to each and everyone of the mutliple layouts in the Master.

                So I thought that perhaps when I open the source data in a script I'd need to go to a certain layout in both the source and the Master, then import records

                • 5. Re: In Script - choose source layout during Import Records
                  philmodjunk

                  When you set up import records, you specify a table occurrence, defined in the source file, not a layout. this is part of the import records step. In that dialog this is called the "source table" but if you compare the list of names in this drop down to the table occurrence boxes found in Manage | database | Relationships in this file, you'll find that every "box" has its name listed in this drop down even if several of these occurrences refer to the same table. Each occurrence has its own found set and sort order so the occurrence you select here can affect what records are imported. If you import from a closed file, you should get all the records in the table. If the file is first opened, you get the found set of the occurrence specified, this is where a script is often performed in the source table to set up the needed found sets prior to import. This script will need to select a layout that uses the refers to the same table occurrence in layout setup | Show records from that you selected in the source table drop down.

                  If "table occurrence" is a new idea, you may want to check out this tutorial on the concept as this is key to understanding how Filemaker systems work: Tutorial: What are Table Occurrences?

                  • 6. Re: In Script - choose source layout during Import Records
                    wckwong

                     Phil, thank you again for your in-depth explanations