BlogFileMaker

Drag and Drop jQuery Interface for Exporting Records to CSV

By March 11, 2016 September 5th, 2019 24 Comments

I often find myself scrutinizing a clean, intuitive user interface feature, wondering how I can add it to a FileMaker solution. That exploration almost without fail leads me to the vast world of JavaScript and its unbelievable ecosystem of code libraries. With little more effort than a turn of a key, one such library, jQuery and its companion set of user interface features jQuery UI, unlocks the door to the rich UI elements we as FileMaker developers marvel at.

But does it really open that door for FileMaker developers? Isn’t there still a learning curve to get any interaction working in a FileMaker web viewer? Even if we get past that hurdle, how do we get it to do anything with our FileMaker data? In short, yes, there are core concepts to absorb which my colleague Mike Duncan lays out beautifully in his recent blog post “Getting Started with JavaScript and FileMaker”. The advent of the FMP URL protocol provides a mechanism that completes the circle of communication between the web viewer and the FileMaker file itself. It allows me to mirror Mike’s words in saying that this is indeed a big deal.

Below is a small proof-of-concept of this promising method to enhance user experience with JavaScript-based interactions. It is inspired by a real-world challenge to present to the user a short, pre-selected, sortable, and easily deciphered list of fields to export in spreadsheet format. By and large FileMaker’s native “Specify Field Order for Export” dialog of the “Export Records” function has us covered, complete with the ability to constrain the list of fields to export to only those contained on the layout. However, many developers consider it best practice to adhere to field naming conventions that facilitate database management. As a result, often the field names that appear in the native export dialog, replete with underscores, abbreviations, field type indicators etc., end up being difficult for users to read. As a solution matures, the list likely bloats with developer utility fields that only further confuse users. The confusion deepens when users want to include fields from related records in their export or attempt to run exports on virtual lists whose field names give no indication at all of what data they contain.

In leveraging jQuery’s sortable connected lists feature, the developer can present the user with a controlled, easy-to-read, and stylistically malleable export interface. This echoes the kind of fluid interaction found on the websites and applications we use every day. Drag-and-drop objects with user-friendly labels that represent fields available to export allow for quick selection and ordering of export columns. The list of fields available for export is based on editable text (in this case stored in a value list) that the export script parses out. Therefore, the developer can limit fields shown, which to include in the export as a default, and even save user preferences by storing the text in a user record.

Demo File

Clicking the “Export” button in the demo file calls a script that opens a popover. It initiates the export field selector interface by looping through the value list containing both the fully qualified and user-facing field names. It injects that data as line items of two unordered lists: the first with fields to exclude from the export and the second with ones to include. The calculation in the web viewer is set to pull in the HTML, JavaScript, and CSS held in the off-layout text box, substitute in the unordered lists, and prepend the whole thing with “data:text/html” which tells the web viewer to render the HTML that follows.

"data:text/html," & Substitute ( GetLayoutObjectAttribute ( "html" ; "content" ) ; 

[ "||data||" ; $$data ] ;
[ "||fileName||" ; Get ( FileName ) ] ;
[ "||scriptName||" ; "exportToCSV" ]
)

Drag and Drop Fields to Export

The jQuery connected list JavaScript enables dragging, dropping, and sorting the export field objects and with just a few lines of code added at the end of the “update” function that is called when the export field object is released into a different position than where it started, a FileMaker script can be called using the FMP URL protocol.

Drag and drop fields to export.

Drag and drop fields to export.

The trick is to build the FMP URL in the JavaScript so that it conforms to this format:

fmp://$/[FileMaker File Name]?script=[FileMaker Script to Run]&$param=[Script Parameter]

Setting the “window.location” object in the JavaScript to the URL works as a redirect that asks the operating system to open up your file in FileMaker (which is already open) and run a script with a parameter.

