An Approach to FileMaker Server-Side Script Debugging

The ability to schedule the execution of FileMaker Scripts – that is scripts created in ScriptMaker/Manage Scripts using FileMaker Pro/FileMaker Pro Advanced client application – under FileMaker Server was one of the sleeper features when it was first introduced in FileMaker Server v9 in July, 2007. It didn’t even rate a mention in the original press release but it’s become a very important tool in my developer bag ever since. Some examples of how I’m using FileMaker Server Side scripts include:

– one client requires a .xml file to be downloaded each day from a URL and imported into a file that then conditionally updates and creates new records in related tables and logs the execution

– we use server side scripts with our fmSMS solution to enable our clients to have the sending of the SMS messages performed by FileMaker Server instead of the client application. For bulk operations (e.g. sending several hundred or more messages at the one time) or for scheduling messages to be sent in the future this enables a user to offload the heavy lifting to the server which can typically perform the scripts faster and without tying up the user’s computer waiting for the script to run

– another client send and receives emails from a FileMaker database. Sending bulk emails previously would take almost 8 hours for their mailing list and require a dedicated computer to handle this that couldn’t be used for the duration of the script.

– we update account balances overnight for another client so that all searches can be performed on stored – and therefore indexed – number fields instead of unstored calculation fields, which can result in a find operation taking seconds vs minutes for large data sets.

FileMaker Server Side Scripts – Before You Begin

There are several important caveats that you need to know before you dive head first into setting up your FileMaker Server Side scripts:

– not all Script steps are supported. Only “Server” compatible script steps are supported. You can view the list of server compatible script steps by changing the Show Comptability popup menu in the bottom left hand corner the script window when editing a script:

All script steps that are NOT server compatible will then be greyed out, leaving you with the list of compatible script steps:

If you’re hoping to offload the generation of PDF invoices which are then emailed to a contact then you’re out of luck as far as native FileMaker script steps are concerned. You can use the Send Email via STMP Server step but the Save Records As PDF step is not available – hopefully this will be supported in a future version of FileMaker Server.

– some Script steps are only compatible when certain options are selected. For example the Send Mail step is only compatible when used with the send via Server option (not client):

Any supported script step that has the option of presenting a dialog box to the user is only compatible when the dialog is not displayed – you’ll need to select the “Perform without dialog” option in this case. Examples of this include Sort Records, Commit Records and Delete Record.

– the Allow User Abort script step determines what happens when a server side script encounters an unsupported script step. From the FileMaker Server v11 Help:

If the Allow User Abort script step option is enabled (On), unsupported script steps will stop the script from continuing.
If the Allow User Abort script step option is disabled (Off), unsupported script steps are skipped over and the script continues to execute.
If this script step is not included, scripts are executed as if the feature is enabled, so unsupported script steps will stop scripts.

– A server-side FileMaker script that is running on one FileMaker server cannot open a database that is hosted on a different FileMaker server.

– Server-side FileMaker scripts run in separate sessions on the Database Server. Each session has its own copy of global fields and variables.

– FileMaker Server can also reference FileMaker plug-ins (see the FileMaker Server v11 Help for details on how to install and enable plug-ins under FileMaker Server)

– there is no script debugger!

Server Side Script Logging

The lack of a script debugger is the focus of the rest of this post. FileMaker Pro Advanced provides a number of tools to assist developers in debugging scripts, including the Script Debugger and the Data Viewer. Both of these tools make life easy for the FileMaker developer to see what is happening at each step of the script and the current values in fields and variables that the script is referencing. However when it comes to debugging scripts under FileMaker Server you’re essentially on your own and need to roll your own solution – remember that there is no interface on FileMaker Server to show you the progress of each script and set breakpoints or access the Data Viewer. The FileMaker Server Log Viewer shows some information AFTER the script has executed which can help but it is very limited. The list of Schedules in the Admin Console also shows when the schedule was last completed and when it’s due to run next.

Some of my server-side scripts have hundreds of steps – when things go wrong or you don’t get the result you expected (or nothing appears to happen) you’re not sure where to start. I needed a solution that was as dynamic as possible and would involve the minimal amount of additional schema changes and let me insert the equivalent of breakpoints at any point in the script to capture information about the current state. My solution to this was to create a built-in log table that I can populate as I go without having to change contexts (i.e. no switching to a new layout based on the log table occurrence to create records). This can be done quite easily using the Magic Key/Pivot technique but I wanted a solution that didn’t require any new relationships and required the least amount of additional script steps so I turned to FileMaker’s internal SQL feature that is currently only exposed through the use of an external plug-in.

This approach does require a plug-in – there are several free and paid ones to choose from – but for me it’s the neatest way to adding logging features to a server side script. Once you’ve installed and enabled the plug-in under FileMaker Server you can easily add some logging features to your database to help you debug a server-side script process. Here’s my approach to server-side script debugging/logging:

– in the database that contains the script I copy/paste in a new table: ServerLog. This table has a handful of fields and FileMaker Pro will then create a single table occurrence and layout for you automatically (you can make the layout visible only in Layout mode). Do NOT rename or delete the table occurrence!

