Drag and Drop jQuery Interface for Exporting Records to CSV

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.

Get the Demo

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.

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

• “Getting Started with JavaScript and FileMaker” by Mike Duncan
• “FileMaker 13: Working with Base64” by Mike Duncan
• “Using the Virtual List Technique” by Jeremy Brown
• jQuery UI
• jQuery UI Sortable, Connected Lists
• jQuery