Updating data between FileMaker and ODBC data sources

In general, committing data in ODBC data sources works the same as committing FileMaker data. For example, FileMaker clients save changes to external data when you exit a field or move to another record. For more information, see Committing data in records. However, because external data is controlled by different software and can be used interactively by users working outside of the FileMaker Platform, there are certain actions you can take to ensure you have the latest record data and table schema.

To display the latest data from an ODBC data source:

  1. Open the file with the link to the external data source.

  2. In Browse mode, choose Records menu > Refresh Window.

    Current data, including data entered by users outside of FileMaker clients, appears. You can also flush the internal cache with the Refresh Window script step.

    For ODBC tables, FileMaker clients use "optimistic locking" to control data entry. FileMaker clients don't automatically refresh data when you begin entering data in an external record or when you do a Find on ODBC data.

To sync field schema between a FileMaker and an ODBC data source:

  1. Choose File menu > Manage > Database.

  2. In the Manage Database dialog box, click the Fields tab.

  3. Choose the table you want to work with from the Table list.

    FileMaker Pro displays fields from the ODBC table. The field names appear in italics.

  4. Click Sync.

    FileMaker Pro updates the schema between the ODBC source and the "shadow" table. You have not changed the field schema in the data source. You have only updated the FileMaker table schema for any new or missing fields. For example, if you delete a field from an ODBC table in the relationships graph, when you click Sync again, the field from the external data source will reappear.

Notes 

  • After you synchronize, any field options you set in FileMaker Pro might be reset if the ODBC data source doesn’t force that option. For example, if you selected the Not empty validation option, after synchronizing, you might have to reset that option.

  • The following errors may appear. For more troubleshooting information, visit the Knowledge Base.

    • This error message

    Displays when

    <Table Missing>

    A table is renamed or removed from an external database

    <Field Missing>

    A column is renamed or removed from the external database table

    <Data Source Missing>

    • A data source or a database is renamed or removed

    • The connection fails; the appropriate error message for connection failure is also displayed

    <Primary key missing>

    FileMaker Pro cannot identify a unique key for an ODBC table or view; this error is displayed in the relationships graph and in field objects