Integration Archives

Exporting Cross Platform vCards From FileMaker

September 19, 2013 by Darren Burgess 6 Comments

vCard is a standard file format for distributing contact information from applications such as Mac’s Address Book and Microsoft Outlook. These applications have the ability to export contact records into a vCard file, or import an existing vCard. We see them all the time attached to emails.

Naturally, as FileMaker developers we may consider how we can leverage this data sharing tool in our FileMaker applications. If you want to create your own vCard export, then you can head over to Wikipedia and check out the vCard 3.0 specification and use that to build your calculations and scripts to format your contact data into the required format. Store that in a field and export the field contents and you are done. You can even use FileMaker to pop it in a new email. Easy.

One Step Forward

Now, any self-respecting experienced developer is also lazy, and in the process of solving this problem I first searched to see what else had been done. Gotta get it done fast, right? So a quick search found a simple vCard export solution, by Chris Pye of MacTec. There is a sample file there as well.

You would think I was just about done now, with the only steps remaining being to integrate Chris’ scripts into my customer solution. (Thanks for sharing, Chris!) Unfortunately there is one ginormous caveat, and that is the resulting vCard, when generated from a Mac, is not compatible with Windows. And my customer works entirely on Macs but requires that vCards are cross-platform. What happens when you try to import a Mac generated vCard into Windows Outlook is… nothing. Blank fields.

It so happens you can actually open the vCard file (it is just text) in WordPad, then re-save it, and it will then open in Outlook, however I can’t really ask my customer to do that. That WordPad trick did provide me a clue however.

File Encoding Is Crucial

Turns out that FileMaker’s Export Field Contents script step defaults to UTF-8 character encoding (hey – someone fact check that for me). That format is not really relevant, other than I thought this might be why it was not working in Windows. Next I thought I would check out the free Base Elements plugin (thanks, Nick!) to see if it had a function that would do what I needed. Luck was on my side, as there are two functions that will help, BE_SetTextEncoding and BE_WriteTextToFile.

After the Export vCard script deals with constructing the vCard data in the variable $vCardData, here is what the script steps look like:

