3 Replies Latest reply on Oct 25, 2012 7:16 AM by philmodjunk

    FMP - Script - import FM earlier to FM current - multiple Tables

    SeanO

      Title

      FMP - Script - import FM earlier to FM current - multiple Tables

      Post

           I am setting up a script to import data from one FM file into multiple tables within the current FM file from an earlier backup. The imported file is in Filemaker native format.

           The area I need assistance with is to assign a variable to a user selected file. The backups are named "Monday" "Tuesday" "Wednesday" etc. The scipt will not know the name of the source data file it is importing. The user needs to select this file via dialog.

           The restore process is currently working as intended - however it is requesting the name of the source file with every table it is updating. Needless to say this is unecessary and could lead to user error in repeatedly selecting the source file.

           Below is a portion of the script to give a better indication of how the script is performing.

           Two specific things I am looking for - in order of importance.

           1: How to set a variable to allow the user to select the source file once, up-front to automate the rest of the import script.
           2: Is there a better way of doing this than I currently am.

           Many thanks in advance for your input and expertise.

           Sample of some of the data below:

           #--------------------------------------Makes sure the user wants to do this process...
           Show Custom Dialog [ Title: "Purge and Restore ALL Records"; Message: "You are about to Purge and Restore ALL Records.¶¶Press OK to Continue or Cancel to EXIT this ruting"; Default
           Button: “OK”, Commit: “Yes” ; Button 2: “Cancel” , Commit: “Yes” ]
           If [ Get (LastMessageChoice) = 2 ]
           Exit Script [ ]
           End If
           #
           #--------------------------------------Begin the Process ...
           Go to Layout [ “MstrLister” (MstrLister) ]
           Show All Records
           Delete All Records
           [ No dialog ]
           Import Records [ Target: “MstrLister” ; Method: Update existing ; Add remaining; Character Set: “Windows ANSI” ; Field Mapping: Source field 1 import to MstrLister::Lister ]
           [ No dialog ]
           Show All Records
           Go to Layout [ “MstrCompType” (MstrCompType) ]
           Show All Records
           Delete All Records
           [ No dialog ]
           Import Records [ Target: “MstrCompType” ; Method: Update existing ; Add remaining; Character Set: “Windows ANSI” ; Field Mapping: Source field 1 import to MstrCompType::MstrCompType ]
           [ No dialog ]
           Show All Records
           Go to Layout [ “MstrSuburb” (MstrSuburb) ]
           Show All Records
           Delete All Records
           [ No dialog ]

        • 1. Re: FMP - Script - import FM earlier to FM current - multiple Tables
          philmodjunk

               You have two choices for how you set your script up to determine the file name and path for the source file (The file from which you will be importing data):

               1)

               You can use insert File to insert a file using the "store a reference option". The user sees the standard open file dialog, they select the desired back up file and then the script inserts the file into a container field so that it can extract the filepath from the container field and put it in a variable that can then be used with each import records script step.

               2)

               You can present the user with a field with a value list of your backup file options (Monday, Tuesday, Wednesday, ....) The script can use this user selected value along with a predetermined location for the back up file to construct a file path and put it in a variable.

               With either method, your script would look like this:

               Set Variable [$Path ; // put expression here to compute filepath and file name. Must be fully qualified file path and name to the source file]
               Import Records [no dialog ; “$Path” ; Add ; Windows ANSI ]

               The key trick is in adding $Path without losing your field mapping that you have specified for each import records step. I set up the import records step with an explicit reference to a file. I then select "file" from the data source drop down a second time and type in $Path as the first row of text in the specify file dialog.

          • 2. Re: FMP - Script - import FM earlier to FM current - multiple Tables
            SeanO

                 Phil thank you for your prompt and insightful response. As usual your suggestions are spot on. I would not have thought of looking in the "Fields" section of script for a solution. Of the two solutions you proposed, I much prefer the first as it will allow for greater flexibility in selecting the file source - ie: from a flash drive etc

                 I am, however, struggling to implement it. I do struggle with variables a bit and think I'm missing the "container" field step. The "Insert File" line does not pop up a user dialogue box, leaving me unable to proceed. Im also struggling with which order to add the variable and which "Get" function to use to get the name of the user file.

                 Could you please assist with the correct syntax/order in the "Let the User Select the File" portion below? I have left the first iteration of actually importing the data below this step, to show the continuation of the script.

                 Just out of curiosity, is there a way to copy a script as it appears in the script editor to paste into notepad etc? Would have made displaying the steps below a lot easier.

                 Many thanks Phil.

                 #--------------------------------------Let the User Select the File ...
                 Insert File [ ]
                 [ Custom title: "Choose the Source File to Import" ]
                 [ Storage method: Reference ]
                 [ Display icon ]
                 [ Compression: Never compress ]
                 Set Variable [ $Path ]
                 # User Abort Step Below is ONLY for testing purposes and will be removed in final version
                 Allow User Abort [ On ]
                 Pause/Resume Script [ Indefinitely ]
                 #--------------------------------------End File Selection ...
                 #--------------------------------------Begin the Process ...
                 Go to Layout [ “MstrLister” (MstrLister) ]
                 Show All Records
                 Delete All Records
                 [ No dialog ]
                 Import Records [ Target: “MstrLister” ; Method: Update existing ; Add remaining; Character Set: “Windows ANSI” ; Field Mapping:
                 Source field 1 import to MstrLister::Lister ]
                 [ No dialog ]
                 #--------------------------------------End MstrLister Import ...
                 #--------------------------------------Begin MstrCompType Import ...

            • 3. Re: FMP - Script - import FM earlier to FM current - multiple Tables
              philmodjunk

                   You need to define a field of type container--you can specify global storage for it.

                   Then you need to place this container field on your layout and select it for your insert file step so that you see:

                   Insert File [Reference ; YourTable::YourContainerField]

                   All "insert" script steps require that the field into which data is being inserted be physically present on the current layout.