What Are Your Imports Waiting For? FileMaker Perform Script on Server – Episode II

Introduction

I hope that since our previous blog post you have been exploring the possibilities created by FileMaker’s Perform Script on Server (PSoS). This article is a continuation of the PSoS discussion and includes more cool things you can do with it, specifically running database imports on FileMaker Server.

This spring I presented these techniques at four FileMaker Dev Meetups on the West Coast. As promised, I’m posting my techniques here, as well as uploading the example database files I have been using to do the import on server examples.

 

In this post we will see how to have FileMaker Server run imports and free up the FileMaker client to allow users to perform other tasks without waiting for the import to finish. It really is like having minions at your beck and call…exciting stuff. Plus as an added bonus Donovan (one of our talented Beezwax developers) has provided a little custom function that enables a spinner graphic to keep the user informed of the progress of your import.

Setting Up The Server

Let’s first step back for a moment and look at some of the challenges involved in performing an import on FileMaker Server.

By default, there are 2 places from which a server-side script can import a file: the Documents directory and the Temp directory.

Let’s say you have a externally-stored Container field in your file and you want to:

• Insert an Excel file into that Container
• Ask FileMaker Server to get the path to the file
• Have FileMaker Server hand the path over to an Import step so that the importing would then happen server-side.

The problem is that the external container file is neither in the Documents directory nor the Temp directory. One might think, “No problem, I’ll just use Export Field Contents and I’ll script it to put the file into one of those directories.” However, Export Field Contents is not a server-compatible script step.

At first glance we thought, “Damn. Not possible.” Then Simon (our FileMaker Server guru, and developer of bBox), presented the possibility of using a plug-in, creating a symlink to the file in the Documents directory and then handing over the path to the Import script. We worked on that for a while and managed to get it working, but it was too fragile and required too much work for a hopefully widely-deployed solution…not to mention that we also have the plug-in dependency.

Don’t get me wrong, I really like using server-side plug-ins, but in this case it’s not ideal.

FileMaker Server 13 Folder Paths

A week goes by and then…an epiphany! With FileMaker Server 13 you can now set the path for your externally-stored Container data. Yes, you heard that correct, and here is the first step you’ll need to set up to get this working.

First log into your server and navigate to – /Macintosh HD/Library/Data

Inside this directory create another directory. I called mine “Mini” (short for “Minions”, of course). This is going to be the alternate location where you’ll upload your database.

Because you just created this folder you’ll want to make sure it has the correct permissions

cd to the Data directory – at the prompt type cd and the path to that Data folder

 cd /Library/FileMaker Server/Data

Next, run “ls -l” to check the permissions on those items you might see that the folder you just created has your name associated with it and you instead want it to be “fmserver”.

At the prompt type:

 ls -l

You should then see something like the following:

---

 total 0
 drwxrwxr-x  10 fmserver  fmsadmin  340 Mar 16 23:00 Backups
 drwxrwxr-x   3 fmserver  fmsadmin  102 Dec 11 11:12 Caches
 drwxrwxr-x  21 fmserver  fmsadmin  714 Mar 11 08:34 Databases
 drwxrwxr-x   4 fmserver  fmsadmin  136 Mar 17 16:18 Documents
 drwxr-xr-x   2 vmenanno  fmsadmin   68 Mar 17 16:13 Mini
 drwx------   3 fmserver  fmsadmin  102 Dec 11 11:12 Preferences
 drwxrwxr-x   2 fmserver  fmsadmin   68 Dec 12  2012 Scripts
 drwxrwxr-x   3 fmserver  fmsadmin  102 Feb 15 22:45 Streaming

If you don’t know your way around the command line ask a friendly Unix sys admin about it, as you really don’t want to mess up this part.

Next, you’ll want to change ownership on items in there. You’ll be asked for the admin password and get a that warning to make sure you know what you are doing.

At the prompt type:

 sudo chown fmserver mini

WARNING: Improper use of the sudo command could lead to data loss or the deletion of important system files. Please double-check your typing when using sudo. Type “man sudo” for more information.

To proceed, enter your password, or type Ctrl-C to abort.

Once you are done type “ls -l” to confirm that your changes have been applied.

 macmini:Data vmenanno$ ls -l
 total 0
 drwxrwxr-x  10 fmserver  fmsadmin  340 Mar 16 23:00 Backups
 drwxrwxr-x   3 fmserver  fmsadmin  102 Dec 11 11:12 Caches
 drwxrwxr-x  21 fmserver  fmsadmin  714 Mar 11 08:34 Databases
 drwxrwxr-x   4 fmserver  fmsadmin  136 Mar 17 16:18 Documents
 drwxr-xr-x   2 fmserver  fmsadmin   68 Mar 17 16:13 Mini
 drwx------   3 fmserver  fmsadmin  102 Dec 11 11:12 Preferences
 drwxrwxr-x   2 fmserver  fmsadmin   68 Dec 12  2012 Scripts
 drwxrwxr-x   3 fmserver  fmsadmin  102 Feb 15 22:45 Streaming

