MightyData

FileMaker and WordPress Consultants

  • Company
    • About
    • Contact
    • Team
    • Artists
  • Services
    • Consulting
    • Development
    • Our Process
    • Coaching
    • Training
  • Results
    • Customers
    • Success Stories
    • Testimonials
  • Blog

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.

Bookcase shelves full of books

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.

FileMaker script to scan book with iPhone

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.

Book database in FileMaker Pro

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.

Book database in FileMaker Go (iPhone)

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.

Filed Under: Barcodes Tagged With: Demo file, FileMaker Go, Insert From URL, Integration, Web service

Comments

  1. WesL says

    September 6, 2012 at 4:50 pm

    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?

    Reply
  2. Anders Monsen says

    September 7, 2012 at 9:23 am

    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.

    Reply
  3. WesL says

    September 7, 2012 at 11:34 am

    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!

    Reply
  4. Mike says

    September 12, 2012 at 9:47 pm

    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!

    Reply
    • Anders Monsen says

      September 14, 2012 at 9:17 am

      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.

      Reply
  5. Paul says

    January 10, 2013 at 7:47 am

    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

    Reply
    • Anders Monsen says

      January 10, 2013 at 10:39 am

      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

      Reply
  6. Joe G says

    January 28, 2013 at 12:45 pm

    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

    Reply
  7. Anders Monsen says

    January 29, 2013 at 9:31 am

    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

    Reply
  8. Chris A says

    February 10, 2013 at 1:57 pm

    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.

    Reply
    • Anders Monsen says

      February 11, 2013 at 11:28 am

      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

      Reply
  9. Peggy Conant says

    July 2, 2014 at 9:35 am

    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

    Reply
    • Kirk Bowman says

      August 31, 2014 at 8:54 pm

      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

      Reply
  10. Anna M. Lithgow says

    May 17, 2016 at 6:48 am

    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

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Let’s get started on your project.

It will be more fun than you think.

Get in Touch

  • Company
  • Services
  • Results
  • Blog

Copyright © 2023 · Parallax Pro Theme on Genesis Framework · WordPress · Log in