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:
- Run a DDR (Database Design Report – available with FileMaker Advanced) pre-migration to mySQL
- Setup DSN to mySQL
- Change table occurrences of affected tables to new mySQL tables
- Use DDR to search for all references to affected tables and adjust any misaligned fields used in scripts or displayed on layouts, relationships, etc
- 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.
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.
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
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.
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.
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