drilix.com

Freedom, Community & Sustainability

As the developer of the FmPro Migrator software, I appreciate that you have written this useful and informative article. And as you mentioned in the article, this technique is a perfect solution for small and uncomplicated FileMaker database files.

Your readers may be interested to know about some of the other challenges which can affect a successful FileMaker conversion project.

Large Numbers of Fields - It seems that if you have a large table containing hundreds of fields, the CSV/TAB delimited file exporting process breaks down. Columns might get mixed up and data can get lost. I have not trusted this process as being a reliable process. This is why I always use an ODBC connection to retrieve the data from FileMaker, and I use a direct connection to MySQL to insert the records. Using an ODBC connection makes it possible to transfer gigabytes of data reliably between databases.

Image Fields - There are white-papers available on the internet explaining why database servers are not an efficient way to store and serve images for a website. In fact, there is a popular product used in the FileMaker marketplace called SuperContainer from 360 Works, which stores images outside the database too.

I make it possible for developers to export FileMaker images directly to data files on their local disk or to store them within MySQL BLOB columns.
However if you have a small database you are using for a hobby or very small business, it probably doesn't matter very much. It is probably easier in these cases to just store the images in BLOB columns within the database. Especially since I can generate all of the PHP code necessary to manage these images via a web browser.

Repeating Fields - Within FileMaker Pro, there are fields called repeating fields, which represent a non-relational way of storing data within a field. You can actually think of a repeating field as being similar to a PHP or JavaScript array from a programming perspective. Each repeating value is separated by a specific ASCII character within the database. MySQL won't know what to do with this info so it will get lumped together within the same field, and won't be very usable. Repeating fields data should be placed into a separate related table, referencing the Primary Key of the parent table, and this is what I do during the conversion process.

In fact, I would suggest extracting the repeating fields data into MySQL, re-importing that data back into FileMaker and building a relationship to the related table. Then replace the repeating fields with Portals on the FileMaker layouts. This way it is possible to automate the conversion of the portals into PHP/JavaScript based Grid objects to create a professional looking web application.

Getting Field-size Info - There is no easy way to figure out how much data is stored within the fields of a FileMaker database. This information is vital for the purpose of creating the SQL code to build the tables within MySQL. If you guess wrong, MySQL will silently truncate the data when performing the data transfer. In some cases, this is Ok, but if the information was vital to your application, it could be disastrous.
So what I do is read all of the records from the database table in order to count up the maximum number of bytes used for each field. Then I build the table creation SQL code based upon using this field-size metadata. I like for my table structure to be neat and tidy, so I build the field sizes in increments of 25. This means that if you have a maximum of 45 characters stored within a field, I increase that size to become a VARCHAR(50). It is likely that you might need that extra space anyhow someday, and it doesn't cost anything if it isn't used. Fields larger than 255 chars get created as LongText columns.

Relationships - When dealing with relationships and a web application, you have a couple of choices. If you are using a PHP framework, you may want to build and manage the relationships within the framework instead of the database itself. Otherwise, if you are manually writing some simple PHP scripts, you might allow the database to handle the referential integrity.

Generating PHP Code for Layouts - So now that you have your data transferred to MySQL - what are you going to do about providing a GUI for your data? I have figured out how to read the XML definition for each layout object, and use that metadata to generate the PHP code for a working PHP web application. There are a lot of great looking GUI objects available on a FileMaker layout which can be translated directly into web objects. These objects include: charts, tab controls, static and dynamic value lists (using database data), portals (converted to grids), vector graphic objects (ovals/circles, rectangles, rounded rectangles, lines), checkboxes, radio buttons, date picker, static image objects, buttons, tooltips and merge fields.
If you were writing the code manually, you might have a tendency to skip adding these features to your web application because they would be too time consuming to implement.