Connecting FileMaker Pro to FileMaker Server with the FileMaker Data API

FileMaker Server 16 (and FileMaker Cloud 1.16.0) included a new trial feature called the FileMaker Data API (application programming interface), which allows web services or applications to access data in hosted FileMaker solutions without needing any FileMaker client software to be installed. This is similar to the current XML and PHP APIs which have been around for over 10 years, however the Data API is a REST (Representational State Transfer) API which means you don’t need to use any particular programming language to work with the Data API.

In their Product Roadmaps over the past couple of years FileMaker Inc. have indicated that they will be investing in the Data API in future releases of FileMaker platform. Whilst they have not included the XML and PHP APIs in their list of deprecated features their focus will be on the Data API which opens up the FileMaker platform to an almost unlimited number of external apps and services.

We’ve been working with REST APIs for many years – sending SMS messages, integrating with Accounting platforms and eCommerce platforms – so we were pleased to see FileMaker Server (and now FileMaker Cloud) include a REST API as this now opens up many possibilities for integration by external web services and applications, as they don’t need to know “FileMaker” in order to make requests with the Data API. If you’re familiar with making HTTP requests (GET, POST, PUT, DELETE) and working with JSON data formats then you will have no issues working with the FileMaker Data API.

As the FileMaker Data API is a trial feature – the trial expires on September 27, 2018 and the Data API will cease to operate – we normally wouldn’t recommend our clients use this feature in any live systems as it is likely to change and you will have to upgrade to the latest version of the FileMaker platform before the trial expires in order to keep using it (after making any necessary changes to ensure compatibility with the final release of the Data API). There is also the issue of licensing – the Data API is currently free to use in the trial period, but FileMaker plan to have a licensing model in place before the end of the trial. Without knowing what the final licensing model will be makes it hard for clients to commit to using the Data API at this stage.

During a recent project we had encountered a challenging issue that turns out was perfect for the Data API. This project has a FileMaker Server v16 hosted solution with a Custom Web Publishing/PHP interface. All users access the solution using a web browser – for security reasons installing FileMaker Pro was not an option. One requirement of the project was to allow printing of labels with barcodes and picking lists – we solved this by generating PDF files via the new support for generating PDF files on FileMaker Server as well as Excel files (see our blog post from last year on how we did this). We still ended up using a local FileMaker file to import the Excel file containing the data for the shipping labels and to create the barcodes.

This process was working smoothly but was more suitable for doing large bulk shipments and involved a number of steps that you normally wouldn’t have to perform in a purely FileMaker Pro interface:

• users would first have to find the records via the web interface that they wished to generate labels for
• they would then click a button to call the server side script to create the PDF or Excel file
• they would then have to download this Excel or PDF file to their local machine
• for PDF files they would simply open them and print as needed, and for the Excel files they would open the local FileMaker file and click a button to import the Excel file which then generated the labels and barcodes

As you can see there’s a number of steps involved, whereas with a pure FileMaker Pro interface there would typically be only 2 steps:

• find the records to print
• click a button to print the shipping labels or picking lists

The process was also the same regardless of whether you wished to print a single shipping label or 100 labels. We had been discussing ways to streamline and improve this process, knowing that we couldn’t connect to the solution using a live FileMaker Pro connection. We were leaning towards building a integration using either the XML or PHP APIs, or using a product like RESTfm when we realised that the Data API might be another option worth considering, knowing that it was a trial feature and would stop working in September but would be a better long term investment. Now that FileMaker Pro v16 supports cURL and JSON natively it started to make even more sense, even though using FileMaker Pro to integrate with FileMaker Server via the Data API doesn’t make too much sense at first.

We built a basic prototype in a few hours to authenticate and make simple requests to get single and bulk shipping labels and picking lists and were impressed by the speed of the responses, so decided to press ahead and incorporate this functionality into the existing local FileMaker solution that users were already using to import the Excel files and print labels and barcodes. We were further able to link the web interface with this local FileMaker file through the use of FMP URLs by adding buttons on the web interface that called scripts in the local FileMaker file and passed in parameters, so the updated process to print labels and packing lists is now down to 2 steps:

• find the records to print
• click a button to print the shipping labels or picking lists

If you’re thinking about using FileMaker Pro as a client for the FileMaker Data API here’s some tips and tricks we learnt along the way:

• the FileMaker Data API Reference can be found on your FileMaker Server at this URL – https://localhost/fmi/rest/apidoc/ – and contains examples for the requests and responses you can expect when authenticating, working with records and performing finds. The FileMaker 16 Data API Guide is the other main reference to working with the Data API. I recommend having both of these resources open in separate tabs when working with the Data API for the first time
• we recommend using tools like Postman when working with the Data API to learn how to structure your requests. You can convert your working requests to cURL using the Generate Code Snippets feature which you can then copy and paste into your FileMaker Insert From URL script step and format for use with FileMaker Pro. See our previous article about about using Postman environments with the REST API.
• the Data API is not currently a complete replacement for the XML or PHP APIs. For example you cannot insert container data or run FileMaker scripts with the Data API, or retrieve metadata about your FileMaker solution such as layout schema. The Data API does not currently return data such as the number of found records like the getFoundSetCount() method does in the PHP API, so you need to use workarounds for these.
• date formats appear to work in a similar way to the PHP API in that you need to specify them in MM/DD/YYYY format regardless of your date formats on your server or FileMaker solution. Dates are also returned in the same MM/DD/YYYY format (e.g. 11/30/2017 for November 30th, 2017).
• when performing a find query the fields you are searching for don’t need to be on the layout you specified in your URL, but only fields on the specified layout are returned in the response.
• when performing a find query that results in no records found the use of the –show-error cURL option determines whether the Insert From URL script step returns an error (1631) or not. This is documented in the Supported cURL options page help page under the Handling errors heading.
• one issue that caused us grief is an apparent bug with the use of the JSONSetElement function with spaces when used in a particular way the the cURL options. We have now standardised on specifying the JSON for each Data API request using the –data @$data syntax instead of the alternative methods
• to help with debugging we recommend using the –trace and –dump-header options with every request

The final cURL options generally look like this for a typical request (assuming you have already authenticated and stored the access token somewhere):

"--request POST" & 
" --header " & Quote( "Content-type" & ": " & "application/json" ) & 
" --header " & Quote( "FM-data-token" & ": " & $$restAPIAccessToken ) & 
" --data @$json" & 
" --trace $$cURLTrace" & 
" --dump-header $responseHeaders"

Using FileMaker Pro as a client for the FileMaker Data API has us thinking about new ways we can leverage the Data API from an offline FileMaker Pro or FileMaker Go client, such as syncing records when using FileMaker Go on an iPhone or iPad and removing the overhead of having to have a live connection to your FileMaker Server. The Data API is perfect for uploading or downloading small changes from an offline file and once the shipping version of the Data API is available in the next version of the FileMaker platform (and the licensing model revealed) we expect to see more widespread use of the Data API, particularly for offline files running under FileMaker Go.