FileMaker 17 Data API Guide

The FileMaker^® Data API is an application programming interface (API) that allows web services to access data in hosted databases. Because this API conforms to Representational State Transfer (REST) architecture, the FileMaker Data API is a REST API.

Your web service or application calls the FileMaker Data API to obtain an authentication token for access to a hosted database, then uses that token in subsequent calls to create records, update records, delete records, and perform find requests.

The FileMaker Data API returns data in JavaScript Object Notation (JSON), a text format that is commonly used with REST APIs because it is independent of specific programming language formats.

This guide assumes you are experienced with:

• using FileMaker Pro Advanced to create databases. You should understand the basics of FileMaker Pro Advanced database design and the concepts of fields, relationships, layouts, portals, and containers. See FileMaker Pro Advanced Help.
• using FileMaker Server to host databases. You should understand how to deploy FileMaker Server, enable access to hosted databases, and monitor hosted databases using FileMaker Server Admin Console. See FileMaker Server Help.
• using REST APIs in server-side applications or web services that call POST, GET, PATCH, and DELETE methods with data in JSON format. You can use any programming languages or tools you choose.

To use the FileMaker Data API, follow these steps:

1. Prepare your database for FileMaker Data API access using FileMaker Pro Advanced. You can create a database or prepare an existing database. See Prepare databases for FileMaker Data API access.
2. Write code that calls FileMaker Data API methods to find, create, edit, and delete records in a hosted database. See Write FileMaker Data API calls.
3. Host your solution using FileMaker Server with FileMaker Data API access enabled. See Host a FileMaker Data API solution.
4. Test that FileMaker Data API access is working correctly. See Test a FileMaker Data API solution.
5. Monitor your hosted solution using Admin Console. See Monitor FileMaker Data API solutions.

REST API client

1

6

Web server

2

5

FileMaker Data API Engine

3

4

Database Server

1. A REST API client sends a FileMaker Data API call (HTTPS request) to the FileMaker Server web server over the HTTPS port (port 443). FileMaker Pro Advanced does not need to be installed or running.

2. The web server routes the request through the FileMaker Web Server Module to the FileMaker Data API Engine.
3. The FileMaker Data API Engine converts the HTTPS request (URL and JSON data) into a format that the Database Server component can understand, and requests data from the database hosted by the Database Server.

The FileMaker Data API Engine requires that ports 3000 and 8989 be available.

4. The Database Server sends the requested FileMaker data back to the FileMaker Data API Engine.

The Database Server requires that port 5003 be available.

5. The FileMaker Data API Engine converts the FileMaker data into an HTTPS response (URL with JSON data) to respond to the call, passing the data back to the web server.
6. The web server sends the HTTPS response to the requesting REST API client.

If you aren't experienced using REST APIs, consider the following alternatives to publish your FileMaker data on the Internet.

FileMaker WebDirect^™: Web users connect to your database hosted on FileMaker Server to view, edit, sort, or search records if you give them access privileges. They don't need to install additional software—just compatible web browser software and access to the Internet or an intranet. The user interface resembles the desktop FileMaker Pro Advanced application. The webpages and forms that the web user interacts with are dependent on the layouts and views defined in the FileMaker Pro Advanced database.

See FileMaker WebDirect Guide.

Static publishing: If your data rarely changes, or if you don't want users to have a live connection to your database, you can use static publishing. With static publishing, you export FileMaker Pro Advanced data to create a webpage that you can further customize with HTML. The webpage doesn't change when information in your database changes, and users don't connect to your database.

See Publishing data on static webpages in FileMaker Pro Advanced Help.

Custom Web Publishing: To integrate your FileMaker database with a custom website, use the Custom Web Publishing technologies.

See FileMaker Server Custom Web Publishing Guide.

You can create a FileMaker Pro Advanced database to use with the FileMaker Data API or use an existing database. If you are creating a database, you can design the layouts and fields that your FileMaker Data API solution requires. If you are using an existing database, consider creating a layout specifically for your FileMaker Data API solution.

FileMaker Data API calls that access record data require that you specify a layout. The FileMaker Data API uses the default view that is defined for the layout that you specify. Specify a layout that defines Form View as the default view for the layout. If you use a layout that defines Table View as the default view, the FileMaker Data API will not be able to get data from related records.

The FileMaker Data API requires your REST API code to log in to a database session using a password-protected account. Assign passwords to database accounts that are used for REST API access or use an OAuth service provider for those accounts.

Note:When defining account names and passwords for FileMaker Data API solutions, use printable ASCII characters, for example a-z, A-Z, and 0-9. For more secure account names and passwords, include punctuation characters such as "!" and "%," but do not include colons. See FileMaker Pro Advanced Help.