update: function(event, ui) {
        if ($(this).parent().data("oktoupdate") == true) {
            var desired = [];
            var undesired = [];
            var headers = [];
            $.each($("#sortable1 li"), function(i, item) {
                desired.push($(item).data("dbid"));
            });
            $.each($("#sortable2 li"), function(i, item) {
                undesired.push($(item).data("dbid"));
            });
            $.each($("#sortable2 li"), function(i, item) {
                headers.push($(item).data("dbname"));
            });
            updateList = "omit|" + desired.join("|") + "|headers|" + headers.join("|") + "|include|" + undesired.join("|") + "|end";
            theURL = "fmp://$/||fileName||?script=||scriptName||&$param=" + updateList + "&$change=1" ;
            window.location = theURL;
            $(this).parent().data("oktoupdate", false);
        }
        else {
            //alert("not ready yet");
        }
    },

In this case, the script parameter is simply a list of export fields to include in the export. Because the script is called every time the user drags an export field object to a new location, the list stays in sync with what is displayed in the web viewer. This works super fast, in part because JavaScript is blazingly quick, but also because the FileMaker script that is being called only needs to parse a small amount of text.

Once the user is satisfied with the export field order, clicking “Export to .csv” launches a process that loops over the found set of records. This gathers the columns of data the user specified in a return delimited list, exported as a .csv file. The dirty secret here is that the script actually uses the tab character to separate the data (only because I couldn’t get comma-separated to work). However, when the user opens the file, an export of the data with easy-to-read column headers occurs.

The drag-and-drop export records interface in this sample file represents decidedly only a tiny sliver in the range of possibilities of UI enhancements that JavaScript can accomplish. So, there is a lot more to explore. The introduction of the FMP URL protocol makes exploring the possibilities all the more exciting. It elevates the web viewer from a purely representational data visualizer to an interactive portal with an actual impact on FileMaker database processes.

References

 

Ross Johnson

Ross Johnson

Ross is a FileMaker developer with an avid interest in user interface design. He studied French and political science and has an unquenchable curiosity for foreign language, sociology, and how things work. Outside of work, Ross enjoys hitting up the restaurant scene or trying his hand at culinary creations and taking his giant fluff ball of a dog for walks.

