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.
Great post! This really helped me out. One suggestion for anyone else, make sure your file doesn’t have any spaces.
Also, do you know if you can use url calls like this to open the camera app to snap a photo and return?
Thanks! I agree with the file name convention. Best not to have to deal with spaces in the file name, or any script name, even though these could be URL encoded.
In FileMaker GO, if you have a container field and want to snap a picture and insert, just tap in the container field. This will bring up an option to select a photo from a library, or take a new one.
It might be possible to use an app like Instagram if you want to edit the photo before bringing it into FileMaker Go, but I have not found anything about a url scheme for the native camera app yet.
I guess that I’ll have to look into a third party app then. I am looking for a way to bypass the container options. Thanks again for the help!
Can you create a post that outlines the process step by step. A tutorial of sorts.
I am a newbie so I don’t quite understand all of this.
Thanks!
Thanks Mike, that’s a good idea. I’m planning further explorations in this area, and I’ll try to make some of those post more of a step-by-step instruction. We also offer classes and coaching sessions if you are more interested in this topic.
Hey Anders,
great stuff you wrote and it did the job .. or better the app 😉
Say i did purchase the pic2shop pro app .. and got this problem .. the EAN-code did not return 🙁
Any idea what my problem is ?
This is the line i used :
“p2spro://scan?callback=fmp%3A//%24/” & Get ( FileName ) & “%3Fscript%3DScan%26param%3DEAN”
so basically the same as you have .. but so for no luck
Thanx for any idea where to luck for
Hi Paul,
From what I can tell your url scheme looks valid. I don’t know if Pic2Shop Pro’s “format” parameter is required, as I see that’s missing from your url.
Anders
Hi Anders,
Great work! I too have a library I’d like to collect book data. I tried your solution and most of it appears to work. But I’m not sure where I put my API key. I did enter it in the System layout but I continue to get the error message: No API key.
Am I doing something wrong??
Thanks
Hi Joe,
I placed my key in the System layout in the key field. This field is set up as a global field. In the opening script there is a step to set the key into a global variable. You can alter this step if you want and refer to the global field when checking for the key in the “ISBN Db API Check” script and “Import Book Summary” – you can either point to the global field, or you can hardcode the API key in the script. They key is to create a URL with that API key, as in below. I set this into a variable to see the URL in the Data Viewer to make sure the API Key and ISBN showed up in the URL.
“https://isbndb.com/api/books.xml?access_key=” & KEY_GOES_HERE & “&results=texts&index1=isbn&value1=” & ISBN_GOES_HERE
Regards,
Anders
Great ideas for looking up data from a website. I was looking for a means to do the exact same thing.
I did notice one bug in the parseJSON custom function when it deals with the value of publish_date. Since it’s being passed a comma as the ending character it truncates the resulting value after the Month and Day. If you modify the _end value as done in the below code it resolves this issue and returns the Month Day, Year value as returned from the Open Library lookup. Of course this assumes that the data returned from the Open Library lookup will always be in a “MonthName Day, Year” format…
Let( [
_clipboard = JSON ;
_length = Length( startString ) ;
_position = Position( _clipboard ; StartString ;1 ; 1 ) ;
_end = If ( startString = "publish_date" ; Position( _clipboard ; endString ; _position + _length ; 2 ) ; Position( _clipboard ; endString ; _position + _length ; 1 ) ) ;
Result = Middle( _clipboard ; _position + _length ; _end - _position - _length )
];
Trim( Substitute( Result ; [": " ; ""] ; ["\"" ; ""] ; ["}";""] ; ["{" ; ""] ; ["[" ; ""] ; ["]" ; ""]) )
)
Anyways thanks for the tips and sample file it helped give me some new ideas for my own solution.
Hi Chris, Thanks for stopping by and noticing this error with the publish date. I found that parsing JSON often required dealing with exceptions, and this proves the point. Possibly there are other exceptions that spring up now and then, since sometimes the JSON returned from Open Library contains varying information. The function I wrote appeared to handle most of the requests that I made, but certain books will have multiple authors, images, and other information that this example didn’t account for. Glad this article helped with your ideas.
Regards,
Anders
Wondering if anyone has tried using this kind RESTful solution with a URL that requires authentication? I would like to use a json parsing scenario for bringing in web form submission data, but the results would definitely be available only to restricted users.
Peggy Conant
Peggy – This is one limitation of the Insert From URL script step. It does not have tools to handle authentication like OAuth. Take a look at the Base Elements plugin. It has some additional HTTP header functions. http://www.goya.com.au/blog/free-baseelements-plugin-featured-filemaker-solutions
Hi, I am a newby to Filemaker, i tried to use the insert from url with the new api version of isbndb.com, changing xml to json but it didn´t work so
The example is this “http://www.isbndb.com/api/v2/json/myapikey/books?q=science”
but how can i add access_key=&index1=isbn&value1=”
so i can get all the data from isbndb not only summary and notes?
Thanks
Anna