FileMaker, mySQL, and ESS; A Little Known Secret, to Me Anyway
Very nice time saving trick from Aaron Gutleben.
By Aaron Gutleben
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. The issue, of course, was we had to maintain all the business logic associated with those FM tables after they had been moved to mySQL. This is something I had done before, and my normal process is:
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
The above process works well, although it is, at times, painstakingly slow because 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 and 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
More…FileMaker, mySQL, and ESS; A Little Known Secret, to Me Anyway.