Losing relationships & changing layout fields on move to ESS

[bungi]

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's 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.

Anyone else have this problem? Any recommendations/solutions?

Thanks,

[William Keyte]

yes, I'd like to know the answer to this one. I experienced this one recently doing exactly the same thing with MySQL. Problem is that tables and fields are identified within Filemaker by an internal XML identifier which, of course it loses.

Hope you get a more useful reply and get an answer.

Will

[AlesD]

I know this is very old thread ... Anyway I have same experience as guys above and I would like to know if some discovered some useful workaround to keep existing relationships and layout/script bindings???

Any advice is greatly appreciated.

Thank you

 

[AlesD]

Actually I just find out workaround myself. You need to follow FM's fields creation order in your SQL table and make sure you have deleted existing ESS table if had wrong order before switching your DSN.

Edited August 10, 2018 by AlesD

[Lee Smith]

Hi AlesD

You might find this white paper helpful.

 Checklist For Moving FileMaker Code

HTH