A Beginner’s Guide to Working with Lists in FileMaker Pro

As you become more familiar with FileMaker Pro and make the leap from beginner to intermediate developer you will inevitably become accustomed to working with lists. By list I’m referring to a series of values separated by carriage returns such as

apple
banana
pear
mango

The list might be referencing a series of repeating fields, some related fields from another table, or it might be a series of values that you concatenate manually to achieve the desired result. You might use your list in a script as part of a loop that iterates through the list and performs actions for each value in the list, such as creating a new record or sending an email. You might be creating a list of delimited data to use in a chart or you might be generating a list to use as a match field in a relationship (see below for more details).

List Related Functions

FileMaker Pro has a number of native calculation functions to help you generate lists and extract values from a list:

List ( field { ; field…} ) – the list function was originally introduced with FileMaker Pro 8.5 (there hasn’t been a .5 release since) and as it’s name suggests its sole purpose is to generate a list of values.

The field reference can be any related field, repeating field, or set of non-repeating fields; an expression that returns a field, repeating field, or a set of non-repeating fields, or a variable. For example you might wish to generate a list of all the Invoice Numbers for a particular Contact record. Assuming you have a table occurrence named Invoices that is related to Contacts by a Contact ID match field the following calculation:

List ( Invoices::InvoiceNumber )

would return the list of Invoice Numbers for the current customer record. You can mix and match field references, variables and literal strings – for example:

List ( "INV1056" ; Invoices::InvoiceNumber ; $invoiceID )

It’s important to note that the List function ignores blank values – List ( “apple” ; “” ; “banana” )  would return:

apple
banana

ValueCount – this returns a count of the total number of values in specified list. For example ValueCount ( List ( “apple” ; “banana” ; “orange” ) ) returns 3.

LeftValues ( text; numberOfValues)  – this returns the first numberOfValues from the specified list. N.B. the resulting list of values returned always ends with a carriage return. For example LeftValues ( List ( “apple” ; “banana” ; “orange” ) ; 1 ) returns:

apple¶

RightValues ( text; numberOfValues ) – this returns the last numberOfValues from the specified list. N.B. the resulting list of values returned always ends with a carriage return. For example RightValues ( List ( “apple” ; “banana” ; “orange” ) ; 1 ) returns:

orange¶

MiddleValues( text ; startingValue ; numberOfValues)  – this returns the middle numberOfValues from the specified list, starting at startingValue. N.B. the resulting list of values returned always ends with a carriage return. For example MiddleValues ( List ( “apple” ; “banana” ; “orange” ) ; 2 ; 2 ) returns:

banana
orange¶

FilterValues ( textToFilter ; filterValues ) – this returns all the items in textToFilter that are also in filterValues, in the order they were originally entered in textToFilter. This allows you to compare two lists to find the values they both have in common. N.B. the resulting list of values returned always ends with a carriage return. For example FilterValues ( List ( “apple” ; “banana” ; “orange” ) ; “banana¶orange” ) returns:

banana
orange¶

GetValue ( listOfValues ; valueNumber )  – this allows you to retrieve a specified value from a list of values, typically used when you are looping through a list and performing some actions for each value in the list. N.B. the value returned does NOT end with a carriage return. For example GetValue ( List ( “apple” ; “banana” ; “orange” ) ; 2 )returns:

banana

Multi-Key fields

One of the more common uses for lists mentioned above was for use as a match field in a relationship. Most relationships typically use a single field/value on both sides of the relationship. For example the Company ID field in the Companies table (primary key) matches the Company ID field in the Users table (foreign key). FileMaker Pro also lets you enter mutliple values into the same field which has the effect of increasing the number of possible matching values on the other side of the relationship. You simply enter the values separated by carriage returns and FileMaker will look for matches for every line/value you have entered.

If you entered the following values into a text field (you would typically use a text field with global storage):

C1045
C2784
C3698
C1077

FileMaker Pro will then look for any matching records for each of these 4 values. Multi-key fields can be a very powerful feature of FileMaker Pro and are also often referred to as multi-line keys.

Generating a list of values from a field in a found set of records

One of my favourite new features of FileMaker Pro v13 was the new “List of” option when creating a Summary field. This option creates a return-delimited list of non-blank values in specified field for the current found set of records. One obvious purpose of this function is to create a list to use for a Multi-key field – you can’t use a Summary field itself as the match field in a relationship but you can use the result it generates in a global or calculation field.

