pc Error 100 only occurs when script runs on Server

[brian rich]

I'm working on a data exchange project where I am build a product list using selected data from two other product files. The new data exchange database has been built from scratch, the two other product files are in separate legacy databases which have existed for some years, are in regular use and run reliably doing what they do.  All the databases are hosted on Filemaker Server 11 Advanced

 

Building the product list involves selecting sub-sets of data in the product files and then importing a few fields into the new product list. A controlling script calls two other scripts each of which call in scripts in the remote files to create the required subset, then imports the subset into the new list  The record numbers are modest, less than 13,000 in total and overall the script takes two to three minutes to run.

 

Running the script using a Client machine works perfectly every time.  The problem occurs ONLY when the script runs on the server, when it fails to do the import from just ONE of the product files. The import from the other file is successful. 

 

After adding some error logging code, I've been able to determine that the error code is 100 (file missing) and that it occurs during an IMPORT RECORDS script step.  I'm running the script using the admin privilege set, and the server uses the same privilege set when it runs the script.

 

Could anyone suggest why the error 100 only occurs when the script is running on the server, and what ithe File Missing error might be referring to? 

 

Thanks

 

Brian

[Wim Decorte]

what's the file reference path for that particular import?  Compared to the other file paths for the other imports?

[brian rich]

Hi Wim

 

The file references for the import script steps are one line definitions:

 

file:PARTS LIST

 

file:Lxxxx UK Parts

 

The first reference is for the import step that fails with the error 100, the second reference imports without error.  All three databases are hosted on the same FMP Server.

 

The four fields defined for import from the PARTS LIST are all indexed text fields, which don't have any lookups, auto-enter calculations or validation routines defined for them.

 

Both the PARTS LIST and Lxxxx UK Parts files are related to other external databases on the same FMP Server.  Some of the relationships are established using one of the fields that is imported by each import.

 

Thanks

 

Brian

[Wim Decorte]

are all 3 files physically in the same folder on the FileMaker Server?  If they are not then that is the problem.  You will need to add a relative path in addition to the one you have.

[brian rich]

No, they are all in the same folder 'Databases'. 

 

An earlier version of the data exchange file (the one receiving the imported data) is in the Removed folder which is in the 'Databases' folder, but the last modification on this is several weeks ago so I don't think it is active.

 

I'm baffled - just ran the script ClientSide and it runs without a hitch.

 

I may try rewriting the script again in case it has become corrupted.

 

Thanks

 

Brian

[brian rich]

Update - 

 

Just found myself with the same problem again on our Filemaker 11 server (Windows), an import script that worked every time when run client-side fails when running server-side.  The error reported is error 100, file is missing.

 

The fundamental issue is that you do not appear to be able to export or import anything other than text based file formats when running a server-side script.  This is true even if the table you are importing from is in the same database, the error 100 still occurs. (What caught me out this time!)

 

The workaround for this is to export the data as a text file using CSV, TXT or MER formats;  FP7, Excel, HTML, DBF, XML formats do NOT appear to work - all generate the error 100.  (If you need to export/import repeating fields, this is supported if you use the MER format)

 

You can only use the servers document or temporary folders as a place to store the export files, but this is easily referenced using get(documentsPath) or get(temporaryPath) to build the file path.  There is no need to delete existing files of the same name, they are simply overwritten.

 

Edit:  One other issue to be aware of if you are not using US system formats is that FMP11 server does NOT respect regional settings on the server, so when you import your text file of data, dates in the non-US format dd/mm/yyyy will end up with the dd and mm fields transposed. You will need to post-process after the import to correct this.  If the script has to be available both client-side and server-side, then you'll need to apply the above post-processing conditionally as the transposition doesn't occur on the client.

 

Can anyone confirm if the situation has changed with FMP12 or FMP13 servers, or are we still limited to text file formats?

 

 

HTH

 

Brian