– install and enable the plug-in that can perform the internal SQL operations for you: see the list of plug-ins at the end of this article that I’ve used recently. I currently use the free BaseElements Plugin(thanks Goya!) and my example file uses the BaseElements plug-in to demonstrate my approach. Instructions for installing and enabling plug-ins under FileMaker Server can be found in the FileMaker Server v11 Help under “Managing plug-ins”.

– if your database has an OnFirstWindowOpen/startup script or a OnLastWindowClose/close script (as set under the File Options for the file) you might need to modify these depending on your requirements. These scripts will be performed by the server-side script and depending on their compatibility with server side steps and the use of the Allow User Abort script step that may create unexpected results. I typically bypass the startup and close scripts when performed under FileMaker Server by adding the following steps to the top of the script:

If [ PatternCount ( Get (ApplicationVersion); "server") ]
    Exit Script [  ]
Else

... continue with script as normal

– I set a local variable at the top of the actual server-side script that I wish to debug to allow me to enable or disable the logging on the fly:

Set Variable [ $EnableLoggingSQL; Value:1 ]

– as I’m using a plug-in I need to test that the plug-in is installed (and possibly registered and also the correct minimum version if required)

– I first delete all previous entries in my ServerLog table as I’m only interested in the log files for the current script execution (you can bypass this if you wish to keep a history). Here’s where the power of FileMaker’s internal SQL engine kicks in – I don’t need to change layouts, show all records, then delete them and return to the orignal layout. I can simply make one function call as the calculation value for a Set Variable script step (N.B. all examples in this post are using the BaseElements plug-in):

Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("DELETE FROM ServerLog") ) ]

The function call and syntax will depend on the plug-in you use but the SQL statement will generally be similar. Here the use of “DELETE FROM ServerLog” deletes all records in the ServerLog table. Note that I’m wrapping the plug-in call within the Case function which checks whether logging has been enabled (the $serverLogSQL variable is set to either 1 or 0 at the top of the script) – if logging is enabled the plug-in function will be evaluated, otherwise it will be ignored. Be careful when using the SQL Delete command as, unlike FileMaker Pro, you WON’T get a dialog box confirming that you wish to delete the records.

– Adding a single Set Variable script step is usually the maximum that you need to add if you wish to capture and log information about the current state (e.g. by calling one of FileMaker’s functions) or by checking the result of the last script step or retrieving the current value of a variable or field. For example the following Set Variable steps will return information using FileMaker Get Functions:

Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("INSERT INTO ServerLog(Description) VALUES('Logging Commenced at " &  Get ( CurrentTimeStamp ) & "'" & ")"))]

Set Variable [ $serverLogSQL; Case ( $EnableLoggingSQL ; BE_FileMakerSQL ("INSERT INTO ServerLog(Description) VALUES('Get (AccountName) result " &  Get ( AccountName ) & "'" & ")") )]

– you don’t always necessarily need to add a Set Variable step to create a log entry: anywhere you can access FileMaker’s calculation dialog you can create a log entry. For example you can add a log entry when the Exit Script step is performed as long as you specify a calculation result. I usually decide where in my script I’m going to start debugging and add one or more Set Variable steps at the appropriate places then perform the script manually using the FileMaker Server Admin Console:

– when setting up the new Schedule for the server side script in the FileMaker Sever Admin Console you’ll need to specify the Account Name and Password to use when performing the script. Make sure the Account Name and Password has sufficient privileges to perform the required steps. I generally create a new Account for server side scripts with the appropriate privileges. It’s also worth nothing that Get(AccountName) returns the account name that the script was run under and Get(UserName) returns the schedule name.

When it comes to setting up the frequency for the Schedule that you create in the FileMaker Server Admin Console you might be surprised that there’s no option that lets you create a script that runs every x minutes all the time. The limitation is that the start time and end time of a schedule must fall within the same day – there’s no option to tell it to run every day every 5 minutes for example. You can work around this by creating a schedule that starts say at 12:01AM and finishes at 11:59pm if you require a script that runs constantly every 5 minutes.

Whilst debugging I strongly recommend enabling the email notification to get as much information as possible about the execution of the server side script. You can also use the Log Viewer in the FileMaker Server Admin Console to view relevant log entries – these are often helpful as they pinpoint the script step that caused an error. It’s also worth noting that not all errors are actual errors. For example if you’re script involves a Find operation that results in no records found (error #401) this will be captured but might be completely expected. Also if your FileMaker script involves a Loop that uses the Go to Record/Request/Page [ Next ] step you’ll get an error when it gets to the last record (error #101 – once again this would be expected). You can use the following step immediately above the Go to Record/Request/Page [ Next ] step to bypass this:

Exit Loop If [ Get (FoundCount ) = Get (RecordNumber ) ]

The FileMaker Server Admin Console also allows you to specify some Script Options. You can select Abort schedule if time limit reached or server stopped in the Schedule assistant to abort the FileMaker script schedule if the script takes longer to run than the specified Time limit, or if the Database Server stops:

I’ve created an example file that you can download and pull apart (you’ll need to also download the BaseElements plugin (link below) and install and enable this on FileMaker Server): ServerLogging.fp7

Here’s a list of the plug-ins I’ve used with my server-side debugging approach:

BaseElements Plugin

SQL Runner

MMQuery