4 Replies Latest reply on Feb 17, 2012 1:55 PM by TSGal

    FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source

    BruceMills

      Title

      FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source

      Post

      I have used FM since Claris 1.0. I run several FM and FM/ODBC apps to remote MySQL dev and production servers. I find that no mater how carefully I integrate the data source when switching from a dev server to a live server, I have to laboriously QA every layout, every value list and every script because the tables, fields and value names are all scambled.

      I maintain bit-level synch between my dev and remotes db servers but no mater what I do, I cannot port the database without this bizarre and labor intensive step. Then I have to maintain two apps in synch. One for live and one for dev. I can't possibly be the only person who has run into this problem but cannot find any information anywhere that sheds any light on it. I found one blog that said it is impossible to port ODBC sources in FileMaker. If this is true it should be documented!

      This should be a simple turn-key procedure. Any body have a clue or workaround?

        • 1. Re: FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source
          philmodjunk

          Are you doing this by taking an existing table occurrence and changing the data source reference?

          • 2. Re: FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source
            BruceMills

            In answer to your question - sort of. That is, I am taking an existing schema, completely comprised of external tables sychronized to a MySQL development database and switching the data source to link to an identically structured and populated remote database. I have one application that is set up this way and has been operating of over 2-years. But it is hell to move to a new server.

            I have built these FM apps around multi-table MySQL databases. Typically the apps are admin interfaces to remote web-based databases that are also accessed by server-side scripts to run websites. I build them on a dev server on a local network that mimics the live environment. When they are ready for testing I move them to a remote testing server and eventually they are implemented on the production server. The MySQL DB table structure is identical in all cases. The app is identical in all cases. The only difference is the ODBC data source. In theory, I should be able to just change the data source and everything should work. But it doesn't.

            First, when you change the data source, even if it is to the exact same server it was connected to before, you have to manually re-match all the tables in your FM schema. If it is simple and no column names are duplicated anywhere in the database this will probably do the trick. If, however, you have common column names in more than one table like: name, type, phone, etc. your foreign keys, field matches, layouts, value lists and scripts will be completely scrambled. For a large database with a lot of tables and relationships it is impractical to maintain and port a dev or testing version of your app to the live server on a periodic basis. After porting you have to work on the live database or forget about FM all together. I like FM even for this use as well as for prototyping UI and would like to see this work. But I cannot find any useful informtion about this problem.

            The first issue, that of matching tables, would seem to be pretty simple. Just match up the table names to the schema. I cannot understand why FM is unable to do this. The second issue, I suspect has to do with qualified `field.column` names in the SQL environment. It appears that FM cannot properly form or identify qualified column names when switching data sources. I am experimenting with creating databases that use a table prefix in every column name (such as loc_name for location name and usr_name for user name). This way, no two columns in the entire database ever have the same name. While my testing is not comprehensive, this seems to mitigate the scrambling of column names throughout the applicaion. Still, there is a lot of extra work.

            The basic ODBC interaction does work in FM and is useful. As long as you don't need to port it. If this ODBC interface was fully functional, it would be a very compelling feature that could certainly introduce FM into markets it does not now serve.

            • 3. Re: FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source
              BruceMills

              OK. Since I seem to be the only person interested in answers to this problem I guess I will have to answer it myself.

              The answer is NO WAY.

              While it is true that FM recognizes external data sources, specifically MySQL via the ODBC MySQL 5.1.x connector (32-bit only), that's about the all that can be said. After extensive testing with various column naming schemes and data source swaping procedures. I can now conclucively say that this simply doesn't work. While you can connect in a single instance to an explicit db on an explicit server, this feature is NOT PORTABLE. That is, unless you want to rematch all the tables and foriegn keys in your schema, and redo all the field linking in your layouts and rewrite all your scripts for each and every instance on each and every client.

              This feature is a fraud and should be removed from FileMaker least more people waste more time trying to figure out that it doesn't work.

              • 4. Re: FileMaker Layout, Value Lists and Script Synchronization Lost When Moving ODBS Data Source
                TSGal

                Bruce Mills:

                Thank you for your posts.

                Currently, FileMaker Pro /Server does not provide a way to port the ODBC references.  The references have to be recreated when switching to another server.  Our Development and Testing departments are aware of the issue, and there is no additional information available at this time.

                TSGal
                FileMaker, Inc.