[Article] Getting data from Filemaker to Servoy

by David Workman
Data Mosaic

For the Filemaker developer, Servoy's import command can be somewhat of a letdown. A number of familiar features are missing -- most notably the ability to "remember" imports and update matching records that have been changed. These are powerful Filemaker features that you don't get out of the box with Servoy.

In Servoy's defense, it is not envisioned as a knowledge workers desktop database tool that you keep open alongside excel for your data manipulation tasks. It's a powerful GUI programming environment and if you are coming from pure Java/Swing programming, then Servoy is child's play.

So for the programmer setting up various ways of importing data to Servoy, it is just a standard programming task using the built-in file functions or working directly with the backend database with SQL load files. Even then, I would hazard a guess that a large majority of Servoy programmers are using Marcel's most useful data plugin to help us do the heavy lifting. You can even use Filemaker as a data source using a heavily modified JDBC driver by Scott Butler to program two-way data exchange scenarios.

But if you are of the opinion that you just want to get your data out of Filemaker and into Servoy as simple as possible so you can move on to more fun Servoy programming tasks -- here's a simple no-nonsense approach that relies on your Filemaker skills to accomplish.

Evaluation of data structures

Let's be honest here: your Filemaker data structures are probably not the paragon of fine database design. Probably more akin to the $1.49 40-ouncers you used to slug in college. Which is to say that all it has going for it is that the job gets done.

With Servoy, you are now attempting to fashion a fine wine offering crisp flavors of tropical fruit and melon, balanced with hints of grapefruit and pear. Or at least an alcohol you can take out of the paper bag.

So you've done your SQL homework, your data structures are normalized up to a reasonable point, and you have a new naming convention for field names that doesn't include characters used to represent swear words in cartoon strips.

At this point, one side of the equation is starting to look rather like the first Klingon humans come into contact with sometime in the far future compared to our shiny new data structure. How to communicate between the two?!

What you need is a translator -- a set of Filemaker tables that your old Filemaker tables can talk to and which Servoy also recognizes as almost human.

Translator for Filemaker

Here's the steps:

1. create duplicates of your Filemaker structures with no data using the clone command.
2. export a few records from each of your original Filemaker files
3. import these records into the cloned files
4. for each clone file, create a script that "remembers" the most recent import

At this point you have created a set of translation files that your original Filemaker files can talk to. No matter how you rename or reorder your fields in the translation files, you have scripts that will remember the import mappings from the originals to the translators.

Translator for Servoy

Now it is time to make these translator files understandable to Servoy and SQL. The key here is that when you use the Servoy import tool, Servoy automatically matches up fields that have the same names. For all fields that don't match on names, you have to navigate a very unwieldy drop down menu to select the proper field to import into. If you have a large number of fields to choose from, unwieldy is an understatement. I'd rather listen to conservative talk radio. Well, maybe not.

Anyway, what you want to do now is rename the fields in your translator files to correspond to the field names that Servoy is hooked up to. After a bunch of typing and proofreading your spelling, Servoy now understands the translation files.

I find it useful to go one more step here and create a very simple Servoy solution that has one form for each table that I am importing to. This way I can easily peruse my data after an import and I am not cluttering up a production solution with import tasks.

The import process

This process is repeatable for as long as you need to bring data over from Filemaker:

1. delete all records from your translation files
2. export the records from your Filemaker originals that you want to send to Servoy
3. import the records into your translation files using your saved import scripts
4. export the records out of your translation files with the first line being field names
5. open up your very simple Servoy import solution
6. optional: for each table you are importing to, make sure the found set is empty
7. optional: remove records from Servoy that match the import records
8. import the records into Servoy using the import menu item

Item (6) is good to do because import records are added to the current found set. So if you start with an empty found set you can be assured that you are only looking at the imported records when you review the data when you're done with the import.

Item (7) is necessary as this process will not "update" records that match on a primary key.

Additional notes

• The translation files should be completely "flat". No calcs or summary fields. Just text, number, date fields and blob fields.
• Don't import into stored Servoy calculation fields.
• Use one date format for all the date fields in your translation files. Remember to select this date format in Servoy's import wizard.
• The translation files should have a 1-to-1 correlation with the tables in Servoy.
• You can get fancy and create up to very complex calculations in your original Filemaker files to reorganize the data for smoother import into Servoy's data structure. You would then be exporting these calculations to the translation tables in place of original fields.
• For large imports, jack up the memory that your Java VM uses.

Summary

I can confirm that this process works for 100s of thousands of records. With the translation files in place, it is a fairly painless repeatable process. Which allows you to tweak the added calculations fields in your original Filemaker files until you get the data going over in exactly the format that you want.

As a Filemaker developer new to Servoy, this is a process that relies on your Filemaker knowledge to accomplish. Down the line as you get comfortable with Servoy, you will eventually find it easier to do all this using Servoy/SQL skills and some of the additional tools mentioned at the beginning of the article.