Server Side Imports and Exports

[Steven H. Blackwell]

This is a joint message from Wim Decorte and Steven H. Blackwell.

We have been seeing a number of reports that indicate there is considerable confusion about the new server side import and export capabilities of FileMaker® Server 10 and FileMaker® Server 10 Advanced. We want to assist developers by clarifying how this new feature is supposed to work.

First and foremost, there are only 2 folders that FileMaker Server can use to export to or import from:

1. the Documents Folder in the root level of FileMaker Server.

2. A temporary folder created (and deleted) for the instance of the script schedule

This apparent limitation addresses permissions issues in the privileges bubble in which FileMaker Server runs. The account that runs FileMaker Server has access to those folders but not necessarily others.

The import/export path cannot be dynamic when using the export or import feature on the server. The temp path gets generated for each script session so it will vary from schedule to schedule. On Windows that path looks something like this:

C:/WINDOWS/Temp/S43

The name of the subfolder is specific for the running script schedule and is deleted when the schedule ends.

If you want to use a location that persists, your only option here is to put the file in the FileMaker Server Documents Folder and import from there or export to there.

Here is an example of an export. Given a file with a table alias City with fields Country and City, and a layout named Test, the following construct will produce an exported file called testfile.txt when it is run server side. The file will be sorted by Country, then by City, will contain all records for the table, and will be a tab delimited text file. The script:

Go to Layout [“Test” (City)]

Show All Records

Sort Records [specified Sort order:  City::Country; ascending 

City::City; ascending}

[Restore; No Dialog]

Set Variable [$varpath; Value: “filewin:” & Get(DocumentsPath) & “testfile.txt”]

Export Records [File Name: “$varpath”; Character Set “Windows (ANSI); Field Order: City::Country

City::City]

[No dialog]

Note again that the Get(DocumentsPath) when run server-side resolves to the Documents Folder in the root level of FileMaker Server. FileMaker Server has permissions to read from and to write to that directory. It may well not have permissions to read from or to write to other directories on the server machine. Similarly the Get(TemporaryPath) when run server-side will resolve to the temporary subfolder created for the running script schedule.

Thus, developers wishing to use server side exports or imports must channel those actions through FileMaker Server’s Documents Folder or the Temporary folder. Also note that the script above requires no user action through the User Interface. The Go to Layout step is used to set the correct context based on the table alias from the Relationship Graph attached to the layout. The layout does not have to have any fields placed on it.

We hope this explanation clarifies what is happening with this feature and that it helps developers utilize this important new feature correctly.

Wim and Steven

This post has been promoted to an article

[mr_vodka]

I am still curious to see if this works with system DSNs for ESS. Technically ifs its system wide, then permissions should not be an issue. If indeed the importing can only be done from these two locations, then FMI missed the mark high with this one. Just my 2 cents.

Edited January 28, 2009 by Guest

[Steven H. Blackwell]

John, I don't know the answer for sure. I have had one report to the effect that it did not work with ESS. But more testing may be needed. Perhaps we can test further.

Also, remember that this is likely a first step in this area.

Steven

[bcooney]

Just to clarify, when you say, "The import/export path cannot be dynamic when using the export or import feature on the server," do you mean that you CAN use $var as the import path, but it should point to a file (whose name can change) in the Documents folder?

I plan to receive XML files via FTPeek and will download them to the Documents folder on the server. I wish to import from them daily, but the filename will change. That's a variable path by my definition. Is this not possible?

[mr_vodka]

Hi Steven.

I havent had time to test it properly as well. I will have to agree though that the ability to server side import was to me one of the biggest reasons to upgrade to FMS10. (I am speaking generally and not personally since I get it through VLA maintenance) It just feels short. ESS is a great feature and for FMS not to have the ability to import from it just seems a little silly to me.

Most of the features in FM10 are worth an upgrade IMHO, however, I dont think I can say the same for FMS10. The next best part besides server side import is the MUCH needed ability to *simplify the backup / server side scripts; which was most definately appreciated. :

[Baloo]

Importing from as MySQL ESS to a FMP table via FSA10's automated scripting is working like a champ for me in a sandbox environment (the only place we've deployed 10 so far).

However now that server 10 allows for drop-downs based on ESS field values the question becomes: What compelling reasons remain for running the imports and having to maintain duplicate data sets?

[mr_vodka]

This is good news if there are no issues.

As for why its needed, there are some things that are just FASTER as a straight SQL import into FileMaker rather than trying to use ESS directly. Due to the way FileMaker's ESS engine is designed, if there is a very large database (such as Peoplesoft commonly is) it load the entire data dictionary which in essence cripples the ability to even use the relationship graph. ESS is great in most cases, but in some its virtually useless.

[Wim Decorte]

Yes, you are correct: you can use a variable as the path & file name in the import/export setup.

But that path name should point to one of the two locations.

[Steven H. Blackwell]

Barbara:

In addition to what Wim said, take a look at the new Get(DocumentsPathListing) function.

Steven

[bcooney]

Thank you Wim and Steven. I saw that new function, Steven, but FTPeek has a similar GetFileList which I use to grab the files in the FTP Incoming folder into a $var.

[The Software Man]

So to get things right for importing on a MacOS X.

I have an xml exported file from FMP 6.0 that has been placed in the directory:

"Server HD:Library:FileMaker Server:Documents:MyFile.xml"

I then have a FMPS 10 script that only has one step:

Import File[No Dialog; MyFile.xml; Add; Mac Roman]

