This blog post is the second of a three-part series about the FileMaker OData API, introducing a demo file with more than 40 examples plus helpful tips, along with Q&A. Part one of the series highlighted some of the key features of the API which make it an exciting addition to the FileMaker platform. As a follow-up, this post will cover the basics and dig into the nuances of using OData with Claris FileMaker.
Get Started With Hands-On Learning
As a learning tool, this post shares an example file that illustrates the basics of many common FileMaker OData API requests. I’ll sometimes refer to this functionality as “FM OData” for short.
Using the example file in a fully-hands-on fashion requires having either Claris FileMaker Cloud, or a Linux FileMaker Server installation available. If you do not have an OData-enabled FileMaker server at your disposal, you are still encouraged to download the example file and review its contents. Even without a server, there is much which can be learned just by browsing the content.
Setting Up The Example Files for Hosted Use
If you do not have an OData-enabled FileMaker server, or if you do not wish to host the sample data file, please skip ahead to the Forty Examples of FileMaker OData section.
Download the Example and Data files:
- Download the example file archive.
- The archive should contain the following two files:
- OData_Examples.fmp12 (Sample OData requests)
- OData_SampleData.fmp12 (Optional sample data file)
Prepare and host the OData_SampleData file:
- Open the OData_SampleData file using FileMaker Pro.
- Disable the existing Admin [Full Access] account.
- Set up a new [Full Access] account.
- Set up a new [Data Entry Only] account.
- Secure the newly added accounts with strong passwords.
- Upload the OData_Sample file to an FM OData enabled server.
Prepare the OData_Examples file:
- From the Examples menu, select “Access To Hosted File“
2. From “Access To Hosted File” choose the appropriate option:
- Configure With Claris ID
- Configure With Account Credentials
3. Supply the hostname of the OData server, and the credentials to access the [Read Only] account of the OData_SampleData file.
4. Click OK to save the hostname and credentials. (Note that credentials are cleared when the example file is closed.)
Forty Examples of FileMaker OData
The example file contains (more than) forty examples of common FileMaker OData requests. Each example documents the necessary endpoint URL and HTTP headers, and includes both a sample request and response content. A link to the relevant Claris documentation is provided for each example in the file.
The examples progress from basic concepts, such as retrieving all records from a table, to more advanced concepts such as Batches and Change Sets.
If you have hosted a copy of the OData_SampleData file, you can test running the examples against your hosted file, and even edit the examples to experiment with variations.
As much as possible, examples for tips and tricks are included, to help you get the most out of your introduction to FileMaker’s OData.
Nuances of using FM OData
These are a few nuances to keep in mind as you work through the FM OData API examples and sample queries.
- FM OData schema references use Table Occurrence names (not Table names).
- When invoking a Script via FM OData, both the On First Window Open and the On Last Window Close scripts are invoked.
- When matching a record via Primary Key, FM OData checks the table definition for a field defined to be unique and non-null, and takes the first-such found field as the Primary Key field.
- When matching a record via Primary Key, if no there is no field in the target table which is set to unique and non-null, then FM OData will use the record’s Record ID value as a primary key.
- OData query filters (e.g. “eq”) and query filter functions (e.g. “contains” ) are case-sensitive.
Questions and Answers
The following are answers to questions which may arise as you first get started with FM OData. Each point listed below is also illustrated in the example file.
Please keep in mind that, as FM OData is still a very recent technology, we are all still learning the answers. As such, it is possible that the best solutions to these questions may be yet to come.
1) Returned fields
Q: How do I specify a subset of fields to return, instead of having my query return data for all fields in a table?
A: Use the $select query option. (#7 in the example file)
2) Log Privacy and Avoiding GET
Q: Can I use a POST HTTP request, instead of a GET, to avoid having sensitive data logged in the OData access log?
A: Queries that fetch data are required to use the GET HTTP verb, however, such queries can be wrapped inside of an OData batch request, which results in the overall HTTP request being transmitted as a POST (#45 in the example file).
Note: Credit and thanks go to participants of DIGFM, where I first presented on FileMaker OData, for raising the question of whether or not queries could be sent via POST.
3) Receiving JSON data with UUIDs
Q: How do I fetch a UUID Number field as a JSON string, so as to avoid potential data loss with a long numeric value?
A: Include the following header in the HTTP request: “Accept: application/json;IEEE754Compatible=true” (#27 in example file)
4) Sending JSON data with UUIDs
Q: How do I send a UUID Number in a request, without loss of precision? If I quote the value within the JSON, I receive a type mismatch error…
A: Clumsy workaround: Rather than send the request using JSON, opt to use the XML-based Atom format, which will preserve the integrity of long numeric data. (#29 in example file)
5) Case-Insensitivity
Q: How do I perform a query using a case-insensitive filter?
A: Use the toupper or the tolower query functions to wrap the field which requires case-insensitivity. (#16 in example file)
6) Filter Documentation
Q: Where can I find a list of all of the possible query filter operators?
A: The Oasis OData Specification has a list of query operators. Scrolling down a little further there, you will also find a list of query functions.
What Comes Next? Transactions
Part three, the final installment of this series, will conclude with an introduction to the exciting world of FileMaker’s OData transactional operations.
OData for FileMaker – Beezwax Blog Series
Part 1: OData for FileMaker – New Player in an Old Game
Part one highlights key features of the OData API which make it an exciting addition to the FileMaker platform.
Part 2: OData for FileMaker – Examples, Tips, and Nuances
Part two introduces a demo file with more than 40 examples, includes a few tips, as well as Q&A.
Part 3: OData for FileMaker – Transactional Operations Part three introduces the exciting world of FileMaker’s OData transactional operations. [Note: (Jan 27, 2023) The introduction of native Script Transactions in the FileMaker platform gives us a new starting point for transactional operations.]
Thank you for your interest! I hope this helps you with your FileMaker Development.
-Steve