Using JSON Data to create JavaScript Flare Hierarchy Graph in FileMaker Pro Web-viewer

Contributors

• Milan Mujovic – SMALLCO
• Davide Puppo – MELASTUDIO
• Tobias Liebhart – KOSCHIER SE

Specials thanks to Caleb Ruth who encouraged me to hold an un-planned un-session  on this subject at dotFMP un-conference in Berlin 2018. This consequently brought some great tips from the community and made this writing happen much faster then it probably would.

Abstract

The d3-hierarchy is a module that implements several popular techniques for visualizing hierarchical data, such as Node-link diagrams, Adjacency diagrams and Enclosure diagrams. It facilitates rapid multi-scale inference: micro-observations of individual elements and macro-observations of large groups.

In this article we will present a way to implement d3.tree layout into FileMaker Pro 16s web-viewer.

The d3.tree layout implements the Reingold-Tilford algorithm for efficient, tidy arrangement of layered nodes. The depth of nodes is computed by distance from the root, leading to a ragged appearance. Radial orientations are also supported. Implementation based on work by Jeff Heer and Jason Davies using Buchheim et al.’s linear-time variant of the Reingold-Tilford algorithm. Data shows the Flare class hierarchy, also courtesy Jeff Heer.

Useful links

Related to this article:
Github url to the files used as a starting point in this article: https://bl.ocks.org/mbostock/4339083
JavaScript library d3.v3.min.jshttps://d3js.org/d3.v3.min.js
Download the example FileMaker file here: JSONFlare.fmp12 (please note that the file can only be useful with FileMaker Pro 16 Advanced and higher)
Credentials for this file are: Username: admin; Password: admin

About JSON:
Wikipedia link: https://en.wikipedia.org/wiki/JSON

More about Flare:
Flare: http://flare.prefuse.org/
Jeff Heer: https://homes.cs.washington.edu/~jheer/
Jason Davies: https://www.jasondavies.com/

Introduction

I have been introduced to the „Flare graph“ (the graphical representation of data using flare class hierarchy…) by Davide Puppo and i liked it straight away. It is probably one of the better ways to show the data and it also comes with the flare – it looks great. In dotFMP 2018. un-conference in Berlin i presented the part of what we did to make it possible to implement this type of visualization  in our favorite FileMaker Pro platform. As the session was not planned, the notice was short and the previous night was long, my audience might not have got the complete picture of what it is or could be. I hope this article will fix that fairly.

Methodology

If we study the files from the Github link provided above, we will see that the flare hierarchy graph produced is dependent on the „d3.v3.min.js“ library and also on the text file sitting in the same root called „flare.json“ which holds all of the data in a JSON formatted chunk of text.

We where able to define two major challenges standing in the way of implementing this graph into FileMakers WebViewer:

1 ) Exporting the data from FileMaker into the „flare.json“ file would not be the best option (i could accept a challenge on this conclusion). First of all, we would have to transfer the data to some distant location only to bring it back in, secondly, we need to have the file exported in utf-8 format which would require a plugin or a work around that might be to lengthy and ugly for the purpose, and finally, it does not work well when the file is called from FileMakers WebViewer.

2) Creating a hierarchy JSON code from FileMaker with the embedded child records down the infinite line of possible relationships did not look like an easy doable task, on the contrary.

Once we resolve these 2 issues we thought it would be an easy path forward to put it all together and make it work. And we where right.

Challenge #1 – exporting and calling „flare.json“ from JavaScript embedded in web viewer 

After a few attempts we abandoned the idea of exporting the file „flare.json“ and then calling it back with JavaScript embedded inside the web viewer. It did not work instantly and it looked like the wrong way to go anyway. Our colleague Dušan who is a JS Jedi  suggested that we rework the JavaScript embedded in the HTML and transform the entire „flare.json“ file into the JavaScript object. That allowed us to call it directly from any text field or a global variable generated in FileMaker.
In the provided example file, we can see this switch inside the script „Set WebViewer“ on line 19 where the global variable $$HTML is built. If FileMaker calculation dialog had line numbers you would see that the global variable $$JSON is called on line 46. The global variable $$JSON is created in the script step on line 15 of the same script. $$JSON is just reading the calculation field „Product::_ini_JSON_UCL“ where the actual magic of creating the JSON lies.
Once we got that in place we could call JSON from the text file or a global variable and use it as an JS Object to show the graph. Fine.

How do we now build the JSON document we need from the actual FileMaker record set?

Challenge #2 – building a JSON document to represent the array of child records down the infinite line

