replace local table with external data table?

[noneofyo]

Our office has a Filemaker Pro 11 database that is already in use (has been for years). What I want to do is replace the central "Users" table in the FMP database with our "Users" table on our mysql database server.

No problems setting up the external data source, using Actual Open Source ODBC connector. I can add the table, but when I change the relationships to use the new external table, it ruins all of the layouts. Same table name, same field names, but it still knows it is a new table so the layouts don't display the data anymore (lots of Table Missing).

Is there a way to replace the local table with the external one without messing up all of the layouts?

Any suggestions are appreciated.

Thanks!

[Vaughan]

My understanding is that FMP uses internal field-ids to identify fields in tables and on layouts etc. My guess is that the ids for the external MySQL table do not match the original field-ids. To be honest I have no idea what the ids for external tables would be...

I don't see any easy or quick solution.

[sgastevep]

No problems setting up the external data source, using Actual Open Source ODBC connector. I can add the table, but when I change the relationships to use the new external table, it ruins all of the layouts. Same table name, same field names, but it still knows it is a new table so the layouts don't display the data anymore (lots of Table Missing).

I just ran into this... I am doing testing on a large database, and over the VPN some queries were taking too long so I thought I'd use a local backup copy that I have. I changed the ODBC DSN to point to my local computer instead of the remote host, thinking that it shouldn't confuse FileMaker, and I ended up with all the same Table Missing errors you are seeing.

However, from what I understand, FileMaker in layouts/etc refers to Table Instances (or whatever the right term is) and not to table objects themselves.

So, I went into my relationship graph and sure enough, all the tables were greyed out and marked as Missing. However, clicking on each one, hitting the Edit pencil icon in the icon row under the relationship area allowed me to pick the table of the same name in the new DSN, and re-activated all the layouts/etc.

Worth a shot, but probably too late to solve your problem.

[uPDATE: I just saw this added new "xxxx 2" tables, so while some things worked that referred to instances, some other things are broken that must have been referring to actual tables. Bummer. ]

[uPDATE2: I figured I didn't have much to loose, so I copied the extra Calculation/Summary fields I had added to the ODBC tables, then deleted the ODBC tables entirely from the "Tables" tab [ignoring errors about them being used in relationships/scripts/etc] and then went into the relationship graph and used the edit pencil to re-pick the tables from the graph, put the calculation/summary fields back in, and everything seems to be working. But this is only a test DB with a few layouts and reports, so test well on a backup. ]

[Reed]

So what you're saying is if our IT department changes the address of the SQL server in the future, I'll have to go through the hassle of updating all the supplemental fields etc. in my interface? Strange and annoying....