9 steps to discover how to get your FileMaker data into Drupal

By Derek DeRaps, Drupal Developer, in this guestpost you can learn in 9 points how to get your FileMaker data into Drupal. He proposes two ways: write FileMaker scripts to push data to Drupal, or use Drupal to pull data from FileMaker. However he suggests the latter and here’s why.

 FileMaker scripting is a major PITA

''Pushing field-level data to Drupal with the OnObjectSave trigger sounds like such a great idea at first blush, but major blockers surface pretty quick. Guess what happens every time you modify any FileMaker field: OnObjectSave calls your custom script to push a REST request to Drupal services, and the FileMaker client interface locks up for the duration of the request! That's about a three second "freeze" every time your client edits a field. “Well, let’s simply log the change for later processing by a server-side FileMaker script.” Great idea. But there still remains the impossibility of maintaining object-level triggers on each your 50 fields × 10 layouts = 500 point-and-click-operations = “Wait, did I miss one? How the heck do I track that down.”
For the FileMaker scripting gurus who hope to avoid the pain of PHP coding, there is still hope. You might try pushing record-level data to Drupal with the OnRecordCommit trigger. Since this is a layout-level trigger, maintenance is feasible, and your Drupal configuration is all point-and-click: set up the Drupal Services module to receive your REST requests. To send those REST requests in FileMaker scripting, you’ll need a plugin that provides HTTP request functions. The Troi URL Plugin costs $300 but provides everything you need out of the box. The 360works ScriptMaster plugin is free but only provides HTTP GET and POST; you can write the necessary HTTP PUT and DELETE functions via the plugin interface in a language called Groovy (=Java).

The best approach: skip Drupal Services entirely.

I know, it’s a bold idea. And it was also rejected by our lead developer when I first proposed it. But here’s where we ended up: skip the FileMaker “push” entirely and pull all FileMaker changes via Drupal Web Services Client! This approach has the added benefit of reducing our dependency on FileMaker scripts that reside outside our project codebase. Here’s the basic idea:
FileMaker work
  • Add a “Deletions Log” table to keep track of any records that were deleted in FileMaker.
  • Create a custom script “Log Deletion” that will add a row to the “Deletions Log” table when records are deleted.
  • Use FileMaker Pro Advanced (yes, you need the “Advanced” version) to modify the default “Menu Actions” for your FileMaker file. Have the “Delete” menu action call a custom script to add the ID of the deleted row to your new “Deletions Log”.
  • Add a “modification timestamp” field to all your FileMaker tables.
Drupal work
  • Modify your WSClient LIST operations to filter on that “modification timestamp” field.
  • Add new LIST and DELETE operations to act on your new “Deletions Log” layout.
  • Add a “Skip FileMaker CRUD” field to all your to-be-sync’d entities.
  • Create a new function in your existing custom module to perform the sync operations.
  • In this sync function, call each of your WSClient LIST operations, and pull all the FileMaker records where “modification_timestamp > $timestamp_of_last_sync”.
  • Process the records returned by those LIST operations into their corresponding Drupal entities.
System work
  • Use cron (whether Drupal or system) to hit your custom module’s “sync” function on a regular basis.
Admittedly, there are more than a few gotchas for each of the steps in this recommended approach. But fortunately, yours truly has tripped over all of them and found solutions to each. Here’s the walk-through.

Step 1. Add a Deletions Log to your FileMaker file

This log table needs two fields: the Drupal Entity ID of the record which was deleted, and the Drupal Entity Type to which it belongs.

Step 2. Write the Log Deletion custom FileMaker script.

This custom FileMaker script will create a new row in the Deletions Log table and set it with the Drupal Entity Type and Drupal Entity ID of the record being deleted.
To get the Drupal Entity Type, you might use the TableName of the deleted record to lookup the corresponding type. Or you might use a global variable of the current table to store the Drupal Entity Type. The Drupal Entity ID is available as a field of the record being deleted. Next, add script steps to switch to your “Deletions Log” layout and create a “New Record/Request”. After setting the Drupal Entity Type and Drupal Entity ID into the new record, you’re all done!
Note that if the Drupal Entity ID of the deleted record is not set, we may assume that our Drupal Sync Script has not yet pulled the new record into Drupal, and the deletion need not be logged.

