We have the same problem. The ESS table occurrences to MySQL tables via ODBC do work properly, including seeing the fields and being able to edit the relationship. However, you cannot rename the table occurrence name itself.
It seems almost as if a FileMaker programmer decided that the OK button should be disabled when no table is selected in the list above. However, when the Data Source is a single-table via ESS, there are no items in the list to select from. So, in that case, the OK button should NOT be disabled, as that prevents a FileMaker developer from renaming the table occurrence.
We've tried this and been stopped by this bug in the following versions of FileMaker Pro Advanced: 13.0.9, 14.0.6, 15.0.3, and 16.0.1.
Any plan to address this issue?
IIRC, the renaming must be set up when you select the ODBC source. Try to add a new TO and select external source. Name it what you will, then connect as needed.
OK. Here are the steps that reveal the problem:
- Create a (or have an existing) Data Source that is ODBC.
- Go to Manage Database, creating a table if it's not there.
- Go to the Relationships, and choose Add a Table.
- Select your Data Source that is ODBC.
- No tables are listed below, but you can type in a Name.
- The OK button never becomes activated, even after you type in a Name, so you cannot save this new table occurrence.
A similar set of steps happens when I want to rename a table occurrence:
- Double-click the table occurrence in the Relationships graph.
- Type in the Name box.
- The OK button does not become enabled, making it impossible to save the changes to the table occurrence Name.
Both of these things used to work (perhaps in FileMaker 12?), since we have multiple table occurrences based on the same Data Source, and they have names other than the name of the Data Source.
The problem seems to be that the "Specify Table" dialog does not show the table (only one per Data Source in our situation) within the Data Source after we select the Data Source (and even when the Data Source is already selected on an existing table occurrence). The strange thing is that everywhere else seems to know about the Data Source's table. For example, under the Manage Database's Tables tab, I can see a table from the same ODBC Data Source, and in the existing relationships, the fields are available to modify how a table occurrence is connected to an existing table occurrence from the ODBC Data Source. The problem seems to be the list of table(s) for an ODBC Data Source in the Specify Table dialog.
I see your #5 (No tables are listed) may be the problem. I always click the table then change the name (for new or alias'). Thank you for the update on the issue.
OK. An update:
We used FileMaker client ON the Server to connect to itself so that we could be sure the machine running FileMaker (that we were using to do development on in this test) has the correct ODBC drivers with the DSN configured.
However, we STILL could not rename any existing table occurrences, nor could we add new ones using the functioning Data Sources.
We even tried to add a Data Source using the same MySQL database as our existing tables. We could add the Data Source, but then still could not add any new table occurrences using that data source.
So, to summarize: we have working tables and table occurrences that, via ESS, use tables from a MySQL database. We can search, add new records, etc. However, we cannot rename an existing table occurrence, nor can we add a new table occurrence. Duplicating an existing table occurrence works, although the new TO has the same name as the original with a trailing number added (e.g. "MyTable" duplicated gives "MyTable 2") and we cannot rename it to something sensible.
The problem seems to be that, when using the Specify Table dialog with an ESS Data Source, the list of tables is not showing anything, and the OK button does not become active unless a table is picked. So, with nothing to pick (even for a working TO), you can type whatever you want in the Name field, but you cannot click OK.
UPDATE: What does work is if we leave the "Schema Name" empty in the ESS Data Source. Then we see all tables in the MySQL database. Is there any reason filling that in with the correct name should cause no tables to be visible? And, if that "filter" prevents any tables from showing in the Specify Table dialog list, why do the existing table occurrences that use Data Sources with that Schema Name filled IN still function properly?
The documentation about this, found at Connecting to an External SQL Source | FileMaker , doesn't make clear why this would have this affect.
Hey, TSGal - any chance you could weigh in on this?
Thank you for your posts.
If you open the file locally, are you able to rename the ESS table?
Which ODBC driver and version are you using to connect to MySQL?
1 of 1 people found this helpful
I also found an article by SixFriedRice - http://sixfriedrice.com/wp/getting-started-with-external-sql-sources/
It indicates that, for a MySQL ESS Data Source, the Schema Name is the user who owns the tables, not the name of the database. So, it seems like we have the wrong value in the Schema field in our ESS Data Sources. The strange thing is that the existing tables and table occurrences using those Data Sources work properly, even with a seemingly invalid value in the Schema field. The only negative effect seems to be that the table list filters down to nothing, which prevents creating new Pos or renaming existing ones. Is that expected behavior?
If you DO modify your existing ESS Data Source to clear out that problematic Schema Name field, FileMaker will apparently FORK the base-table(s) that use that Data Source, breaking things badly.
So, for example, let's say you had an ESS Data Source named "SQL_Referrals" and there was thus a base-table named "referrals" in your FileMaker database using that data source, along with a handful of table occurrences using that base-table. If you clear that Schema Name value in the Data Source and save that change, FileMaker will create a NEW "referrals 2" base-table, and change the oldest table occurrence that used your original base-table to use the NEW base table. Furthermore, if you had any supplemental calculation fields in the original base-table, they will be broken (missing field, etc).
The only fix when this happens is to change all table occurrences over to the new base-table, since the old one is now lost. And, if you used any supplemental calculation fields from that table on layouts, etc., you have to hope that you can re-create them in the exact same order in which they were created in the original table so that they have the same internal FileMaker Field ID, otherwise any layout on which they appear will either have a broken field reference or possibly display the wrong field.
So, this is not really an acceptable work-around.
It seems that, in some earlier version of FileMaker, the way we had our MySQL ESS Data Sources configured worked properly with the Schema Name filled in, since we were able to create and use table occurrences. Then, at some later stage, that no longer functioned properly, and correcting it to work the way the current version of FileMaker expects forks the tables that use that Data Source, almost as if you deleted the Data Source and added a new one.
Any advice or further information, TSGal ?
Thanks for the update, Dan.