10 Replies Latest reply on Dec 18, 2012 4:05 PM by DanielShanahan

    Importing Records via Scheduled Script

    JulianJohnson

      Hi There,

       

      I posted about this within the server section earlier so apologies if you have seen a similar issue twice today. I haven't had any feedback and have made some progress so I thought I'd post again.

       

      I have a server with several databases on, all stored within the Library/Filemaker Server/Data/Databases folder (they are in a sub-folder within here but don't neccessarily need to be if it helps).

       

      I have a scheduled script, which should import a found set of records into a table within my database (database A) from another database in the same folder (database B). Since I need only a subset set of records from the table, I have a a table occurrence based on database B in my relationships graph.

       

      The process is that the script goes to this table occurrence, performs a find to end up in a found set of the records I need to import, then it goes to the table I want to import the records to then does the import. This works perfectly if run from Filemaker Pro but when run as a scheduled script it fails with the error 100 (file is missing).

       

       

      Since posting this in the server forum I have messed around with a test file to eliminate anything odd in my database and got the same results. I then did some digging and it appears that I can only do a scheduled import from one of two locations on the server: the server's documents folder or the temporary folder. Is this right and, if so, does anyone have a solution that allows you to import records from a database held in the same directory on the same server (it could be my path that's wrong etc!)? If not, is there anything else I can do to get around this?

       

      The path I am attempting to use is:

       

      filemac:/Server HD/Library/FileMaker Server/Data/Databases/[subfolder]/[database]

       

      however, the path:

       

      filemac:/Server HD/Library/FileMaker Server/Data/Documents/[database]

       

      does work.

       

      It does see a bit odd as I would imagine that importing from the same or another database on the same server would be the most common reason to use the Import Records script step in a scheduled script. The only fix I can think of at the moment is to schedule an hourly backup of the databases into the documents folder but it seems a bit unneccesary.

       

      I hope this makes sense, I'm desperate to get it working as it's the very last stage of a project that is done and just needs this working to go live!

       

      Thanks in advance.

      Jules

        • 1. Re: Importing Records via Scheduled Script
          ch0c0halic

          Sorry, you cannot do it that way. FMS cannot import from one FMP file into another, FMP<->FMP import is not allowed.

           

          Export the found set to a Merge, CSV, or TSV file format and then import that file into the second table.

          • 2. Re: Importing Records via Scheduled Script
            JulianJohnson

            Hi There,

             

            That's very helpful thanks.

             

            I've been playing with the script and it now successfully exports the right found set into a .csv or tab file. However, the problem I now have is in scripting the import - if I manually select the field to import withint he script step I cna then see the columns within it and map those to my fields. If I open the script step and try to map the fields when the fiel is referenced by the path, it's not picking up the details of the file to import I can't then tell it how I want the fields mapped.

             

            I'm saving the file to the Library>Filemaker Server>Data>Documents folder and can see the file within the folder on the server.

             

            I need to do some more testing/tinkering on it but any ideas much appreciated.

             

            Thanks in advance.

            Jules

            • 3. Re: Importing Records via Scheduled Script
              DanielShanahan

              Jules,

               

              It took me awhile to understand this process as well.  I've compiled some notes for myself in a step-by-step fasion.  I am including those notes here in the hopes that they are helpful.  Of course, if you and anyone else sees an error, please let me know.

               

              ----------------------------------------------------------------------------------

               

              FileMaker Server 12 (FMS) schedule importing from another FileMaker 12 (FMP) file.

               

              1. FileMaker Server schedule cannot import from one FMP file to another.

               

              2. Export as .xlsx to maintain field labels. (I'm not positive on this but as I recall, the .csv export did not offer to import with the first row as field names.)

               

              3. Specify the import as .xlsx.

               

              4. Run the export from FMP client at least once.  Then run the import with a dialog.  This will enable the fields to be properly mapped.  Future iterations can be run without dialog and, in fact, must run that way for a schedule.  The reason for running the export from FMP client at least once is that Get ( DocumentsPath ) and Get ( TemporaryPath ) are different for FMP and FMS.

               

              5. FMS schedule can only export/import to/from the documents path and temporary path.  Use Get (DocumentsPath ) and Get ( TemporaryPath ).

               

              6. Documents path and temporary path for FMS is not the same as FMP.

               

              7. The temporary path is session based.  When using it with a FMS schedule, that session lasts only as long as the schedule.  Once the schedule is completed the temporary path directory and all files and sub-directories are deleted.  Therefore, there can only be one schedule, not two.  That is, there cannot be one schedule that exports from the source db and another schedule that imports into the receiving db.  When the second schedule runs to import, those files from the exporting db have been deleted with that temporary directory.

               

              8. FMS schedule opens a layout in memory.  If the layout has a trigger that has a script step that is incompatible with FMS it will cause an error and abort.  For example, onLayoutEnter calls a script that has the script step "Install Menu Set".  This step is incompatible with FMS, will cause an error and abort.  In such cases, create an FMS account in the Security settings (e.g. fmsadmin) and trap for the account name.  Use a condition to only run the script step if the account name is not the FMS.

              1 of 1 people found this helpful
              • 4. Re: Importing Records via Scheduled Script
                JulianJohnson

                Hi Daniel,

                 

                That's really helpful thanks. I've been with the client today and we've shceduled some tinkering time so I'll run through your advice and hopefully get it working. I'm failry positive now as I have had it working on one of the sets of records I need to move, it's the mapping on import that's the really tricky bit.

                 

                Thanks

                Jules

                • 5. Re: Importing Records via Scheduled Script
                  comment

                  DanielShanahan wrote:

                   

                  2. Export as .xlsx to maintain field labels. (I'm not positive on this but as I recall, the .csv export did not offer to import with the first row as field names.)

                   

                  IMHO, Excel is the worst choice for an interim format. If you want to include field names, use either the Merge format or (preferably, IMHO) export as XML.

                  • 6. Re: Importing Records via Scheduled Script
                    DanielShanahan

                    Thanks for the comment, Michael.  Can you say why you think Excel is the worst choice?

                    • 7. Re: Importing Records via Scheduled Script
                      comment

                      DanielShanahan wrote:

                       

                      Can you say why you think Excel is the worst choice?

                       

                      Because it's liable to actually modify data due to data type issues. Thus you may find your ZIP codes stripped of leading zeros, for example.

                      • 8. Re: Importing Records via Scheduled Script
                        mcrostie

                        Hi All

                         

                        Just to put my 2 cents in about XL, we have had issues between the mac osx and PC versions of XL, that have converted the dates by 4 years

                         

                        Regards

                         

                        Michael

                        • 9. Re: Importing Records via Scheduled Script
                          comment

                          I didn't even want to start on dates and times...

                          • 10. Re: Importing Records via Scheduled Script
                            DanielShanahan

                            Ah, right.  Good to know.  Thanks.