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.