Editing ODBC data sources

An ODBC data source lets a FileMaker Pro 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 Pro 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:

• connect to the data source via an ODBC driver and set up DSNs. See Configuring an ODBC client driver.

• add an ODBC data source. See Connecting to external data sources.

After you have linked the ODBC data source to a FileMaker Pro file, you can set and edit options.

To edit an ODBC data source:

1. Choose File menu > Manage > External Data Sources.

2. Select the ODBC data source in the list and click Edit.

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 Pro 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.

   Note  FileMaker Pro encrypts passwords. However, encryption on data sources depends on whether encryption is supported by the ODBC driver.

5. To enhance performance, you can filter the list of external tables or views that are available for you to add to the relationships graph. The default is to list all the tables from the data source.

   • 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. You can filter the list of tables by type: Tables, Views, or System table.

   Note  If you want to include other types, deselect all the Filter by Type options.

Setting up an ODBC table in the relationships graph

After you have added an ODBC data source, you can work with ODBC tables in the relationships graph and on layouts as you would with other FileMaker tables. You can retrieve, add, update, and delete data in the ODBC table interactively, in real time.

1. Choose File menu > Manage > Database.

2. In the Relationships tab in the Manage Database dialog box, click .

3. In the Specify Table dialog box, for Data Source, choose ODBC data source.

4. If you are prompted, enter the user name and password for the ODBC data source.

   You see a list of the external tables for that data source.

5. Select the table you want and click OK.

   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 Pro 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 custom app. 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.

Considerations before you begin working with ODBC data sources

• Decide whether you want to work with ODBC tables interactively in the relationships graph or through "static" ODBC imports. For an overview of the various ways of working with ODBC data sources, see Using ODBC and JDBC with FileMaker Pro.

• Determine which tables you want to access, and which data sources they are in.

• If FileMaker Pro cannot automatically determine a table’s primary key, you will be asked to select the columns that comprise a unique key. FileMaker Pro requires every table to have one or more columns that have a unique value for each row.

Limitations on working with ODBC data sources

• You cannot change the schema of ODBC data sources. However, you can add supplemental fields to do calculations and summaries on data from ODBC tables.

• 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.

• The SQL Server Timestamp data type is not supported.

• Binary large objects (BLOBs) such as pictures and sounds are not supported.

• 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 Auto-Enter tab, you can auto-enter serial numbers when you create records, but not when you commit records. (The external database controls how records are committed.) When a lookup is to related data in an ODBC data source, copy next lower value and copy next higher value are disabled.

  • 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 Pro 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.

• FileMaker Pro files linked to ODBC data sources don’t inherent relationships that are created in the ODBC data sources.

Notes 

• You can apply conditional formatting to data from external fields. See Defining conditional formatting for layout objects.

• When databases hosted by FileMaker Server or FileMaker Cloud are linked to ODBC data sources, FileMaker clients do not need to set up a DSN to access the external data source. However, the DSN must be set up on the machine running FileMaker Server or in FileMaker Cloud.

• FileMaker Pro does not use SQL set semantics on queries. FileMaker Pro may execute one query for each related table on a layout. If you want to retain certain SQL join behavior or obtain the results of SQL predicates such as GROUP BY, create views. Then access the data from FileMaker Pro through those views. (Views are sometimes called "virtual tables.")

• Long queries, attempts to scroll the entire ODBC table, or opening a large table can result in slower performance.

• Microsoft SQL Server: By default, DATETIME, DATETIME2, and SMALLDATETIME data from ODBC tables is imported as the timestamp field type. You can change the field type of a timestamp shadow field to either date or time, but the data must be structured in a specific manner. If the imported ODBC table contains timestamp data and you treat the timestamp field as a date field, the time portion must equal 12:00 AM (or midnight) for all records in the table. Likewise, if you import an ODBC table that contains timestamp data and you treat the timestamp field as a time field, the date portion must equal 1900-01-01 (or January 1, 1900) for all records in the table.

  As with any ODBC table, if the field is a primary key and the values are not unique, the results may be inconsistent.

• If you work with database files hosted by FileMaker Pro or FileMaker Server that access ODBC data from Microsoft SQL Server, you can configure the host computer to enable single sign-on (SSO). See the Knowledge Base.