Do you love FileMaker? Do you work with (or for!) someone who does? I’m now a Drupal Developer at Mediacurrent, but my last position (at Georgia Tech’s Office of International Education) involved a lot of FileMaker work, and this FileMaker integration would have been a lifesaver, so here goes! First, a bit of background on FileMaker for the unindoctrinated: “Tackle any business task... Powerful, easy-to-use database software for yourself or a team. Create forms, reports and labels. Share on a network or over the web.” Lots of non-technical folks love to use FileMaker; it's a cheap and intuitive alternative to more expensive visual database solutions, like Microsoft Access. It's the #1 best-selling standalone database for both Macintosh and Windows and has won dozens of awards in the industry. (Source: ScottWorld.com). At Mediacurrent, we recently integrated a client's existing FileMaker database with a freshly-launched Drupal 7 website. Here’s an overview of the workable solutions we explored:

  • Drupal → Filemaker
    push data to RESTfm (a REST server for FileMaker) via the WSClient and Rules modules.
  • Filemaker → Drupal
    send REST requests from FileMaker to Drupal Services via the Troi URL plugin.
  • Drupal ← Filemaker
    pull data into Drupal via a LIST response from RESTfm.

Ingredients

 

  • FileMaker Server ($3k)
  • FileMaker Pro Advanced (yes, you need the “Advanced” version) ($500)
  • RESTfm ($400)
  • Troi URL plugin (optional, for pushing directly Filemaker to Drupal) (web server license - $300).
  • Drupal ($Priceless.00)

 

Baking Instructions, Drupal → Filemaker

 

  • Enable PHP Web Publishing (details) on your FileMaker server. (Documentation v10 and v12)
  • On your FileMaker database (aka your FileMaker “file”), go to the Privileges settings and enable “[Full Access]” for PHP Web Publishing. (details, and documentation)
  • Install and configure RESTfm on the Web Server of your choice. Check out the RESTfm manualfor instructions. I chose to follow the manual's suggestion and keep-it-simple by installing RESTfm on IIS (Windows web server) on the same server hosting FileMaker Server.
  • Test your RESTfm configuration by browsing the RESTfm HTML interface: "http://your-server/RESTfm".
  • Test creation, deletion, and modification of records via the RESTfm demo page, "http://your-server/RESTfm/demo.html". Note that RESTfm returns a 500 error if any fields on your layout are set to both (1) Auto-Enter: Prohibit modification of value during data entry, and (2) Validation: Not empty. The assumption here is that FileMaker processes the validators before calculating the Auto-Enter fields. You will also see this error if you leave empty any field set to Validation: Not empty.
  • Test CRUDL (CREATE, RETRIEVE, UPDATE, DELETE, LIST) access to RESTfm from your browser with the Postman extension for Chrome or Poster add-on for FireFox. If none of that made sense, you may want to do some reading up on REST servers.