I would also recommend reading the fileMakerhacks series on using Summary list fields in relationships.

Custom Functions and Plug-in Functions

There will be times where you need to manipulate lists and go beyond the native FileMaker functions – for example you might want to compare two lists and return all the values that are different in both lists, sort the list of values, or you might want to remove any duplicate values from a list. Here’s some of the custom functions and external plug-in functions that I use on a regular basis:

SortList ( ValueList ; Sort ; Type ) – this custom function allows you to sort a list with options for ascending or descending order

XORvalues ( ListA ; ListB ) – this custom function returns all lines that are different in two lists. If you only specify one list it will also eliminate duplicates from the list and return a single instance of each value

ZapValues ( ListA ; ListB ) – this custom function will compare two lists and remove the items in List B from List A (this is the inverse of the native FilterValues function)

Trim4 ( text ) – if you are using the native functions that also return a trailing carriage return you can use this custom function to remove this at the same time

AddRemoveListItem ( theList ; value ) – this custom function will add an item to a list if it does not already exist; otherwise the item is removed from the list

There will be times when you are not able to use a custom function due to the limitations of custom functions – recursive custom functions are limited to either 10,000 interactions or 50,000 iterations depending on the style of recursion they use (this article has more details on the limitations). When you hit these limitations you will need to use a plug-in function which are not bound by the same limitations as custom functions. Databuzz is a proud sponsor of the open source BaseElements plug-in which has a number of handy list related functions including:

BE_Values_Unique ( listOfValues {; caseSensitive } ) – this removes any duplicate values from the specified list

BE_Values_Sort  ( listOfValues ) – this sorts the list in alphanumeric order

BE_Values_FilterOut ( textToFilter ; filterValues {; caseSensitive } ) – similar to the ZapValues custom function, this will compare two lists and remove items from the textToFilter list that are found in the filterValues list
(N.B. all references are to FileMaker Pro v14 which was the current shipping version at the time this article was written)

Working with FileMaker Server and plug-ins

At Databuzz we use plug-ins in many solutions that we develop – the BaseElements Plug-in plug-in has become a standard feature of most solutions we deploy (if you also use the BaseElements plug-in you should sponsor the plug-in). One of my favourite features of FileMaker Server 12 was the ability to be able to have a server side script schedule install and/or update a plug-in.

If you login to your FileMaker Server using the FileMaker Server Admin Console application and navigate to the Database Server > Server Plug-Ins tab (for FileMaker Server v12 you’ll find this under FileMaker Server Overview>Configuration>Database Server) you will see 2 options which are disabled by default:

  • Enable FileMaker Script Engine (FMSE) to use plug-ins
  • Allow Install Plug-In File script step to update Server plug-ins

If you’re going to be working with server side script schedules that use plug-ins you will need to enable both of these. If you attempted to install/update a plug-in using a server side script schedule and the Allow Install Plug-In File script step to update Server plug-ins was disabled you would get this error:

This plug-in could not be updated automatically. Error 3 (Command is unavailable (for example, wrong operating system, wrong mode, etc.))

Once you enable the Allow Install Plug-In File script step to update Server plug-ins option you will be able to install plug-ins successfully via a server side script schedule, however you might not think it is working at first. Plug-ins get saved to the following directories:

  • Windows: [drive]:\Program Files\FileMaker\FileMaker Server\Database Server\Extensions\
  • Mac OS: /Library/FileMaker Server/Database Server/Extensions/

Here’s a screenshot showing the contents of my Extensions folder:

Screen Shot 2015-06-10 at 9.10.43 am

You’ll notice there are 2 plug-ins installed: the BaseElements plug-in and the SMTPit Pro plug-in. However looking at the list of installed plug-ins in the Admin Console only shows the SMTPit Pro plug-in:

Screen Shot 2015-06-10 at 9.10.02 am

 

Even though the BaseElements plug-in does not appear in the list it is installed and loaded, as I can perform server side script schedules that use the plug-in successfully. The only way I’ve found to update the list is to restart the server machine – logging out and back in to the Admin Console and restarting the Database Server do not appear to update the list of plug-ins in my experience.

If you look at the Log Viewer after restarting the Database Server you will only see log entries for the plug-ins that are visible in the Admin Console as well:

