1 2 Previous Next 17 Replies Latest reply on Aug 23, 2013 1:47 PM by DonColacino

    Save a match field in import script step

    DonColacino

      Title

      Save a match field in import script step

      Post

           I'm having trouble writing a script to import records from a table in an external Filemaker 12 file.  I want to update matching records and add remaining records as new records.  I can save all of the intended settings but when I run the script allowing it to display a dialogue box, the match field is not selected.  It just appears with the single arrow to "import" the field, rather than the double arrow that indicates that records should be matched based on that field.

           When I open the script to edit it, the double arrow appears on the intended match field.  When I run the script the match field is not indicated.

           Can anyone tell me what I'm doing wrong?

        • 1. Re: Save a match field in import script step
          philmodjunk

               Does the data import successfully?

               Why have the script open this dialog at all?

          • 2. Re: Save a match field in import script step
            DonColacino

                 The data does not import successfully; it delivers an error message because it needs a matching field to be specified.  

                 The dialogue box is just the debugging tool that showed me that it was losing the matching field specification

            • 3. Re: Save a match field in import script step
              jameshoty

                   Once you resolve the error message condition by specifying a matching field, that setup is saved and the script to import will remember it. This is what I experience it. This applies to I porting from Importing from Excel too..

              • 4. Re: Save a match field in import script step
                DonColacino

                     Thanks for the suggestion.  I tried this, but the next time I ran the script it didn't retain the setting.  

                • 5. Re: Save a match field in import script step
                  jameshoty

                       I try to simulate your scenario and am able to import without the problem that you describe. Not sure if I understadn it correctly. (I could not find the button to attach the .fmp12 files in this forum). 

                  • 6. Re: Save a match field in import script step
                    DonColacino

                         James,  Thanks for trying to reproduce this.  I have uploaded an image that shows the field mapping as it appears in the script (left window), the field mapping as it appears in the dialog box when the script runs (right window), and the error message that is displayed when the script runs whether I allow it to display the dialogue box or not (bottom window).  I have circled the match field indicator in both field mapping windows.  Does this help?

                    • 7. Re: Save a match field in import script step
                      philmodjunk

                           In the screen shot on the left, there is no source table specified. In your script, have you set up a file reference by selecting the "Specify data source" check box that appears in the lower right corner of the script editor when you select that script step?

                           You need a valid file reference before the script can retain the field mapping settings.

                      • 8. Re: Save a match field in import script step
                        DonColacino

                             Yes.  The file reference is specified, and when I select the matching field in the dialogue box it works perfectly.  Of course, the objective is to have it automatically import from the file without user intervention.  

                             The purpose of this automatic import is to sync records between two copies of the database.  The file being imported is one of several tables exported from another copy of the database.  Am I missing some better way of accomplishing this?

                        • 9. Re: Save a match field in import script step
                          philmodjunk

                               What you describe should work perfectly and the FileMaker script should not be losing the reference to the source file.

                               Am I correct that the file from which you are importing data is always located in the same place with the same file name?

                               Since you are importing from a FIleMaker FIle, you also have the option of creating a Tutorial: What are Table Occurrences? in Manage | Database that uses an external datasource reference to directly access the data in this table. Even if you still need to import the data, this enables you to set up the import so that you refer to the table occurrence inisde your target file to access data from the source file.

                          • 10. Re: Save a match field in import script step
                            DonColacino

                                 Well, it's not the reference to the source file that is being lost.  It is only the match record designation that's being lost (see the red circles in the images of my earlier post, above), which prevents the import from updating matching records in the target database.  

                                 I'm not sure I understand your alternative solution.  When I have a table occurrence pointing to the external datasource of the other Filemaker files, how do I import between tables... copy fields, one at a time, from one table to the other?  

                                 The external files are only created when another copy of the database exports modified records.  If any of the external files are not present when the target database is opened, it would display an error message for each missing datasource.  

                            • 11. Re: Save a match field in import script step
                              philmodjunk
                                   

                                        Well, it's not the reference to the source file that is being lost.  It is only the match record designation that's being lost, which prevents the import from updating matching records in the target database.

                                   Your screen shot shows otherwise. THe left hand image displays a complete lack of any source table or source field references--not just the matching field designation. That's why they are all blank in that image.

                                   

                                        I'm not sure I understand your alternative solution

                                   You can use import records to copy data from one table to another even when the two tables are defined in the same file. When you select a "source table" for the import, you are actually specifying a table occurrence and thus you can specify a table occurrence that refers (via external data source reference) to a table from another file. This can, at times, simplify some details of a data import task if that external data source reference can be made to work for your database.

                                   

                                        The external files are only created when another copy of the database exports modified records.  If any of the external files are not present when the target database is opened, it would diplay an error message for each missing datasource.

                                   True, but that would also be the case when it came to import data directly from that file as well. In both cases naming the file differently or locating it in a different folder could create issues for your script. So could changes to the structure of your source table--if made after the script was defined.

                              • 12. Re: Save a match field in import script step
                                DonColacino

                                     OK, I see what's happening.  The reason my script setup import dialog box isn't showing any source fields was because I was using a variable for the filename.  It found the correct file when ran the script (as shown in the dialog box on the right), but it lost the match field designation.  If I use a hard-coded filename it doesn't lose the match field designation.  That looks like a bug to me.

                                     I don't have to use a variable for the filename if I attempt to import each of the 15 tables with an explicit "Import Records" script step.  I wanted to use a loop, but that didn't work because there was no way to map the fields of the 15 different tables in a loop.  As a result, I have to "brute force" it.

                                     The error messages for missing external files are manageable in the script because all I have to do is suppress error messages around the script step.

                                     Thanks!  Your comment about the import dialog helped me find the solution!

                                • 13. Re: Save a match field in import script step
                                  DonColacino

                                       But wait.... there's MORE!

                                       When I enter an explicit filename in the Import Records dialog box, Filemaker doesn't retain the "Matching Names" field mapping setting.  It reverts to "Last Order," which could cause problems, I think, if I ever perform an import using a different field order, partial field set, or different mapping.  Is this a bug or am I missing something?  

                                       All I want is an automated import from a group of tables in a known location, into matching tables of the target database, without the user having to iknow anything about the mechanics of the import.

                                        

                                  • 14. Re: Save a match field in import script step
                                    philmodjunk

                                         I've encountered this one before and I don't consider it a bug but that's my personal opinion.

                                         I have scripts where you use Insert File to insert a file "by reference" into a container fields and then the script extracts the file path from the container field and uses it for a import records operation. The user sees a dialog for selecting the source file and then the script does the rest once they have selected the file.

                                         When you use a $path variable, FileMaker does not have a valid reference to the file at the time you set up the field mapping because at that moment in time, the variable is empty. If you, instead, use a "hardwired" file reference, Filemaker can then open that file to set up and record the field mapping, but then you lose the flexibility you were after by using the $path variable.

                                         The solution is to enter BOTH a valid "hardwired" file reference and the $Path variable. List the $path variable first. Then, when setting up the import records step, FileMaker tries to use the $Path and when it doesn't work, it then moves on to the "hardwired" reference to open the file and let you map the fields. But when you execute the script, a value is assigned to the variable and the first reference is used to access the file and import the records.

                                         This will look like this in your specify file dialog:

                                         $Path
                                         Explicit reference goes here.

                                         For more on $Path variables, see this thread and the exploration file that you can download from it: Exploring the use of a $Path Variable in Scripts

                                    1 2 Previous Next