Filter and Sort FileMaker with JavaScript

Starting with FileMaker 16, developers can use native JSON function for a variety of tasks. From simple navigation and script parameters to API integrations and web viewers, JSON objects prove to be a great addition to the FileMaker platform. However, FileMaker has only six native JSON functions and these are limited to the following basic tasks:  set a JSON element, format a JSON object, get a JSON element, delete a JSON element, list JSON keys, and list JSON values. While it can be enough in many cases, we could still use additional functionality to sort, filter, and further manipulate JSON objects. Some talented developers have come up with great custom functions for FileMaker that allow JSON filtering and sorting, but most of the time these are pretty slow, especially when the JSON object gets bigger.

Codence does a lot of API integrations and we use JavaScript libraries that expect data in the JSON format so naturally, we use JSON a good bit. Instead of relying on FileMaker to manipulate the JSON data, we decided to use an alternate approach and utilize JavaScript to perform these functions. While this requires some familiarity with web viewers and FileMaker’s native JSON functions, you don’t have to know JavaScript in order to integrate these functions and begin using them in your FileMaker 19 solution.

Use Case

First, let’s decide when it’s better to use native JSON functions within FileMaker versus using JavaScript to save the day. JSONSetElement() can become your best friend because it allows you to create any JSON object you would ever need:  nested objects, arrays, arrays inside arrays, etc. JSONFormatElements() is very useful in a data viewer to view the logical structure of your JSON object and JSONListKeys() combined with FileMaker’s ValueCount() is a great method for counting elements for a given key. While these native FileMaker functions allow you to create and manipulate the complex JSON objects needed for many JavaScript integrations, our JavaScript functions will help you take your integrations to the next level. Using these functions will allow you to build filters for calendars, Kanban boards, advanced settings for charts, multi-table search features, widgets, intricate UI elements, and much more.

In the following example, we’ll build a smart list of users (your FileMaker users, for example) that can be filtered and sorted based on any value.

Filtering Based on Single Value

For the purpose of this article, I have built a simple widget that displays an array of JSON objects on the screen. Each object has a name, id, gender, and an image property among other keys that we are not going to use in this example. Once I simplify the array, it looks like this:

Now, our goal is to build a function in FileMaker that will filter this object based on any single value. In order to achieve this, we will need the following: one JavaScript function, two short FileMaker scripts, a global field with a value list, and a few script triggers. All filtering will be done in a web viewer:

function filterByOne(data, key, value){
     var value = value.toLowerCase();
     var key = key.toLowerCase();
     var filterOnly = JSON.parse(data).filter(function (obj) {
         return obj[key] == value;
     });
     jsonFilter = JSON.stringify(filterOnly);
     FileMaker.PerformScript('wv_res_filterByOne', jsonFilter);
 }

Next on Filtering & Sorting 

The function, filterByOne, receives three parameters from FileMaker: data (the big JSON array we had in a beginning), key (the JSON Key we want to use for filtering; in our example, “gender”), and value (what this key should be equal to). In other words, you ask this function to only return objects when the key equals that value. To perform this operation, we use the JavaScript array method filter() and then return the result back to FileMaker. This JavaScript function will go in between the <script> tags of your web viewer code.

Now let’s add the two FileMaker scripts (JS_filterByOne and wv_res_filterByOne) that we referenced in the JavaScript code. The first script will be called from a filter field and accepts two parameters – key and value. On line 5, we will also save all data in a global variable if this is the first time we do the filtering. On line 12, we use a FileMaker 19 script step to call the JavaScript function illustrated above. Don’t forget to specify the web viewer name and in the web viewer settings, the “Allow JavaScript to perform FileMaker scripts” option should be enabled.

The second FileMaker script, wv_res_filterByOne, is even simpler – all it does is it accepts the filtered JSON objects and sets the current data field (or a global variable if you prefer to store the data there).

When we put everything together, we get a nice FileMaker dropdown capable of filtering data inside a web viewer:

Compared to the time needed to filter a big JSON array in FileMaker (by looping through all records or using a custom function), this little JavaScript solution is extremely fast. When you begin to add more filters, you’ll need to add additional logic to make them work together. The basis process is to collect all data in FileMaker, define a criteria, and send it to a JavaScript function to do all the work. When that’s done, call another FileMaker script to set either a global field or a variable.

Filtering Based on Multiple Names

Filter is one of the many useful array methods we can use to manipulate the data. There are two other methods we highly recommend investigating named map and sort. While sort is pretty self-explanatory, let’s review another example in FileMaker that uses map to build a more complex filter. Instead of using a dropdown, we will use a portal to filter a web viewer to only show the names that we select in FileMaker. 

In this example, we aren’t able to pass a single value like “male” or “female”. Instead, we need to construct and return a delimited list of names in FileMaker and then pass those to the web viewer. Here is the JavaScript function we will use to receive this data:

function filterByMultiple(data,id_list){
    var arr = id_list.split("\r");
    var res = [];
    arr.map(function(name) { 
         JSON.parse(data).filter(function (obj) {
             if(obj.name== name){
                   res.push(obj)
              };
         });
     });
     jsonFilter = JSON.stringify(res);
     FileMaker.PerformScript('wv_res_filterByMultiple', jsonFilter);
 }

JavaScript doesn’t natively work with delimited lists so the first step is to convert this list to an array. The Map function allows us to go through that array and, for each element (name, in this case), filter the JSON object similar to the first example. Each object that meets the filter criteria gets added to a new array which we will pass back to FileMaker. The FileMaker scripts will then construct the list of names and set a data field or variable with the result. In a real-world scenario we would recommend using record ids instead of names and perhaps adding a third parameter so you can use any key in this function. There is room for enhancement and optimization within this example, but we believe these two JavaScript functions can become a great tool for anyone looking to improve their web viewer integrations.

Using JavaScript in your FileMaker apps just became a whole lot easier; learn more JavaScript tips here.