Step 3. Modify your FileMaker file’s Menu Actions.

Tools → Custom Menus → “Custom Menus” Tab → Create → and choose “Records”. From there you can edit the “Delete Record” Menu Item and add an action to perform the “Log Deletion” script outlined above. Don’t forget to create a “Custom Menu Set” and mark it as the default for your FileMaker file.

Step 4. Add a “modification timestamp” field to all your FileMaker tables.

Add this “calculation” field to each of the FileMaker tables you want to sync: 
Let ( ~trigger = GetField ( "" ) ; 
  Round ( Get ( UTCmSecs ) / 1000 ; 0 ) - 
  Timestamp ("01/01/1970" ; "00:00:00")
)

The first line triggers our field to update every time the record is modified. The second line gets the current UTC (~GMT) time in seconds since 00:00 on 1/1/0000. The third line converts that to epoch/unix time (seconds since 00:00 on 1/1/1970).

Step 5. Add a filter to your WSClient LIST operations

If you haven’t yet created your WSClient LIST operations, see part one of this blogpost. Also, make sure you’ve reviewed the RESTfm manual which describes how to add filters to your LIST requests. Filters can be added to any LIST request with a combination of two URL parameters: one to specify the field, and one to specify the value. For example, if your field were named “modification_timestamp”, your WSClient LIST operation URL might look like:
http://your-server/RESTfm/your-database
/layout/your-layout?
  RFMsF1=modification_timestamp&RFMsV1=>
@timestamp_of_last_sync

Note the greater-than sign before @timestamp_of_last_sync. This tells RESTfm that we want any record whose field value is greater than the value we pass to it. Also note that I suggest using the RFMmax URL parameter to explicitly define how many records your server can realistically handle (probably a few hundred). Without specifying a value, RESTfm defaults to only 25, which may cause you to miss some records.

Step 6. Write LIST and DELETE operations for your “Deletions Log”

Follow the steps outlined in part one to write additional LIST and DELETE operations for WSClient. These will act on your “Deletions Log” layout.

Step 7. Add a “Skip FileMaker CRUD” field to all your entities

Choose "boolean" for the field type.

Step 8. Update your Rules to respect the "Skip FileMaker CRUD" field

To each UPDATE Rule created in part one, add two "Data Comparison" conditions. The first condition acts on "your-entity:field_skip_filemaker_crud" and the other acts on "your-entity-unchanged:field_skip_filemaker_crud" (the entity data before it was modified). Also add the first condition to each of your CREATE and DELETE rules. Be sure to check the "Negate" option, because we only want our rules to execute when this field is FALSE or missing.

Step 9. Pull and process FileMaker data!

