May 7 2009 
FileMaker to Valentina using FMPro
The second in a tutorial series showing you how to use the newly released FMPro Migrator 4.74 to move your database to Valentina.
The first step in performing any FileMaker Pro database migration is to get schema info about the FileMaker Pro database.
There are multiple ways to get the schema info from FileMaker Pro databases, which is covered in detail in the How to Migrate FileMaker Pro to Valentina PDF file on the www.fmpromigrator.com website. The detailed PDF is close to 100 pages long, as it covers all of the various Get Info processing step options, and ODBC driver installation steps on both Mac OS X and Windows platforms.
To summarize the process here, I will just use the Table XML method to gather info from the .fp7 database file using FileMaker Pro Advanced. This process copies all of the Table/Field definition info from FileMaker Pro Advanced, puts the info onto the clipboard from Manage Database dialog.
Get Info - Copy Table XML
(1) Select all of the tables on the Tables tab, (2) click the Copy button.
Once the Table XML info has been put onto the clipboard, the Paste button will become active. Click the Cancel or Ok buttons to close the Define/Manage Database dialog.
Get Info - Continue Button
Within FmPro Migrator, click the Step 1 Get Info button (with the ClipBoard XML menu option selected). The yellow Continue... button will become visible once FmPro Migrator has completed processing the Table XML from the ClipBoard. Clicking the Continue button opens the Migration Process window where you will continue the migration process.
Get Fieldsize
After clicking the yellow Continue... button, the Migration Process window will be displayed. All of the remaining migration tasks will be performed from this window.
Click on a table in the Tables list. Once a table is selected, a list of fields contained within the table will be displayed in the Fields list. These fields represent the info gathered from the source database by FmPro Migrator.
In order to properly create the table in the Valentina database, FmPro Migrator needs to figure out the maximum amount of data stored in each field within the FileMaker Pro database table. This information is then used to automatically build the table creation SQL code for the Valentina database. Since there is no command available within FileMaker Pro for determining the maximum size of each field, FmPro Migrator reads every record from the table in order to make this calculation. The results are then stored within FmPro Migrator's embedded database for used in Step 3 when the table creation SQL code is generated.
Click the Step 2 Get Fieldsize button. FmPro Migrator will make an ODBC connection to the source FileMaker Pro database, and put up a progress dialog as it is reading thru the records. After the records have been successfully read from the table, the status menu at the bottom of the Step 2 button will change from "Not Started" to "Completed". The status will be set to "Failed" if the Get Fieldsize step fails for some reason.
If the Get Fieldsize step fails:
1) Check to make sure that the source database is open within FileMaker Pro/Advanced on the local computer.
2) Make sure that ODBC sharing is enabled.
3) For FileMaker 7+ databases, make sure that there is a TO on the RelationshipGraph which exactly matches the name of the base table being queried by FmPro Migrator.
4) Open the Define/Manage Database dialog and verify the existence of the base table within the FileMaker Pro database.
Create Table
Click the Step 3 Create Table button to create the table in the destination database.
FmPro Migrator will generate the table creation SQL code, connect to the Valentina database and create the table. Once the table has been successfully created, the status menu under the Step 3 Create Table button will be changed to "Completed".
Holding down the shift key while clicking the Step 3 Create Table button drops and re-creates the table in the Valentina database.
Transfer Data
Click the Step 4 Transfer Data button to transfer data from the source table in the FileMaker database to the newly created table in the Valentina database. Once the data has been transferred successfully, the status menu below the Step 4 Transfer Data button will change from "Not Started" to "Completed".
If the data transfer process fails, an error message will be displayed containing the text of the error message returned by the destination database.
Some of the most common data transfer errors include:
FileMaker numeric fields containing non-numeric data.
FileMaker date fields containing non-date/time data.
FileMaker primary key fields containing duplicate or empty values.
Each of these problems can be resolved by using the buttons above the Fields List to change the data types for the fields so that the table can be re-created as text fields in the Valentina database. This technique doesn't resolve the underlying issue with having incorrect data within the FileMaker Pro fields, but it does enable you to successfully transfer the data into the Valentina database where you can write SQL commands or stored procedures to clean up the data.
Repeating Fields Data
FileMaker Pro repeating fields include multiple data items separated by an ASCII(29) character. The FileMaker Pro database engine knows how to separate out the various repeat values for display and calculation purposes. But a standard SQL database will simply display all of the values lumped together in one field.
If the original FileMaker Pro table contains repeating fields, then extra buttons will be displayed below the Step 2 - Step 4 buttons. These buttons provide features to convert repeating field data into a relational database structure within the Valentina database. Each repeating field value is written into a related table with an foreign key value referring back to the primary key of the original parent table (as shown in the screenshot above).
In the next edition, we will look at migrating relationships.
 |
|
