Feeding FileMaker

the joy of json

A couple of weeks ago, Brent Simmons and Manton Reece announced the release of JSON Feed. This is a specification for a news feed format in JSON.

Wheel recast

I can hear you now. A new news feed specification? You cannot be serious! 🎾 What’s wrong with proven specs such as RSS or Atom?

And perhaps a few quieter, more timid voices. What’s wrong with XML?

And there you have it. JSON is a very straightforward format for software development, and because of that it’s now supported out-of-the-box by all manner of software products, and because of that it’s use as a data format has become the new normal.

Using JSON greases the wheels to wider adoption.

FileMaker

Right on cue, as if to demonstrate my point about the widespread adoption of JSON, the 30 year old database product FileMaker recently grew to version 16 and one of the headline new features was out-of-the-box support for exchanging data with other applications using JSON.

It may not quite top the Tiobe Index, but I have used FileMaker on and off across just about all of those 30 years. And consequently — quietly, timidly, let me tell you that I love it!

I’m pretty sure you’ve already realised it, but these two unrelated and yet related events have prompted me to look at implementing a JSON Feed newsreader in FileMaker.

If you have FileMaker and want to play along, you can download the example database that accompanies this article.

Database

FileMaker is a database first and foremost. And all good databases must start with a data model. To model JSON Feed, you need look no further than the spec. Ignoring a few subtleties, there is a Feed and each Feed has many Items. So when simplified a bit the data model looks like this

Curling ⛸

FileMaker makes use of the open source utility curl to provide the capability to download a JSON Feed. You use the Insert from URL script step to invoke cURL and in my example solution it looks something like this:

Insert from URL [
    Select;
    With dialog: Off;
    $json; // 1
    Get ( ScriptParameter ); // 2
]

Copying and pasting FileMaker scripts into a blog post is not that straightforward, so bear with me on this but referring to the numbered comments I have added above:

1. $json is the name of the variable into which I want to store the retrieved JSON
2. The script accepts a parameter, which is the URL of the feed to be fetched and the Get(ScriptParameter) is the FileMaker function you use to retrieve that parameter.

So if everything works as planned when the script is executed I end up with a variable $json that contains the JSON retrieved from the feed URL and I pass this to my next script processJSON [json].

Parse the JSON

The job of the processJSON [json] script is to parse the received JSON and store it in the database. It does this by first assigning the received script parameter into a variable $json and then using the new FileMaker JSONGetElement function to parse the required field from that variable. Here's a link to the documentation for JSONGetElement if you want to find out more.

The actual JSON retrieved for a JSON feed looks a bit like this

And since we’ve modelled our database on the JSON Feed spec it’s an easy job to copy the data from the feed into the Feed table in the database

Set Field [ Feed::version; JSONGetElement ( $json ; "version" ) ]
Set Field [ Feed::title; JSONGetElement ( $json ; "title" ) ]
Set Field [ Feed::home_page_url; JSONGetElement ( $json ; "home_page_url" ) ]
... and so on

If you’re not familiar with FileMaker scripts, some of the examples in this post may look a bit clunky. To be honest that’s partly because they are! FileMaker is a veteran product with all the baggage of backwards compatibility that that entails. It has reinvented itself many times but much of the core scripting language is unchanged over the decades.

But it’s also not the type of tool where you type code into a text editor — in general the coding is done by selecting options from menus, popups etc. This makes it easy to do, but when you paste the resulting code into a blog post it can look somewhat unwieldy.

In summary — not quite as clunky as it looks!! And, for me, the rapid productivity more than makes up for this.

Going loopy

I’ve collapsed the array of “items” in the JSON feed example included above, so let’s expand it here and look at how we parse the items array from the $json variable.

FileMaker has just one real construct for iterating through a collection and that is the Loop statement. Here’s the relevant extract from the script.

Set Variable [ $i; Value:0 ]
Set Variable [ $itemCount; Value:ValueCount ( JSONListKeys ( $json ; "items" ) ) ]
Set Variable [ $count; Value:0 ]
Set Variable [ $count[2]; Value:0 ]
Loop
    Set Variable [ $item; Value:JSONGetElement ( $json ; "items[" & $i & "]" ) ] // PARSE JSON
    Perform Script [ “processItem [item]”; Parameter: $item ]
    Set Variable [ $result; Value:Get ( ScriptResult ) ]
    If [ $result = "NEW" ]
        Set Variable [ $count; Value:$count[1] + 1 ]
    Else If [ $result = "MOD" ]
        Set Variable [ $count[2]; Value:$count[2] + 1 ]
    End If
    Exit Loop If [ Let ( $i = $i + 1 ; $i ≥ $itemCount ) ]
End Loop

It’s essentially a for loop, using the variable $i as the loop counter - zero-based in this case unlike FileMaker's own repeating fields. The key line is the one I have marked with the meaningful comment PARSE JSON. This again uses the new JSONGetElement function - and extracts the JSON for the current iteration of the items array into a variable $item. It uses array notation to access the current occurrence. This is basically items[$i], but because JSONGetElement accepts a string as the parameter it becomes the rather more cumbersome "items[" & $i & "]". Did I mention clunky? 😁 But at the end of the day it selects the $i-th element of the items array.

Armed with the JSON for the current item in $item, this value is then passed to a sub-script called processItem [item] which then accesses that piece of JSON in the same way as above to create (or update) the FeedItems table with each field from the item. For example:

Set Field [ FeedItems::url; JSONGetElement ( $item ; "url" ) ]
Set Field [ FeedItems::external_url; JSONGetElement ( $item ; "external_url" ) ]

More to come

So we’ve implemented the basics on a JSON newsreader with only a modicum of effort using FileMaker. The full repository where the example file for this article, along with the latest version of the solution, can be found on Github.

The JSON Feed spec strongly advises feedreaders to use Conditional Get — only fetching the feed if it has changed — to minimise resource usage. Our implementation doesn’t do that, so in my next article we’ll give that a try.