The FileMaker Web Viewer as a Javascript Calculation Engine (CSV to JSON example)

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.

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

Now, there’s really no reason not embrace it fully, and I predict that we’ll see a large group of developers turning more and more of their UI (and other) duties over to the Web Viewer.

This makes perfect sense.  Javascript seems to be in its 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.

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&param=" + 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&param=" + 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.

Update: check out “part 2” of this here.

Featured Posts

Follow Along

Stay up to date with the latest news & examples from SeedCode

28 Comments

  • Ehsan

    I get the privilege error when trying to execute the functions. What should I do

    • seedcode

      Hi Ehsan,

      The fix is simply to enable the new privilege. Here’s how.

      1. Select File / Manage / Security from the FileMaker menus.
      2. Click on the “Extended Privileges Tab”, then double-click the “fmurlscript” keyword.
      3. Check “on” for any privilege sets you wish to be able to use the fmp url protocol.
      4. That’s it!

      This privilege set was new in FM16 and you can read more about it in the FM16 section here: http://www.seedcode.com/pmwiki/index.php?n=DayBackForFileMaker.FMPURL

  • Josh

    Hey Jason, great work. Regarding IE and the clipboard, I did a similar workaround but I have it restoring any overwritten clipboard text after the operation is completed, but this doesn’t restore non-text clipboard info.

    Is it better just to warn the user that their clipboard will be cleared and to cancel and paste if they need first? Is using the clipboard still the only workaround you know for IE urls?

    • Jason Young

      HI Josh,
      Right, the clipboard is not ideal, but is the only way we know how to do this with the Windows/IE URL limitations. One thing you can do is to set up a global container to hold the clipboard contents. Before you start the web viewer routine, go to this new container field and Paste in whatever you have from the clipboard, then when the web viewer routine is completed, you can return to the container and then Copy the contents back into the clipboard, This works for text as well as images, even though it’s a container.
      hth,
      -Jason

  • Steve

    I’m not seeing anything in the WebViewer – should I be seeing something here?

    • Jason Young

      Hi Steve,
      Sorry for the late reply. No the web viewer just runs the javascript and then returns the results via FMP script. In practice, we’d keep the web viewer “offscreen”.
      -Jason

  • Trevor Yancey

    I’d like to use this solution but I’m curious to know why the web viewer on the Sample File does not show anything. If the script Convert Company Found Set to JSON is ran, the results in the $sc_html variable are sent to the WebViewer object, but nothing displays.

    • Trevor Yancey

      Never mind….I took a closer look and found that you did not set up the web viewer to do anything with the JSON data. Thanks anyway!

  • Andreas Thyholdt

    Great article. I wonder about the need for exporting the libraries at all. I would think it would be more efficient to just insert the contents of the layout objects into global variables and reference these variables between tags in the HTML. Or Am I missing something?

    • Jason Young

      Much later the next year…
      Right, and this approach actually performs better on mobile. I like the folder approach as it’s easier to develop and debug as you can open those external files in the browser or with an editor, but once you’re ready for production, “packaging” everything up into a single string is probably the way to go.
      Thanks!
      -Jason

  • Hi there,
    I’m using web viewer in a series of application and I found this post extremely useful. I’m now trying to work on opening up a pdf file with embedded a call to a inner fm script, directly from a web viewer. Now the technique to open up a javascript from a temp folder is extremely helpful i believe even for this specific purpose, because if I try to open the url directly from the web viewer, unfortunately there is a security block from adobe that stops the call. Do you think that it might be possible to workaround it with the javascript technique?

    Thanks!

  • Matt

    Does this work with FileMaker Go and WebDirect? Export to a temporary directory only works on Windows and Mac. I notice the example doesn’t show how to get the results of ExecuteSQL and convert to JSON using the csv2json library without exporting to a temp file and reimporting.

    • Hi Matt,
      It does work fine in Go. You can export to the temp directory and make references to it with the web viewer. However, not in WD. My method of exporting to the temp directory is just a personal preference, and creating one inline string for the web viewer, without any exports ,is certainly doable…and probably would perform better on Go.
      Cheers!

  • Mike

    Thanks for the info it has been very helpful! I have only been using Filemaker for about a week now, so sorry if I am overlooking something simple. I am trying to use some html code I have to generate a PDF through a web viewer. I was able to get all the js files for creating the PDF copied to a temp directory as you suggested, but the html function will only execute up until the line to save out the PDF. Is there anything in the Filemaker web viewer that would not allow a file to be saved to a directory?

    Thanks

    • Hi Mike,
      I’m not sure, but there are libraries for creating/saving pdfs from javascript, but I don’t have any direct experience with them. http://mrrio.github.io/jsPDF/ is one I’ve seen, and may be worth a look.
      hth!

  • Mike Davis

    Jason,

    Thanks for typing this up. I am currently struggling to get some libraries to work with my javascript code. I am curious – was your method to put the library on a layout and use “GetObjectAttribute” because you didnt want to create an extra field for it, or because you found this to work more reliably? Filemaker doesnt like the library I am using (KenticsJS) and I am wondering if switching to this might work. Either way I am going to give it a try and thanks for your post again.

    • Jason Young

      Hi Mike,
      The only reason we did it this way was so that all that functionality would survive cloning. It’s a habit we’ve gotten into with calendars and different date formats. It might be the way to go here, as the library string never has a chance to get “touched” by FileMaker’s formatting. We do this in open scripts in the free SQLexplorer if you want to re-engineer from there.
      hth,
      Jason

  • Lloyd Hird

    Hey man, great article. It half answered the question I was searching for answers for!!

    Just a quick question, is there a way to have all of your required JavaScript libraries built into the data base itself and not referenced? The reason I ask is we have a file maker solution that I am trying to put gauges and meters into. I found an awesome JavaScript/HTML5 solution that is free and has some really great designs, but some of our users will not ALWAYS have access to the net (eg. FMGO users with no cellular signal)

    Any ideas?

    • Jason Young

      Hi Lloyd,
      Sorry I missed this and the late response!! We export the libraries to your local temp directory so a live connection is not required for this to work.
      Cheers,
      Jason

  • I am cheering and crying at the same time. Why on earth do we still have to resort to hacks like this for basic functionality.

  • Great inspiring article, Jason!
    One question: What is the benefit of storing libraries as static layout objects over having them in a libraries table or fields of a single-record settings table?
    And a small clarification – as far as I recall the fmp:/$/filename path should link to an already open file while fmp:/~/filename links to a locally stored file. So fmp:/$/ should be the most suitable solution for web viewer initiated scripts. The only thing I am not sure about is what will happen if you try to use fmp:/$/ when having multiple instances of the same filename open from different sources.

    • Jason Young

      Hi HOnza.
      Thanks for the kind words! The only advantage to the static text approach is that it survives cloning. I believe you’re right about the $ and I’ve been meaning to look at that and update the post along with what Joel brought up…when I get a chance 😉
      Cheers!

  • This is great, Jason, thanks! It really opens up the possibilities for using the FMP URL w/ Web Viewers. A couple comments:

    a) As to “now there’s really no reason not to embrace it fully”:
    I do still have _some_ hesitation because of the conflicts that can arise when someone has both FMP12 & FMP13 installed on their machine. It seems it doesn’t happen to everyone, but I can vouch for the problem of FMP13 not being able to recognize web-viewer FMP URLs, & instead the URL tries to launch the script in FMP12. Fortunately on Mac there’s RCDefaultApp*. I haven’t checked yet if this is a problem on Windows too.

    So my “hesitation” in using these techniques is that I can’t be certain that it will work in a user’s environment — at least not without extra instructions/fixes/downloads… 😛

    b) FYI: You can use single quotes around HTML attribute values so that you don’t have to escape double quotes inside a FileMaker calculation, e.g.:
    “”
    instead of
    “”
    I blogged about it at:
    http://blog.jsfmp.com/post/44812369776/use-single-quotes-in-html-attributes-filemaker-dataurls

    *http://www.rubicode.com/Software/RCDefaultApp/ (“a Mac preference pane that allows a user to set the default application used for various URL schemes”)

    • looks like your blog ate my examples. You can see more at the URL I posted.

  • john renfrew

    great post Jason!!
    got me thinking, so just changed the JSON output to XML so I can save, then import to a fake table with an XSLT. really very smooth.

    john r

    • Jason Young

      Thanks John! Imho “got me thinking” is the highest praise a blog post can receive!

  • Bruce

    Nice article! “Export to the text directory” I think you mean the temp directory?

    • seedcode

      =) Thanks, Bruce

Leave a Reply

Your email address will not be published. Required fields are marked *

Check out some of our other posts ...

Suggesting Appointment Slots

Show Available Slots that Match Multiple Criteria Schedulers often look for gaps in their schedules to find the open resources for each opportunity. But sometimes,

Introducing Draft Settings Mode

Following up on this idea that people stretch themselves when they feel a little safer, we’ve been very focused on the customization experience for DayBack

New Longer Timescales for DayBack

Resource Scheduling Swimlanes You can now extend the pivoted scheduling view in DayBack to show items by week instead of solely by day. This lets

FileMaker Summer Camp – Recap

Unconference Sessions If you missed Pause in October, here’s a look at the sessions that attendees hosted. All the sessions are listed in this post

COMPANY

FOLLOW ALONG

Stay up to date with the latest news & examples from SeedCode

© 2024 SeedCode, Inc.