We all know that one of the biggest updates to FileMaker was the inclusion of native FileMaker JSON parsing. And now that we have that, we can, with much less effort, parse JSON we receive from an API into fields. And we can reduce the effort, even more, using our Generator file, a free tool that ‘generates FileMaker code’.

When I first joined Geist Interactive, I had little experience with Generator. I’d only opened the file a few times but didn’t understand its use. I closed it and went back to hand-writing the code I’d need to parse a JSON object using FileMaker 16’s native JSON functions.

FileMaker JSON Parsing: The Catalyst

Someone in the FileMaker Community asked the question about how to do FileMaker JSON Parsing and so I decided to give this tool a try on a Saturday night. Turns out Generator is a pretty great tool for working with JSON among other things.

The primary question the author of the post had was about drilling down into a JSON object, getting to the 5th element in an array, which itself is in an object, which is another object. That’s a very distinct and long path. Well, Generator can parse JSON easily. Here’s how.

  • It can read a JSON object, even a complex one, and create a map of the entire object.
  • Once the map has been created, Generator can link the map up to a file and table (your custom app) and actually create the fields needed for each mapped key in the JSON.
  • And once the field has been created, Generator can then create the script steps needed to 1) pull the mapped keys’ values out of the object; and 2) set the mapped fields with the correct data.

Reread that last point. Generator will literally do 80% of the work for you. It will remove all the tedious steps we would have to do without this tool. Would you like to save 80% of your time developing this use case? I thought so.

Let’s take a look at each step in picture form. You can also watch the video where we walk through the process step-by-step.

By the way, you need to have the latest version of the Base Elements plugin installed before proceeding.

Generator creates a map of the object

In my experiment, I used www.wunderground.com‘s API to gather the 10-day forecast of the city in which I live, Denver. I received that response after manually constructing the URL and pasting that into my browser. Here’s a sample of the response.

{
  "response": {
  "version":"0.1",
  "termsofService":"http://www.wunderground.com/weather/api/d/terms.html",
  "features": {
  "forecast10day": 1
  }
 }
 ,
 "forecast":{
 "txt_forecast": {
 "date":"12:04 PM MST",
 "forecastday": [
 {
 "period":0,
 "icon":"partlycloudy",
 "icon_url":"http://icons.wxug.com/i/c/k/partlycloudy.gif",
 "title":"Thursday",
 "fcttext":"Partly cloudy. High 66F. Winds NW at 5 to 10 mph.",
 "fcttext_metric":"A mix of clouds and sun. High 18C. Winds WNW at 10 to 15 km/h.",
 "pop":"0"
 }
…
}

To start the process to parse the JSON, I took the response and copied it to my clipboard, then moved over to Generator and pressed “New Map”. The tool took the JSON object in my clipboard and created this map.

FileMaker JSON Parsing - JSON Field Map

The map created by my JSON object

This object had a few nested arrays. Generator detected those, and it allowed me to make a map for each nest. That was pretty cool.

FileMaker JSON Parsing - The nested array's Map

The nested array’s Map

Generator Maps the JSON path to fields in my file

The goal was to parse the JSON and get the data from some or all of the key/value pairs into fields, and Generator provides the tools to achieve this goal.

To start, I had to choose a file and table into which these fields will be placed. Generator can map these JSON paths to any open file. I chose my “ForecastFile” and the tables for the forecast and the details.

Choose file

Choosing the open file and a table in that file

Now that the map is created and the link to an open file and table is set, Generator will actually create fields in the linked file for each JSON path row you set. It will do any of the following:

  1. Create fields for ALL the JSON Path rows, using the JSON path key as the field names
  2. Create fields for the JSON path rows you choose, ignoring the others, using the JSON path key as the field names
  3. Create a specific custom-named field for a JSON Path row.

Here’s a map I created for the details portion of the object and the fields.

FileMaker JSON Parsing - Mapped Fields

Mapped Fields

These actions are pretty amazing. Until I started using Generator, I didn’t know a FileMaker script could create fields in a table. I mean, how cool is that? I opened my Manage Databases in my Forecast File and there were the fields in the table!

Generator Creates the script steps to parse and set the fields

This next step can be very tedious. First variables are created using JSONGetElement() to find the exact value of a JSON path. Then Set Field script steps have to be created to set the fields with the values from the variables. This is a mind-numbing task fraught with possible errors.

Generator does the tedious work for you. It will literally generate script steps to do both of these things. One simple button created the following for me:

FileMaker JSON Parsing - Created Script Steps

Generator created these script steps

FileMaker JSON Parsing: The remaining 20%

Generator takes care of much of the work of FileMaker JSON parsing and setting fields. But there’s always some final tasks to take care of. In my example above, the script turned out to be a subscript, and I had to pass the script the JSON object as a script parameter. Since the details records where children of a parent record, I also had to write script steps that grabbed the parent ID and set it to the foreign key field. Finally, in my example I had to set up a loop that stepped through all of the array elements of the daily forecast data.

But these final things are unique to my custom app. Though I might like it, Generator can’t do ALL the work for me. I still have to put a few thoughts into it.

A Time-Saving Tool

Generator is an essential tool for FileMaker developers. It works very easily to create the FileMaker JSON Parsing code and that is just scratching the surface of this tool’s capability. We are always looking for ways to save time, and Generator does that.

I am a convert. Even though I became a decent FileMaker developer by hand coding, I will use this tool all the time to parse JSON, to create the fields, and create script steps, as we saw above. I encourage you to give it a try: test out the free tool, use my demo and video as a place to start. You’ll be impressed as I was.

Download the Demo


Also published on Medium.

4 responses to “Like a Boss: FileMaker JSON Parsing with Generator”

  1. Rick Torchia says:

    Jeramie,
    Great Demo showing a fantastic tool. Really amazing!
    I am a complete noobie when it comes to both API and JSON. So, I was hoping to get some additional help with how you got the response from Weathreunderground in the form of a JSON object. You said that you configured the URL to get this, but I do not have a clue on how to get this information.
    Thanks in advance.
    Rick

    • Jeremy Brown says:

      Howdy. Thanks for commenting!
      I did skip that step in this post as I didn’t want to make the post TOOO long. I’m working on a post that will describe this process using Postman and Generator. Look for that early next week.

      Jeremy

  2. Rick Torchia says:

    Jeremy, thanks. I will watch for it.

Leave a Reply