Posts

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

In this series, “Use FileMaker Like A Boss“, we’re exploring those techniques, mindsets, and tools that make us a better developer. I’m starting the series with topics based on my own discoveries during my FileMaker formative years. In this post, we will take a look at the FileMaker Data Viewer.

As a developer who stumbled into FileMaker, I always had a copy of FileMaker Pro Advanced, but the tools that were included largely went unused. The data viewer, when I finally found it, proved to be something that I can’t live without. The amount of information presented there was amazing. As I look back, I’m surprised I could do anything in FileMaker without the data viewer.

The FileMaker Data Viewer Up Close

The FileMaker Data Viewer has quite a bit of functionality packed into the small window. It is a place to view values of variables and fields and expressions. When I need to test a function out or work on my ExecuteSQL() syntax, I use the data viewer. I’d hazard to say it’s the most commonly used window in our tools. It certainly is for me.

There are two tabs: Current and Watch. The current tab, as it says right there, shows the variables and fields being used in the current script. Seems simple enough, but there’s a lot of power in this statement. We’ll get into those in a bit.

The Watch tab is a place to “Monitor the values of fields, variables and expressions.” Again, simple on the surface, but this space allows for some great complex work.

What’s happening now

The Current tab shows the value of all fields and declared variables in the current script. Here you can see all the fields that you need for this script and their value if applicable. Such usefulness!

The Current Tab of the FileMaker Data Viewer

The Current Tab of the FileMaker Data Viewer

 

FileMaker Data Viewer Facts

There’s much going on in the Current tab:

  • Fields that are not part of the current context of the script (the underlying Table Occurrence of the current layout) have a value of “<Unrelated Table>”. That’s okay.
  • If you’re using the script step Set Field by Name, you will not see that field in the list.
  • The default sort is the Names column, but you can sort by others as needed.
    • Variables are included as they’re declared, so these may be out of order until you specifically click on the Names column.
  • Fields are always at the top, and variables are sorted to the bottom with global variables above local variables.
  • A field or a variable can be added to the Watch tab via the button with that name. We’ll take a look at this in a bit.
  • Global variables are shown here as well.

More About Variables

Variables are shown in the Current tab, whether they are the local kind ($var is available only for the current script) or the global kind ($$var is available for the entire file). These are listed below the fields in the data viewer sort order. A variable gets added to the Current Tab only after a script step in the current script declares it. If the script debugger enters a subscript, the local variables for the parent script are no longer available. They will disappear from the data viewer list until the debugger returns to the parent script. This is important. If I declare $Id in the parent script, it is NOT automatically available to a subscript. It would be available if I passed its value to the subscript and then re-declared it there.

Why the FileMaker Data Viewer is Useful

Many developers set their scripts up to perform actions on a blank layout. This layout contains no fields or objects save maybe a “Processing this record.” piece of text. Since there are no fields on this layout, so I need to use the data viewer to view the fields and their values. If, for example, I create a new record on a blank layout, after each Set Field [] script step, I want to make sure the fields in the underlying table are getting filled as I would expect. I don’t have to look at the layout itself. Instead, I keep my eye on the Data Viewer Current tab.

Another useful feature is the ability to change the value of a variable, be it a local one (as the script is running) or a global one (any time). Let’s say the script declares $y to have a value of 275. That’s nice, but as I debug, I want to test another value. So, after it has been declared, I can click on the variable in the data viewer and change its value. After clicking “OK”, the value gets saved back to the variable, and that value will be used in the rest of the script.

The value of a declared variable can be updated in the data viewer.

The value of a declared variable can be updated in the data viewer.

Final Thoughts About the Current Tab

  • Fields are automatically sorted since they all begin with their table name.
  • Variables are also sorted by name by type. However, in a long list of fields and variables, it can get tricky to find a certain variable. So adopt a naming convention that will put like variables next to each other, something like $idProperty and $idCorporation, etc. Some folks use dot notation: $id.Property and $id.Corporation.

The Watch Tab

This tab is probably the most used by FileMaker developers.

The Watch Tab of the FileMaker Data Viewer

The Watch Tab of the FileMaker Data Viewer

