The FileMaker Web Viewer as a Javascript Calculation Engine (CSV to JSON example)
Entry posted by John Sindelar
1,393 views
The FileMaker 13v2 update has generated a lot of excitement about the fmp: protocol now being available for running scripts in all deployment situations. Previously, locally running files handled the protocol differently than their Go and Hosted counterparts. This made some of us a little hesitant to use this powerful new technology.
This makes perfect sense. Javascript seems to be in it’s hey day, with thousands of comprehensive, mostly free, libraries handling all aspects of user interaction for the increasingly ubiquitous web browser. FileMaker developers often bemoan the lack of modularity in their platform, and here’s one possible avenue to change that. Not tapping into this wealth of material for our FileMaker solutions seems like a huge lost opportunity.
Unlocked example file. Download here.
However, it’s not just the UI power from Javascript that we get, although that certainly is the jewel. We also get access to a rich library of data manipulation tools, written in Javascript, that we can easily incorporate into FileMaker via the web viewer, and potentially end up with something as powerful as our native scripts and calculations. This was an idea I originally heard from my colleague Tanner Ellen, and although I didn’t get it at the time, I do now!
There is some overhead in coming up with a framework for loading Javascript libraries into your web viewer, but once you’ve solved that, adding and deploying additional libraries should become as easy as looking up a custom function on the web.
Download the sample file here: FMP2JSON.fmp12
Let’s look at an example where we’ll take some FileMaker data and convert it to a JSON format (in FileMaker) using the Web Viewer and some simple JavaScript…
For this example I’m using two javascript libraries I found on github. The first is
https://github.com/cparker15/csv-to-json
which references just one other library
https://github.com/douglascrockford/JSON-js/blob/master/json2.js
I did modify the csv-to-json a little bit to give me a single function I could call, but this didn’t require any deeper understanding of the code and took just a few minutes. The json2 is unmodified.
Deploying the libraries
There’s a few strategies you can use for getting these libraries into your web viewer html. I like exporting them as external references to the temp directory and reference them in the main html as it keeps the main html cleaner and easier to read. However, you may prefer simply inserting the entire library inline in your html.
I create a layout in my file and paste the libraries as static text onto them and give them a name. I can then use GetLayoutObjectAttribute ( <objectname> ; “Content” ) to get the text of the library, put that into a global field and export it to the temp directory as a .js file, and save the formatted paths to variable. The variable value will look something like this:
“<script src="file:///private/var/.../csvtojson.js" type="text/javascript"></script> <script src="file:///private/var/.../json2.js" type="text/javascript"></script>”
If you’re doing the inline method, you can just insert the text into your html right from the function.
Creating an html Template
Once my libraries have been exported to the temp directory, and their paths saved as variables, then I can construct my html template that I’ll set to the web viewer when I have my data. I like to write it to a global field, as it can make it easier to read than a global variable in the data viewer, but either is fine.
Our html template for this is actually pretty simple:
data:text/html, <!DOCTYPE html> <html lang="en"> <head> <script src="file:///private/var/folders/.../csvtojson.js" type="text/javascript"></script> <script src="file:///private/var/folders/.../json2.js" type="text/javascript"></script> <script type="text/javascript"> var mydataRaw = "<<MYDATA>>"; var mydata = csvToJson(mydataRaw) ; var p = encodeURIComponent(mydata) ; var url = "fmp://$/FM2JSON?script=WriteJSON¶m=" + p ; window.location = url ; </script> </head> </HTML>
That’s it. All the action is in the Javascript in the <head>, so let’s take a look at those 5 lines:
var mydataRaw = "<<MYDATA>>";
Here, we’re declaring a variable for the csv we want converted, but since this is a template, we’ll use the “<<MYDATA>>” string as a placeholder. When we have our csv we’ll substitute that value in when we send the html to the web viewer.
var mydata = csvToJson(mydataRaw) ;
Here, we’re just passing our csv to the function from the csv-to-json library and declaring the result to a new variable called mydata. At this point we have our JSON, we just need to get it back to FileMaker.
var p = encodeURIComponent(mydata) ;
We’re using the the fpm:// url protocol to run the script back in our FileMaker file with our JSON as the parameter. Since this is a URL, we’ll need to encode the JSON. encodeURIComponent is a native javascript function that is similar to FileMaker’s GetAsURLEncoded function. This line encodes the JSON and writes it to a newly declared variable of p.
var url = "fmp://$/FM2JSON?script=WriteJSON¶m=" + p ;
Now we simply create our url for running the filemaker script to the variable url. FM2JSON is the filemaker file name. Since we’re creating this template in FileMaker you can use “fmp://$/” & Get ( FileName ) & “?.. to generate the url and add a little portability to the code. The “$” denotes that this is a local file, so if the file is hosted, then that’s not needed. WriteJSON is the script name and consists of one line which writes the script parameter to a global field.
window.location = url ;
Now that we have our url, then we just use the native Javascript object window.location to load our url and run our script. That’s it…right?…not quite!
Internet Explorer URL limit or “John, I have a hacktacular workaround on Windows.”
Unfortunately, Internet Explorer has a length limit it allows on urls of 2083 characters with a path length of 2048 characters. This severely limits our ability to send our parameters back to FileMaker via the fmp: protocol. For our JSON example, 2083 characters is very likely not enough to even contain a whole record, let alone a found set. After some googling and head scratching, we did come up with a work-around. Unlike other browsers, IE gives you the ability to interact with the system clipboard without prompts using the clipboardData object like this:
window.clipboardData.setData( 'Text' , mydata );
We can then not worry about appending a large parameter to our url and simply call a script that pastes the clipboard to a global field, so now our javascript for windows looks like this:
var mydata = csvToJson(mydataRaw) ; var url = "fmp://$/FM2JSON?script=WriteJSON" window.clipboardData.setData( 'Text' , mydata ); window.location = url ;
Using the clip board is not ideal, but is the only solution we’ve found so far for returning the data to FileMaker on Windows.
Getting the csv
We first need to use FileMaker to get our csv, we’ll then substitute it into our template for <<MYDATA>> and set a web viewer with our template html. ExecuteSQL is the easiest way to generate a csv into a FileMaker expression and this technique was originally developed for the web viewer portal in SQLExplorer. I also like the technique of exporting the current found set as a csv to the temp directory and then using Insert From URL to write the csv into a FileMaker global field. You’ll want to substitute out the additional quotes that FileMaker adds during the export, but then it works great.
Download the sample file here: FMP2JSON.fmp12
This is just a small example of bringing additional scripting and calculation functionality into a FileMaker solution and the possibilities are potentially endless. So the next time you start writing that nested recursive masterpiece of a custom function from scratch, maybe do a quick swing through github or stack overflow to see if somebody’s saved you the trouble.
The post The FileMaker Web Viewer as a Javascript Calculation Engine (CSV to JSON example) appeared first on SeedCode.
Source
0 Comments
Recommended Comments
There are no comments to display.