24 Comments

  • Avatar Alistair CAMPBELL says:

    Thanks for the great explanation and especially the database, i will learn a lot.
    alistair

  • Avatar Mike Beargie says:

    Hey Ross, Great work. I was wondering if there was a way to make this WebDirect compatible? I’ve played around and can get the export portion to work, but not the drag and drop itself. Let me know if you have any thoughts.

    • Ross Johnson Ross Johnson says:

      Hi Mike, thanks for the feedback. As far as I know, I’m not sure there is a simple way to make it WebDirect compatible. WebDirect currently does not support deep linking which is what the JavaScript depends on to output to FileMaker the fields selected to export. However, you could probably make it work with a little custom web publishing where you update a user session record with fields to export.

      • Avatar Mike Beargie says:

        Thanks, the main issue I ran into was that the drag and drop actions didn’t even work. It was just highlighting button text and showing the 4-way arrow on mouseover, but not actually allowing for any interactivity. So it may be a lost cause at that point.

        • Ross Johnson Ross Johnson says:

          Hi Mike. I finally figured out what the issue is for the drag-and-drop functionality for WebDirect. Debugging in Chrome shows that there is a “Uncaught SyntaxError: Unexpected token ILLEGAL” error which in many cases means that there is a zero width character that is breaking the code. I replaced the jQuery UI JavaScript that I copied wholesale into the text box the web viewer is referencing with the CDN reference.

  • Avatar Mike Beargie says:

    Oh sweet! I’m going to try and get a working demo file in with my devcon presentation this year about WebDirect Integration Innovations. This would make an excellent demo for the power of the WebViewer inside of WebDirect. Thanks so much for your help, I’ll make sure to get your credit on the content!

  • Avatar steven oconnor says:

    this is great and very inspiring – thanks. I have an issue with multiple instances of thin in 1 file. I have duplicated and renamed the script and edited the webview content to reflect this.

    Now it all works perfectly on a mac but fails to register the moved filed on a PC. It all looks fine – fields are rendered and moved back and forth nicely – but the export always uses the default ‘include’ fields…

    any ideas? it’s basically not tricking the $change=1 part of the script at all on the PC!

    • Ross Johnson Ross Johnson says:

      Hi Steven, thanks for the feedback. It sounds like dropping the field object in the include column isn’t calling the script via the fmp URL. Have you tried it with the debugger on?

  • Avatar holstermedia says:

    Hi Ross
    Sadly I don’t have a recent advanced version 🙁
    Weirdly it works as expected on the PC for the first instance – so I assume I’m just missing something in the second instance. I’ll have to find fmpa from somewhere!

    • Ross Johnson Ross Johnson says:

      That’s odd that it would work in one instance but not in the other… I imagine there is some sort of syntax error for the one that doesn’t work. Keep us posted on what you find if you are able to test it with the debugger at some point.

  • Avatar holstermedia says:

    ok I bought fma to help resolve this!

    now both instances fail on PC but both work on Mac.
    that’s with no changes apart from running in fma 15 vs fmp 14 on the PC.

    script debugger runs without error – but I can clearly see $$data doesn’t change from the initial setup, even when dragging fields back and forth…

    any ideas?

    • Ross Johnson Ross Johnson says:

      The $$data variable only gets set when initializing the export. The $$exportRowHeaders and $$exportRow variables are the ones that should be updated when dragging the field objects back and forth.

      I just tested it out on FileMaker 14 on PC with no issues. So, I’m not quite sure why it would be failing for you…

    • Avatar holstermedia says:

      I think I’ve narrowed it down to the content of the Value List. Is there some limit to it? Or certain calc fields not supported? Can’t see why…

      My full Value List fails (only on PC) – but I can grab a random 10 or 15 of these and it works. I’ve viewed the source of the web viewer and it all looks neat and correct
      – no duplicates
      – no missing |’s
      – tried removing spaces 🙂

      • Ross Johnson Ross Johnson says:

        I am guessing you are running into a URL character limit. The fmp URL that is being called in the JavaScript can get lengthy because it includes as a parameter a compilation of every field to include and omit in the export. Because Internet Explorer (which is what the web viewer uses in Windows) limits URL’s to 2,048 characters, the URL is likely being rejected.

    • Avatar holstermedia says:

      ah – thanks I think that’s the issue…well caught!
      I’ve found a hack using the clipboard in windows so I can reduce the url length. Shame as this is such an excellent function on a Mac…

      http://www.seedcode.com/filemaker-webviewer-javascript-calculation-engine/

      Could it work without including the ‘omit’ fields? At least then we could have a long picking list and just limit the selection to say 5 or 6 fields…

      • Ross Johnson Ross Johnson says:

        Clever workaround using the clipboard.

        Yeah, I think it will actually work without sending the ‘omit’ fields in the parameter. If you delete the 3 occurrences of the string “omit|” + desired.join(“|”) + from the code that is saved off-layout, that should limit the parameters to only the header name and included fields.

    • Avatar holstermedia says:

      great – that worked for now. So the ‘omit’ data is not required and now the client can happily select about 6 fields from a list of 100…thanks!

  • Avatar holstermedia says:

    I’ve d/l your new demo and ran that off fms and get the same error on a PC – again working perfectly on the mac. So at least I know it’s a pc issue not a syntax…

  • Avatar Carlos Esteban says:

    Hi,
    I´m very interesting in this options because I´m trying to put this option to my employees – to do easy data reports.
    I can use it in Filemaker 17 Pro but I cant use this via WEBDIRECT. It is possible?. Thanks again.

    • Ross Johnson Ross Johnson says:

      The demo file on this blog post is currently only compatible with FileMaker Pro. Because there are limitations with JavaScript callback functions in WebDirect, there is not unfortunately an easy way to get it working. Mike Beargie, who commented previously on this blog post, incorporated a version of this feature in his 2016 DevCon presentation that was compatible with WebDirect. I would suggest reaching out to him.

Leave a Reply