If you’ve made it this far, congratulations! Those sweat and tears were worthwhile. Let’s look at Drupal configuration now. Start by installing the WSClient and Rules modules. (How to install modules). WSClient doesn’t support configuration of a full REST client via its user interface. In order to configure a WSClient service to consume RESTfm, you’ll have to write it in code with a custom module (for the unfamiliar, review this module creation tutorial (general) and this module developer's guide (technical)). All the code that follows was adapted from the "WSClient Examples" module (included with the WSClient module), and I suggest reviewing it. Note that the useful example will not appear unless you have the RESTful Web Services module enabled. Your code might look like this inside hook_default_wsclient_servicein your custom module:

service = new WSClientServiceDescription();
$service->name = 'restfmclient';
$service->label = 'RESTfm Client';
// Replace "server" with the hostname of your RESTfm web server.
$service->url = url('http://server/RESTfm', array('absolute' => TRUE));
$service->type = 'rest';

// Add HTTP Basic Authorization headers.
// Or you can look into using RESTfm's support for authentication via API key.
// @see http://www.restfm.com/manual_v1/authentication
$curl_options[CURLOPT_HTTPAUTH] = CURLAUTH_BASIC;
// Replace "filemakeruser" and "password" with your FileMaker file login.
$curl_options[CURLOPT_USERPWD] = ‘filemakeruser:password';
$service->settings['curl options'] = $curl_options;

But we’re not done yet! RESTfm is persnickety. It wants you to send data to it in just the right structure: an array called 'data', pointing to another array called '0' (zero), pointing to a third array of fields and values. In PHP code, it looks like "array('data' => array(0 => array('field_name' => $value)))". In order to formulate our data into this structure, we must create three separate data structures for WSClient to pass on to Rules. (some info on Rules data types)

// Data structure to send on UPDATE or CREATE:
//   array('data' => array(0 => array('field_name' => $value)))
$service->datatypes = array(
  'request_data_wrapper_outer' => array(
    'label' => 'request data - outer wrapper',
    'property info' => array(
      'data' => array(
        'type' => 'request_data_wrapper_inner',
        'label' => 'request data - inner wrapper',
      ),
    ),
  ),
  'request_data_wrapper_inner' => array(
    'label' => 'request data - inner wrapper',
    'property info' => array(
      '0' => array(
        'type' => 'request_data',
        'label' => 'request data',
      ),
    ),
  ),
  'request_data' => array(
    'label' => 'request data',
    // HERE: LIST THE FIELDS YOU WANT PUSHED TO FILEMAKER
    'property info' => array(
      'title' => 'text',
      'nid' => 'integer',
      // 'field_your_field' => 'text',
      // ...
    ),
  ),
);

Phew. Glad that’s out of the way. Now, let’s setup the data structures to receive data back from a RESTfm CREATE operation.The data we receive looks like "array('meta' => array(0 => array('recordID' => $fm_id, 'href' => $uri)))".

// Create the data structures needed to receive responses from RESTfm CREATE.
//   array('meta' => array(0 => array('recordID' => $fm_id, 'href' => $uri)))
$service->datatypes = array_merge($service->datatypes, array(
  'create_result_wrapper_outer' => array(
    'label' => 'response from CREATE operation - outer wrapper',
    'property info' => array(
      'meta' => array(
        'type' => 'create_result_wrapper_inner',
        'label' => 'response from CREATE operation - inner wrapper',
      ),
    ),
  ),
  'create_result_wrapper_inner' => array(
    'label' => 'response from CREATE operation - inner wrapper',
    'property info' => array(
      '0' => array(
        'type' => 'create_result_data',
        'label' => 'response from CREATE operation - data',
      ),
    ),
  ),
  'create_result_data' => array(
    'label' => 'response from CREATE operation - data',
    'property info' => array(
      'recordID' => array(
        'type' => 'integer',
        'label' => 'FileMaker Record ID',
      ),
      'href' => array(
        'type' => 'text',
        'label' => 'Resource request URI',
      ),
    ),
  ),
);

Now we’re ready to create our CRUDL operations! It is important to remember that RESTfm performs these operations against FileMaker layouts and not against the actual underlying tables.Note that RESTfm needs a FileMaker Record ID for RETRIEVE, UPDATE, and DELETE operations. This Record ID is an internal FileMaker identifier and can be found by creating a calculated field in your FileMaker table set to "Get ( RecordID )". Also note that there is no way to set or modify a record's Record ID (FileMaker assigns it automatically).

// Replace "your-FileMaker-dabase" and "your-FileMaker-layout" here:
$url = 'your-FileMaker-database/layout/your-FileMaker-layout’;
$targeted_url = $url . ‘/@recordID’;

// CREATE operation.
$operation = array();
$operation['label'] = 'CREATE';
$operation['url'] = $url;
$operation['type'] = 'POST';
$operation['data'] = 'restfm_data';
$operation['parameter']['restfm_data'] = array(
  'type' => 'request_data_wrapper_outer',
  'label' => 'request data - outer wrapper');
$operation['result'] = array('type' =>
  'create_result_wrapper_outer', 'label' => 'response');
$service->operations['create'] = $operation;

// RETRIEVE operation.
$operation = array();
$operation['label'] = 'RETRIEVE';
$operation['url'] = $targeted_url;
$operation['parameter'][‘recordID’] = array(
  'type' => 'integer',
  'label' => 'FileMaker Record ID');
$operation['result'] = array('type' => 'text', 'label' => 'response');
$service->operations['retrieve'] = $operation;

// UPDATE operation.
$operation = array();
$operation['label'] = 'UPDATE';
$operation['url'] = $targeted_url;
$operation['type'] = 'PUT';
$operation['data'] = 'restfm_data';
$operation['parameter']['restfm_data'] = array(
  'type' => 'request_data_wrapper_outer',
  'label' => 'request data - outer wrapper');
$operation['parameter'][‘recordID’] = array(
  'type' => 'integer',
  'label' => 'FileMaker Record ID');
$operation['result'] = array('type' => 'text', 'label' => 'response');
$service->operations['update'] = $operation;

// DELETE operation.
$operation = array();
$operation['label'] = 'DELETE';
$operation['url'] = $targeted_url;
$operation['type'] = 'DELETE';
$operation['parameter'][‘recordID’] = array(
  'type' => 'integer',
  'label' => 'FileMaker Record ID');
$operation['result'] = array('type' => 'text', 'label' => 'response');
$service->operations['delete'] = $operation;

// LIST operation.
// You may add parameters to the URL to filter the result set.
// @see http://www.restfm.com/manual_v1/uri-restfmdatabaselayoutlayout
$operation = array();
$operation['label'] = 'LIST';
$operation['url'] = $url;
$operation['result'] = array('type' => 'text', 'label' => 'response');
$service->operations['list'] = $operation;

And don’t forget to return the service when you’re done.

// All done.
$services[$service->name] = $service;
return $services;

Okay, folks, the hard part is out of the way; pat yourselves on the back! All that’s left is Rules configuration. Head to the Rules Configuration page and set up

  • a “RESTfm Client: CREATE” action on event “After saving new content” - Include three "Create data structure" actions to set up the RESTfm request data: - (1) request data, (2) inner wrapper, (3) outer wrapper - Include a "Set a data value" action to save the returned FM recordID into your entity. - The "Set a data value" value would look like "response:meta:0:recordID".
  • a “RESTfm Client: UPDATE” action on event “After updating existing content” - Again, include three "Create data structure" actions to set up the RESTfm request data.
  • a “RESTfm Client: DELETE” action on event “After deleting content”

That takes care pushing Drupal data to FileMaker. Stay tuned for part deux of this blog post for a walk-through of FileMaker → Drupal solutions. Would you like to see this topic presented at DrupalCon Portland? +1 the session proposal and leave a comment for the selection committee! Link to DrupalCon Session Proposal.

 

Additional Resources

Inline WYSIWYG Editing in Drupal

Responsive Design: Mobile Menu Options

Display Suite - A Themers Perspective

 
Tags
Development
Drupal
FileMaker
REST
REST Server
RESTfm
Services
Troi
Troi URL Plugin
Web Services
WSClient