where I have chosen MyFile.xml from the Choose File Dialog

However, this fails when run from the scheduler on the server with an error (100)

You state that the file to import needs to be in the Documents folder in the root FileMaker Server directory.

Just to be sure I wasn't mistaken even tried the user's documents folder:

"Server HD:users:admin:Documents:MyFile.xml"

What the heck am I doing wrong.

[Steven H. Blackwell]

Do you need a style sheet to transform the file? The grammars may not match. Can you do this manually?

Steven

[bcooney]

I would say that you need to go to a layout based on a table occurrence of the table that you wish to import the records before your Import Records. If you show the Import Mapping dialog, are you seeing proper field mapping, and records? If not, then it's probably that you need a stylesheet.

[The Software Man]

Well, the script worked Manually, but I will forgo the xml travails. I will use .xls format. And to make ensure compatibility, I manually exported sample data from the same file that I want to import to.

I changed the script to:

go to layout["my big layout" (mydetails)]

set variable[$varPath; Value: "filemac:" & get(DocumentsPath) & "details.xls"]

import records[No Dialog; Source: $varPath; Worksheet ""; Add; Mac Roman]

I place a copy of the file in both directories:

"Server HD:Library:FileMaker Server:Documents"

and

"Server HD:Users:Admin:Documents"

I still get an error

scripting error (100) at "Details: Import Daily: Import Records"

I know that some of you have tried this on a Wintel server, anyone done it on a MacOS server?

And thanks again for the prompt responses.

Edited February 27, 2009 by Guest

[The Software Man]

I even tried to be tricky. To debug the problem I added an export records to the script. That way the import is accessing the same file.

go to layout["my big layout" (mydetails)]

set variable[$varPath; Value: "filemac:" & get(DocumentsPath) & "details.xls"]

export records[No Dialog; "$varPath"; unicode (UTF-16)]

import records[No Dialog; Source: $varPath; Worksheet ""; Add; Mac Roman]

Still get an error (100) on the import records.

Edited February 27, 2009 by Guest

[bcooney]

Can you set a field to $varPath, so that you can see what path the script is using?

/MacintoshHD/Library/FileMaker Server/Data/Documents in the Mac OS

is the directory that the file should be in. This is how Get (DocumentsPath) evaluates on FMS. How about using "file:" instead of "filemac:"? Or, set $varPath to "file:" & Get(DocumentsPath) & "[color:red]/details.xls"

omg-Could this be it?! You need a "/" before the file name.

So, "file:" & Get (DocumentsPath) & "/details.xls"

Edited February 28, 2009 by Guest

[The Software Man]

As suggested I set a field to fill with the $varPath.

It computed to

filemac:/Server HD/Library/FileMaker Server/Data/Documents/myfile.xls

However, no export file ever shows up there.

[bcooney]

OK, so the path is correct. No export file could mean no found set when exporting. So, make sure that you have one.

Edit: I just had to test this..and it worked just fine. What I noticed, though, is that on my iMac FMS10 server, I had to "refresh" the Documents directory before I saw the file although it was there by then. I also sent myself an email, and that worked too.

My script:

Go to Layout ["Table"]

Show All Records

Set Variable [$path; Value:"file:" & Get(DocumentsPath) & "/test.xls"]

Export Records [No dialog; "$path"; Macintosh]

As I was setting this up, I was asked which Account to use when running this script. So, check that the Account that you are using has Export privs.

What's in the log?

Another thought: There are two Documents folders;

/MacintoshHD/Library/FileMaker Server/Data/Documents

and

/MacintoshHD/Library/FileMaker Server/Documents

Edited February 28, 2009 by Guest

[Chris Robot]

This really helped. I was banging my head on the desk trying to figure this out.

Is it possible to update the original post with mention that there are two "Documents" folders in the FileMaker Server Directory (at least on MacOSX server.)

/MacintoshHD/Library/FileMaker Server/Data/Documents

and

/MacintoshHD/Library/FileMaker Server/Documents

In this case, you can write to .../Data/Documents and you can't write to .../Documents .

Thanks!

[Steven H. Blackwell]

Get(DocumentsPath) should return the correct directory. Are you saying that it did not?

Steven

[Slobey]

Hoping this string is still alive.

I am having the same problem. I am getting the export file to work fine. It exists in the documents folder but I can't get it to import.

I am using the same path to import that I used to export and I set a field with the variable and the path shows

"filewin:/C:/Program Files/FileMaker/FileMaker Server/Data/Documents/780 West.txt"

And that is where the file resides but it won't import.

What am I doing wrong?

Mike

[Steven H. Blackwell]

Don't know what's happening here. Anything from the logs?

Steven

[Wim Decorte]

if you trap for errors after the import script step, what error do you get?

[Slobey]

I am not sure what happened but when I ran it today to capture the error, it worked!!

Sorry for the trouble, not sure what happened.

Thanks

Mike

[Steven H. Blackwell]

Glad you are up and running.

Steven

[Anitram]

Hello

I have a problem with the export on the FM Server. On the client the script work's fine, but on the server, the file cannot be written. And I have no idea why.

Set Variable [$DocPath; Value:"filewin:" & Get(DocumentsPath) & "/test.csv"]

Export Records [No dialog; "$DocPath"; Windows (ANSI)]

Get(DocumentsPath) returns /C:/Program Files (x86)/FileMaker/FileMaker Server/Data/Documents/ and no error arises, errorcode is 0!

What's wrong?

Thanks

Martina

[bcooney]

Looks like you have an extra slash in the path.