JavaScript for FileMaker: DataTables

DataTables is a JavaScript library that takes your data and turns it into a table in a web viewer. The built-in features provide a lot of functionality out of the box, and the library can handle thousands of records.

The DataTables library has been tested by thousands of users and is used many places throughout the web. It is a reliable library.

Does this work for FileMaker?

This library is extremely useful in FileMaker. The plethora of options and the built-in features give you complete control over the data presentation without extra scripting. Additionally, since the data is loaded into the library, there’s no slowness when it comes to scrolling through records. All data is static.

The methods (interactions that work with your FileMaker custom app and data) cover all the possible cases we’d need to build for our clients.

DataTables Examples

There are many possible uses for this library:

As a list view.
As a portal, showing related records.
To show virtual list records
As a value list picker

Features

Options

Here is just a partial list of the possible options:

Style the table according to styling frameworks (such as Bootstrap), or make your individual changes.
Display paging
Type-ahead filtering
- This can apply to the entire table
- Or it can be for only specific columns
Column Sorting
- Apply this to all columns
- Apply this to specific columns
Freeze a column or a row (as in a header row)
Create Subsummary rows* (this is probably the most JS-intensive operation)
Hide or show columns
Set a default sort
Always keep one column sorted
Change the number of records showing per page

Full list of Options

Interacting with FileMaker

DataTables will allow you to interact with your FileMaker custom app and data. You can even use FileMaker scripts to interact with the DataTables table. Here’s a partial use case list:

Click on a row and open up that record in a FileMaker layout
Use a FileMaker script to add records to the table
Use a FileMaker script to sort records or change any other options

How to Set it Up

‘Installing’ in Your App

The easiest way to get this set up, to test it out, is to start with the DataTablesStart.fmp12 file (click the button below).

The FileMaker file attached here has a few fields and a web viewer. The fields, Code_HTML, Code_Data, Code_JS, and Code_FMScript, contain data relevant to the library. The HTML_Calc field substitutes out some placeholder text. The web viewer renders what’s in the HTML_Calc field.

By the way: This field-to-web viewer rendering method is completely legitimate. There’s a lot of good reasons for this method. There are other ways to work with code, but for now, stick with this method.

Here are the steps:

Simply import the one record from the DataTablesStart.fmp12 file into a new table in your custom app.
Set up the HTML_Calc field in your file to be the same as in the DataTablesStart file.
Create a layout similar to the example file’s layout (or copy/paste the objects from the Start file do your own).
Make sure that the web viewer’s calculation dialog is set to read the HTML_Calc field.
That’s it. You should see a DataTable set up with sample data.

For this library, I’d recommend using this method for now. We will explore other ways later.

Get the File

The Data

This is what the data looks like for this example. It is an OBJECT that holds values of one record that will be displayed in the table. (Any of these properties can be displayed. We’ll discuss how to update the table to show more columns).

Multiple records are gathered in an array. See this video to learn how the data was collected.

Once the records are collected as an array of objects, the array is placed into a field: “Code__Data” and is then calculated into the JavaScript inside the web viewer.

More Info About the Data

{
        "Address": "2434 East 6th Avenue",
        "Area": "",
        "City": "Denver",
        "Country": "United States",
        "Division": "South",
        "Email": "jlbmagic@gmail.com",
        "FirstName": "doug",
        "ID": 17,
        "ID_Company": "1",
        "LastName": "Henning",
        "Phone": "(234) 993-0909",
        "Sales": 952,
        "State": "CO",
        "Title": "CEO",
        "Zip": "80206"
    }

The Callback to FileMaker

The JavaScript Function

var table = $('#myTable').DataTable();
    table.on('click', 'tr', function () {
        var data = table.row( this ).data();
        var id = {id: data.ID};
        var obj = JSON.stringify(id);
        console.log(obj);
        var theURL = "fmp://$/__FileName__?script=__Script__" + obj;
window.location = theURL;
});

It uses the fmp URL, which means, currently, this will NOT work in WebDirect (though we know that is set to change in the next version of FileMaker).

Quick Explanation

Start Here:

var table = $('#myTable').DataTable();

The first line of this JavaScript finds the div element inside the web viewer that holds the table. It is found in a div with an id of “myTable”. It assigns this table to a variable “table”.

table.on('click', 'tr', function () {

This is the beginning of the function. It says “when someone clicks on a row (“tr”) of the table, run this function.

 var data = table.row( this ).data();
        var id = {id: data.ID};
        var obj = JSON.stringify(id);

Here we are assigning variables with data. First we get the data out of the row. Then we assign a variable “id” to a JavaScript object with the property “id” and the .ID part of the row (that comes from the JSON object above). Finally we stringify the object: we turn it into a JSON object.

Continue…

  var theURL = "fmp://$/__FileName__?script=__Script__" + obj;
window.location = theURL;

This code simply invokes the fmpUrl. It sends the obj variable back to FileMaker.

Notice the placeholder text in here:

__FileName__

and

__Script__

These are replaced with the current file name and a script name in the “Code_FMScript” field.

If you’re having trouble getting this to work, refer to this blog post from LuminFire.

If you’re using FileMaker Pro 18 Advanced, you can update the ‘fmp://’ part of the function to “fmp18://”.

Updating the Options

The JS code in the field “Code_JS” (shown to the right) is the place where the options can be updated.

Look through the documentation to find exact details about each option.

To update the columns that are visible in the table, focus on the “columns” property. Create one JSON object in an array for each column you wish to show.

All the Options

    $('#myTable').DataTable({
//options here
            data: __Data__,
            order: [ [3,'asc' ], [2, 'asc'] ],
             fixedHeader: true,
            orderFixed: [3,'asc'],
            pagingType:'full_numbers',
            searching: true,
            pageLength: 15,
            lengthMenu: [[10, 30, 50, 60, 75, 100, -1], [10, 30,50, 60,75, 100, 'All']],
            columns: [
                {
                    data: 'ID',
                    title: 'ID',
                    visible:false
                },
                {data: 'FirstName', title: 'First'},
                {data: 'LastName', title: 'Last'},
                {data: 'Title', title: 'Position'},
                {
                    data: 'City',
                    title: 'City'
                },
                {
                    data: 'State',
                    title: 'State'
                },
                {
                    data: 'Country',
                   title: 'Country'
                }
                ]

        });

Other Thoughts

We’ve written a lot about DataTables. Find the posts here.

DataTables Posts

DataTables has a great API that you can call to change something about the table. You can:

Add more data
Remove rows
Change the column order
Change the sort order
Group rows

and much more.

In our FileMaker DevCon 2019 Training session, we explored this. Review this page to help you see more we can do.

Using the DataTables API