Looks good. And in some ways that’s really the hard part! So, let’s move now to what you need to do on the server.

Log into the FileMaker Server Admin tool and navigate to the “Database Server” tab on the left and then click on “Folders.”

Note: If you are already using the Additional Database Folder feature then you may need to set it up as stated here because again you’ll need to have things in the right place for the import to work correctly. If these items are grayed out it mostly likely is because you currently have hosted files in that directory. If so, you’ll need to close them.

Check the checkbox to “Enable additional database folder 1” and enter the path to that new folder you created.Then check the next checkbox “Enable container data folder 1” and this time point it to the Documents folder as shown in the picture below:

At the bottom there is a way to validate your path and to save your changes when you are done. Now you are ready to upload your database into your alternate location and start taking advantage of the many script minions to do your tedious heavy lifting imports on your behalf.

Import Example – Files

I have created a file called “Import_Example” which you can tear apart and explore how this technique works. Before you upload it to your server, make sure you put a password on it. Currently the Full Access account name is “Admin” with no password.

Here is the file:

Import_Example.fmp12

Here is an Excel spreadsheet with 100,000 rows of data, which we’ll import into the database:

BabyNamesInArizona.xlsx

Now we’ll go ahead and upload the database file to the server.

Once you have selected Upload to FileMaker Server you’ll be presented with the following dialog.

IMPORTANT: To get this to work correctly you’ll have to upload to the alternate database folder location.

Testing The Import

Go ahead and open the hosted file now.

If you want to see the data as it starts to appear in the BABY_NAMES table, then open that layout in a new window.

Are you ready to experience a whole new level of User Interaction?

Now, go ahead and drag the “BabyNamesInArizona.xlsx” file into the “Drag file to be imported here” area. Then all you need to do is exit the field and the server will take over.

You’ll see that nice little progress spinner telling you that the server is busy doing something. Yeah, it sure is busy! It’s busy importing 101,767 records to be exact. The best part is that the FileMaker client on your computer is not running the import. It’s now running on the server.

If you were to close your laptop and go home that import would continue to complete on FileMaker Server. Isn’t that cool?! I really think so.

One of the things we have been wanting to do with our FileMaker Solutions is provide better user feedback in the UI. I think we now have some fantastic ways to do this. We’ll explore a few of them in additional blog posts. And I just might be covering these topics in the “Engaging Design, Snappy User Experience, and Creative User Interaction” session at FileMaker DevCon 2014.

I haven’t been this excited about what FileMaker has done since FileMaker 7. FileMaker 13’s Perform Script on Server opens up a slew of new opportunities for creating great user experiences that allow the user to not only be informed but be more productive, saving time & money.

Import Example Database – Under The Hood

This example file contains 4 tables:

• FILE
• RESULT
• SHADOW
• BABY_NAMES

The FILE table will hold a record for each import action initiated by the user.

The RESULT holds a log of things that happen in your script because once the script runs on the server there is no way to debug it.

The BABY_NAMES table is the table into which we import the excel data.

The SHADOW table has a one-to-one relationship between FILE and SHADOW.  More on this shortly.

Here is what the graph looks like.

Importing records into the SHADOW table instead of the FILE table allows us to continue editing the record from which the import was initiated in the FILE table without worrying about the record being locked. Because there is a one-to-one relationship between these two tables, the script only has access to update the SHADOW table without locking the FILE record. Setting it up this way requires a little overhead.

You need to make sure you create a SHADOW record every time you create a record in the FILE table. And turn on cascading delete so if you were to delete the FILE record it automatically deletes its twin in the SHADOW table without creating orphan records.

While the import is executing on the server you would be able continue editing the record you initiated the import on. When the script completes the import your record feedback in the top right corner will change from a progress spinner …

…to the status and the time it took to complete

The visibility of the spinner is set by using the following object visibility calculation:

    SHADOW::COMPLETED or IsEmpty ( FILE::DATA )

Easy stuff right? It is not described in this blog post much, but worth checking out is the custom function “WebSpinningIcon” that has the Base64 encoded animated spinner gif. I’ll invite Donovan to post more about this technique, but for now you get a glimpse of what is possible here.

Conclusion – Imports on FileMaker Server

If you have clients who import Excel files this is a great method for them to have a fluid user experience, allowing them to continue to use the database and be productive while the server does the heavy lifting. We now have great opportunities to deliver a fantastic user experience that saves time and can offer great productivity gains.

If you end up using PSoS in creative ways that you think are cool and different we would love to hear about it, so please comment below, or write to us. This is just the beginning, and I know the FileMaker community has many interesting and unique ways in which they are putting PSoS to good use. In my presentations so far, I noticed many light bulbs going off for developers realizing how they can improve their solutions. More blog posts to follow with regards to PSoS, so please stay tuned.

Ciao for now, Vincenzo

Vincenzo Menanno is director of FileMaker development at Beezwax.