Joe’s Amazing Technicolor Weblog

We last looked at this a few months ago, but have been revisiting it to come up with something a little more robust. Notes:

• there’s a good overview of using PHP with FM at the sixfriedrice blog.
• the API for FileMaker’s PHP interface is available at http://YOURSERVER.URL:16000/docs/PHP%20API%20Documentation/index.html
• I’d missed this last time around, but accounts & permissions are a little funky. fmphp needs to be added to the Extended Privileges of the database you’re trying to get to, and must have the same privilege set as the account you’re connecting as.
• The solution we settled on is a CLI PHP script running hourly, checking for mail to send. Launchd would be the logical way to do the scheduling, but always drives me nuts. Fortunately the server in question has cron set up (so much simpler!)

The code we’re more or less using:

#!/usr/bin/php
<?php
set_include_path(get_include_path() . PATH_SEPARATOR .
  '/Library/FileMaker Server/Web Publishing/publishing-engine/php/lib/php/');
require_once('FileMaker.php');

echo "PHP email-sending-script, running at " .
  date('m/d/Y H:i') . "\n";
$layout = 'Outgoing_Email';
$fm = new FileMaker('Layout Name');
$fm->setProperty('username', 'your filemaker username');
$fm->setProperty('password', 'your filemaker password');

$findCmd =& $fm->newFindCommand($layout);
$findCmd->addFindCriterion('Sent_Flag', '< 1');
$result = $findCmd->execute();
if (FileMaker::isError($result)) {
  if ($result->code == 401) {
    exit("No emails to send.\n");
  } else {
    exit("trouble: " . $result->message . "(" . $result->code . ")");
  }
}

$records = $result->getRecords();
foreach($records as $record) {
  echo "To: " . $record->getField('Recipient') . "\n";
  echo "Subject: " . $record->getField('Subject') . "\n";
  $headers = array(
    "From: filemaker@example.com",
    "MIME-Version: 1.0",
    "Content-type: text/html"
    );

  /*
   FM helpfully encodes < and >...
   */
  $body = preg_replace('/&lt;/', '< ', $record->getField('Body'));
  $body = preg_replace('/&gt;/', '>', $body);
  $rc = mail($record->getField('Recipient'),
       $record->getField('Subject'),
       $body,
       implode("\r\n", $headers)
      );
  if ($rc) {
    $update = $fm->newEditCommand($layout, $record->getRecordId());
    $update->setField('Sent_Date', date('m/d/Y'));
    $update->setField('Sent_Time', date('H:i'));
    $update->setField('Sent_Flag', '1');
    $result = $update->execute();
    if (FileMaker::isError($result)) {
      exit("trouble updating the database after sending email: " .
        $result->message . "(" . $result->code . ")");
    }
    echo "Mailed!\n";
  } else {
    exit("Mail didn't work.\n");
  }
}

We already had a serviceable in-house email server, so why change? In no particular order,

• Cost savings. We’d been spending $1k+ annually on postini’s spam protection service alone, which Google Apps conveniently bundles in for free. Also a maintenance contract on the email software, depreciation on the server, electricity
• One less server to manage
• Far superior webmail and group calendaring to what we’ve been using

When first thinking about doing this conversion I talked to a bunch of people whose organizations were already using google apps. Nobody really had any complaints. Given the upside, I certainly don’t feel like I’m in a position to complain, either, but I thought it’d be worth mentioning a few issues I encountered:

• I applied July 22nd for nonprofit status, and heard back (in the affirmative) on August 5th. Fortunately, it’s entirely possible to switch over with just a standard account, but I wish I’d applied earlier and not had to worry about whether or not that was going to go through.
• While it’s nice that there’s an open source LDAP solution for syncing user accounts, it has had some issues and even when working it only handles people’s names, email addresses, and passwords. At the time I was working with it, the best bet was to use the latest tarball, into whose directory I had to copy the above-mentioned file. Even after that, I continued to have enough trouble to abandon ship — was quicker to do the work by hand than to fix the bugs.
  In case anyone’s also trying to hook this tool up with Apple’s Open Directory, I had some initial luck with the following:
  Command: set ldap_url LDAP://servername.example.org
Command: set ldap_base_dn dc=servername,dc=example,dc=org
Command: set ldap_user_filter (objectclass=apple-group)
  though it doesn’t look like anyone’s worked through the mapping from Apple’s schema.
  Beyond just working stably, it would be great if group membership could be used to configure mail lists — I see that’s listed under possible improvements (bottom of page). I’ll try to take a look at that some day & see how feasible it would be to work it in.
• Transferring old emails is a pain if you’re not on a mainstream email server (raise your hand if you’ve heard of Stalker Software’s Communigate Pro). We’re doing it by setting both the old and new servers up in the same email client & copying messages over through it, but then we lose the date emails were sent / received.
• I couldn’t find any way to turn on IMAP access across the board, so I did a lot of logging in & setting people’s account preferences. This wouldn’t be a big deal if it weren’t for the old email transferring issue mentioned above.
• While it’s possible to create an email list containing all addresses in the domain, only admins can send mail to it; to get an all-staff list, I had to actually type everybody’s address in. More motivation to work on the LDAP group-to-list mapping mentioned above.

Some context — my new gig features a big ‘ol FileMaker installation, which has a number of automated maintenance routines. Some of those routines send emails, through a convoluted process involving FileMaker calling a GUI MUA (e.g. Mail.app). There are a number of practical problems with this, such as the requirement of another computer running, always logged into an account capable of sending the emails.

We’d done a bit of research about strictly server-side alternatives, and found surprisingly little. The best resource was Graham Sprague’s page about sending emails via FileMaker’s XSLT Web Publishing tool. We gave that approach a try, but didn’t get any results, or anything useful from FileMaker’s logs to explain why things weren’t working. I’m not sure what FileMaker version Graham’s example was written for, perhaps something’s changed with version 9?

Rather than dive into FileMaker’s proprietary XSLT system to debug things, it occurred to me that this might be a job for FileMaker’s PHP API. Sure enough, after about 15 minutes of consulting the API Doc, we were sending emails based on the contents of a FileMaker record.

We’re still working on ironing out the details, but here’s the rough proof of concept PHP file. It works with the example email database from Graham’s XSLT sample, with the php permission added to the database. Plenty of missing features such as cc & bcc fields, actually checking for the ‘send’ flag, checking for errors, any kind of authorization or authentication, etc. In other words, you probably don’t want to be running this on a publicly accessible webserver, but at least it presents the basic idea in a simple form.

<?php
require_once('FileMaker.php');
$fm = new FileMaker();
$fm->setProperty('username', 'send_email');
$fm->setProperty('password', 'whatever_the_password_is');
$fm->setProperty('database', 'Email');

$findCmd =& $fm->newFindAllCommand('Utility_Email');
$result = $findCmd->execute();
$records = $result->getRecords();
foreach($records as $record) {
  $headers = array("From: " . $record->getField('from'));

  mail($record->getField('to'),
       $record->getField('subject'),
       $record->getField('message'),
       implode("\r\n", $headers)
      );
}
echo "Emails sent";