BlogFileMaker

FileMaker, mySQL, and ESS; A Little Known Secret, to Me Anyway

By December 18, 2013 5 Comments

Recently, I was working on improving performance for a FileMaker solution that had a large web integration. It was decided that the best approach was to move a few primary tables from FileMaker to mySQL using ESS.

Of course, we had to maintain all business logic associated with those FM tables after moving them to mySQL. Here’s my normal process for this:

  1. Run a DDR (Database Design Report – available with FileMaker Advanced) pre-migration to mySQL
  2. Setup DSN to mySQL
  3. Change table occurrences of affected tables to new mySQL tables
  4. Use DDR to search for all references to affected tables and adjust any misaligned fields used in scripts or displayed on layouts, relationships, etc
  5. Test

The process works well, although it is, at times, painstakingly slow. Once I change the underlying base table of the affected table occurrences, any relationships, security, scripts, and even fields displayed on layouts, often change and need to be fixed. I’ve always wondered if there was a way to maintain all the dependencies when making such a change; yesterday, the light went on thanks to some work I was doing with Roger Jacques. Before I get into those details, I want to point out that the mySQL schema, in this case, was a mirror of the FM tables (same table names and same field names).

What We Discovered

I should point out that I’m not a mySQL admin, nor do I have much experience with mySQL outside of ESS integrations. That said, Roger was using a free tool called Sequel Pro to manage what he was doing with mySQL. It turns out, that if you sort your FileMaker fields by creation order, and then use Sequel Pro to arrange (drag/drop) the mySQL table columns to match the creation order of the fields in FileMaker, dependencies are maintained.

It is still important to check the DDR (as mentioned in my process above). However, we maintained all relationships, scripts, and layout objects when we did this. This made my day and greatly improved the amount of time I spent on thsi process.

Be Aware of Shadow Tables

While the process is simple, it is important to know that when using ESS, FileMaker creates a shadow table. This shadow table is used in relationships, etc., and must be correct. If you are working in the Manage Database and change a table occurrence to a mirror based on mySQL, you should NOT see any changes to fields used in relationships. If you do, you need to cancel out of the Manage Database. This will delete the shadow table which is necessary. Then, simply check the column order in Sequel Pro and try again.

FileMaker mySQL Support and Innovation

If you have questions about using FileMaker mySQL or need support in your next development project, please contact our team. As a FileMaker Platinum Partner, we’ve helped thousands of companies organize their FileMaker solutions. We’d love to help you achieve your goals on the platform.

Aaron Gutleben

Aaron Gutleben

Aaron has been a developer for more than twelve years. His journey with FileMaker has been vast and has allowed him to work within many industries, and work on some amazing projects which have included authoring Module 13, FileMaker Go, for the FileMaker Training Series (FTS). Aaron was also a contributing author for the first twelve modules, and is certified in FileMaker 8, 9, 10, 11, and 12. When not so focused on work, he is either spending some quality time with his kids, playing/recording music, or is enjoying his love of motorcycles on some of the beautiful mountain roads near Lake Tahoe.

5 Comments

  • Avatar andre says:

    I found a very easy MySQL manager – Valentina Studio http://www.valentina-db.com/en/valentina-studio-overview Does everything you need, and does it very well.

  • Avatar Geoffrey says:

    Hi,

    I’m currently trying to do exactly what you’re describing, I think. I have a FileMaker Server 11-hosted database that we’re upgrading to FileMaker Server 13, and we’re trying to move most of its tables to MySQL that FileMaker will access via ODBC. I’ve followed the steps you’ve described, creating the fields in MySQL in the same creation order as FileMaker and naming them identically, but it doesn’t “just work” for me like it seems to have done for youΓÇöI get “misaligned fields” all over the place in layouts and scripts and relationships. When you wrote this post, were you working on FileMaker Server 12 or 13? Do you have any other insights as to why it might not work, or other ways to get FileMaker to relink the fields properly when replacing internal tables with identical MySQL ones?

    Thanks,
    Geoffrey

  • Avatar Chris Bishop says:

    Same for me, Geoffrey. At least in FileMaker 13, I also had to take into account any deleted fields in FileMaker. So basically there’s an internal ID number for each FileMaker field, and if one were ever deleted, it would leave a numeric hole. The solution was to create dummy fields in MySQL to plug these holes.

    You’re then left with crap fields in your MySQL table. You can delete these, but then if you ever redo the shadow table, the same misalignment will happen.

  • Avatar Pascal Robert says:

    I’m trying to do the same thing, with FileMaker 13. Do you have calculation and global fields?

    It’s a shame that we can’t edit the layout and scripts in a text mode that would allow us to change the references with a find&replace inside a text editor.

    • Avatar Mike Duncan says:

      Calculation and Global fields will count as well, and consume a field position. You can also get the internal field ID by querying the internal filemaker tables that contain this info. If you set a variable with the following calculation, you will get a listing of the tables and fields, including the type of field the internal field id.

      ExecuteSQL ( ”
      SELECT t.BaseTableName, f.FieldName, f.FieldID, f.FieldType
      FROM FileMaker_Fields AS f
      JOIN FileMaker_Tables as t ON f.TableName = t.TableName
      ORDER BY f.TableName, LOWER ( f.FieldName )
      ” ; “” ; “” )

      Hope this helps,
      Mike

Leave a Reply