You must enable the Access via FileMaker Data API extended privilege in each database you want to access using the FileMaker Data API. If you don't enable the FileMaker Data API extended privilege in the database, REST API applications won't be able to use the FileMaker Data API to access the database even if the database is hosted by FileMaker Server.

To enable FileMaker Data API access for a database:

1. In FileMaker Pro Advanced, open the database using an account that has the Full Access privilege set. Alternatively, you can open the database using an account that has the Manage Extended Privileges access privileges.
2. In the Extended Privileges tab of the Manage Security dialog box, select the fmrest extended privilege to enable it.
3. Assign the privilege sets that include the fmrest extended privilege to one or more accounts.

Note:For security reasons, enable the fmrest extended privilege only in the privilege sets for accounts that you want to allow access to your hosted database.

See “Creating and editing privilege sets” in FileMaker Pro Advanced Help.

The FileMaker Data API provides a REST API to access data in hosted databases. The FileMaker Data API allows your code to:

• log in or log out of a hosted database. See Connect to or disconnect from a database.
• create, edit, delete, or get a record; or get a range of records. See Work with records.
• perform find requests. See Perform a find request.
• set global field values. See Set global field values.
• run FileMaker scripts. See FileMaker scripts and the FileMaker Data API.
• uploading data into container fields. See Upload container data.
• access data in external FileMaker tables. See Log in to an external data source.
• use a different layout for response data when getting a record or a range of records. See Get a single record, Get a range of records, and Perform a find request.

The FileMaker Data API does not support:

• access to data in external ODBC data sources.
• FileMaker plug-ins.
• script trigger activation through user interaction. A FileMaker Data API solution can activate script triggers only by running a FileMaker script.
• access to the server machine's file system. For example, the FileMaker Data API does not support the FileMaker Pro Advanced function Get(TemporaryPath). This function returns an empty string when used with the FileMaker Data API. Files can be stored in a container field, but there is no access to the server's file system.

The FileMaker Data API returns field data as it is stored in the database, not as it is displayed in FileMaker Pro Advanced.

When you installed FileMaker Server, you installed the FileMaker Data API reference files. This reference provides detailed information about all of the calls supported by the FileMaker Data API.

Note:To view the reference information, check that FileMaker Data API access has been enabled in Admin Console. See FileMaker Server Help.

• To view the reference in a browser window on the master machine, enter the URL
  https://localhost/fmi/data/apidoc/
• To view the reference in a browser window on a remote machine, enter the URL
  https://host/fmi/data/apidoc/
  where host is the IP address or host name of the master machine running FileMaker Server.

• On a Windows server, the reference files are in the folder
  [drive]:\Program Files\FileMaker\FileMaker Server\Documentation\Data API Documentation
  where [drive] is the drive on which your FileMaker Server deployment resides.

  If you install using a non-default location on Windows, your installation location replaces the beginning of the default installation path
  [drive]:[installation_location]\FileMaker\FileMaker Server\Documentation\Data API Documentation

• On a macOS server, the reference files are in the folder
  /Library/FileMaker Server/Documentation/Data API Documentation

Notes

• The reference files show variables in the URLs by using a keyword preceded by a colon (:). For example: :database
• This guide shows variables in the URLs by using an italic font. For example: database-name

• There may be URLs or parts of URLs where text case doesn't matter, but in general, treat URLs as if they are case sensitive. For example, if you use a lowercase database name to log in to a database session, continue to use a lowercase database name for all subsequent URLs that pass the same session token. Otherwise, you may receive an invalid token error message.
• Strings in URLs must use URL encoding, also known as percent encoding, which is normal for HTTP requests. For example, to specify a layout name that includes a slash character, you must specify the slash character as the following encoded value: "%2F"
• String data values specified in parameters in the body of the request must use UTF-8 encoding.
• In general, the FileMaker Data API treats numeric data values as if they were in double-precision floating-point format (binary64). Use the corresponding data format in the programming language that you use. (Numeric data values should not be enclosed in quotation marks and should not use URL encoding.)
• Data values for number, date, time, and timestamp fields follow the same limits as specified by FileMaker Pro Advanced. See FileMaker Pro Advanced Help.
• The fields and portals that you specify must be on the layout that you specify.
• To specify related fields, use the portalData syntax.

  Note:The tablename::related-field(repetition-number).record-id syntax from the previous release is still supported, but the portalData syntax is preferred.

• For container field data, the FileMaker Data API returns a URL with the path reference to the container data object.

FileMaker scripts can automate frequently performed tasks and can combine several tasks. When used with the FileMaker Data API, FileMaker scripts allow web services to perform more tasks or a series of tasks. See Running FileMaker scripts.

To see script steps that the FileMaker Data API supports, in the FileMaker Pro Advanced Script Workspace, click the Compatibility button and choose FileMaker Data API. Script steps that are not dimmed are supported for the FileMaker Data API. Some script steps perform differently in the FileMaker Data API than in FileMaker Pro Advanced. See FileMaker Pro Advanced Help.

Scripts run by the FileMaker Data API cannot run scripts in other FileMaker files unless the files are hosted on the same installation of FileMaker Server and unless the fmrest extended privilege is enabled in the other files.

In FileMaker Pro Advanced, both scripts and user actions (such as the user clicking a field) can activate script triggers. But in FileMaker Data API solutions, only scripts can activate script triggers. For information on script triggers, see FileMaker Pro Advanced Help.

Notes

• Consider the amount of data and the number of records a script may return, and define scripts accordingly. In FileMaker Pro Advanced, a script may return all the records from a table or from the current found set. But if a script returns all the records from a table, a web service may run out of memory trying to process the records.
• Use accounts and privileges to restrict the set of scripts that a web service can run. Verify that the scripts contain only web-compatible script steps, and provide access only to scripts that should be used from a web service.
• Consider the side effects of scripts that run a combination of steps that are controlled by access privileges. For example, if a script includes a step to delete records, and the web service does not log in with an account that allows record deletion, the script will not perform the Delete Records script step. However, the script might continue to run, which could lead to unexpected results.
• In the Script Workspace, grant full access privileges to a script to allow the script to perform tasks that you would not grant individuals access to. For example, you can prevent users from deleting records with their accounts and privileges, but allow them to run a script that would delete certain records under conditions defined in the script.
• Some scripts that need only one script step when run from a FileMaker Pro Advanced client may require an additional Commit Record/Request script step to save the data to the host when run in a FileMaker Data API solution. Because web services don't have a direct connection to the host, they aren't notified when data changes. For example, features like conditional value lists may not work as expected because the data must be saved to the host.
• Any script that modifies data should include the Commit Record/Request script step, because data changes aren't accessible until the data is saved to the server. This includes script steps like Cut, Copy, and Paste. Many single-step actions should be converted into scripts to include the Commit Record/Request step. When designing scripts that will be run from a web service, include the Commit Record/Request step at the end of a script to make sure all changes are saved.
• Open each script that web users might run, and verify that the script will run properly when the database is hosted as a FileMaker Data API solution. Check that the script uses only script steps that are supported by the FileMaker Data API, as described above.

The FileMaker Data API calls consist of the following components.

The FileMaker Data API uses an access token to define a connection to a database. That access token must be used in the header of all subsequent calls to the hosted database. The access token is valid until you log out of a database session or for 15 minutes after the last call that specified the token. (While the token is valid, each call that specifies the token resets the session timeout counter to zero.)

To upload container data, use an HTTP POST method with the containers URL specifying the database name, layout name, record ID, field name, and a field repetition.

To perform a find request, use an HTTP POST method with the _find URL specifying the database name and the layout, and additional information to specify the query fields and criteria, sort order, starting record, and number of records. You can also specify portal information to limit the number of related records that are returned.

To set the values for global fields, use an HTTP PATCH method with the globals URL specifying the database name.

Script execution order

You can specify the script.prerequest, script.presort, and script parameters on a single API call. Each keyword can be specified only once. FileMaker Server processes these parameters as part of the API call in this order:

1. Go to the layout specified in the URL.
2. Perform the script named by script.prerequest, if specified.
3. Perform the action specified by the API call (get, create, edit, delete, find).
4. Perform the script named by script.presort, if specified.
5. Perform the sort specified on the API call:
   • For Get a range of records, perform the sort specified by the _sort parameter.
   • For Perform a find request, perform the sort specified by the sort parameter.
6. Perform the script named by script, if specified.
7. Return the result set for the API call, with the offset and limit parameters applied, if specified.

Notes

• For calls that use HTTP GET and HTTP DELETE methods, the scripting parameters are included as URL parameters; see Get a single record, Get a range of records, and Delete a record.

For example:

https://<host>/fmi/data/v1/databases/customers/layouts/entry/records/14?script=UpdateProcessing&script.param=14
• For calls that use HTTP POST and HTTP PATCH methods, the scripting parameters are included in the request body; see Create a record, Edit a record, and Perform a find request.

For example:

{"query":[{"Title":"Office Manager"}], "script.prerequest":"Eliminate duplicates"}
• For the script parameters script.param, script.prerequest.param, and script.presort.param, you may specify only a single text string. To pass in multiple parameters, you can create a string delimiting the parameters and have your script parse out the individual parameters. For example, pass "param1|param2|param3" as a list with the "|" character URL-encoded as: param1%7Cparam2%7Cparam3

When an error occurs, the FileMaker Data API returns:

