How to Integrate JSONata with FileMaker for Data Processing

This tutorial will guide you through the process of integrating JSONata with FileMaker, enabling seamless data processing and interaction between FileMaker and a web application. We will cover setting up the environment, creating the necessary scripts in FileMaker, and building the web application to process data using JSONata.

Prerequisites

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

Step 1: Set Up the Project Directory

mkdir C:\JavaTest
cd C:\JavaTest

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: Set Up 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: Create the HTML File

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <title>Proofgeist Demo with JSONata</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;
        alert('Received data from FileMaker: ' + parsedData.data + ' and query: ' + parsedData.query);

        // Process the data using JSONata
        processJSONData(parsedData.data, parsedData.query);
      } catch (error) {
        alert('Error processing data from FileMaker: ' + error.message);
      }
    }

    function sendDataToFileMaker(dataToSend) {
      alert('Attempting to send data to FileMaker: ' + dataToSend);
      if (typeof FileMaker !== 'undefined') {
        FileMaker.PerformScript('HandleDataFromWebViewer', dataToSend);
        alert('Data sent to FileMaker');
      } else {
        alert('FileMaker.PerformScript is not available in the web 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);

        // Send the processed data back to FileMaker
        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>
  <input type="text" id="input-data" placeholder="Enter data to send to FileMaker">
  <button onclick="sendDataToFileMaker()">Send Data to FileMaker</button>

  <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 JSON Data</button>
  <pre id="json-output"></pre>
</body>
</html>

Step 5: Create the JavaScript File

const jsonata = require('jsonata');

function receiveDataFromFileMaker(data) {
  try {
    const parsedData = JSON.parse(data);
    document.getElementById('filemaker-data').innerText = parsedData.data;
    document.getElementById('jsonata-query').value = parsedData.query;
    alert('Received data from FileMaker: ' + parsedData.data + ' and query: ' + parsedData.query);

    // Process the data using JSONata
    processJSONData(parsedData.data, parsedData.query);
  } catch (error) {
    alert('Error processing data from FileMaker: ' + error.message);
  }
}

function sendDataToFileMaker(dataToSend) {
  alert('Attempting to send data to FileMaker: ' + dataToSend);
  if (typeof FileMaker !== 'undefined') {
    FileMaker.PerformScript('HandleDataFromWebViewer', dataToSend);
    alert('Data sent to FileMaker');
  } else {
    alert('FileMaker.PerformScript is not available in the web 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);

    // Send the processed data back to FileMaker
    sendDataToFileMaker(JSON.stringify(result));
  } catch (error) {
    alert('Error processing JSON data: ' + error.message);
  }
}

// Expose functions to the global scope for the HTML to access
window.receiveDataFromFileMaker = receiveDataFromFileMaker;
window.sendDataToFileMaker = sendDataToFileMaker;
window.processJSONData = processJSONData;

Step 6: Build and Serve the Project

npx webpack
node server.js

Open your browser and navigate to http://localhost:3000.

Step 7: Set Up FileMaker Scripts

Script: SendDataToWebViewer

# Set Variables
Set Variable [ $data; Value: Table::JSONField ]  // The JSON data from a field
Set Variable [ $query; Value: Table::QueryField ]  // The JSONata query from a field

# Combine JSON data and query into a single JSON object
Set Variable [ $json; Value: JSONSetElement ( "" ; "data" ; $data ; JSONString ) ]
Set Variable [ $json; Value: JSONSetElement ( $json ; "query" ; $query ; JSONString ) ]

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

Script: HandleDataFromWebViewer

# Set Variable
Set Variable [ $data; Value: Get(ScriptParameter) ]

# Set Field
Set Field [ Table::Field; $data ]

Step 8: Test the Integration

Open FileMaker Layout with Web Viewer: Ensure the Web Viewer is set to http://localhost:3000.
Run the SendDataToWebViewer Script: This script sends JSON data and the JSONata query to the Web Viewer.
Verify Data Processing: Verify that the received data and query are displayed correctly in the Web Viewer. Check the output of the processJSONData function. Ensure that the processed data is sent back to FileMaker.

By following these steps, you will have successfully integrated JSONata with FileMaker, allowing seamless data processing and interaction between FileMaker and your web application.

If you have any further questions or need additional assistance, feel free to reach out.