There’s a lot one can do here. You can:

  • View developer-friendly data about the file (#1 in the picture above).
  • Test out a function or a long calculation there (#2 in the picture above)
  • Watch specific field or variable values in the currently-running script (#3 in in the picture above)
  • Add variables and values that effect the logic of the script
  • Change variable values that result in the logic of the script changing

Let’s examine each one in turn.

Test out a function

As I work to develop a script or calculated field, I can test out what I think will be the syntax in the data viewer’s Watch tab. I can add a new calculation with the + button and work away. If I’m in the correct context, if I’m on a layout that contains the table and the fields I am working with, those fields and related ones) are available to the data viewer. So I can work something out.

Watch a specific field or value values

As a script runs, the data viewer Current tab fills up with fields and variables. I can move one ore more of them over to the Watch tab and hold them there to view them separately. Once a field or variable is in the watch tab, it will stay there until you remove it (or FileMaker crashes).

Add Variables

I can even add a variable to this tab and use it in the currently-running script. Here’s a scenario: A script is supposed to pick up an optional script parameter with the value of “Contacts”. However, I run the script from the Script Workspace. The $param variable doesn’t get the script parameter, so I can manually set it:

Let (
 $param = “Contacts”;
 1
 )

This variable will remain “Contacts” throughout the script, just as I need it to. The 1 in this calculated section allows the Let function to work correctly. It is only the variable’s value I care about.

Change a Variable’s Value

Similar to adding a variable, I can change the value of the variable in the calling script. This is a bit tricky, but it does work. Here’s how: As a script runs, the script sets the value of $param to “Students” which came from the button’s optional script parameter. But I want to test the logic of the “Contacts” section, so I can use the above function to change the $param from “Students” to “Contacts”. As long as I press the button “Refresh Values” this value will override what was initially set in the script.

Be careful with this. What you have set here will override the script’s declaration even if you didn’t intend for it to happen. It is best to use this feature sparingly and to remove the variable as soon as possible. This issue also happens when you declare a variable, as described above. So be careful!

A final few words about the Watch Tab

  • The Watch Tab’s values are lost when FileMaker has a hard crash. The tab will be blank when reopening FileMaker.
  • The watch tab is not file specific, so you’ll see many fields or functions that have nothing to do with what you’re working on right now. As long as you don’t mind it, keep them. Like a sock drawer or closet, it is always good to clear them out one in awhile.

The FileMaker Data Viewer is essential to a developer’s process. I can’t recall the last time I didn’t have it open. I’ve grown in my use of this tool in the past few years. I strongly recommend you take time and explore all it has to offer.

Many of us FileMaker developers stumbled into the platform. We discovered it one day on our computer or were given a copy of it. We found FileMaker to be useful to rapidly develop a custom app for our personal or business needs, and so we invested time into learning it. Our evenings and weekends were spent on such places as lynda.com, youtube.com, the FileMaker Community, and other places where we could begin to understand how to use the program.

Along the way we picked up techniques that may or may not be the best technique for a given situation. We also may not have become aware of all the tools a developer has to better develop, test, and analyze.

If that is you, stay tuned. In this series of posts, we will explore what it means to learn FileMaker like a boss as we develop the techniques and flesh out mindsets we all should have.

My Stumbling Story . . .

I am a former teacher, teaching for 15 years, in grades 2 through 7. About year 11, I stumbled into FileMaker in a school that used the application simply to print paychecks for students. Though the administrator was the only one to use it, I became fascinated with the app and begged to help out with the development. I did the usual things described above and developed a pretty good system for my school: for the teachers, administrators, and secretarial staff. It expanded to three schools and 100 people using it.

A few years into my development, I stumbled upon parts of the platform that I had never used. I started to learn FileMaker more like a boss, and now those tools and concepts are essential to daily work. I look back on the very clear events and wonder how did I go so long without using, for example, the Script Debugger (I really did spend about 2 years NOT using that thing)

The years have paid off, and I feel like gained my footing in my development. I’ve worked hard, have struggled with learning techniques, and have learned from the community and some of the best (and possibly award-winning) developers. Everyday I regain more of my footing.

Let’s Learn FileMaker Like a Boss

  • learn great techniques quicker
  • understand the essential concepts of FileMaker
  • use all the tools available for the entire platform
Learn FileMaker

These goals are here because I discovered these items ‘late’ in my development learning. We are going to explore how to become a FileMaker Boss! in this series. We’ll start with three items I discovered for the first time well into my development career. We will discuss such things as the Script Debugger, the Data Viewer, the Developer tools and other tools.  Finally, we will explore such concepts as relationships and transactions. We will describe techniques such as related value lists or data visualization. And all the while, we will be a FileMaker Boss!

The Topics

We will cover many topics, and those will be listed here. Check back for a new topic. The first three topics come directly from my own learning back in my beginner days. Those three are:

 

See yah soon as we Learn FileMaker Like a Boss.