FileMaker Pro: Exporting and importing repeating field data
Posted by Pierre Igot in: MacintoshFebruary 8th, 2008 • 3:51 pm
I recently stumbled upon a problem with exporting data from a FileMaker Pro database including repeating fields, and I think I have found a solution, so I thought I might as well share it in case it can be of any use to anyone else.
When you explore FileMaker Pro’s help feature about exporting data, it does warn you that some file formats used when exporting data do not support repeating fields.
What it does not tell you, however, is that FileMaker Pro can also have trouble importing data from repeating fields.
For example, I discovered that, if I export data from a FileMaker Pro 7 database (.fp7 file format) that includes repeating fields in XML format, the XML data generated by the exporting process does include the data from repeating fields. I can check this by opening the XML data in a text editor. The multiple values for repeating fields are definitely included in the file.
However, if I then try to import this XML data into a FileMaker Pro 6 database (.fp5 file format) that includes the same repeating fields, FileMaker Pro 6 fails to import the repeating field data properly. It only imports the first value of each repeating field, and appears to ignore the other repeating values.
I tried a variety of workarounds to no avail, until I discovered that, by using the “Merge” file format when exporting data from my FileMaker Pro 7 database, and then importing that file in “Merge” format into my FileMaker Pro 5 database, I was able to preserve all field values, including for the repeating fields in my database.
This is a relief to me because I was already considering having to completely change the design of my database to eliminate repeating fields altogether. With this “Merge” file format option, I no longer need to do this. Phew! I have no idea what this file format was initially designed for, but I don’t really care…
(If you are wondering why I have to do this export/import procedure in the first place, the reason is that I occasionally need to update a database that is hosted on a server running Mac OS X Server 10.3 and FileMaker Pro 5 Unlimited. On my computer here at home, I prefer running a more recent version of FileMaker Pro [version 7], with is slightly more Mac OS X-friendly. The older FileMaker Pro 6 application still runs properly under Mac OS X 10.5, but it doesn’t have proper font smoothing, etc. Upgrading the server to a more recent version of FileMaker Pro Unlimited, on the other hand, would be a rather expensive proposition, and we simply do not have the need or the budget for this at the moment.)