An Almost-Universal API Template for Claris FileMaker and FileMaker Pro

Connecting FileMaker to APIs dramatically expands what we can do with the platform. In most cases making that connection all comes down to a single script step: Insert from URL (with just a few cURL options).  However, getting this step just right can be tedious and time-consuming. It helps to have something like an API Template for FileMaker Pro.

Getting a successful API integration set up and connected to Filemaker Pro is all about eliminating possible issues on a step-by-step basis. A series of proven steps can be very reliable and lead to success even in the most complex environments. We have even implemented an API in a system where we didn’t have access, due to the system being behind the client’s firewall!

This article will lay out Skeleton Key’s process for ensuring successful API integrations and provide an example file to make your life easier.

#1: Stay away from FileMaker – use Postman first.

Before trying to get the API set up in FileMaker Pro, use Postman to validate the basics: URL, Authentication, and the most simple GET call you can find. Postman is a widely used API development tool and many APIs even provide “Collections” with example calls; just search Google for “[MYAPI] Postman collection”. This link provides a good introduction to how to use Postman: https://www.guru99.com/postman-tutorial.html

#2: Don’t try to solve your ultimate goal first.

When integrating with an API you likely have a goal in mind, like creating an email campaign in Mailchimp. Put that on the back burner for now. The biggest challenge is to make the first successful API call, as once that is done, everything will be easier. The first call should always be the simplest call to the target API you can find, i.e., a GET call without any parameters or data payload. To find that call, of course, you’ll first need to find the API’s documentation (such as https://mailchimp.com/developer/marketing/docs/fundamentals/#connecting-to-the-api), and not a third-party blog post that purports to summarize that documentation for you. Stay away from POSTs, PATCHES, and DELETEs for now.

With a simple GET call—still using Postman—you can test what is often the biggest initial hurdle: Authenticating to the API. There are many methods, and Postman can handle most of them right out of the box:

… as a successful Authentication call helps you to confirm that you have the correct URL, Authentication Token, Username, etc.

#3: Never try to compile the cURL options yourself.

I have read many online forum posts on how to precisely and manually configure the cURL options in the FileMaker “Insert from URL” script step. Don’t bother! That work has been done for you already.

Being part of the FileMaker community brings a lot of advantages, and one of them is that you don’t have to figure everything out for yourself. Let’s use that benefit to skip such details, and just make your FileMaker Pro API integration work. To do so, we’ll use a script that handles all of this for us, provided by the great people at Proof+Geist: https://github.com/proofgeist/mfm-http.

In our example file, we’re using the HTTP script from their sample file to which we have added a simple structure that can be used as a foundation to integrate with many APIs. To do this we simply translate what worked in Postman to a copy of their “Universal Endpoint Template” script.

The (Almost) Universal API Template—Getting Started

Skeleton Key’s API Template file is built so all you have to do is copy and paste scripts into your own solution, as none of our scripts rely on the schema or structure of our example file. That said, it’s up to you if you want to develop and test your integration using the example file or copy the template scripts to your solution first. To start in your own file, simply copy the “API” script folder with all scripts in it; nothing else needs to be copied.

When integrating with an API, there will always be a few central settings that will remain the same for all calls and some that change depending on the exact call that is being placed (e.g., the endpoint that the call is targeting).

The “HTTP Handler” script and its subscripts handle all the elements common to all calls:

• The Base URL:
  • Configure this in the script “HTTP Handler”
  • This is the part of the URL that remains the same for all calls.
  • For Mailchimp, this could be something like “https://us18.api.mailchimp.com/3.0/“
• Headers:
  • Configure this in the script “-  API CONFIG Headers”
  • Headers send information about the call to the server, in most cases that includes things like what type of content (“Content-Type”) is being sent (JSON, Form Data, etc.)
  • The documentation for the API you are calling will indicate what to use
  • Note that the “Content-Length” Header is generated by the example file automatically
• Authentication:
  • Configure this in the script “-  API CONFIG Authentication”

The “Endpoint” scripts handle anything specific to the specific call:

• The Endpoint:
  • The “endpoint” for the URL, such as “/campaigns” which lists all campaigns from Mailchimp
• Request Body:
  • It compiles any data that needs to be sent
  • The data itself should be passed in from a calling script, demonstrated here with “Test” scripts
• Query Parameters:
  • Not always used, but when they are, this makes up the part of the URL following the “?” such as “https://www.google.com/search?q=test”

Postman to FileMaker Pro– Initial Configuration for the API

Now it’s time to transfer the basic configuration details from Postman to the FileMaker scripts to get the FileMaker Pro-API configuration going:

Base URL

Edit Script “HTTP Handler”

1. Enter it in the step “Set Variable [ $baseURL; Value: “ENTER BASE URL HERE” ]”

Authentication

Edit the script “- API CONFIG Authentication” as follows:

1. In line 20 (see comments in the script), select the Authentication type the API requires by enabling ONLY the corresponding variable.
   1. In the case of Mailchimp, it is “Bearer Token”.
2. In line 59, under the “Else If” section corresponding to the selected Authentication method, fill in the variables needed.
   1. Most sections have a comment “No Edits needed below”; don’t edit what comes after that comment for that section, as everything is compiled the way it needs to be for the final call.
   2. For the Mailchimp example, only the Token is needed:

IMPORTANT NOTE: While, for the sake of this example, we hardcoded the token into the script, this is not the best practice and should be avoided. It is beyond the scope of this post to go into details on why, but credentials should always be stored encrypted and in such a way that neither a Database Design Report nor a full data export would accidentally reveal any sensitive information.

Headers

Edit the script “- API CONFIG Headers”

The template file comes with two common content types as examples. Simply enable the one you need, or edit it and add something else. You can add more Headers by simply duplicating one of the variables and editing its JSON key and value. For Mailchimp only one Header is required, the “Content-Type”.

That’s it for the required central configuration. These core scripts are now available for all endpoint calls!

IMPORTANT NOTE: To assist with troubleshooting the HTTP script, you can capture a lot of details to a global variable $$HTTP_LOG (again, with a tip of the hat and thanks to Proof+Geist! For this technique!). To enable this advanced logging, simply enable the step “Set Variable [ $$ENV_DEBUG_HTTP ; Value: True ]” in the HTTP script and then review $$HTTP_LOG variable after you’ve placed a call. We’ve also included another troubleshooting resource, i.e., a URL override, which I will go into in more detail below.

Postman to FileMaker Pro– the first endpoint

With the basic configuration out of the way, it’s now time to configure an endpoint script. The idea here is to have a separate FileMaker script per endpoint. When the first one works, it can be duplicated and edited as needed for the next endpoint, and so on.

The endpoint scripts are designed to never interact with the schema which will keep them as universal and portable as possible. Any data needed to make the call should be passed in as a script parameter, and the API response that is passed back from the API should be passed back to the calling script via an ‘Exit Script’ script step.

To follow the Mailchimp example, one of the most basic endpoints is to list all Campaigns for a given account.

Here’s how to configure the Campaign endpoint script:

1. First, I recommend you duplicate the Endpoint Template script so you can refer to the original (or duplicate it again) if needed.
2. Edit two steps in your endpoint script:

3. Test the endpoint.
4. In the test script folder, duplicate the template script or create a new script to call the endpoint script and capture the result in a global variable to review:

5. Run the test and review the results.
   • If anything does not work, make sure $$ENV_DEBUG_HTTP is enabled (see above), and then review $$HTTP_LOG for any details.

IMPORTANT NOTE: The test script might seem like an unnecessary complication at this time, but it will come in very handy when you have to pass data to the endpoint script. The test script allows for quick and easy testing of this data pathway. Once everything works as expected, you can have total confidence in the endpoint script, and will likely never touch it again – it just works.

Adding Options

Many API endpoints can be modified by adding Query Parameters, which the Universal template is set up to support. Per the Mailchimp documentation, a query option of “count” can be added to determine how many campaigns should be returned:

In the template file, query parameters are handled in the endpoint script, however, they can be moved to calling scripts to handle different query scenarios and then passed into the endpoint script. To use a query parameter:

1. Enable the template script step under the “Params/ Query Parameter” section.
2. Edit it so the Key and Value match what is needed:

3. To add more parameters, simply duplicate the script step, and edit the JSON Key and Value pair:

Send Data with a POST Request

The Universal Template is designed so that all the central configuration and setup is done once, and so every added endpoint can be re-used without any extra work. Once the first endpoint works, you are off to the races. Everything that follows is extremely fast now, so buckle up.

Let’s take creating a campaign in Mailchimp as an example. The documentation looks like this:

Mailchimp is just like many other APIs, i.e., the differences between endpoints are very small, so the changes needed are minimal. For example:

1. Duplicate the “Mailchimp – Campaigns” Script and rename it to “Mailchimp – POST Campaign”.
2. In that script, change “METHOD” from GET to POST, as the actual endpoint is the same, i.e., “/campaigns”. That’s it. The endpoint is done. Really.

1. The only required Body Parameter is “type”. The body has to be set in $data to be included in the $requestJSON. Since the body changes based on what kind of Campaign is created, it is best to pass that into the endpoint script as a parameter. This way the endpoint script can be used whenever a campaign needs to be created. Here is how you add a Body to the request:

1. Duplicate one of the Test scripts and rename it to match the endpoint, e.g., “Test Script – Mailchimp POST Campaign”.
2. Change the Perform Script step to run “Mailchimp POST Campaign”.
3. In the Test Script, compile the required Body:{   “type” : “regular”}
4. Then pass that Body to the endpoint script as the “data” element. When adding JSON to a JSON parameter make sure to pass it in as type JSONObject!
5. Now run the test script and review the output, this should have been a successful POST request and you created a new Campaign on Mailchimp.

To integrate this endpoint into your solution, use the Test script as a template to get the data you need to send to the API from your database, and then write the result back to a field—but do all of that outside of the endpoint script so it can be called from different scripts if needed.

Create More Endpoints

Just follow the same pattern:

• Duplicate an endpoint script, and edit it as needed.
• Duplicate a Test script, compile test data for the endpoint, and test it.

Everything from here forward should be both fast and reliable, as the hard work is already done.

Troubleshooting

Troubleshooting is tough. You can spend a lot of time in the debugger and still not find the answer. Many times, a call will work in Postman and look the same in FileMaker, but something still does not work as expected.

One of the best ways to troubleshoot is to monitor exactly what you’re sending to the API from FileMaker Pro. The best way to do that is to send your call to a tool like webhook.site, which will display exactly what you send it. This is a reliable way to see what the server on the other end of your call actually receives. A misplaced quote, extra carriage returns, or even an invisible ASCII character, can be detected this way.

But don’t just take my word for it – the best way to see the power of a tool like this is to try it out for yourself:

1. In your browser, navigate to webhook.site.
2. The page will immediately generate a unique URL for you:

3. Keep the website open and copy your unique URL.
4. In the Universal Template file, in the HTTP Handler script, around line 31, you can set a variable called $overrideURL. Enable that step, and paste in your unique URL. This will divert all calls to that URL without editing the endpoints or base URL anywhere else.
5. Remove or temporarily change your API credentials so they don’t get exposed on that site. THIS IS A VERY IMPORTANT STEP!
6. Run the Test script.
7. Review the details on the website.

All the details are broken out, i.e., headers, Method, Query Parameters and Body. Paste the same URL in Postman and send a request from there: Now you can compare the calls and find differences, pointing you to what needs to change in FileMaker.

Below is a rough map to help clarify which script makes up which part of an API call:

We hope the Universal Template gives you a head start on your API integrations. While this post does not cover all possibilities, it should be a good starting point, and eliminate the tedious process of figuring out your own cURL options.

Finally, a big shout out to the FileMaker community and, again, Proof+Geist for providing the HTTP script! And, of course, if you have any questions about how to use the template (which you can download using the form below), or in integrating your FileMaker solution with a particular API, please feel free to contact us!

If you want to download the file and see for yourself, simply fill out the form below and we’d be happy to send you a copy: