FileMaker 16 Data API Guide
About the FileMaker Data API
Overview
The FileMaker® Data API is an application programming interface (API) that allows web services to access data in hosted solutions. 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 solution, 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 to create databases. You should understand the basics of FileMaker Pro database design and the concepts of fields, relationships, layouts, portals, and containers. See FileMaker Pro Help.
- using FileMaker Server to host solutions. You should understand how to deploy FileMaker Server, how to enable access to hosted solutions, and how to monitor hosted solutions using FileMaker Server Admin Console. See FileMaker Server Help.
- using REST APIs in server-side applications or web services that call POST, GET, PUT, 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:
- Prepare your database for FileMaker Data API access using FileMaker Pro. You can create a database or prepare an existing database. See Prepare databases for FileMaker Data API access.
- Write code that calls FileMaker Data API methods to find, create, edit, and delete records in a hosted solution. See Write FileMaker Data API calls.
- Host your solution using FileMaker Server with FileMaker Data API access enabled. See Host a FileMaker Data API solution.
- Test that FileMaker Data API access is working correctly. See Test a FileMaker Data API solution.
- Monitor your hosted solution using Admin Console. See Monitor FileMaker Data API solutions.
How a FileMaker Data API call is processed
-
A REST API client sends a FileMaker Data API call (HTTPS request) to the FileMaker Server web server over the HTTPS port. FileMaker Pro does not need to be installed or running.
The HTTPS port is 443 by default, but the server administrator can specify a different port when FileMaker Server is installed.
- The web server routes the request through the FileMaker Web Server Module to the FileMaker Data API Engine.
- 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 solution hosted by the Database Server.
- The Database Server sends the requested FileMaker data back to the FileMaker Data API Engine.
- 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.
- The web server sends the HTTPS response to the requesting REST API client.
The FileMaker Data API Engine requires that ports 3000 and 8989 be available.
The Database Server requires that port 5003 be available.
Web publishing alternatives
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 solution 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 application. The webpages and forms that the web user interacts with are dependent on the layouts and views defined in the FileMaker Pro 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 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 Help.
Custom Web Publishing: To integrate your FileMaker database with a custom website, use the Custom Web Publishing technologies.
Prepare databases for FileMaker Data API access
Determine the data to access
You can create a FileMaker Pro 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.
Protect your hosted solutions
The FileMaker Data API requires your REST API code to log in to a solution 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 Help.
Enable FileMaker Data API access
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:
- In FileMaker Pro, 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.
- In the Extended Privileges tab of the Manage Security dialog box, select the fmrest extended privilege to enable it.
- 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 solution.
See Creating and editing privilege sets in FileMaker Pro Help.
Write FileMaker Data API calls
FileMaker Data API features
The FileMaker Data API provides a REST API to access data in hosted solutions. The FileMaker Data API allows your code to:
- log in or log out of a hosted solution. See Calls that connect to or disconnect from a database.
- create, edit, delete, or get a record; or get a range of records. See Calls that work with records.
- perform find requests. See Call that performs find requests.
- set global field values. See Call that sets global field values.
The FileMaker Data API does not support:
- uploading data into container fields
- access to data in external ODBC data sources
- FileMaker plug-ins
- executing FileMaker scripts
- activating script triggers
The FileMaker Data API returns field data as it is stored in the database, not as it is displayed in FileMaker Pro.
FileMaker Data API reference information
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.
- To view the reference in a browser window on the master machine, enter the URL
https://localhost/fmi/rest/apidoc/
- To view the reference in a browser window on a remote machine, enter the URL
https://
host
/fmi/rest/apidoc/
wherehost
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
[installation_location]
:\Documentation\Data API Documentation- On a macOS server, the reference files are in the folder
/Library/FileMaker Server/Documentation/Data API Documentation
Data format notes
- All data values 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 encoded value: "%2F"
- The fields and portals that you specify must be on the layout that you specify.
- To specify related fields, you must use the syntax
tablename::related-field
- To use field repetition 2 and larger, you must use the syntax
tablename::related-field(repetition-number)
- When editing records, you must use the record ID syntax
tablename::related-field(repetition-number).record-id
- For container field data, the FileMaker Data API returns a URL with the path reference to the container data object.
REST API call components
The FileMaker Data API calls consist of the following components.
Component | Description |
---|---|
An HTTP method (also known as an HTTP verb) |
The FileMaker Data API uses the following HTTP methods:
|
An HTTP header |
The FileMaker Data API uses the following headers:
|
A call URL | The FileMaker Data API URLs all start with the following: /fmi/rest/api/ |
Parameter data in JSON format | Not needed with Log out of a solution, Delete a record, Get a single record, or Get a range of records |
Calls that connect to or disconnect from a database
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 solution. The access token is valid until you log out of a solution 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.)
Log in to a solution
To log in to a hosted solution, use an HTTP POST method with the auth
URL specifying the name of a hosted solution.
If the account name and password is authenticated, your code receives an access token that defines your connection to the solution.
HTTP method | POST |
---|---|
URL | /fmi/rest/api/auth/solution solution – the name of the hosted database |
HTTP header | Content/Type: application/json |
Parameters | Account name and password to log in to the hosted solution, and the layout to switch to after logging in, in JSON format. For example: {
"user":"admin",
"password":"admin",
"layout":"Tasks"
} |
Response | The access token, the current layout, and an error code of 0. For example: {
"errorCode": "0",
"layout": "Tasks",
"token": "fdde29fa175eb1cc8347512ca327b191619fc32ed65efaab26d8"
}
See Error responses. |
Log in to a solution using an OAuth identity provider
To log in to a hosted solution, use an HTTP POST method with the auth
URL specifying the name of a hosted solution.
If the account name and password is authenticated, your code receives an access token that defines your connection to the solution.
HTTP method | POST |
---|---|
URL | /fmi/rest/api/auth/solution solution – the name of the hosted database |
HTTP header | Content/Type: application/json X-FM-Data-Login-Type: oauth |
Parameters | The OAuth request ID, which contains the callback URL for authentication; the OAuth identifier returned by the OAuth identity provider; and the layout to switch to after logging in. All data must be in JSON format. For example: {
"oAuthRequestId":"E65B98CC17429CO643A31119F",
"oAuthIdentifier":"B164A4629A776E5177445DR223",
"layout":"Contacts"
} |
Response | The access token, the current layout, and an error code of 0. For example: {
"errorCode": "0",
"layout": "Contacts",
"token": "fdde35fa175eb1cc8621782fd327b191619fc32ed65efaab26d8"
}
See Error responses. |
To get the OAuth parameters in JSON format:
-
Get the list of supported OAuth providers by using an HTTP GET method with this URL:
https://host/fmws/oauthproviderinfo
where host is the IP address or domain name of the master machine in your FileMaker Server deployment. The list is returned in JSON format.
- Select a supported OAuth provider, and get a tracking ID for your solution.
-
Use an HTTP GET method with this URL:
http://host/oauth/getoauthurl?trackingID=tracking-ID&provider=OAuth-provider&address=127.0.0.1&X-FMS-OAuth-AuthType=2
where host is the IP address or domain name of the master machine in your FileMaker Server deployment, tracking-ID is the developer-generated tracking ID for your solution, and OAuth-provider is the name of your selected OAuth provider.
The HTTP header for this request needs to include the following:
- X-FMS-Application-Type: 9
- X-FMS-Application-Version: 15
- X-FMS-Return-URL: http://127.0.0.1/
- Read the response header for the X-FMS-Request-ID data. This response header contains the OAuth request ID that you will use to log in.
- Read the response header for the X-FMS-Return-URL data. Call the URL returned in this parameter to allow the user to authenticate with the OAuth provider.
- The "identifier" returned by the OAuth provider is the OAuth identifier parameter that you will use to log in.
See Creating accounts that authenticate via an OAuth identity provider in FileMaker Pro Help.
Log out of a solution
When your code is done accessing the hosted solution, use an HTTP DELETE method with the auth
URL specifying the name of the hosted solution.
The session token is sent in the request header.
If your code does not log out of the hosted solution, the access token becomes invalid
when the FileMaker Data API session times out
15 minutes after the last call that specified the token.
HTTP method | DELETE |
---|---|
URL | /fmi/rest/api/auth/solution solution – the name of the hosted database |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | None |
Response | An error code of 0. For example: {
"errorCode": "0"
}
See Error responses. |
Calls that work with records
Create a record
To create a record, use an HTTP POST method with the record
URL specifying the solution name and the layout.
HTTP method | POST |
---|---|
URL | /fmi/rest/api/record/solution/layout solution – the name of the hosted database |
HTTP header | Content/Type: application/json FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | Record data in JSON format containing field-and-value pairs that specify values for fields that are in the target layout. For example: {"data":
{
"field_1": "value_1",
"field_2": "value_2",
"repetitionField(1)" : "fieldValue",
"Orders::OrderDate.0":"12/22/2015"
}
}
Note:To create an empty record with default values for each field, specify an empty data object in JSON format as the parameter. For example: {"data":
{
}
} |
Response | An error code of 0, and the record ID of the record that was created. For example: {
"errorCode": "0",
"recordId": "25"
}
See Error responses. |
Notes
- When you create records using the FileMaker Data API, field validation is enforced. If the data does not pass field validation, you will receive an error message and the record will not be created.
-
To create a related record, omit the record ID or use the value 0 (zero) as the record ID for the related record followed by the new field value.
For example, use either of the following to create a related record:
"Orders::OrderDate" : "12/22/2017" "Orders::OrderDate.0" : "12/22/2017"
Only one related record can be created per create record call.
Edit a record
To edit a record, use an HTTP PUT method with the record
URL specifying the solution name, layout, and record ID.
HTTP method | PUT |
---|---|
URL | /fmi/rest/api/record/solution/layout/recordid solution – the name of the hosted database |
HTTP header | Content/Type: application/json FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | Record data in JSON format containing field-and-value pairs that you want to update. The data may specify related records or portals that are on the layout. Only the fields you specify are updated; other fields in the record are not changed. If "{}" is provided as the data value, the target record will not be updated. Optional parameter: Modification ID. Specifying a modification ID ensures that you are editing the current version of a record. If the modification ID value does not match the current modification ID value in the database, the record is not changed. For example: {"data":
{
"lastname": "Bob",
"lastname": "Smith",
"equipment(2)" : "PC",
"Orders::OrderDate.2":"12/20/2017",
"Orders::OrderDate.0":"12/22/2017", // create a related "Orders" record with 0 as the recordId
"deleteRelated": "Orders.3" // delete a related "Orders" record with the "deleteRelated" string
},
modId: "3"
} |
Response | An error code of 0, and the record ID of the record that was edited. For example: {
"errorCode": "0",
"recordId": "53"
}
See Error responses. |
Notes
- When you edit records using the FileMaker Data API, field validation is enforced. If the data does not pass field validation, you will receive an error message and the record will not be updated.
-
To create a related record, omit the record ID or use the value 0 (zero) as the record ID for the related record followed by the new field value.
For example, use either of the following to create a related record:
"Orders::OrderDate" : "12/22/2017" "Orders::OrderDate.0" : "12/22/2017"
Only one related record can be created per edit record call. -
To delete a related record, use the string "deleteRelated" followed by the related record to delete.
For example:
"deleteRelated" : "Orders.3"
Only one related record can be deleted per edit record call.
Delete a record
To delete a record, use an HTTP DELETE method with the record
URL specifying the solution name, layout, and record ID.
HTTP method | DELETE |
---|---|
URL | /fmi/rest/api/record/solution/layout/recordid solution – the name of the hosted database |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | None |
Response | An error code of 0. For example: {
"errorCode": "0"
}
See Error responses. |
Get a single record
To get a record, use an HTTP GET method with the record
URL specifying the solution name, layout, and record ID.
You can also specify portal information to limit the number of related records that are returned.
HTTP method | GET |
---|---|
URL | Format 1:
/fmi/rest/api/record/solution/layout/recordid solution – the name of the hosted database For the portal keyword: The portal portion of the URL is optional. If the layout includes portals, specify the portal names for better performance. If the portal portion is omitted, the call will return all related records in all portals on the layout. |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | None |
Response | An error code of 0 and the record data in JSON format. For example: {
"errorCode": "0",
"record": "{...}"
}
See Error responses. |
Notes
- To limit the number related records returned, the FileMaker Data API supports the Allow vertical scrolling option in the Portal Setup dialog box. If this option is selected, then the FileMaker Data API returns the first 50 rows from the portal. If this option is not selected, then the FileMaker Data API returns the number of rows specified for the Number of rows option.
- To scroll through the portal rows, use the syntax
offset.<portal-name>
andrange.<portal-name>
, where<portal-name>
is the value specified for the portal in the Inspector in FileMaker Pro. If you omit the offset and range values for portal rows, the default for offset is 1 and the default for range is 50.
Get a range of records
To get a range of records, use an HTTP GET method with the record
URL specifying the solution name, the layout, and additional information to specify a starting record and the number of records.
Optionally, you can specify the sort order of the records.
You can also specify portal information to limit the number of related records that are returned.
HTTP method | GET |
---|---|
URL | Format 1:
/fmi/rest/api/record/solution/layout?offset=starting-record&range=number-of-records solution – the name of the hosted database For the sort specification, the information must be specified in JSON format. field-name is the name of a field to use as the basis for sorting the records. You can specify multiple field names. For the sort order, specify the "ascend" or "descend" keyword, or specify a value list name for value-list-name. For the portal keyword: The portal portion of the URL is optional. If the layout includes portals, you may want to specify the portal names for performance reasons. If the portal portion is omitted, the call will return all related records in all of the portals on the layout. |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | None |
Response | An error code of 0 and the range of record data in JSON format. For example: {
"errorCode": "0",
"records": "[...]"
}
See Error responses. |
Notes
- If you omit the offset and range values, the default for offset is 1 and the default for range is 100:
offset=1&range=100
- If you omit the sort order values, the default is
&sort=[{ "fieldName": "recordId", "sortOrder": "ascend" }]
- To limit the number related records returned, the FileMaker Data API supports the Allow vertical scrolling option in the Portal Setup dialog box. If this option is selected, then the FileMaker Data API returns the first 50 rows from the portal. If this option is not selected, then the FileMaker Data API returns the number of rows specified for the Number of rows option.
- To scroll through the portal rows, use the syntax
offset.<portal-name>
andrange.<portal-name>
, where<portal-name>
is the value specified for the portal in the Inspector in FileMaker Pro. If you omit the offset and range values for portal rows, the default for offset is 1 and the default for range is 50.
Call that performs find requests
To perform a find request, use an HTTP POST method with the find
URL specifying the solution 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 find related records.
HTTP method | POST |
---|---|
URL | /fmi/rest/api/find/solution/layout solution – the name of the hosted database |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | A query in JSON format specifying the fields and find criteria. Optional parameters specifying omit requests, the sort order, starting record (offset), number of records (range), and portals for limiting the number of related records that are returned. For example: {
"query":[
{"Group": "=Surgeon", "Work State" : "CA"},
{"Group": "=Surgeon", "Work State" : "NY", "omit" : "true"}],
"sort":[
{"fieldName": "Work State","sortOrder": "ascend"},
{"fieldName": "FirstName", "sortOrder": "ascend"} ]
}
Example with offset, range, and portals: {
"query":[
{"Group": "=Surgeon", "Work State" : "CA"},
{"Group": "=Surgeon", "Work State" : "NY", "omit" : "true"}],
"portal": ["Portal1","Portal2"],
"range": "10",
"offset": "1",
"offset.Portal1": "1",
"range.Portal1": "5"
} |
Response | An error code of 0 and the JSON representation of the found set. For example: {
"errorCode": "0",
"data": [record1, record2, ...]
}
See Error responses. |
Notes
- In a solution that has many related records, querying and sorting portal records can be time consuming. To restrict the number of records and rows to display in a related set, specify the offset and range parameters.
- You cannot specify global fields as find criteria. If you specify a global field with a find request, you receive an error message. Instead, set the global field value before the find request. See Call that sets global field values.
Call that sets global field values
To set the values for global fields, use an HTTP PUT method with the global
URL specifying the solution name and the layout.
HTTP method | PUT |
---|---|
URL | /fmi/rest/api/global/solution/layout/ solution – the name of the hosted database |
HTTP header | FM-Data-token, which specifies the access token returned to the call that logged in to the solution |
Parameters | A JSON object with field-and-value pairs specifying the global fields to set. For example: { "globalFields":
{
"gCompany":"FileMaker",
"gCode":"95054"
}
} |
Response | An error code of 0. For example: {
"errorCode": "0"
}
See Error responses. |
Error responses
When an error occurs, the FileMaker Data API generally returns an HTTP 400-level status code with additional information in a JSON object.
HTTP status code | HTTP category | JSON error format | Description |
---|---|---|---|
400 | Bad request | {
"errorMessage": "error detail"
} |
Occurs when the server cannot process the request due to a client error. |
401 | Unauthorized | {
"errorMessage": "Unauthorized"
} |
Occurs when the client is not authorized to access the API. If this error occurs when attempting to log in to a hosted solution, then there is a problem with the specified user account or password. If this error occurs with other calls, the access token is not specified or it is not valid. |
404 | Not found | {
"errorMessage": "Not found"
} |
Occurs if the call uses a URL with an invalid URL schema. Check the specified URL for syntax errors. |
415 | Unsupported media type | {
"errorMessage": "Unsupported Media Type"
} |
Occurs if the "Content/Type: application/json" header is not specified or if a different content type was specified instead of the "application/json" type. |
477 | FileMaker service error | {
"errorMessage": "FileMaker error message",
"errorCode": "FileMaker error code"
} |
Includes FileMaker error messages and error codes. See FileMaker error codes in FileMaker Pro Help. |
Notes
- If the FileMaker Data API Engine is not running or cannot be reached, the status code 0 (zero) may be returned without an error message.
- For information about additional HTTP status codes returned, see www.w3.org.
Host, test, and monitor FileMaker Data API solutions
Host a FileMaker Data API solution
- Complete all of the steps in Prepare databases for FileMaker Data API access.
- Check that FileMaker Data API access has been enabled and properly configured in FileMaker Server Admin Console. See FileMaker Server Help.
- Verify that the web server, the Web Publishing Engine, and the FileMaker Data API Engine are running.
-
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.
Test a FileMaker Data API solution
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.
Monitor FileMaker Data API solutions
The server administrator can use Admin Console to start or stop the FileMaker Data API Engine, change FileMaker Data API settings, monitor FileMaker Data API clients, track FileMaker Data API call use, and view the FileMaker Data API log file.
To | Use |
---|---|
Start or stop the FileMaker Data API Engine | Admin Console's Status pane or a CLI command. See Starting or stopping FileMaker Server components and Using the command line interface in FileMaker Server Help. |
Change FileMaker Data API settings | Admin Console's Web Publishing > FileMaker Data API tab. On this tab, enable the use of the FileMaker Data API, enable logging for FileMaker Data API calls, set the maximum size for the FileMaker Data API log file, and set the logging level for messages written to the log file. See FileMaker Data API settings in FileMaker Server Help. |
Monitor FileMaker Data API clients | Admin Console's Activity > Clients tab. This tab shows details about the client and about the solutions being accessed. See Administering clients in FileMaker Server Help. From this tab, you can disconnect FileMaker Data API clients, but you cannot send messages to them. While FileMaker Data API clients are connected to a solution, statistical data about the clients is shown in Admin Console's Statistics > Clients tab. See Viewing client statistics in FileMaker Server Help. |
Track FileMaker Data API call use | Admin Console's Web Publishing > FileMaker Data API tab. This tab displays how many FileMaker Data API calls are permitted by your FileMaker Server license, how many calls have been used in the current month, and how many calls were used in the prior month. If you are approaching the limit on calls, you can also buy more calls using this tab. See FileMaker Data API settings in FileMaker Server Help. |
View the FileMaker Data API log | Admin Console's Log Viewer tab. FileMaker Data API components generate log information related to clients accessing hosted solutions via the FileMaker Data API. See FileMaker Data API log in FileMaker Server Help. |
FileMaker Data API integration with Tableau
About integration with Tableau
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 solutions into Tableau Desktop.
Requirements for the Tableau Web Data Connector
- Tableau Desktop, minimum version 9.1, for Windows or Mac.
- A password-protected FileMaker Pro 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/rest/tableau/fm_connector.html
wherehostname
is the fully qualified host name of your FileMaker Server.For example:
https://myserver.mycompany.com/fmi/rest/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.
Prepare to import data into Tableau
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.
FileMaker field type | Tableau data type |
---|---|
text | string |
date | date |
time | string |
timestamp | datetime |
number | float |
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 portals. To import data from related records, create a FileMaker Pro layout based on the related table, or import data from separate FileMaker Pro 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.
Set up the data connection in Tableau Desktop
- In Tableau Desktop, under Connect (at the left side of the screen), choose More > Web Data Connector.
- Enter the URL for your FileMaker Server endpoint
https://<hostname>/fmi/rest/tableau/fm_connector.html
where<hostname>
is the fully qualified host name of your FileMaker Server. -
In the Import Data from FileMaker File dialog box:
-
Sign in to the FileMaker Pro solution by entering the following information or by using an OAuth identity provider.
- Source Database Name: the name of the FileMaker Pro solution
- Source Layout Name: the name of the FileMaker Pro layout
- Account Name: the name of the FileMaker Pro account with the fmrest privilege
- Password: the password for the FileMaker Pro account
- Select Enable incremental refresh to enable incremental refresh.
-
- 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 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.
Notes
-
When specifying the Source Layout Name, make sure the layout name is unique. If your database has two layouts with the same name, the Tableau data connection cannot distinguish between them. Tableau displays only one name, and it may not be the layout you wanted.
-
Use Enable incremental refresh to import only the new records.
- After importing FileMaker data with incremental refresh enabled, select the Sheet tab in Tableau to go to the worksheet.
- Choose Data > FM: solution_name / layout_name > Extract > Refresh (Incremental).
- Select the Data Source tab.
- Click Update Now to display the new records.
- Enabling incremental refresh does not create an ongoing, live connection between Tableau and the hosted FileMaker database. You must run the incremental refresh manually.
- Incremental refresh imports only the new records. FileMaker Pro records that have been modified or deleted are not updated. To get modified data or to remove deleted records, you must create a new workbook in Tableau and reimport the data.
- Incremental refresh creates a field named -recordId. If you make changes to this field, you may be unable to perform an incremental refresh.
- In Tableau, you can change the schema and data that has been imported. But if you modify the schema or data in Tableau, those changes are not transmitted back to the FileMaker Pro file.
- If you change the schema in the FileMaker Pro file, you must create a new workbook in Tableau and reimport the data.
- Tableau Desktop database can be hosted on Tableau Server for Windows.
- If you close the Tableau workbook and reopen it, incremental import no longer works.
- After the data connection is established to Tableau, the FileMaker Web Data Connector caches the user account and password until the workbook is closed, with the following considerations:
- If the FileMaker session times out while you are connected to Tableau, the FileMaker Web Data Connector tries to reconnect the user to FileMaker Server.
- If the Tableau connection expires, the FileMaker Web Data Connector tries to reconnect to FileMaker Server as long as the Tableau workbook is open.
- If the workbook is closed and then reopened, you must enter your account name and password again during the initial data import.
- The Tableau Data Source page displays up to 1,000,000 (one million) rows, even if more records are imported.