Resources / Tutorial

Integrating JSONata into FileMaker

FileMaker's native JSON functions are fine for simple reads, but querying and reshaping nested JSON gets painful fast. JSONata is a purpose-built JSON query language — here's how to run it inside FileMaker through a Web Viewer, with data flowing both ways.

FileMakerJSONJSONata

This walks through wiring JSONata into FileMaker end to end: a small web project that loads JSONata, a Web Viewer to host it, and the two FileMaker scripts that pass data in and pull results back. If you're new to running JS inside FileMaker, start with using JavaScript in the Web Viewer first.

Prerequisites

Basic knowledge of FileMaker and scripting
Basic understanding of HTML and JavaScript
Node.js and npm installed
FileMaker Pro installed

Step 1 — Set up the project directory

mkdir C:\JSONataTest
cd C:\JSONataTest

Step 2 — Initialize the project

npm init -y
npm install -g http-server
mkdir public src
touch src/index.js public/index.html public/test.html webpack.config.js server.js

Step 3 — Webpack configuration

const path = require('path');

module.exports = {
  mode: 'development',
  entry: path.resolve(__dirname, 'src', 'index.js'),
  output: {
    filename: 'bundle.js',
    path: path.resolve(__dirname, 'public'),
  },
  module: {
    rules: [
      {
        test: /\.js$/,
        exclude: /node_modules/,
        use: {
          loader: 'babel-loader',
          options: { presets: ['@babel/preset-env'] },
        },
      },
    ],
  },
};

Step 4 — The HTML page

This page loads JSONata from a CDN, receives data from FileMaker, runs the query, and sends the result back:

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>JSONata Demo</title>
  <script src="https://cdn.jsdelivr.net/npm/jsonata@1.8.4/jsonata.min.js"></script>
  <script>
    function receiveDataFromFileMaker(data) {
      try {
        const parsedData = JSON.parse(data);
        document.getElementById('filemaker-data').innerText = parsedData.data;
        document.getElementById('jsonata-query').value = parsedData.query;
        processJSONData(parsedData.data, parsedData.query);
      } catch (error) {
        alert('Error processing data from FileMaker: ' + error.message);
      }
    }

    function sendDataToFileMaker(dataToSend) {
      if (typeof FileMaker !== 'undefined') {
        FileMaker.PerformScript('HandleDataFromWebViewer', dataToSend);
      } else {
        alert('FileMaker.PerformScript is not available in a plain browser.');
      }
    }

    function processJSONData(jsonInput, query) {
      try {
        const json = JSON.parse(jsonInput);
        const expression = jsonata(query);
        const result = expression.evaluate(json);
        document.getElementById('json-output').innerText = JSON.stringify(result, null, 2);
        sendDataToFileMaker(JSON.stringify(result));
      } catch (error) {
        alert('Error processing JSON data: ' + error.message);
      }
    }
  </script>
</head>
<body>
  <h1>FileMaker Data: <span id="filemaker-data"></span></h1>
  <h2>JSONata Example</h2>
  <textarea id="json-input" rows="10" cols="50" placeholder="Enter JSON data here"></textarea>
  <input type="text" id="jsonata-query" placeholder="Enter JSONata query">
  <button onclick="processJSONData(document.getElementById('json-input').value, document.getElementById('jsonata-query').value)">Process</button>
  <pre id="json-output"></pre>
</body>
</html>

Step 5 — The JavaScript module

If you'd rather bundle JSONata than load it from a CDN, mirror the same functions in src/index.js and expose them globally so the Web Viewer can call them:

const jsonata = require('jsonata');

function receiveDataFromFileMaker(data) {
  const parsedData = JSON.parse(data);
  document.getElementById('filemaker-data').innerText = parsedData.data;
  document.getElementById('jsonata-query').value = parsedData.query;
  processJSONData(parsedData.data, parsedData.query);
}

function sendDataToFileMaker(dataToSend) {
  if (typeof FileMaker !== 'undefined') {
    FileMaker.PerformScript('HandleDataFromWebViewer', dataToSend);
  }
}

function processJSONData(jsonInput, query) {
  const json = JSON.parse(jsonInput);
  const result = jsonata(query).evaluate(json);
  document.getElementById('json-output').innerText = JSON.stringify(result, null, 2);
  sendDataToFileMaker(JSON.stringify(result));
}

// Expose to the global scope so the HTML (and FileMaker) can reach them
window.receiveDataFromFileMaker = receiveDataFromFileMaker;
window.sendDataToFileMaker = sendDataToFileMaker;
window.processJSONData = processJSONData;

Step 6 — Build and serve

npx webpack
node server.js

Open http://localhost:3000 in a browser to confirm the page works before embedding it.

Step 7 — The FileMaker scripts

Send JSON plus a JSONata query into the Web Viewer:

# Script: SendDataToWebViewer
Set Variable [ $data  ; Value: Table::JSONField ]    # the JSON data
Set Variable [ $query ; Value: Table::QueryField ]   # the JSONata query

# Combine into one object
Set Variable [ $json ; Value: JSONSetElement ( "{}" ;
    [ "data"  ; $data  ; JSONString ] ;
    [ "query" ; $query ; JSONString ]
) ]

Perform JavaScript in Web Viewer [
    Object Name: "WebViewer1" ;
    Function Name: "receiveDataFromFileMaker" ;
    Parameter: $json
]

Receive the processed result back:

# Script: HandleDataFromWebViewer
Set Variable [ $data ; Value: Get ( ScriptParameter ) ]
Set Field [ Table::ResultField ; $data ]

Step 8 — Test the round trip

Open the FileMaker layout with the Web Viewer pointed at your page (or the bundled HTML).
Run SendDataToWebViewer — it pushes the JSON and query in.
Confirm the query result renders in the page and lands back in your FileMaker field via HandleDataFromWebViewer.

With that in place, you can lean on JSONata's expression language for filtering, mapping, aggregation, and reshaping — the things that turn into deeply nested, unreadable formulas with FileMaker's native JSON functions.

Wrangling messy JSON in FileMaker?

Integrations, transformations, and Web Viewer tooling built into your solution. I take this work on directly — tell me what you're trying to process.

Work with me

Calling AI APIs from FileMaker

Read it →