Web service Archives

Using a Web Service to Generate Barcodes

September 4, 2013 by Anders Monsen 4 Comments

Recently I came across a web service over at ProgammableWeb that generates barcodes. This might be useful if you scan barcodes, and want an alternate method to create barcodes in your FileMaker solution.

The web service, Barcodes4.me, is a RESTful service and requires no API or fee. It supports a handful of barcodes types, as well as QR codes, and requires a simple URL call. Using FileMaker 12’s Insert from URL you can send a query to this web services and insert a barcode image into a container field.

Given that this is a free service, there is no guarantee how long it will remain available or a free service. The types of barcodes available also are limited. This is still a good option to keep in mind when developing and testing a mobile solution with barcodes. I have included a small demo file to show this in action.

WebServicesDEMO.fmp12

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.

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.