# Set the FilePath
Set Variable [$FilePath; Value:Get ( DesktopPath ) & "vCard.vcf"]
# Set text encoding to ANSI
Set Variable [$var; Value:BE_SetTextEncoding ( "ANSI_X3.4-1986")
# Create a file using the data stored in the local variable.  
# This step replaces the usual Export Field Contents script step.
Set Variable [$var; Value;BE_WriteTextToFile ( $FilePath ; $vCardData )]

Just When I Thought It Was Done

But we are not. The file generated will not open in Microsoft Outlook. Bummer. Not one to give up, I tried opening the vCard in Windows NotePad and found that there were no carriage returns, just a string of data mashed together. Clearly, Windows did not like the FileMaker “¶” character that was used to build the vCard data.

Not all paragraph returns are the same – check out the table of Unicode characters documented in FileMakers Code() function. Code(10) is a LineFeed. Code(13) is a Carriage Return (remember typewriters?!? A carriage return is a linguistic artifact from this ancient invention). Next step, and the one that finally solved the issue, was to substitute all of the FileMaker “¶” characters with LineFeed characters using the Char() function. Something like this:

Set Variable [$vCardData; Value:Substitute( $vCardData; "¶"; Char (10) )]

And now the vCard, generated from a Mac resident FileMaker database, is automagically cross-platform. Maybe next time we will work on vCard importing. See you then!

Hooking Formstack into FileMaker

June 13, 2013 by Anders Monsen 10 Comments

There are generally three ways to get web-based form data into FileMaker.

You can set up Instant Web Publishing using FileMaker Server Advanced, exposing a FileMaker layout directly to the web.
You can write a custom HTML form and submit the data using FileMaker’s PHP API.
You can use a third-party interface that works with FileMaker’s PHP API.

Using IWP seems like it requires little or no effort. However, aside from the additional expense of Server Advanced, certain programming constraints still exist. Accounting for multiple layouts or conditional fields that appear based on answers can complicate your solution quickly. A custom form requires a basic knowledge of HTML and associated web technologies (CSS, Javascript, PHP, etc.), but again runs into issues if you want AJAX-like feedback and actions.

Using a third-party option like Formstack gives you the flexibility of custom HTML technologies and offloads much of the design work. Still, design is only half the picture, as you need to integrate something like Formstack into FileMaker.

Getting Started With Formstack

The starting page for any developers is Formstack’s Developer Central page. You will need a Formstack account, and there is an associated cost with this method though you can try it for free first. For our purposes we’re going to use the Webhooks method instead of the REST API. The webhooks method sends data in a key/value format to an external URL upon the form submit action. That external URL is your PHP page, where you create the code to parse the submitted data and push it into FileMaker.

Your FileMaker file is where data will end up after the form is submitted. This will need to be hosted, and set up for fmphp access. The web interface layout(s) need only the fields that will receive data from the form. This gives you a couple of options: you can use the data separation model to them hook into this database and display your data or process it for any analytics; or, you can use the same database and build your analytics process as part the solution.

Setting Up the Webhook

Once you have built your form, you open the Settings and decide where to point the form for each submission. Note the two actions in the “After the Form is Submitted” section. This is where you point to your page that processes the data, and redirect the user after the data is processed.

To process the form data, we need FileMaker’s PHP API files, Formstack’s own class (Formstack.php) and an API key from Formstack. The Formstack items are found in Formstack’s Dashboard, in the Forms tab, in the Social Media section.

You need to write at least one file, with an optional second file. The first will process the data behind the scenes. In these instances, I have simply called it “webhook.php”. I created a second page, where the form will redirect after being processed. This is a simple “Thank you” page. You can customize the Thank You to display any text you like, and include a link back to the survey.

The Webhook Code

The webhook.php files contain PHP code to handle the data.

/* FORMSTACK SECTION */
require_once('Formstack.php');
define('API_KEY' , 'YOUR_FORMSTACK_API_KEY_GOES_HERE' );

// variable to skip extract function
$skipExtract = $_GET['skipExtract'];

$formstack = new Formstack(API_KEY); //instantiate the class

$form = $formstack->form($_POST['FormID']); //internal form ID
$form_name = $form['name']; // capture name

$data = array('form_name' -> $form_name );
foreach( $form['fields'] as $field ) {
  $id = $field['id'];
  $name = $field['name'];
  $value = $_POST["$id"];

  if($skipExtract) {
   $extract = $value;
  } else {
   $extract = fs_extract($value);
  }

  // check for sub values
  if($value == $extract) {
   $data["$id"] = "$value";
  } else {
   foreach( $extract as $subkey => $subvalue) {
    $data["$subkey"] = "$subvalue";
   }
  }
}

/* FILEMAKER SECTION */
require_once('FileMaker.php');
$fm = new FileMaker('MySurveys','hostaddress','user','pwd');
$action = $fm->newAddCommand('web_layout');

// now set the fields
$action->setField('FirstField', $data['fieldid']); // see below for the number
// etc for additional fields

$result = $action->execute();

Note that in this case for the setField action you need to know the field ID issued from Formstack. To find the Field ID, you need to go to Publish >> Advanced, copy the contents of the “Form HTML” field into a text editing program, and find the field(s) being submitted. There you will see something like id=”field1234567″ name=”field12345567″ associated with each field. You need only the number. Return to your webhook.php page and fill in the fielded associated with each field you want to map for the FileMaker database. With Formstack you also can set up notifications, view stats, and more.

Job Application Form

For an example of how the front end works in action, check out our job application form. This form is created in Formstack, displayed on the web and then imports into a FileMaker database to parse the data and keep future notes.

While there might be slightly more effort in setting this up than building your own form, Formstack allows for an easy customization of forms and does most of that work for you. The required code to hook your forms into FileMaker is fairly minimal, yet the rewards save time and effort in the long run.

Getting Started With QuickBooks Integration

March 18, 2013 by Anders Monsen 2 Comments

Intuit’s QuickBooks software organizes business information: contacts, vendors, orders, invoices, inventory, and more. Many small businesses use QuickBooks as their back-end accounting software, alongside FileMaker. Often these two applications perform separate functions, but sometimes there may be a need to integrate them. I’m going to briefly discuss one such method, using the FM Books Connector plugin (FMBC) from Productive Computing, Inc.

I’m not going to cover installing the plugin, as the files that are included with the plugin explain how, in detail. However, there are some items to keep in mind:

FMBC runs only on Windows.
You need to have QuickBooks, install the plugin into the directory FileMaker uses for plugins, and run a couple of installers that allow for XML communication via QuickBooks. Again, PCI has step by step instructions for this process, along with a sample database that will test each required piece.
QuickBooks needs to be up and running when you make your queries, edits, and new record requests from FileMaker.
Finally, the plugin needs to be registered, or it can run in demo mode for 30 days, though you will run into time-out issues while in demo mode.

On Screen Reference Manual

All requests from FileMaker to QuickBooks are made through function calls from FMBC. These function calls are contained in scripts, and at the very basic, contain steps to open the connection, send data, return a result, and close the connection. Exactly what takes place in the “send data” part depends on your action, and any data returned may include confirmation, content from QuickBooks, or an error message. FMBC comes with a handy developer’s guide, but this is only half the picture. The other half entails understanding Intuit’s On Screen Reference Manual and deciphering the XML tree.

Each action such as adding a customer, adding a sales order, editing a customer, etc., are grouped under Message Types.

When you select from the list of types, you can view all the requests, response, and XML tree. To initiate a request and retrieve a response you need to know the XML element names (i.e., field or column names) in QuickBooks. For example, adding a customer would require something like this:

Certain elements are required; others are optional, but they must appear in the same order in your script as in the XML tree. Otherwise, your result will contain an error message. Luckily, FMBC comes with a few sample scripts, and the developer’s guide contains some sample code. As with many plugins, sending the action can be accomplished by setting the value of a global field or a variable with the function call, such as:

Set Variable [ $Result ; Value: PCQB_RqNew( "CustomerAdd" ) ]

Which Message Type?

The key in writing any scripts to query QuickBooks via FMBC is first to outline which message type will be used, and then review the XML for fields in QuickBooks. From there, map the QuickBooks’ fields to fields in FileMaker, and use the appropriate function call. So, adding a customer, for example, requires the “CustomerAdd” message type and the Name. Salutation is an optional value that comes after the CompanyName. Additional fields and values are then added as needed.

Once you add the various fields you want in your new customer action, you need to deal with the result. You cannot assume the data will be added, so handling errors is critical. Successful calls via the plugin result in a zero, so checking for this value or !!ERROR!! text when sending the data will indicate whether something is wrong. If an error is detected, FMBC includes a handy function called PCQB_GetStatus that will show the error message. A few of the errors I’ve run into include values expected in the wrong order, the QuickBooks application being unavailable, and certain pre-conditions or values not in place.

Along with adding records, FMBC lets you add related records, query existing data, and edit data already in QuickBooks. Here are a couple of example “gotchas” to keep in mind.

Adding sales orders requires that items already exist in QuickBooks’s Item Inventory table.
Certain elements like Terms and Ship Via options will need to exist in QuickBooks when sending over sales order data, if you include these values. This isn’t always spelled out, and must be deciphered from error messages.
You will want to make sure updates only are attempted from Windows. So if you run a cross-platform user environment, prevent the scripts from running on the Mac OS using the Get( SystemPlatform ) function.

Integrating QuickBooks with your FileMaker solution is quite feasible, but requires careful planning and testing. Once you set up the first few connections and learn how to interpret error messages the process begins to flow more smoothly. An alternative to running each script in debug mode is to create an error table and log any errors there as they occur. Then you can review the table and pin point where the script faltered. Integrating FileMaker and QuickBooks should hopeful reduce data entry (no longer in both places), and allow users a friendly front end method to create and update data, and also to see the data you already have in QuickBooks.

Real-World FileMaker Web Service

August 10, 2012 by Anders Monsen 14 Comments

In a previous post, I enthused about FileMaker 12’s new “Insert from URL” script step. The ability to pull in data from web pages through RESTful web services is akin to the science fiction idea of wormholes into other dimensions. Programmable Web currently lists over 6,000 web services in its directory, and the trajectory only points upward. Though some web services come with commercial restrictions, and others may change without notice, getting this data into FileMaker has never been easier.

In this article I’m going to move from general enthusiasm to showing a FileMaker Pro database that I built to import book information based on ISBN values (a link to my demo file appears below). The power of “Insert from URL” grows exponentially when combined with the FMP URL protocol and FileMaker Go. In a fairly short amount of time I was able to integrate my iPhone to scan a book’s ISBN bar code from within FileMaker Go–reading the data using an app called Pic2Shop, then using “Insert from URL” to load the rest of the book information, virtually eliminating any manual data entry.

The reason I chose books for my example is that it combines two passions of mine: books and FileMaker.

Years ago in FileMaker 5.0 days, I built a database to track my book collection. Each book has certain data I need to track, such as author, title, publisher, value. etc. Entering this manually took a great deal of time. Looking for books that I didn’t have also presented problems.

With hundreds of books (paperbacks included), keeping track of books I bought years ago became a tedious effort. I would write down on index cards ones that I had or wanted, and if I happened to be in a used bookstore, I’d pull out that card from my wallet and try to find the book. With the iPod, it was possible to export some text files to the iPod, but I didn’t always carry my iPod everywhere. I carry around my iPhone, and so text notes replaced the old paper notes. However, this method is far from efficient.

When FileMaker 12 came out I rebuilt my old library database using one of the starter solutions that came with iPhone and iPad layouts. After importing my records I now had access to my library on my iPhone, and could scroll through the list.

But what if wanted to add a book? Previously I entered most of my books by hand, or scanned them using Bookpedia or some other tool where I held up the book to my computer’s camera. But I like FileMaker. It lets me control the design of my library. I don’t want to scan books on my desktop using an app, then export that to FileMaker.

Using FMP URL and Pic2shop, I can set up an interface on my FileMaker Go version to scan the book’s ISBN and use Insert from URL to load up the data, all in about one second. So, to the details.

Here is a copy of the demo file (zipped): DemoLibrary

ISBN Lookup

There are several ways to get book details from the ISBN number. You can enter a scaled down Amazon.com URL, such as <http://www.amazon.com/dp/0743273567>. This will give you a web page with all the book information that you can scrape, if you want to spend hours figuring out what to pull from the huge HTML page. You could use Amazon’s own web service API, which would return XML. However, I found that Amazon some years ago changed its method, so that instead submitting a straightforward URL, you now had to encode that URL into a key using SHA encryption. After several fruitless attempts, I gave up.

As a commercial enterprise, Amazon probably wants API users to drive traffic to buy books from Amazon, not to pull book information for a personal project. Instead, I turned to Open Library and isbndb.com, services that seem friendlier and more open. Isbndb.com requires a developer API key, but the amount of information they have is staggering. My example files show both methods, though you need to provide your own API key for the ISBNDB API portion to work. Prior to testing the isbndb actions in my demo file you need to request and input your own API key in FileMaker Pro.

Scanning Books using iPhone

In my scaled down sample database, the scan process uses four scripts. For this process I relied on a sample file that Greg Lane from Skeleton Key showed me at DevCon 2012. Greg had demoed this process on his blog almost two years ago, but his process to import the data uses a web viewer and PHP. I wanted native FileMaker 12 stuff, which I already had with Insert from URL.

The first script, triggered from a button on the iPhone, is an Open URL script step:

"pic2shop://scan?callback=fmp%3A//%24/" & Get ( FileName ) & "%3Fscript%3DScan%26param%3DEAN"

This script opens Pic2shop, and once the book is scanned, calls a script in the database called “Scan”. I modified this script from Greg’s demo file to use “Insert from URL” and then it just parses the returned JSON from Open Library with some basic text parsing. (This is why we really need native JSON and XML parsing functions in FileMaker.) The ISBNDB result is in XML, and I found an existing function that parsed XML, except for certain attributes, and so it was back to text parsing for those pieces.

This script then “Inserts the URL” from Open Library into a global field and parses the result with a semi-accurate custom function. I say semi-accurate because the data from Open Library doesn’t always behave as expected. In the instance of The Great Gatsby, the title picked up “Wiki” as the JSON came over with multiple “title” nodes. Also, ideally the function would handle names like F. Scott Fitzgerald as well as L. Sprague de Camp and Clark Aston Smith, not just First Last named authors.

This sample desktop includes a couple of fields where I pulled additional information from isbndb.com; again this requires an API key, and in this sample database that key would go in the System table. The key loads into a global variable on startup. Again, this is a demo file, so some functionality is spelled out to illustrate how things work.

FileMaker Go and iPhone

Once I moved the database to my iPhone, I could scan any new books very quickly, search for books, and even highlight any books on a wish list using conditional formatting — all very handy when far from home and trying to remember if I have a copy of this or that. Sometimes I have multiple editions, and I can tap on the title to view more details.

Conclusion

Combining Insert from URL and FMP URL, or just Insert from URL and FileMaker Go, truly gives you total control to create your own app in a pocket. With FileMaker Go and the iPhone, and all those thousands of APIs out there, just image what you could create! Not to mention turning your own FileMaker Server into a RESTful web service with Goya’s RESTfm, and consuming this as needed on your iPhone.

What are the next steps? The logical one is syncing. After all, once the book is scanned into FileMaker Go, you need your FileMaker Pro database to know this, so you don’t overwrite the books in your iPhone the next time you change your database and bring it over. I’ve heard good things about a certain SeedCode/Todd Geist product.

The Power of Insert From URL

July 27, 2012 by Anders Monsen 17 Comments

When FileMaker Pro 12 launched earlier this year, it seemed that most attention centered on a new layout design surface and the ExecuteSQL function. One example of ExecuteSQL emerged at DevCon 2012: Inspector 4.0, a database analysis tool created in FileMaker, dropped the number of table occurrences from 626 to 135 and the number of relationships from 591 to 81, largely through the use of ExecuteSQL. Yet there is another feature of FileMaker Pro 12 with vast power, at least in terms of integrating with external applications: the “Insert from URL” script step.

In previous versions of FileMaker Pro, interacting with web sites to pull data required a method called web scraping. This usually meant loading a web page in the web viewer, using the function GetLayoutObjectAttribute inside a script to pull the HTML source (after waiting to make sure the page loaded all the way in the web viewer), and then parsing the HTML with text functions.

If you wanted to get XML from web pages, such as geolocation from Google or Yahoo pages, I discovered that the web viewers and XML were not ideal for cross-platform solutions, as it generally failed to provide the XML in a Windows environment. In one instance where I needed geolocation information using Google’s KML, I had to resort to importing XML from a URL and parsing it with XSLT, a method that no longer works in FileMaker Pro 12.

With the “Insert from URL” script, it’s now possible to supply a URL in script and have it load the contents into a field.

Once you run this script, the web page data loads into the specified field. This data can consist of values like HTML, XML, JSON, and images. This enables FileMaker to interact with a variety of web pages, and opens up interaction with REST-ful APIs. For example, you could parse Google’s weather API, its host of other APIs or many other services.

Years ago, I built a library database to track all my books. This database started as a .fp5 instance, moved to .fp7, and recently again to .fmp12. I rebuilt the FileMaker 12 version from scratch to take advantage of FileMaker Go. When adding new books I don’t want to type everything, but with FileMaker Go I can scan the ISBN and bring this into the database, then using Insert from URL I can build HTML string and pull virtually all the data I need from Open Library into my book database. This includes the ability to pull in book cover art into container fields. (Stay tuned for a more detailed breakdown of this database, including the ability to scan a barcode and import all book data, using the fmp:// protocol.)

Most APIs that function this way return data as either XML or JSON (although it appears XML is on the way out), and this requires parsing out the values into the corresponding FileMaker fields. The Open Library API returns JSON or JavaScript as the response format. Unfortunately FileMaker 12 does not have native XML or JSON parsing functions (feature request anyone?), so we still need to use text parsing functions. Andy Knasinski’s ExtractData custom function is ideal for most XML parsing, except it doesn’t pull values in the element attributes, only the values between the tags, or the element itself (defined here). When parsing JSON, other problems arise, as JSON still doesn’t appear to have one single standard. Makah Encarnacao’s article on parsing JSON didn’t address all the nodes that I needed, so I ended up writing my own custom function to get the title, author, cover image URLs, etc. Even then, the JSON sometimes varies when covers are absent, or when book data contains more verbose information.

Yet in the end, the key take-away remains the same: the “Insert from URL” script step greatly simplifies previous web scraping methods, and allows for a greater interaction between FileMaker and web services. I look forward to exploring this new tool in greater detail.

ImageMagick and FileMaker

February 2, 2012 by Anders Monsen 4 Comments

A few months ago I wrote a piece on image manipulation in FileMaker container fields on Mac OS X. That type of manipulation was relatively simple – rotating an image – but required AppleScript and ImageEvents. What if you required a more complex adjustment of images, such as converting from TIFF to JPEG, or compression, blurring, re-sizing?

Image Events actually does some of these types of manipulation, but not always with expected results. Many people turn to Photoshop for these effects. You can also write AppleScripts to interact with Photoshop and FileMaker, but may end up dealing with syntax changes or application version changes. Photoshop also must be open and active, and is expensive.

Another option we looked into was ImageMagick, a powerful and free open source command line tool that also will work with multiple programming language like PHP, Perl, Ruby and others.

Installing ImageMagick

Installing ImageMagick on the Mac requires time and patience. Apparently the people who support this (free, by the way) software only maintain the latest OS-compatible version as an executable installer – meaning Mac OS Lion. The machines that would run this were still on Snow Leopard, so this required a more manual method.

The Mac OS X binary release requires MacPorts, which in turn requires XCode. These first must be installed and configured correctly. MacPorts, once installed, also required a self-update (sudo port -v selfupdate), which in our instance required tweaking rsync settings due to firewall issues. Alas, this step is poorly documented, but in our instance we changed this document: /opt/local/etc/macports/source.conf

By commenting out the last line, which uses rsync, and adding a direct url, we were able to get MacPorts to update properly.

# rsync://rsync.macports.org/release/tarballs/ports.tar [default]

http://www.macports.org/files/ports.tar.gz [default]

Then in the Terminal, we typed the following. Then we sat back and enjoyed many minutes of peace while ImageMagick installed, as this took a while.

sudo port -d sync

Finally, installing ImageMagick itself was a one line command in the Terminal:

sudo port install ImageMagick

Once installed, you can test ImageMagick by running “convert-version” in the Terminal. If properly installed, you will get something like the following:

"Version: ImageMagick 6.7.2-0 2011-09-20 Q16 http://www.imagemagick.org Copyright: Copyright (C) 1999-2011 ImageMagick Studio LLC Features: OpenMP OpenCL"

Integration with FileMaker

Since it now worked in the Terminal, it was time to turn our attention to integration with FileMaker. For this to work we needed to execute a shell statement from FileMaker. As this solution already used Troi File, which has a function to execute shell, we just needed our command. Troi File’s command TrFile_ExecuteShell( ) is used to send commands to ImageMagick. Prior to each shell action we built the command in a variable, which let us debug this in the Data Viewer.

To complicate matters, certain ImageMagick actions returned values, while others returned nothing. For example, the version command returned the string inside the execution step, so setting a variable to TrFile_ExecuteShell populated this variable with the result. This was a perfect way to test whether or not to continue, since computers without ImageMagick installed would not be able to perform anything, and we could exit gracefully if the version command did not contain any ImageMagick information.

There are several commands that we can send to ImageMagick. Each command accepts different arguments. For example, “identify” provides information about the image, while “convert” performs the actual conversions. The shell command string requires the full path to the command, which usually is inside the “/opt/local/bin/” folder.

Our primary requirement was to convert an image from one type to another, filtering and resizing the image to certain specifications. The beauty of ImageMagick and shell scripting is that you can point to a source image across the network and send the converted file to another location across the network.

The convert command uses certain parameters such as source file, resize and resize value, and where to write the file. An example of this with some fill-in-the-blank naming appears below.

"/opt/local/bin/convert path_to_source/filename.TIF -set option:filter:blur 0.8 -resize %RESIZE_VALUE% -strip -quality 90 -write \"path_to_destination/filename.jpg\" +delete"

Alas, this command returns nothing from within FileMaker. There is no way to know whether or not the conversion was successful, aside from a second command using Troi File to see if the new file exists.

Conclusion

While there are significant cost and overhead advantages to using ImageMagick over, for example, Photoshop, there is a corresponding cost in terms of time and effort. The command line doesn’t always invite users to have a great experience.

While I have not tested these features, Monkeybread Software’s plugin for FileMaker comes with image editing using GraphicsMagick, a fork from ImageMagick a few years back. Other plugins provide execute shell commands, so you’re not just limited to Troi File.

Trapping for errors remains important, as sometimes you get feedback, and sometimes nothing, from within FileMaker. Usually you can copy the command from the Data Viewer into the Terminal, and then you will get some feedback about any errors. That said, this is a powerful method to integrate image manipulation into FileMaker.

SyncDek: An Alternative for Audit Logging

July 14, 2011 by Kirk Bowman Leave a Comment

Auditing logging is a common request from customers—the ability to track database activity including adding, editing, and deleting records, who changed it and when. In some types of businesses, like medical and financial, the law requires it. Generally, there are two approaches to audit logging in FileMaker, either native functionality or a third-party tool.

WorldSync has two products that provide audit log functionality. FMDataGuard is a client-side (FileMaker Pro) plug-in that can log database activity using function calls in various calculation windows in FileMaker Pro (security, validation, etc.). SyncDek is a separate server application that communicates with FileMaker Server Advanced via JDBC to log database activity.

While FMDataGuard works well in typical situations, there are some performance considerations in high transaction and network environments. The reason is all the effort to log database activity happens client-side and must travel over the network in a multi-user environment. SyncDek provides a solution to performance issues because it moves a significant amount of the effort to log database activity to the server.

Case Study

Earlier this year we developed a new medical research database for one of the top cardiothoracic surgeons in the United States. Since the database contains “identifying” patient information, it must log database activity. We decided to implement FMDataGuard to meet these HIPAA requirements.

The database was deployed in a Citrix environment with FileMaker Server. During beta testing, the users reported slow performance that we determined was due to the overhead for FMDataGuard. Per the recommendation from Jason Erickson at WorldSync, we implemented SyncDek to replace FMDataGuard and the performance issues were resolved.

SyncDek Approaches

SyncDek is primarily known for database replication (syncing). However, it has additional functionality including backup recovery and audit logging. SyncDek offers a couple of different approaches to audit logging. The “PubFlag” method queries the status of the PubFlag field in each table to determine the activity to log. The advantage of the PubFlag method is that it is compatible with FileMaker Go (iPhone/iPad) and server-side scripting with FileMaker Server.

The “Live Posting” method uses the SyncDek plug-in (not the same as FMDataGuard) to push a request to log activity to the SyncDek server. The advantage of Live Posting is the auditing of activity can be near real-time. Live Posting also supports logging views, which the PubFlag method does not.

In our case we opted for the PubFlag method for logging adds, edits and deletes to move as much of the effort to the server as possible. Since logging views was also a requirement for this customer, we used Live Posting although the client-side impact to log views is light except for large found sets.

Digging Deeper

To gain a better understanding of what’s happening under the hood, I created a contact database with 5,000 records and implemented each method in turn. SyncDek uses a “temp” table in FileMaker to store requests to log database activity. It also stores the audit log in FileMaker (although it is could be a SQL table).

I opened the contact database from FileMaker Server and created three windows—one for the data entry layout, one for the temp table, and one for the audit log table. By creating, editing and deleting records in the data entry window, I was able to see SyncDek processing records in the temp table and creating records in the audit log table. If you implement SyncDek, I strongly recommend using this practice to see how SyncDek works.

To log deletes using the PubFlag method, WorldSync recommends using a custom menu to override the Delete Record and Delete All Records commands. The custom menu calls a script to change the value in the PubFlag field to “4” to mark the record for deletion. SyncDek then performs the delete and stores it in the audit log.

To log views using the Live Posting method, the Sync_LogView function is added to the limited View privilege for each table under Security. If you have three windows open, you will see SyncDek post a record to the “temp” table for the view. Then during the next cycle, SyncDek will track the view in the audit log table and delete the record from the “temp” table. Of course, this requires the SyncDek_TCP plug-in to be installed with FileMaker Pro.

Alternate Techniques

Once I had an understanding of how SyncDek works, I started experimenting with some alternate techniques. When the user performs a Find and SyncDek is logging views, it creates a View record in the “temp” table for every record in the found set. For founds sets of a few hundred records or more, this can have a negative impact on performance. My challenge was to determine a way to log a view for only the first record in the found set immediately after performing a Find.

My first idea was to use a custom menu, the same technique as logging deletes with the PubFlag method. This would override the default behavior of the Previous and Next commands in the Go To Record submenu. Unfortunately, the Previous and Next commands are not available in Custom Menus. Specifically, I wanted to use the “Based on existing command” option so logging would occur even if the user clicked the “Go to previous/next record” buttons in the FileMaker toolbar. (Request to FMI: Please provide the ability to override all commands and subcommands in the standard FileMaker menus via Custom Menus.)

For my second attempt, I tried to create a limited View calculation to check a condition and decide whether to call the Sync_LogView function. I tried several variations including setting a global variable via a script or conditional formatting, and using the Get ( WindowMode ) function. Unfortunately, the order of execution in the limited View calculation does not return a consistent result. If there were a way around this limitation, there are several possibilities to implement this technique.

For my third attempt, I setup an OnRecordLoad trigger to run a script with the Sync_LogView function. It works! The downside is the script trigger must be setup separately for each individual layout. Also, since it is implemented at the interface layer (layout) instead of the data layer (security), it may not meet the requirements in some regulatory situations.

On the upside, the script for the OnRecordLoad trigger, can be modified to work natively with FileMaker (without a plug-in). This replaces the Sync_LogView function by creating a record directly in the “temp” table. (I got this idea while watching activity in the temp and audit tables in the three-window scenario.)

Conclusion

Audit logging is one of three core features available with SyncDek. If you are already using the backup/recovery or syncing functionality, it is a no-brainer to use the audit logging, if you need it. If you are using FMDataGuard or another product/technique, SyncDek provides an alternative to address performance issues on the client-side.

PHP Development for FileMaker Developers

April 29, 2011 by Anders Monsen Leave a Comment

PHP is a fairly flexible language, where developers can create sites using linear or procedural code, or complex object-oriented code. The fact that you need to type most of your code and understand a new syntax is daunting to anyone, especially FileMaker developers who are used to the built-in GUI, native script steps, and pre-existing functions. The ease of setting up a database in FileMaker is one of the primary draws of this software. Despite the more manual effort required when writing PHP, there are many analogies FileMaker developers can use to become more comfortable with the code and process of PHP.

FileMaker solutions generally contain three elements: the data, the logic, and the user interface. In web-based solutions PHP acts as the logic layer. The data exists elsewhere, either in MySQL tables, FileMaker databases, or some other external database. The user interface relies on HTML, CSS, Javascript, Flash, and other interface tools. By finding common ground between certain FileMaker features and PHP scripts we can ease the transition from one to the other.

The core of FileMaker’s logic layer is scripting and calculations. FileMaker scripts are comprised of built-in script steps with various options or parameters. FileMaker calculations combine elements such as text strings, numbers, fields, operators, and built-in functions. Developers can write custom functions, and external plug-ins create more functions. PHP pages often appear side by side with HTML, but strip down the code to just PHP and the appearance is much like a FileMaker script — a sequence of statements, such as variables, function calls, conditional branching, loops, etc.

Although there is no list of script steps to select from in PHP, PHP statements are made up of a series of steps, just like in FileMaker. Just as new FileMaker developers stumble when trying to select appropriate script steps to build specific scripts, PHP developers need to familiarize themselves with certain key functions. For example, a foreach loop in PHP could be expressed in this way:

foreach (array as $item) {  <##> }

Here the array, $item, and <##> are parameters where the developer must consider content and format, much like Set Field [table::field ; value ] contains two different parameters.

Another approach would be to separate PHP functions into two groups: those that look like scripts and those that look like calculations. In the latter group you have built-in PHP functions like substr, number_format, in_array, etc. These tend to appear as short, single-line calculations with one or more parameters. A few years ago Jonathan Stark created a reference page to map such calculations. Here we can see many parallels between FileMaker and PHP, such as Left( “FileMaker Pro Advanced” ; 4 ), which is expressed as substr( “FileMaker Pro Advanced”, 4 ) in PHP.

Another useful analogy takes place in the use of variables. FileMaker variables take the form of local (single $ followed by a text string), and global (double $$ followed by a text string) variables. Most PHP variables start with the single $ followed by a text string. Although $$ variables exist, these are called “variable variables” in PHP, and behave completely differently from FileMaker’s global variable, such that novice developers should not use these.

Just like the Set Variable [$varName ; Value:”varValue” ] script step in FileMaker, PHP takes a similar format, without the “Set Variable” declaration. Instead, the variable is declared and set with the single equal sign.

$phpVariable = substr( "FileMaker Pro Advanced", 4 );

This sets the value of $phpVariable to “File.”

Understanding variables implies their use within functions. If you declared a variable to “FileMaker Pro Advanced,” you could then use this variable in the function.$versionName = “FileMaker Pro Advanced”;$phpVariable = substr($versionName, 4 );The next logical step is to make the contents of $versionName dynamic, either as the result of a form submit action, or a query. Novice PHP developers run into issues when comparing data, as something like if( $myVar = “this”) actually sets the variable, while the double equal sign is the correct method: if( $myVar == “this”).

The essence of integrating FileMaker and PHP is expressed in four requirements: Creating records, Reading or retrieving records, Updating records, and Deleting records (or CRUD, for short). Total integration does not stop with these four elements. Getting layout information and value lists can make interfaces more dynamic. There are functions to execute FileMaker scripts and gather portal information, as well as critical error-trapping methods. However, CRUD is the building block upon which everything else stands.

One of the earliest and most concise write-ups on FileMaker’s PHP API came from Six Fried Rice on PHP API and FMP 9 from May 2007. All the points made here remain valid. Jonathan Stark and Chris Hansen’s comparative essay on FX.php and PHP API – is an excellent overview of the two PHP classes.

Some integration examples of FileMaker and PHP include Joomla, a content management system (CMS), detailed by the folks from myFMButler on integrating FileMaker and Joomla. Another popular PHP framework is CodeIgniter. With minor modifications it is possible to integrate this framework with FileMaker using FX.php.

Further, Google maps, which combine PHP and Javascript, can be integrated into FileMaker through the Web Viewer. A PHP page resides on a server. FileMaker interacts with this page, sending data from each record into the PHP script, which then interacts with Google through their API, and displays the result in a Web Viewer on the FileMaker layout.

Recently Matthew Leering’s post on Google Analytics and FileMaker showed how you can gather data from arrays and insert these into FileMaker. There are several web services that offer APIs to place arrays of data into PHP variables through queries, which developers then can turn around and insert into FileMaker records and fields.

Integration between FileMaker and PHP works both ways, making FileMaker one of these most flexible and powerful database solutions available to individual and enterprise users. From creating custom web front ends to your FileMaker database, integrating with existing CMS frameworks, and writing scripts that import data from web sources into your solutions, the marriage of PHP and FileMaker has tremendous potential. The above examples show just a few of these opportunities.

Image Rotation and FileMaker

April 19, 2011 by Anders Monsen 3 Comments

Recently I was asked whether it is possible to rotate an image in a FileMaker container field. As far as I know this only is possible by exporting the image, using another application to rotate the image, and then re-importing it back into FileMaker. This solution would exist only in a Mac OS X environment, so I turned to AppleScript, and the built-in image manipulation app called Image Events.

Although I am sure refinements can be made to the process, and it currently only works on the Mac OS, the process is fairly straightforward. The file has a container field which stores an image. Another field stores the temporary path for each record’s image. I decided to capture the direction of the rotation (clockwise or counter-clockwise) as a script parameter and set up some basic buttons to handle the process for the demo. To rotate clockwise the angle is 90 degrees, while for counter-clockwise it’s 270 degrees.

Since the AppleScript uses the database name and layout name, I captured these as variables. I also set the AppleScript itself as a variable so I could step through the script and see what it generated. I then built the path for the export, and also converted the slashes to colons for the AppleScript path. Immediately after exporting the image, I ran it through the Image Events to rotate it, and then followed this up with an insert image script step, grabbing the rotated image from its Finder location. I added a platform check so this would run only on the Mac.

There are at least two plug-ins that accomplish very similar goals. 360Works’s free ScriptMaster rotates an image based on a URL, so if this image resides in a container field you first will need to export the image, apply the rotation with the ScriptMaster function, and import the modified image. CNS Image will actually rotate an image inside a container field, and has many other useful features; prices can be found at the CNS web site. Both plug-ins are cross-platform, unlike the AppleScript solution outlined here.

Sample File: ImageRotator.fp7

One Step Forward

File Encoding Is Crucial

Just When I Thought It Was Done

Getting Started With Formstack

Setting Up the Webhook

The Webhook Code

Job Application Form

On Screen Reference Manual

Which Message Type?

ISBN Lookup

Scanning Books using iPhone

FileMaker Go and iPhone

Conclusion

Installing ImageMagick

Integration with FileMaker

Conclusion

Case Study

SyncDek Approaches

Digging Deeper

Alternate Techniques

Conclusion

Let’s get started on your project.