Editing ODBC data sources
An ODBC data source lets a FileMaker file access data from external ODBC tables. You can view and update ODBC tables interactively in the relationships graph in much the same way you view and update FileMaker tables.
FileMaker can connect to many relational database management systems (RDBMSs) that provide ODBC APIs for external use. See Configuring an ODBC client driver for a list of the data sources that are supported.
Important To work with ODBC data sources, you must:
|
•
|
|
1.
|
|
3.
|
You can change the name of the data source, specify a different DSN, set authentication options to log in to a named ODBC data source, or set view options.
|
|
4.
|
For Authentication, the default option is Prompt user for user name and password. Users must enter a user name and password the first time they access the table.
|
If you don’t want any user of this FileMaker file to be prompted, select Specify user name and password (applies to all users) and enter a user name and password. You can also create a calculation to determine user access to the external table. You cannot use variables or fields in these calculations. See Specify Calculation dialog box for more information on setting up calculations.
If you work with shared database files that access ODBC data from Microsoft SQL Server and you want to enable ODBC data source single sign-on, select Use Windows Authentication (Single Sign-on) and enter the SPN (Service Principal Name). See Enabling ODBC data source single sign-on (Windows only).
Note FileMaker encrypts passwords. However, encryption on external data sources depends on whether encryption is supported by the ODBC driver.
|
•
|
For Catalog name, enter a name. Otherwise, tables from all catalogs are listed.
|
|
•
|
For Schema name, enter a name. Otherwise, tables from all schemas are listed.
|
|
•
|
For Table name, enter a name to list only that table.
|
|
6.
|
Note If you want to include other types, deselect all the Filter by Type options.
|
7.
|
Click OK.
|
After you have added an ODBC data source, you can work with ODBC tables in the relationships graph and on layouts as you would other FileMaker tables. You can retrieve, add, update, and delete data in the ODBC table interactively, in real time.
|
1.
|
|
2.
|
. |
3.
|
The external table appears in the relationships graph. The table name appears in italics. For information on linking to and working with tables in the relationships graph, see Working with the relationships graph.
Tip To identify whether a table in the relationships graph is a FileMaker table or from an ODBC data source, move the pointer over the arrow in the table header. You might want to add a color to ODBC tables in order to identify them more easily.
After you add a table to the relationships graph, FileMaker adds a layout with that table name to the Layout pop-up menu and the table is listed in the Tables tab. The table name appears in italics.
Columns from the ODBC table appear in the Fields tab. The field names appear in italics. You can remove fields if you do not want FileMaker Pro to display them within your solution. These columns are not removed from the external SQL data source table schema, only from the FileMaker Pro representation of that table’s schema. See Updating data between FileMaker and ODBC data sources for more information.
|
•
|
|
•
|
If FileMaker cannot automatically determine a table’s primary key, you will be asked to select the columns that comprise a unique key. FileMaker requires every table to have one or more columns that have a unique value for each row.
|
|
•
|
You cannot change the schema of external ODBC data sources. However, you can add supplemental fields to do calculations and summaries on data from ODBC tables. See Using supplemental fields.
|
|
•
|
Fields from ODBC data sources can be used in value lists, but character large objects (CLOBs) such as long text strings are not supported.
|
|
•
|
For value lists with ODBC data, the No access privilege and Limited custom privilege are not supported. To prohibit a user from seeing ODBC data in a value list, you must enforce row-level security in the external SQL database. See Editing value list privileges.
|
|
•
|
When you’re working with external tables in the Fields tab of the Manage Database dialog box, the following field options are not supported. See Setting options for fields for information on field options.
|
|
•
|
|
•
|
On the Validation tab, Unique value and Existing value are disabled. The Maximum number of characters allowed in a field is determined by the external data source.
|
|
•
|
The Storage tab is disabled.
|
|
•
|
FileMaker cannot control validation and other options that ODBC administrators set. See Updating data between FileMaker and ODBC data sources for information on how field options are affected in Sync operations.
|
|
•
|