You might keep this sync code in a custom script that manually bootstraps a Drupal environment, or you might store it in an include file of the custom module you already created (in part one of this tutorial). Note, the code that follows is just a starting point and, with the exception of Deletion Log processing, syncs only one entity type.
Setup.
// Set the new timestamp before sync to 
err on the side of overlap.
$lastsync = variable_get('_your_module_last_
sync_timestamp', 0);
variable_set('_your_module_last_sync_
timestamp', time());

// Load the WSClient service. In part one, 
we named it “restfmclient”.
$service = wsclient_service_load('restfmclient');

// At minimum, we need to know the 
entity type and bundle.
$basetype = ‘your_entity_type’;
$bundle = ‘your_bundle’;

// Now use entity_get_info to set the other 
parameters we’ll need later on.
$entityinfo = entity_get_info($basetype);
$entityid_key = $entityinfo['entity keys']['id'];
$typefield = $entityinfo['entity keys']['bundle'];

Pull FileMaker records that were modified since last sync.
If you’ve followed along faithfully to this point, you should be able to call your WSClient operation like so. 
// Get all records modified in FileMaker since
 this script last ran.
// In part one of the post, we used “list” as 
the operation name.
$response = $service->your-wsclient-list-operation
($lastsync);

But! RESTfm likes to return a 500 error when no records are found. So let’s catch it.
  try {
    // Get all records modified in FileMaker since
 this script last ran.
    $response = $service->your-wsclient-list-
operation($lastsync); 
  } catch (WSClientException $e) {
    $last_response = $service->endpoint()->
client()->lastResponse;
    $body = $last_response->body;
    if (500 == $last_response->responseCode &&
        FALSE !== strpos($body, '401: No records
 match the request')) {
      // OK to ignore error - RESTfm returns 
500 error on no records found.
    } else {
      // Continue error propogation.
      throw $e;
    }
  }

Loop through the returned records
Get the corresponding Drupal entity. Set the new values. Save. 
foreach($response['data'] as $fmrecord) {
  // Check whether FileMaker already had an entity id 
for this record.
  if (empty($fmrecord[$entityid_key])) { 
    // Since FileMaker did not send the entity id, this
 entity does not
    // yet exist in Drupal and must be created (
specifying the bundle property).
    $entity = new Entity(array($typefield => 
$bundle), $basetype);
  } else { 
    // FileMaker had entity id, so the entity already 
exists in Drupal.
    $entities = entity_load($basetype, array($fmrecord
[$entityid_key]));
    $entity = array_pop($entities);
  }
  // We must get our field list from someplace other 
than the data sent to us by
  // RESTfm, because RESTfm leaves empty fields out
 of the response.
  $fields = array_keys(field_info_instances
($basetype, $bundle));
  // Update entity data with values from FileMaker.
  foreach ($fields as $field) { 
    // Check whether RESTfm sent us data 
for this field.
    if(empty($fmrecord[$field])) { 
      // FileMaker sent no data. If the 
field previously 
had data, it was
      // deleted in FileMaker, so delete 
it from Drupal, too.
      unset($entity->$field);
      // Move on to next field.
      continue;
    } 
    // Get the value sent by RESTfm for this record.
    $value = $fmrecord[$field];
    // Non-property fields use a multi-lingual and 
multi-value array structure.
    if(substr($field, 0, 6) == 'field_') { 
      // Set the field's value.
      $entity->{$field}[LANGUAGE_NONE][0]
['value'] = $value;
    } else { 
      // Assume no value array structure to deal with.
      $entity->$field = $value;
    } 
  } 
  // We use a flag to keep Rules from executing 
WSClient 
calls to RESTfm on
  // save, which would cause an infinite loop of 
sync updates.
  $entity->field_skip_filemaker_crud[LANGUAGE_NONE]
[0]['value'] = 1;
  $saveresult = entity_save($basetype, $entity);
  // Unset the flag and re-save the entity. For this
 to work, our Rules
  // conditions must check for the flag in both 
the "unchanged" (i.e.,
  // pre-update) entity data and the new entity data.
  unset($entity->field_skip_filemaker_crud);
  entity_save($basetype, $entity);
  // If the entity was just created in Drupal, 
we want to send the entity id
  // back to FileMaker, so we'll 
call entity_save to trigger an update operation.
  if (SAVED_NEW == $saveresult) {
    entity_save($basetype, $entity);
  }
}

Phew. If you made it through that, you’re doin' swell! Now, let’s process our Deletions Log. Start by calling the WSClient LIST operation we created for our Deletions Log. Remember to catch the 500 error returned by RESTfm on no-records-found!
LIST records in the FileMaker Deletions Log
try {
  $response = $service->your-wsclient
-deletion-log-list-operation(); 
} catch (WSClientException $e) {
  // @see Above.
}

Loop and Delete!
foreach ($response['data'] as $index =>
 $deletionlog_entry) {
  // Get the entity type.
  $entity_type = $deletionlog_entry
['entity type'];
  // Get the entity id.
  $entity_id = $deletionlog_entry['entity id'];
  // Delete!
  $deleteresult = entity_delete
($entity_type, $entity_id);
  if (FALSE !== $deleteresult) {
    // Success, so clear the 
entry from the FileMaker Deletions Log.
    $recordID = $response['meta']
[$index]['recordID'];
    $service->your_wsclient_deletion_
log_delete_operation($recordID);
  }
}''
 
To learn about syncing FileMaker you can contact the author here! .

Popular Posts