6.1 FileMaker and external data sources
The ability to integrate external data sources into your FileMaker solution greatly expands the capabilities of your database. Whether you want to retrieve data from an SQL database, access external systems via ODBC or JDBC, or integrate web services via REST API, FileMaker gives you the tools to seamlessly integrate external data into your database applications. In this chapter you will learn how to connect SQL databases, use ODBC and JDBC in FileMaker and integrate web services via REST API.
Table of contents
- 6.1 FileMaker and external data sources
- Connection of FileMaker databases
- External data sources in gFM-Business
- Connection of SQL databases
- Open and expandable ERP software based on FileMaker
- Steps for connecting an SQL database
- Using FileMaker ODBC and JDBC
- gFM-Business Open Source FileMaker Basis ERP The software for a crash course
- Integration with web services (REST API)
- Summary
Connection of FileMaker databases
The connection of FileMaker databases as External data sources allows you to access data from another FileMaker database as if it were part of the current file. This feature provides a powerful way to share data in multiple FileMaker files and use it in a single system. The external data source is accessed via File > Manage > External data sources by adding a connection to another FileMaker file. Once connected, you can Tableslayouts and Scripts of the external database into your own solution. This is particularly useful for distributed systems where data is stored in different FileMaker files but needs to be used together, e.g. for modular solutions that connect different databases as modules.
External data sources in gFM-Business
In the basic versions, the gFM-Business ERP software only contains external data sources for the connected FileMaker databases. The software can be expanded to include additional data sources from FileMaker databases or SQL databases via ODBC/JDBC or via the REST API.
Connection of SQL databases
Introduction to SQL databases
SQL databases (Structured Query Language) are widespread and are used in many companies to manage large amounts of data. By connecting SQL databases to FileMaker, you can retrieve data from these systems, integrate it into your FileMaker solution and even edit it directly. This is particularly useful if you want to merge data from different systems or use your FileMaker database as a front end for an SQL database.
Requirements for the connection
Before you can connect an SQL database to FileMaker, you need the following prerequisites:
- FileMaker Pro Advanced or FileMaker ServerThese versions support the connection to SQL databases.
- SQL driver: Install the corresponding ODBC driver for your SQL database (e.g. MySQL, Microsoft SQL Server, Oracle). These drivers are required to establish a connection between FileMaker and the SQL database.
- Access dataYou need the access data for the SQL database, including the server address, user name and password.
Open and expandable ERP software based on FileMaker
More information
Steps for connecting an SQL database
- Setting up an ODBC connection:
- Open the "Control Panel" (Windows) or "Utilities" (macOS) and select ODBC data sources (32-bit/64-bit). Create a new system DSN (Data Source Name) for your SQL database by selecting the installed ODBC driver and entering the required connection information.
- Connection in FileMaker:
- Open FileMaker Pro and go to File > Manage > External data sources. Click on Add and select the previously set up DSN. Enter a name for the external data source and confirm the entry.
- Creating layouts and queries:
- Once the connection is set up, you can access the tables in the SQL database in FileMaker. Create a new layout and select the table from the SQL database that you want to view or edit. You can also perform SQL queries directly in FileMaker to retrieve specific records.
Best Practices
- Optimize performanceMake sure that your SQL queries are optimized so as not to impair performance. Use indexes in your SQL database and avoid unnecessarily complex queries.
- Ensure safetyEnsure that the connection to the SQL database is secure, especially if confidential data is being transferred. Use SSL/TLS encryption for data transmission.
Using FileMaker ODBC and JDBC
Introduction to ODBC and JDBC
ODBC (Open Database Connectivity) and JDBC (Java Database Connectivity) are standard protocols that make it possible to connect different databases via standardized interfaces. In FileMaker, you can use both ODBC and JDBC to retrieve data from external sources and integrate it into your solution.
Setting up ODBC in FileMaker
- Set up ODBC data source:
- As already mentioned when connecting SQL databases, you set up an ODBC data source via the operating system. To do this, use the corresponding ODBC driver for the target database and create a system DSN.
- Create a connection in FileMaker:
- In FileMaker Pro, go to File > Manage > External data sources and add a new ODBC data source by selecting the previously created DSN.
- Use of the data:
- Once set up, you can access the connected databases, run SQL queries and use the data in FileMaker as if it were part of your native FileMaker database.
Setting up JDBC in FileMaker
- Install Java Runtime Environment (JRE):
- JDBC requires the installation of a Java Runtime Environment (JRE) on your system. Make sure that the latest version is installed.
- Set up JDBC driver:
- Download the appropriate JDBC driver for the desired database and configure it according to the database manufacturer's instructions.
- Create a connection in FileMaker:
- JDBC connections are usually set up via FileMaker Server. Configure the JDBC connection in the Admin Console of the FileMaker Server and define the access rights.
Use cases for ODBC and JDBC
- Data integrationUse ODBC and JDBC to integrate data from external sources into your FileMaker solution without storing the data in FileMaker itself.
- ReportingCreate complex reports that combine data from multiple data sources and display them in FileMaker.
Best Practices
- Connection securityUse secure connections for ODBC and JDBC, especially if the connection is made via the Internet.
- Error handlingImplement robust error handling mechanisms to deal with disconnections and other problems.
gFM-Business Open Source FileMaker Basis-ERP
The software for the crash course
Download for free
Integration with web services (REST API)
Introduction to REST APIs
REST (Representational State Transfer) APIs are widely used to connect web services and exchange data between applications. FileMaker can act as a client for REST APIs and allows you to integrate external services such as web applications, cloud services or other APIs into your solution.
Using FileMaker as a REST API client
- Using cURL commands:
- FileMaker natively supports cURL commands that are used in
Insert from URL-script steps can be used. This allows you to send HTTP requests (GET, POST, PUT, DELETE) to web services and process the response in FileMaker.
- FileMaker natively supports cURL commands that are used in
- Customization of HTTP requests:
- Customize the HTTP requests to the specific requirements of the API you want to use. This includes setting headers, sending JSON data and processing responses.
Example:
Insert from URL [Target: Table::Field; With dialog: Off; URL: "https://api.example.com/data"; cURL options: "-request GET -header "Authorization: Bearer TOKEN"]
- JSON processing in FileMaker:
- FileMaker provides native functions for processing JSON data, which is often returned as a response from REST APIs. Use functions such as
JSONGetElementandJSONSetElementto analyze and process the data in FileMaker.
- FileMaker provides native functions for processing JSON data, which is often returned as a response from REST APIs. Use functions such as
Integration of REST APIs in workflows
- Data updateUse REST APIs to update or synchronize data from external systems in real time. For example, you could retrieve current weather data, financial rates or stock information.
- Automation of processesAutomate recurring tasks by creating FileMaker scripts that make regular API calls and store the data received in the database.
Best Practices
- API securityEnsure that you use secure connections (HTTPS) and manage API keys and access tokens securely.
- Error handling and loggingImplement robust error handling and logging to ensure that API calls are executed correctly and errors can be easily diagnosed.
- Resource managementPay attention to the limitation of API calls in order to consider the load of external services and possible rate limits.
Summary
Connecting FileMaker to external data sources opens up a variety of ways to extend and optimize your database applications. Whether you're integrating SQL databases, using ODBC and JDBC, or using REST APIs to integrate web services, FileMaker gives you the flexibility and tools to use data from multiple sources in a unified application. By following best practices in security, error handling and performance, you can ensure that your FileMaker solution remains robust, secure and future-proof.