Jun 10, 2015 8:59:25 AM Server Events Information 476 Plug-in enabled: SMTPit Pro SE

I’ve experienced this on both Mac and Windows servers with Server v12, 13 and 14. Once you restart the server machine the list of plug-ins will then be updated, but this can be a bit disconcerting the first time you use server side schedules to install/update plug-ins and don’t see any evidence of them in the Admin Console.

 

fmSMS v3 Coming Soon

We’re putting together the final touches to v3 of fmSMS, our two way SMS FileMaker solution that allows you to send and receive SMS/txt messages from your FileMaker database. With the release of FileMaker Pro v12 we decided to take this opportunity to rewrite fmSMS from the ground up, which has meant the v3 release has taken us a bit longer than we expected. Version 1 of fmSMS was originally created with FileMaker Pro v6, and v2 was developed with FileMaker Pro 9, 10 and 11. With the new v12 file format, plugin installation updates and the ExecuteSQL function we decided that it was a good time to go back to the drawing board and start afresh.

We’re also changing the plug-in we use with fmSMS v3 to the free BaseElements plug-in. The BaseElements plug-in already had a number of HTTP functions which we were able to implement quickly, however it was lacking a function that a number of our customers rely: proxy support. We decided to sponsor the development of this function which was included in the recent v2 release of the BaseElements plugin. The function is called as follows:

BE_HTTP_Set_Proxy ( proxy {; port ; username ; password } )

You can supply the proxy server address, along with the three optional parameters for the port number, proxy server username and proxy server password.

We’ve also reduced the number of files down from 3 to a single file. Using the BaseElements plug-in also allows us to remove any licensing restrictions and to include support for FileMaker Server scripts as standard (previously this required customers to purchase an additional plug-in license). We’ve also included support for additional SMS Gateways around the world, including the USA.

We hope to have the new version ready in a few weeks.

FileMaker’s internal SQL and System Formats

I’ve been using the ability to execute SQL statements via a FileMaker Pro plug-in more and more lately (see An Approach to FileMaker Server-Side Script Debugging for an example of this) and recently encountered one issue that might arise when working with files created with different system formats to those on your computer. It’s important to note that FileMaker Pro uses your computer’s system formats to determine how dates, times, and numbers display and sort when you first created a new file.

I was working with a file that I didn’t even realise was created with different system formats as the developer of the file had set the File Options to “Always use current system settings” which tells FileMaker to use the current system settings for data entry of numbers, dates, and times instead of those saved with the file when it was first created or cloned:

The startup/OnOpen script for the file was also using the Set Use System Formats script step and setting this to [On] which also instructs FileMaker Pro to use the current system formats. It wasn’t until I started using the BaseElements plug-in to enter the current timestamp along with some additional text into a text field that I noticed something was amiss. Instead of inserting a timestamp for my system settings (Australian) which would appear as:

9/11/2011 3:49:07 PM

I was getting this instead:

09.11.2011 15:49:20 Uhr

I did some tests and whenever I evaluated the Get ( CurrentTimeStamp ) function I would get a correctly formatted timestamp for my region (e.g 21/11/2011 3:51:20 PM ) but as soon as I used this in an SQL statement or converted it to a text data type I would lose the system settings and get the original settings that were saved with the file when it was first created (German in this case). This appears to happen anytime you need to convert dates, times etc into a text data type via the calculation engine (not specifically related to using a plug-in), which is what I’m doing with the internal SQL feature and the BaseElements plug-in. For example my plug-in calculation looked liked this:

