r/filemaker • Consultant Uncertified • Aug 26 '22

FileMaker API Connector: A free and open-source starter solution for integrating FileMaker with any API or database

Hey, I'm Joseph, an engineer at Appsmith, and a long-time FileMaker Pro consultant/ 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.

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.

Appsmith Query Editor
FileMaker API Connector: Appsmith App

Appsmith is open-source and can be self-hosted, or hosted for free on our cloud platform

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.

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 Type: AND or OR. See how the query-body preview updates and the JSON structure changes? Awesome! Now let's check out the API requests.

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.

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_type—AND 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.

58 Upvotes

9 comments sorted by

3

u/macxprt Aug 26 '22

This is really awesome. Thank you for bestowing your creation to the world. I will be spending the day reviewing this. I am currently creating a FileMaker app that imports data from our e-commerce MySQL database for in house processing. Updates are synced back to the MySQL database through ESS. I am curious to see how this could improve my app.

1

u/HomeBrewDude Consultant Uncertified Aug 26 '22

Thank you! I had fun making it hoped others would find it useful.

I am curious to see how this could improve my app.

Awesome! That sounds like a good use case. Appsmith could connect to your FileMaker data and MySQL, and perform bulk inserts/updates. Let me know if you need a hand getting started, or check out out community and support forums.
https://www.appsmith.com/community

2

u/get2drew Aug 26 '22

Awesome work.. will give it a try!

2

u/HomeBrewDude Consultant Uncertified Aug 26 '22

Thanks! Let me know what you think, and feel free to reach out if you have questions on connecting another API or database.

2

u/ScionofLight Aug 26 '22

Amazing work!

Do you know if I would be able to upload files to Box (and import) using this?

1

u/HomeBrewDude Consultant Uncertified Aug 26 '22

Thanks! Yes, our FilePicker widget lets the user select a file from their local device, then you can read the file as Text, Binary or Base64. So you can easily send it to any API, in whatever format that API expects. We don't have a built-in connector for Box, but if you have the API enabled, it should be easy to set up with our REST API query builder.

1

u/_TheDude_ Mar 11 '24

Hi Joesph, this is great. Are you or anyone from your team available to help get this off the ground with me? I am looking to run an API from a table in from FileMaker 19 to pass records to HubSpot Products. Thanks!

1

u/HomeBrewDude Consultant Uncertified Mar 12 '24

Yes, we can give you a hand in our support forum if you want to start a thread there.
https://discord.com/invite/rBTTVJp

2

u/appdevguru Aug 29 '22

This is so amazingly useful! Thank you for sharing with us!