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.
I agree the Insert from URL script step is very useful!
You wrote “importing XML from a URL and parsing it with XSLT, a method that no longer works in FileMaker Pro 12” which I believe is not true, it is still possible to import XML files and do XSLT transformations in FileMaker 12.
Rolf, thanks for your comment. I realize now my statement was ambiguous. I meant to say that my process with the XSLT parsing did not work, not the blanket statement about FileMaker 12. The process for my script stopped working in FileMaker 11, even though the exact same process had worked fine in versions 9 and 10. Possibly something changed at the source, but after 11, the Import results in “XML/SQL information is not enough to proceed with import/export.” I have not tested this with other XML/XSL imports, but the switch to Insert from URL now renders this moot (at least for me).
Insert from URL works great for a single piece of data, but the “now broken” import records from XML that you experienced is frustrating. Have you ever found a means by which to help Filemaker through the import without style sheet?
What’s also sorely lacking is the ability to provide credentials, when required, for XML data sources.
Cody, the lack of tools for authentication with web services in FileMaker limits what you can do, especially natively. I have used the BaseElements plugin to work around some of these issues.
Cody, since switching to the Insert from URL method I have not looked again at possible stylesheet options. I hope future enhancements to this function will get around the credentials issue, as well as better native JSON interpretation.
Anders – great article!
Insert from URL is another great aspect of FileMaker 12 that we’re just scratching the surface of. The number of web APIs and Web Services based on SOAP, REST, XML, JSON is amazing. And it’s growing daily.
Unfortunately, as you mention, the implementations of “web services” are like a box of chocolates: you never know what you’re going to get. But when you find a web service or API data source that works, it opens up a tasty new world in FileMaker.
+1 for native XML/JSON parsing
Jay, thanks for your comments, and the link to the API blog.
I’ve spend quite a bit of effort looking into APIs for getting information about books using ISBN via web services. There are several different web services one could use for this, each one with advantages and disadvantages. Some require API keys, some are open. Some require keys and are very restrictive and specific in how keys and URLs are submitted (cough, cough, big online book-and-everything-else seller, cough, cough). Yet despite the jungle of methods involved, with Insert from URL it simplifies connecting to these services, and like you say, opens up a whole new world for FileMaker.
Taking Insert from URL and combining this with FMP URL and FileMaker Go, and you can actually build “apps” for the iPhone/iPad with FileMaker. And wow, what a brave new world that is!
Anders, yes its exciting to see what is possible when you “Re-Think” your solutions. For me DevCon was also great and always learning new ways to improve solutions.
As you mentioned insert from URL is really powerful. Needs to have just a little more added to it to really allow us to interact with web services etc., but nice to see them going in this direction.
Another really powerful sleeper feature is FMP URL. In the keynote Andy also showed a visualization that we do with InspectorPro 4. All your scripts and all the references to each of your scripts (be they scripts or layout objects or script triggers etc.). All being visualized in one view. I find this incredibly powerful. This is done with the web viewer, the D3 javascript visualization library, JSON, and FMP URL. We embed a parameter inside the JSON values and on click of one of the nodes we call FMP url calling a script and passing a parameter. That is how we then can end up finding the script and showing the steps.
It seems to me this is really exciting and that FileMaker should explore giving us more (bidirectional) ways to interact with these kinds of things.
+ 1 for native XML/JSON parsing
Vince, I couldn’t agree with you more. The FMP URL is another great feature, and it would be nice to see more native simplicity in FileMaker Pro for this feature. Using it with FileMaker Go for iPhone and iPad seems to work seamlessly, but appears to require more tools in the Pro version (web viewer, javascript, JSON, and FMP URL). I was amazed at what you did with this in Inspector 4; the visualization is more than stunning, as it adds a new dimension in understanding a FileMaker solution.
That bidirectionality clearly is the future. I think we saw this start with ESS in FMP 9, and Insert from URL/FMP URL works not just with FileMaker and the outside world, but within FileMaker as well, as Inspector shows.
I had been doing insert URL’s from the 360 Works scriptmaster for a long time, which was a neat feature…. except you never knew if you had the whole web page and you often had to loop through to figure out if you had the whole web page or just part of it. This is a nice improvement. Thank you FileMaker and thanks for this blog, Anders!
Thanks, Taylor. I think some cool FileMaker Go apps could come out of Insert from URL combined with some RESTful service. I had an epiphany about that today, with RESTfm as the third piece, and when I went to the Goya website it appears they’ve already thought that’s a future possibility. Image your data accessed only when needed and with a small footprint! I do think I need to check out ScriptMaster for some JSON parsing, though.
Hi I am investigating FileMakers potential for a customer.
He wants mainly to insert and update information in FM using XML technologies – preferrably through a HTTP invocation.
We are aware of the fact that the XML has to be transformed into a format dictated by FM.
Can any of you guys shed some light on whether this approach is realistic and possibly refer til material on the subject?
Thanks in advance
Søren Dalby
Soren, with InsertFromURL, FileMaker retrieves data from another system, usually a web server. Your customer wants to change data in FileMaker from the other system. You could provide a web service for the other system to communication with FileMaker. Check out Web Services Manager.
How do you return a JSON data array and how do you process that array using Filemaker. Busy doing a Cash Loans / Pawn Shop business shop
If you Google “FileMaker JSON” you will find several options including:
1. Modular FileMaker – http://www.modularfilemaker.org/module/json/
2. BaseElements plugin – http://www.goya.com.au/blog/filemaker-and-json
3. Web Viewer – http://www.seedcode.com/filemaker-webviewer-javascript-calculation-engine/
It was a nice read!.
Thanks for the share. 🙂
hOur company provides supreme quality pills. Look at our health contributing site in case you want to feel healthier. Our company offers herb-based health products. Look at our health contributing portal in case you want to feel healthier. Our company provides a wide variety of non prescription products. Look at our health portal in case you want to strengthen your health with a help general health products. Our company offers a wide variety of non prescription products. Take a look at our health website in case you want to to improve your health with a help health products. Our company provides a wide variety of non prescription drugs. Look at our health site in case you want to strengthen your health with a help general health products. Our company offers a wide variety of non prescription products. Take a look at our health website in case you want to look healthier with a help health products.
Our company offers a wide variety of non prescription drugs. Take a look at our health website in case you want to to feel healthier with a help of health products. Our company offers supreme quality non prescription products. Visit our health contributing website in case you want to look healthier. Our company offers a wide variety of non prescription products. Take a look at our health site in case you want to to improve your health with a help health products. Our company offers safe healthcare products. Look at our health contributing website in case you want to improve your health. Our company offers a wide variety of non prescription products. Visit our health site in case you want to look better with a help health products.