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.
Unlocked example file. Download here.
Download the sample file here: FMP2JSON.fmp12
which references just one other library
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:
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:
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) ;
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 ;
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 );
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.