6 Replies Latest reply on Sep 5, 2015 5:32 AM by wimdecorte

    Script on Server unable to "Import Records"

    CatherineTyers

      Title

      Script on Server unable to "Import Records"

      Post

      I am trying to export records from FM to SQL using a scheduled script running on the FM server. My script works fine when run manually but fails with error 100 ( cannot find file ) when run by the scheduler at the “import records” stage.

      I have spent many hours researching this on this forum and the internet and there is a lot of conflicting information. To clarify my exact situation, I am using FM 13 and SQL Server 2008 R2.  I have a single FM file residing on the server containing the source table, the target ESS shadow table and the script.

      To ensure it wasn’t a problem with the external ESS table/DSN, I tried copying between native FM tables within the same FM file.  The Import script step still “cannot find file” when run under Schedule, so it doesn’t appear to be a problem with the external source table, more a problem of importing within a single FM file using the scheduler.

      FM help indicates ”Importing/exporting directly to and from another FM Pro file is not supported via a FM Server scheduled script”, however, this is all done on the same single FM file.  Does this imply we cannot import to and from the same FM Pro file either?

      I have tried all possible paths for describing the FM file within the Import Records script step, nothing works.

      Is there any other way of copying a set of records from one table to another within a single FM file on the Scheduler?  Copy records and paste doesn’t work either, not least because I only want a small subset of the source table fields.  I would prefer not to have to export to an intermediate file and reimport.  I would also prefer not to have to create a “robot” file to run the script from FM Pro on the server as we have a limited number of licences available.

      Can anyone please shed some light on this?

      Thanks in advance, Cathy

        • 1. Re: Script on Server unable to "Import Records"
          philmodjunk

          I'm not sure that you can get this to work as a server side script. You may need to use a "robot file" to schedule this as a script executed "client side". If it works at all, it would be by exporting to a supported file type and location and then importing from the exported file into the ESS shadow table.

          Quote from the following KnowledgeBase Article:

          http://help.filemaker.com/app/answers/detail/a_id/7035/kw/server%20import%20records

          Supporting import/export formats:
          Importing/exporting directly to and from another FileMaker Pro file is not supported via a FileMaker Server scheduled script. The supported import/export formats include:

                 
          • Comma- Separated Text format (csv or .txt)
          •      
          • FileMaker Pro HTML Table format (.htm) Export only
          •      
          • Merge format (.mer)
          •      
          • Microsoft Excel format (.xls or .xlsx)
          •      
          • Tab-Separated Text format (.tab or .txt)
          •      
          • XML format (.xml)
          •      
          • dBase files (.dbf)

          In addition, importing from an ODBC data source is supported.

          Odd as it seems, the FileMaker Pro File Format is not included in this list.

          • 2. Re: Script on Server unable to "Import Records"
            CatherineTyers

            Yes, that is the article I got the phrase "Importing/exporting directly to and from another FileMaker Pro file is not supported via a FileMaker Server scheduled script", but the "another" is a bit ambiguous and it seems very strange that it can't work with the same file it is reading the script from in the first place!

            It unfortunately looks like I'll have to try one of the alternative methods.

            Thanks for your help.

            • 3. Re: Script on Server unable to "Import Records"
              philmodjunk

              Yes, but you appear to have missed the fact that a FileMaker Pro file is not listed in that article as a supported file type and hence my post to point that out.

              • 4. Re: Script on Server unable to "Import Records"
                CatherineTyers

                Actually I hadn't missed that fact and would expect it not to be listed as a supported file type precisely if you cannot import to another FM file. This assumption is reinforced by the text in the article's Additional Notes :

                "In order to import/export to another FileMaker Pro file via a FileMaker Server scheduled script, the script would need to export the records from the FileMaker Pro file to the above listed supported path and then import the records from this file into the other FileMaker Pro file."

                But hey, maybe that's just my optimistic reading of it, I still think it is rather ambiguous.

                • 5. Re: Script on Server unable to "Import Records"
                  eric

                  New Feature Request: http://www.filemaker.com/company/contact/feature_request.html

                   

                  This is what I submitted today:

                  Importing/exporting directly to and from FileMaker Pro file via a FileMaker Server scheduled script

                   

                  "Importing/exporting directly to and from another FileMaker Pro file is not supported via a FileMaker Server scheduled script."

                  It's time to fix this.

                  Just about everyone who imports/exports directly to and from another FileMaker Pro file and has FileMaker Server wants to be able to automate this.

                  It just doesn't make sense that the one source we are most likely to import from is the one we can't use.

                   

                  • 6. Re: Script on Server unable to "Import Records"
                    wimdecorte

                    To be very clear, you can not import between two FM files or between tables of the same FM file, server-side.

                     

                    CatherineTyers wrote:

                     

                     

                    I am trying to export records from FM to SQL using a scheduled script running on the FM server. My script works fine when run manually but fails with error 100 ( cannot find file ) when run by the scheduler at the “import records” stage.

                     

                     

                    This is confusing: you want to push data from FM to SQL but you are using "import records"?

                     

                    The only way that you can push data from FM to SQL is by using the "Execute SQL" script step.  And that one is fully supported server-side, provided of course that you have the proper SQL odbc driver and a properly configured DSN set up on the server.