filemaker.png

Joseph Petty Verified user

Sr. Developer Advocate

Appsmith

Filemaker API Connector: A Free and Open-Source Starter Solution for Integrating Filemaker with Any API Or Database

Note: This is an updated version of my original Reddit post, which can be found here.

Hey, I'm Joseph, an engineer at Appsmith, and a long-time FileMaker Pro consultant and developer. I freelanced in FileMaker Pro for years, integrating APIs like Shopify, BigCommerce, eBay and other services, using the insert from URL script step, curl requests, and roughly a terabyte of \"escaped quotes\" 😖

FileMaker is a powerful low-code platform that can build some pretty amazing apps, but the developer experience isn’t always as… let’s just say — modern. And while curl requests still have their uses, these days, I’d much rather use a Postman-like interface for making API calls. So I built one! And I wanted to share it with the FileMaker community.

r/filemaker - FileMaker API Connector: Appsmith App

This app, built on Appsmith, provides a starting point for connecting your FileMaker data to almost any API or Database using one of Appsmith’s many integrations, and a Postman-like API builder.

r/filemaker - Appsmith Query Editor

 

r/filemaker - FileMaker API Connector: Appsmith App

 

Getting started

The app handles the FileMaker login flow and query building, using a UI to select fields and enter search terms without coding—just like a Find Request in FileMaker. It generates the actual JSON query object for you and runs the API request, returning any matching records.

To get started, click the Fork App button in the top right to copy the app to your Appsmith account. Then, follow the instructions in the app to connect to your FileMaker server.

r/filemaker - FileMaker API Connection Settings

FileMaker API Connection Settings

Click the Test Connection button to verify if the API is working, and then close the setup window.

Enter the layout name you want to query, and the app will pull in the table name, field names, and total record count. This populates the Select widgets in the query builder so you can easily build complex AND/OR queries with multiple conditions.

Click FIND to run the query and the table should populate with the first 100 records from your FileMaker database. This query builder uses Appsmith's JSON Form widget, which dynamically generates a form from a JSON object.

Next, try entering a few search terms using the query builder, and set a Query TypeAND or OR. See how the query-body preview updates and the JSON structure changes? Awesome! Now let's check out the API requests.

r/filemaker - FileMaker API Query Type

FileMaker API Query Type

GET or POST

The FileMaker API uses a GET method to retrieve records from a layout if no specific filter is used. However, to perform a find request, a POST method is used to send the query conditions in the POST body.

 

The search works the same as FileMaker's native find requests, using the same operators for wildcards *, exact matches ==, and others.

AND requests group the conditions as multiple properties of the same object:

{
  "query": [
    {
      "address_state": "FL",
      "first_name": "J*"
    }
  ]
}

OR requests separate each condition into a separate object:

{
  "query": [
    {
      "address_state": "FL"
    },
    {
      "first_name": "J*"
    }
  ]
}

Pagination

Feel free to skip to the next section if your table has <=100 records. Still here? Ok, well it sounds like you might need to paginate your data. But do you? 🤨

If possible, try to request only the records needed client-side and limit the results to less than 100 records, the limit per request for the FileMaker API. If you really need more than 100 records pulled, check out this guide on pagination.

Low-code: Integrate with another database or API

There's a lot you can do without coding in Appsmith, but you can do even more with JavaScript, like controlling widgets’ behaviors and appearances, transforming data, or chaining together multiple actions. This app was built using a few JavaScript nuggets to make the query builder, but it can easily be extended to send data to another API or database without additional coding.

Just add a new column to the table widget and set the type to Button. Then add a new API or database query to send data from the current row to another system.

r/filemaker - Add API Query

Building the query body with JavaScript

The JSONForm widget supports Array and Object type fields, and allows the user to add additional objects—sets of fields and values—to an array. In this case, you are adding new query objects with inputs for the field_name and search_term. The data can be accessed inside the JSONForm widget by referencing JSONForm1.formData.

{
  "query": [
    {
      "field_name": "address_state",
      "search_term": "FL"
    },
    {
      "field_name": "first_name",
      "search_term": "J*"
    }
  ],
  "query_type": "AND"
}

Then, this data is transformed using a map() function, or forEach() function, depending on the query_typeAND or OR.

if (!JSONForm1.formData?.query) {
  return ''
}
let queryBody = {
  query: [{}]
};
let conditions = JSONForm1.formData.query;
let queryType = JSONForm1.formData.query_type;
if (queryType == 'OR') {
  let body = conditions.map(c => ({[c.field_name]: c.search_term}));
  queryBody['query'] = body;
} else {
  conditions.forEach(c => queryBody['query'][0][c.field_name] = c.search_term)
};
return queryBody
}

Server credentials and security

For easy setup and demo, this public Appsmith app was built using a client-side form to input the FileMaker API credentials as an app user. Appsmith also offer a secure datasource feature that saves the credentials on your Appsmith server as an admin, without exposing them to the user. Check out our Authenticated API docs for more info.

What’s next?

I started this app as a fun experiment to learn the FileMaker API and query structure, but it quickly evolved into the perfect starting point to connect FileMaker to any API or database. Hope this helps you get started on your own integrations!

I would love to hear back from you on your experience using the app, or if you would like to collaborate on adding additional features. I may even open-source this app as its own project if others are interested in contributing.

 

Update: April 2024

I got my start with FileMaker Pro in 2010 while working at a national laboratory that used it for data collection. The database needed some updates so I taught myself, and I ended up becoming the main dev for any FMP work at the lab. That lead to some side work, and more work, and then in 2016 I left the lab to start my own company and freelance full time. 

After a few years of FMP, I switched to Google AppSheet for about 4 years, then found Appsmith in 2020 and made it my main tool for freelance work. I joined the Appsmith team about 1.5 years later as a senior developer advocate, and now I lead our freelancer program. It's been an amazing journey, and I couldn't have done it without the communities around each one of the platforms I used along the way. So now I want to help other low-code freelancers in their journeys by building our developer community with freelancers in mind. 

If you're a freelancer in Appsmith, AppSheet, or FileMaker Pro, or any other low-code platform, I'd love to hear from you. And if you want to start freelancing but don't know where to start, feel free to reach out on Discord (joseph_appsmith).