With FileMaker 19 or newer, you can connect to an OData API as an external data source and leverage the RESTful API functionality to retrieve, update, and integrate data between FileMaker and the OData Source. OData (Open Data Protocol) enables the creation and consumption of data services that allow the ability to perform CRUD (Create, Read, Update, and Delete) operations on resources over the HTTP protocol. OData with FileMaker follows widely accepted web standards like HTTP, REST, and JSON, allowing communication between your different systems or platforms. It supports transactions, deletions, and performance enhancements with FileMaker for businesses and clients, making it easier than ever to remotely access FileMaker records.

How To Set Up OData for For FileMaker

Using the functionality of OData for FileMaker requires a bit of setup. You’ll need to make sure you do the following in preparation:

1. Your FileMaker file needs to be hosted using FileMaker server.

2. Your FileMaker file needs authorization for OData and Rest to be permitted in security.

3. FileMaker Server must have OData enabled.

Once you've completed those requirements, we're ready to do the OData operations. To do this, we'll utilize Zapier and Tableau.

Tableau for OData

Tableau allows us to display the database of our FileMaker file remotely with OData. For more information, check out our blog about using FileMaker with Tableau.  For testing purposes, use Tableau Public, which can be accessed from public.tableau.com. 

After Tableau is opened, click the "OData" button in the To a Server Section and it will direct you to this page.

The URL must be set up like this: https://host/fmi/odata/version/filename

After the URL is entered, enter the respective username and password with the OData and Rest enabled. This will redirect you to the data from your FileMaker file and allow you to create a sheet with the fields you wish to display and observe. Tableau allows you to remotely access the records in a FileMaker file hosted with OData.

Transactions and Deletions using Zapier Webhooks

Zapier allows us to create Webhooks to lead to FileMaker and create the CRUD operations through it. For more information, read our blog about setting up Zapier Webhooks for FileMaker using Salesforce.

The GET, PATCH, and DELETE must be custom functions to work with FileMaker and OData and the Header of Content-Type: application/json as shown below.

OData allows users to perform HTTP methods with a consistent syntax shown below for each CRUD operation and give a response with the appropriate information as JSON.

GET Method

The GET Method allows you to request and view data in the database. To request all records in a table, the URL should be set up like this: https://host/fmi/odata/v4/filename/tablename

You can also search for specific records by appending the primary key of the record you wish to see, like this: https://host/fmi/odata/v4/filename/table('primary key')

To request the database names themselves, instead of using the table name, you can replace it with $metadata and it will also have all the metadata for each table.

POST Method

The POST Method allows the creation of new records and tables in the database. To create a record, the URL will look like this: https://host/fmi/odata/v4/filename/tablename

In the Zapier Webhook, under the action tab, set up the payload type as json and set the new data for your record in the data section. Make sure that all the fields match up to the field in Tableau. 

After testing the Zapier Webhook, a new record should appear in Tableau after refreshing it.

PATCH Method

The PATCH Method allows the update of records. To update a record, the URL should look like this: https://host/fmi/odata/v4/filename/tablename('primary key') 

Place the data you want to replace in JSON format in the data section of Zapier.

Tableau will then display the record changes that was used in the request.

DELETE Method

The DELETE Method deletes a record. The URL should look like this: https://host/fmi/odata/v4/filename/tablename('primary key')

Using Queries in OData

There are several options that can be used to query data when appended to the request URL. Queries allow for an easy and simple way of reducing your record on a request and specifying information from your records.

• $filter - Used to filter through records and only records that evaluate as true in the response. https://host/fmi/odata/v4/filename/tablename?$filter=(expression)

• $orderby - Used to order the records as ascending or descending for a specific field. https://host/fmi/odata/v4/filename/tablename?$orderby=fieldname {asc/desc}

• $count - Used to get the count of matching records. https://host/fmi/odata/v4/filename/tablename$count=true

• $select - Used to select only a limited amount of fields. https://host/fmi/odata/v4/filename/tablename?$select=field 1, field 2

Conclusion

OData enhances so much with FileMaker, adding flexibility and allowing the use of RESTful connections that make creating, updating, searching, and deleting data simpler. You can use OData query options to filter, sort, and paginate data, allowing for efficient and targeted data retrieval. It can change how data in FileMaker is manipulated and open up a robust and scalable way to execute a large number of requests at a time. For any further assistance using OData on your FileMaker Server, contact us at DB Services.