In the provided example file, in table „Product“ we can find two calculation fields, both resulting in a text output:

• „Product::_ini_JSON_UCL“
• „Product::“_ini_JSON_Formatted_UCL“

NOTE: These calculations are using JSON functions added in FileMaker Pro 16 to build the JSON text we need to represent the data.

Performance

$$JSON could probably also be generated with any of the plugins that we use to generate JSON in Filemaker as the speed on higher numbers of records could become an issue, in theory. The plugins might help as we know some of them could have up to 20 times and more faster performance when generating JSON compared to the native functions. I have not tested the speed thoroughly. On the amount of records in the testing file provided (1822) on MacBook Pro 2017 with macOS High Sierra version 10.13.4 it loads the $$JSON almost instantly and further shows the graph instantly.

Discussion

This was only a relatively simple example of the way to implement this type of visualization into FileMaker. From here it could take all kind of directions. I am looking into the best way to apply the calculation that builds JSON from FileMaker data on the relationship graph consisting of various tables, for example. Other interesting thing to try would be creating dynamic picture nodes instead of just little circles as presented here. Making nodes that link to some data in or out of FileMaker would be a great feature to.

If you explore the d3 libraries you will find a significant number of very, very cool ways to visually represent the data formatted into a JSON document using JavaScript, exactly like in this example. Go and play, it is fun. After you finish all your homework, of course.

Presenting the graph in the web browser will have to require a completely different approach, of course. In that case we might need to consider exporting the JSON outcome into the actual „flare.json“ text file into a place where the library script can fetch it, and we might want to adjust the actual library to get it from the designated location, if we would not want to keep it in the same folder.

In this example the web viewer output is defined by a global variable. This is done by purpose for the sake of easy testing and controlling the  output, and to facilitate further research. You might want to investigate into the possibilities of giving the portion of data to each user according to the privileges.

I hope the article can be helpful as a first step into the matter and a clear proof that it is possible to implement this kind of graph in our good old FileMaker. From here you can really take it where ever you want.

 

 

How to install and configure a 32 bit ODBC driver on a 64 bit Operating System?

You have a 64 bit operating system, which usually comes with a more modern equipment, and specially a 64 bit server OS, which is most likely to be the case, and you need to install an ODBC driver that would work with a 32 bit application such as FileMaker Pro.

If you install a 64 bit ODBC driver, it will be listed and configurable in “ODBC Data Source Administrator” (Start\Control Panel\All Control Panel Items\Administrative Tools\Data Source (ODBC)), but it will not be able to work with FileMaker Pro (or any other 32 bit application).
On the other hand, if you try to install the 32 bit driver, the system will not recognise it, and it will not appear in your default Data Sources (ODBC) inside the control panel, so you will not be able to configure it and connect to MySQL.

THERE IS A SOLUTION TO THIS PROBLEM – In many places you will read that this is IMPOSSIBLE – WHICH IS NOT TRUE. What you need to do is the following:

  • Download and install the 32 bit MySQL driver, regardless of the fact that you are using a 64 bit OS.It will install the files in the following place (path):C:\Program Files (x86)\MySQL\Connector ODBC 5.1
  • Insteadof following the usual path via the Control Panel (Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC) ) , where the 32 bit driver will not be recognised, YOU NEED TO FIND AND USE THE FOLLOWING PATH:
    C:\Windows\SysWOW64\odbcad32.exe
    .

Database Source Admin tool path for configuring 32 bit drivers if installed on 64 bit Operating System

  • Here you will be able to see your 32 bit ODBC driver and configure it to connect with the MySQL database, in the same way as you would do on a 32 bit system.

In this way, FileMaker Pro as a 32 bit application will be able to recognise and use the ODBC driver installed on a 64 bit Operating System. Please notice that 64 bit applications WILL NOT be able to use the 32 bit driver, so if you are connecting some other, 64 bit application, for example MS Access 2010, you will need to install a 64 bit driver as well, and configure it via the usual path: Control Panel\All Control Panel Items\Administrative Tools\Data Sources (ODBC)
The two drivers will not be installed in the same directory so you could use automatic install feature for both of them. By defauld, the 32bit ODBC driver will be installed in: C:\Program Files (x86)\MySQL\Connector ODBC 5.1, while the 64bit ODBC driver will install inside:C:\Program Files\MySQL\Connector ODBC 5.1, and you should be able to configure it using your Control Panel\Administrative Tools\Data Source (ODBC) path.

If you where reading about connecting FileMaker Pro to MySQL database with MySQL ODBC driver, you can now go back to the article and proceed with instructions.