• an HTTP 400-level status code for standard HTTP errors
• an HTTP 500 status code for FileMaker Server errors

1. Complete all of the steps in Prepare databases for FileMaker Data API access.
2. Check that FileMaker Data API access has been enabled and properly configured in Admin Console. See FileMaker Server Help.
3. Verify that the web server and the FileMaker Data API Engine are running.

4. Use encryption for communication.

   The FileMaker Data API requires your REST API applications to use an HTTPS connection. HTTPS connections use Secure Sockets Layer (SSL) encryption for communication.

   FileMaker Server provides a standard SSL certificate signed by FileMaker, Inc., that does not verify the server name. The FileMaker default certificate is intended only for test purposes. A custom SSL certificate is required for production use. See FileMaker Server Installation and Configuration Guide.

   The language or technology that you use to call the FileMaker Data API must support Transport Layer Security (TLS) v1.2 to communicate with the web server.

Before putting your FileMaker Data API solution in production, verify that it functions as you expect.

• Test features like finding, adding, and deleting records with different accounts and privilege sets.
• Verify that privilege sets are performing as expected by testing with different accounts. Make sure unauthorized users can't access or modify your data.
• Test that your solution behaves the same when invoked from different operating systems.

The server administrator can use Admin Console to start or stop the FileMaker Data API Engine, monitor FileMaker Data API clients, track FileMaker Data API call use, and download the FileMaker Data API log file.

FileMaker Server includes the Tableau Web Data Connector, a sample implementation that accepts REST API calls in JSON format. Use the Tableau Web Data Connector to define a connection between FileMaker Server and Tableau Desktop. The connection uses the FileMaker Data API to import data from hosted FileMaker databases into Tableau Desktop.

• Tableau Desktop, minimum version 10, for Windows or macOS.
• A password-protected FileMaker Pro Advanced database containing the data to import. The database must be hosted on FileMaker Server.

• A valid REST API endpoint. For FileMaker Server, the endpoint is an HTML connection point that provides information needed for web services. The endpoint has the format
  https://<hostname>/fmi/data/v1/tableau/fm_connector.html
  where hostname is the fully qualified host name of your FileMaker Server.

  For example:

  https://myserver.mycompany.com/fmi/data/v1/tableau/fm_connector.html

• A valid custom SSL certificate on FileMaker Server. The Tableau Web Data Connector will not allow importing data from FileMaker Server without a valid custom SSL certificate. If your server requires an intermediate certificate, the certificate must be installed on the server where FileMaker Server is deployed. Restart the server machine after making changes to the installed certificate before connecting with Tableau.

  Note:If your server uses a subject alternative name (SAN) certificate, the hostname must match the common name used in the SAN certificate.

Follow the steps in Prepare databases for FileMaker Data API access to define the layout for importing and to enable the database for FileMaker Data API access.

Note:To import data into Tableau, the table must have at least one record with record data.

The following FileMaker field types are imported as Tableau data types.

The following FileMaker field types are not supported when importing into Tableau:

• container fields
• summary fields. You can create a summary field in Tableau based on data that you import from FileMaker.
• calculation fields. You can create a calculation field in Tableau based on data that you import from FileMaker.
• chart data
• data from FileMaker Pro Advanced portals. To import data from related records, create a FileMaker Pro Advanced layout based on the related table, or import data from separate FileMaker Pro Advanced tables into Tableau and then join the tables in Tableau.
• field repetitions where the display of repeating fields for Show repetitions includes multiple values. A single repetition is supported.
• non-numeric values in number fields. If Tableau finds non-numeric values in number fields, the data will not be imported.

Do not use reserved words as field names in FileMaker Pro Advanced.

1. In Tableau Desktop, under Connect (at the left side of the screen), choose More > Web Data Connector.
2. Enter the URL for your FileMaker Server endpoint
   https://<hostname>/fmi/data/v1/tableau/fm_connector.html
   where <hostname> is the fully qualified host name of your FileMaker Server.

3. In the Import Data from FileMaker File dialog box:

   • Sign in to the FileMaker Pro Advanced database by entering the following information or by using an OAuth identity provider.

     • Source Database Name: the name of the FileMaker Pro Advanced database
     • Source Layout Name: the name of the FileMaker Pro Advanced layout
     • Account Name: the name of the FileMaker Pro Advanced account with the fmrest privilege
     • Password: the password for the FileMaker Pro Advanced account
   • Select Enable incremental refresh to enable incremental refresh.
4. Click Import FileMaker Data.

Tableau imports the data. The processing time depends on the number of records imported, server load, and network throughput. Tableau maps FileMaker Pro Advanced field names and data to dimensions and measures. String data is typically mapped to dimensions, while numeric data is typically mapped to measures. The mapping occurs automatically during import, but you can customize it.