BE_FileMakerSQL ("
INSERT INTO ServerLog(Description)
VALUES('Logging Commenced at " &  Get ( CurrentTimeStamp ) & "'" & ")")

The only solution that I’m aware of is to create a clone of the file and then open it on a computer with the required system formats. It will then use the local system formats and you will get the same results when converting date and time values to a string.

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

 

Additional Resources:

FileMaker’s Internal SQL Engine, part 1 

Server Side Imports and Exports

FileMaker Server 11 Help

FileMaker Server Event Log Messages

Troubleshooting Errors with FileMaker Server Auto Update plug-ins feature

I’ve been spending a lot of time recently deploying solutions that require plug-ins and having FileMaker Server distribute them automatically to clients via it’s nifty Auto Update feature. Inevitably you will encounter issues for some clients when attempting to download the plug-in: permissions issues with the plug-ins on the server, not having the plug-in in the correct location etc. They are usually easy to resolve once you know what the error is.

When you write a script to download a plug-in you end up calling the FMSAUC_UpdatePlugIn function. This is a function of the AutoUpdate plugin (yes you need to use an existing FileMaker plug-in to download a plug-in), which thankfully by default is always installed by FileMaker Pro/Pro Advanced (there are only 3 functions for this plug-in: one checks the version (used to see if the plug-in is installed), another to find the plug-in you’re after on the FileMaker Server computer, and the third to download the plug-in). Assuming you have the plug-in installed in the correct location on your FileMaker Server with the appropriate permissions set (see the FileMaker Server 11 Guide to Updating Plug-ins for the full details) you’ll end up calling the FMSAUC_UpdatePlugIn function to initiate the download of the plug-in. If successful this function will return 0 like a normal successful script step, and if unsuccessful it will return an error number in the range of -6 to 101.

I like to capture the error number and if necessary display that to the user along with a more human readable error description. There are some custom functions that you can use to return the description for the error code. For example you capture the error code into a variable using the Get (LastError) function and then pass that to the custom function which will return the error description. For example using the custom function linked above ErrorToEnglish ( 101 ) returns “Record is missing” and saves you the trouble of looking up the error code in FileMaker’s help. However when using the FMSAUC_UpdatePlugIn function you won’t able to use this as it has it’s own list of error codes which overlap with existing list of FileMaker error codes. For example error 101 means “The function call from the client computer to the computer running FileMaker Server failed. The server computer might be running a previous version of FileMaker Server.” when returned by the FMSAUC_UpdatePlugIn function but could return “Record is missing” from the Go to Record/Request/Page script step.

I ended up creating another custom function that you can call when performing the FMSAUC_UpdatePlugIn function that will return the appropriate description for any errors encountered when attempting to download a FileMaker plug-in from FileMaker Server. Note you could also extend the existing ErrorToEnglish custom function to include a 2nd parameter to indicate whether it is a script step or the FMSAUC_UpdatePlugIn you are capturing the error code for, rather than have separate custom functions.

Using FileMaker Plug-ins with Instant Web Publishing

I normally don’t work with FileMaker’s Instant Web Publishing (IWP), preferring to work with Custom Web Publishing and the PHP API which gives me more flexibility without the limitations of IWP. I was recently working with a client that had deployed an IWP solution hosted by FileMaker Pro (not FileMaker Server Advanced) and wanted to integrate our fmSMS solution, which allows you to send/receive SMS/txt messages in FileMaker. I quickly found a problem though – the plug-in that we use to send the SMS messages was not being recognised by the IWP clients/browsers, despite it working fine in the FileMaker Pro client. The problem took a while to locate but the solution was simple and was not something I could find documented anywhere else so I thought I would write it up here in case this helps someone else out there having the same problem.

As I started to test whether we could deploy our SMS solution via IWP I wasn’t too confident to start with as I wasn’t sure whether plug-ins were supported under IWP in the first place, and had already worked out a backup plan if that was the case. I first checked the FileMaker 11 Instant Web Publishing Guide to see if it mentioned the compatability of plug-ins with IWP, and was encouraged to see the following: “Typically, third party plug-ins can be used for web published databases if they do not attempt to display information to an end-user’s screen, if they do not require direct end-user interaction, if they do not interact with the FileMaker Pro user interface, or otherwise require interaction from end users”. I was bolstered by this as our plug-in doesn’t involve any user interface interaction – it simply communicates with an online SMS Gateway and returns the result into a field via the Set Field script step. As fmSMS is a multi-file solution that was never designed with IWP in mind I decided to create a quick test file with only the basic tables, fields, layouts and scripts necessary to test it under the IWP environment. I made sure all the scripts were IWP compatible, tested it in FileMaker Pro Advanced as the host successfully then was ready to test in IWP.

My first test in IWP returned a “?”, which I know usually means the plug-in was not installed or enabled. However I could see the plug-in listed under the FileMaker preferences and knew it was loaded as FileMaker Pro Advanced was able to run my scripts successfully and return the correct result. I wasn’t getting puzzled at this point and spent a few more hours modifying scripts, creating new plug-in tests to ensure it was loaded but I always ended up with a “?” everytime I called a plug-in function. I gave up for the night and decided to sleep on it.

The next day I started again and continued to get the “?” result no matter what I tried. I was almost ready to give up and pronounce that this plug-in was not compatible with IWP but decided to try one last test. By default I always install (or FileMaker Server installs for me via it’s Auto Update feature) all my plug-ins in the user’s Application Data folder, which is located at the following for each of the supported platforms:

  • Windows XP: C:\Document Settings\User Name\Local Settings\ApplicationData\FileMaker\Extensions
  • Windows Vista/Windows 7: C:\Users\User Name\AppData\Local\FileMaker\Extensions
  • Mac OS X: Macintosh HD/Users/User Name/Library/Application Support

I moved the plug-in from here to the traditional location inside the FileMaker Application folder, which is usually one of these for each platform:

  • C:\Program Files\FileMaker\FileMaker Pro 11 Advanced\Extensions
  • Macintosh HD/Applications/FileMaker Pro 11 Advanced/Extensions

After relaunching FileMaker Pro Advanced and quickly tested everything worked fine I started what was going to be my last test via IWP and it worked! I’m not 100% sure why this is the case as I can’t find any documentation from FileMaker Inc about this but I suspect it relates to the FM Web Publishing application. The FM Web Publishing app is located at either:

  • C:\Program Files\FileMaker\FileMaker Pro 11 Advanced\ExtensionsWeb Support\FM Web Publishing.exe
  • Macintosh HD/Applications/FileMaker Pro 11 Advanced/Extensions/Web Support/FM Web Publishing.app

and it appears that the FM Web Publishing app can only see plug-ins that are also in the FileMaker Pro Application Extensions folder – at least in the case of the plug-in I’m working with, I haven’t tested this with other plug-ins under IWP. If you’re hosting your IWP solution under FileMaker Server Advanced you won’t encounter this as it only applies to hosting IWP solutions with FileMaker Pro/Pro Advanced – you install plug-ins in different locations for FileMaker Server Advanced hosting.

So in summary if you’re working with plug-ins for solutions hosted by FileMaker Pro/Pro Advanced and deployed via IWP and can’t get the plug-in functions to work correctly, make sure you have installed the plug-in in the FileMaker Pro/Pro Advanced Application Extensions folder not in the user’s Application Data folder. Note that if you have the same plug-in installed in both locations the user’s Application Data folder will take preference over the Application Extensions folder so make sure to remove it from the user’s Application Data folder.

FileMaker DevCon 2008 Post Mortem

It’s been a few weeks now since I returned from the FileMaker 2008 Developer Conference in Phoenix, Arizona. It was a whirlwind trip this time – there and back in 6 days with lots of jetlag and not much sleep, no thanks to Qantas for cancelling my return flight and delaying my departure. I’ve finally settled back in and can report on some of the highlights from the conference and the product showcase. I spent most of my time attending any PHP related sessions and learnt a lot about the power of PHP in conjunction with the FileMaker PHP API. I’m currently working on a project that is an upgrade from a CDML/FileMaker Unlimited 5 database driven site to a FileMaker Server 9/PHP driven site. There’s lots of new stuff to learn and I’m impressed with the capabilities of PHP compared to CDML. I particularly enjoyed Lance Hallberg’s sessions on the PHP API fundamentals and the advanced features.

The two sessions on using the Web Viewer to interact with your FileMaker tables and as a dynamic interface were both well attended. Both highlighted the power of the MBS plugin and the Fusion Reactor plugin – the amazing Fusion Reactor also won the FileMaker Excellence award for Solution of the Year. Congratulations to Craig and the gang at Digital Fusion for their amazing plugin which is one of the most truly innovative plugins to be released in the last few years.

In the product showcase I ended up buying some new tools and upgrades:

BaseElements 2 – Goya have released their update to v2 of their DDR Analysis tool BaseElements. This is almost a must have for the professional FileMaker Developer. I’m still using both Inspector and BaseElements as each have their strengths but spend most of my time with BaseElements. SixFriedRice were demoing an early released of a web hosted service – FM Lumen – which they promise will provide deeper and faster analysis of the FileMaker DDR. I can’t wait to give that one a try.

ClipManager – My FM Butler have released a beta of v3.0 of their handy tool Clip Manager which allows you to store and manipulate the contents of the FileMaker clipboard. It’s also at present one of the only ways to copy custom functions and you can use it to build up a library of fields, scripts, layouts and other elements that you can quickly add to your solutions. v3.0 is also the first release that runs on Windows as well as the Macintosh.

Web Services Plugin – I already have a developer licence for the amazing Web Services plugin from FM Nexus. This was featured in the opening keynote and FM Nexus released a beta of v2 which makes it easier to load functions on the fly by storing the WSDL file inside a field in your database.

I also bought the VTC PHP Training CD so I can totally immerse myself in the FileMaker API for PHP. I look forward to attending the next DevCon in 2009 in San Francisco – finally a new city and new new hotel!

FileMaker and Web Services the easy way

I’ve worked on many FileMaker implementations that require integration with external systems, whether it is importing, exporting, posting or syncing data, usually with XML involved in there somewhere. I’ve had many discussions with FileMaker engineering staff at FileMaker DevCons over the years as well about where I would like to see FileMaker Pro expand in these areas.

FileMaker can do a good job and exporting and importing XML data, and with the importing of XML data you can also specify an HTTP address and with the use of variables you can by quite dynamic with setting up your XML imports. Unfortunately things are much more limited on the XML export side of the equation – you can only export XML data to a file location (although you can reference an XSLT stylesheet during the export to transform your XML which can be stored on a web server). To get that XML onto the web you need to look at other technologies available for FileMaker Pro, Windows or the Macintosh and hook into these.

I’ve previously used the Troi URL plugin to assist with doing an HTTP POST of xml data generated via an XML export with a stylesheet then read back into a FileMaker field, but a recent project required the use of Web Services to submit data via XML, which is something that hasn’t been easy to do until now. The new Web Services plugin from FM Nexus makes working with Web Services incredibly simple. As long as you can locate a WSDL file for the web service you do everything else within a FileMaker calculation. This blew me away the first time I got this working – working with web services has never been this easy before.

The plugin does require some additional overhead and maintenance, as you need to create a wsdl folder in the FileMaker extensions folder (where you would normally store the plugin) and put your wsdl’s inside there. There’s no automated way to easily distribute these that I know off, though I ended up creating a simple installer to install the plugin and the wsdl folder. All the XML is taken care of by the plugin and you typically just need to replace any placeholders with the appropriate field from your FileMaker table which couldn’t be any easier. I encountered a number of issues with empty/NULL values that required some additional logic in the FileMaker calculation and each wsdl/web service will probably have it’s own unique requirements, but generally you can work around these with existing FileMaker Pro calculation functions.

You can get all the info and download a demo of the plugin at the FM Nexus website. FileMaker Advisor magazine also did a review of the plugin (note the pricing of the plugin appears to have changed from the review – it’s gone up unfortunately).

Plugins, AutoUpate and FileMaker Pro Runtimes

I’ve written previously about the changes to where plugins are downloaded to with FileMaker Pro 9, which now has 2 possible locations for storing plugins:

1 – the FileMaker Pro Extensions folder and;

2 – the user’s Application Data folder

Having the same plugins (but different versions) in both locations can create some plugin management problems for you as the plugins in the user’s Application Data folder take precedence over plugins with the same name in the FileMaker Pro Extensions folder. Today I encounterd a new scenario where this creates problems for the user – a runtime application that also uses plugins created with FileMaker Pro Advanced v9.

I created a runtime using FMPA 9.0v3 (Windows XP) and installed it on a client’s computer, which also had some of the same plugins in the /Application Support/FileMaker/Extensions folder but several of them were older versions used for other FileMaker Pro databases.

This led to several errors with my runtime as it couldn’t register the older versions of the plugin and my runtime was checking for specific versions of the plugin as it uses functions not available in the older versions. Whilst I am a fan of the AutoUpdate feature and the new download location in the user’s Application Data folder it certainly does create problems with interference with runtime applications on the same computer that use the same but different versions of a plugin, which is something we haven’t had to worry about with previous versions of FMP. I’ve reported this to FileMaker Inc as both a product problem and a feature request for this to be addressed in future releases.

If you’re a heavy user of plugins like myself with FileMaker Pro 9 you will be spending more time on plugin management issues and locating the user’s Application Data folder unfortunately. Runtime applications should only be able to reference plugins installed in the runtime application’s Extensions folder like runtime’s created with previous versions of FileMaker Pro.