Losing relationships & changing layout fields on move to ESS
I have 2 problems, but first I'll give some background info.
I have a FileMaker Pro 11 Advanced database hosted on FileMaker Server 11 Advanced (in a Windows environment). My goal is to migrate all the data in FileMaker to Microsoft SQL Server 2005 as a data storage back-end, and use FileMaker as a data entry front-end.
I used the "FmPro Migrator 5.57 DE" (3rd) tool to create the tables in SQL Server 2005 and copy all the data from all the tables. I made sure that all tables and all the fields were transferred (same exact table names, field names, etc).
I then setup the ODBC connection on the Server's host machine, and added the SQL tables as an external data source in FileMaker. Then in the Relationship Graph, I changed all the tables to point to the SQL tables, instead of the local FileMaker tables.
Now here' where I run into my 1st problem. The SQL tables and the FileMaker tables are identical. Same table name, same number of columns, same column names, same number or rows, same data in the rows. But when I switch the tables from FileMaker to SQL, a bunch of the relationships change. Some are now undefined, and some are just pointing to different columns. Some remain the same, but most are not.
Is there any way to make the switch to the SQL tables without losing the relationships? I have 116 tables, with hundreds of relationships, and going through each table and re-linking each relationship is very very time consuming.
This also leads to my 2nd problem. After re-linking all the relationships, when I go to any layout, most of the fields are displaying data from the wrong column. For example, if the field was displaying "date received" before (when the relationships were using FileMaker tables), now it either displays a completely different column, like "project_id", etc., or it displays "field missing".
Is there any way to make the switch to the SQL tables without changing what the fields in the layouts point to? I have 331 layouts, and I have to manual change each field in each layout to point to the correct data.
Has anyone else has encountered this problem? Does anyone have any recommendations/solutions?