How to migrate FileMaker to FileMaker
7 (Windows and MacOS X)
FileMaker
to FileMaker 7 Quickstart Guide PDF - for MacOS X (1.26Mb)
FileMaker
to FileMaker 7 Quickstart Guide PDF - for Windows (1.23Mb)
Note: Additional information is contained within the <database>_instructions.txt
file created during the file generation process. The <database>_instructions.txt
file contains the actual file names created by FmPro Migrator. This
document uses an example FileMaker 5 database table named "example"
which is migrated to FileMaker 7. The files created for your database
will use the names of the database tables in your FileMaker database.
Usage Instructions - FileMaker to FileMaker 7 on Windows
Note: FmPro Migrator for Windows requires that Perl be installed on
your Windows computer before generating scripts. Activestate Perl may
be downloaded from www.activestate.com.
1) Create an ODBC DSN for each FileMaker database, unless these DSNs
have already been created.
2) Select FileMaker 3/4/5/6/7 as the Source Database in FmPro Migrator.
3) Enter the source FileMaker 7 database ODBC DSN, username, and password
info the fields provided on the FileMaker tab.
4) Create a new test database in FileMaker 7, or open an existing database
file. Enable the ODBC/JDBC Sharing Companion for this FileMaker database.
Your migrated FileMaker 7 tables will be created within this new database.
5) Enter the FileMaker 7 ODBC DSN, username, and password on the FileMaker
tab. Make sure that the ODBC/JDBC Sharing Companion is enabled for the
FileMaker 7 database. Each ODBC DSN entered on the FileMaker tab is
passed through to the created scripts as entered on the FileMaker tab
of FmPro Migrator. Note: The default "Admin" account created
for a new FileMaker 7 database is already configured with ODBC/JDBC
access privileges. If you use another account, insure that ODBC/JDBC
access privileges have been granted to the account.
6) Select or create the destination directory for the migration files
which will be generated by FmPro Migrator.
7) Save the configuration information by selecting Save As from the
File menu.
8) After configuring the FileMaker ODBC DSN for the source FileMaker
7 database, press the Refresh button in FmPro Migrator.
Note: If any FileMaker 7 database names contain spaces, these should
be removed before processing the file.
9) Press the Migrate button to generate the migration scripts and database
documentation files.
10) Install the Perl DBI, DBD::ODBC modules on the Windows server along
with the FileMaker ODBC Driver. The installer for the FileMaker ODBC
Driver is located within the ODBC Client Driver Installer folder within
the xDBC folder on the FileMaker installer CD. (The DBI/DBD::ODBC modules
can be downloaded from www.cpan.org) or installed with Activestate PPM.
Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit
11) Run the example_create_table1.pl Perl script to create
the new database table in the FileMaker 7 database. Note: If FileMaker
7 is the source database, please see the troubleshooting section for
info about working with Container fields and the configuration of NULL/NOT
NULL attributes for each field.
12) After creating the new FileMaker 7 database table it is necessary
to create a layout for the new table. A FileMaker layout is not created
automatically when creating a database table through an ODBC connection.
Make sure that you are viewing this new layout in browse mode when importing
data into the new database table in the next step.
13) When performing a FileMaker to FileMaker 7 migration, the data is
imported into the new FileMaker 7 database table with the Import Records
feature within FileMaker 7. If a FileMaker 7 file is being migrated
to FileMaker 7, the source FileMaker 7 database file may be used as
the source for importing records into the new FileMaker 7 database table.
If a prior version of FileMaker is being migrated into a FileMaker 7
file, the original file should be converted into the FileMaker 7 format.
Then the converted FileMaker 7 file may be imported into the new database
table which has been created within the destination FileMaker 7 database
file.
14) Use the information within the Fields tab of the Define Database
window of the converted database file to copy the calculation field
formulas between databases. Carefully test each formula to determine
if any field name changes or new FileMaker 7 formula features will invalidate
each formula.
15) Use the Relationships tab of the Define Database window to define
relationships between database tables.
Usage Instructions - FileMaker to FileMaker 7 on MacOS
X
Note: FmPro Migrator for MacOS X uses AppleScript to query the source
FileMaker database in order to obtain database structure information.
Migration scripts are created for migrating to FileMaker 7, and these
scripts will need to be run on a Windows computer (or Virtual PC on
MacOS X) due to the need for a FileMaker ODBC driver.
1) Select FileMaker 3/4/5/6/7 as the Source Database on the FileMaker
tab within FmPro Migrator.
2) For FileMaker 7 to FileMaker 7 migrations, enter the source FileMaker
7 database ODBC DSN, username, and password info the fields provided
on the FileMaker tab.
For FileMaker 3/4/5/6 to FileMaker 7 migrations a default ODBC DSN name
of example_fmp_dsn is used for the source database within the generated
migration scripts.
3) Enter the name which will be used for the FileMaker 7 ODBC DSN, username,
and password on the FileMaker tab. Note: The default "Admin"
account created for a new FileMaker 7 database is already configured
with ODBC/JDBC access privileges. If you use another account, insure
that ODBC/JDBC access privileges have been granted to the account.
4) Select or create the destination directory for the migration files
which will be generated by FmPro Migrator.
5) Save the configuration information by selecting Save As from the
File menu.
6) Press the Migrate button to generate the migration scripts and database
documentation files. Note: Migration files will be generated for each
table within the first FileMaker 7 database. This behavior is different
from the way FmPro Migrator migrates FileMaker 3/4/5/6 databases. This
change in behavior means that FileMaker 7 databases are migrated one
at a time instead of migrating up to 50 open database files. This change
insures that generated output files will not be accidently overwritten
if two separate FileMaker 7 database files contain database tables with
the same name.
7) Copy the generated migration files to a Windows PC (or a directory
which can be accessed by Virtual PC on MacOS X).
8) Create an ODBC DSN in the Windows ODBC control panel for each FileMaker
database, unless these DSNs have already been created.
9) Install FileMaker 7 and the FileMaker ODBC driver on Windows (or
within Virtual PC). Each FileMaker 7 CD includes both MacOS X and Windows
versions of the FileMaker 7 database application.
10) Create a new test database in FileMaker 7 (on Windows or Virtual
PC), or open an existing database file. Enable the ODBC/JDBC Sharing
Companion for this FileMaker database, after making sure that the Remote/Local
Data Access Companions are disabled within any older versions of FileMaker
running on the same computer. Your migrated FileMaker 7 tables will
be created within this new database. Note: Unlike previous versions
of FileMaker on Windows, the ODBC driver for FileMaker 7 is no longer
able to connect with a FileMaker database running on a different computer.
All ODBC connections must be made to IP address 127.0.0.1, therefore
the FileMaker 7 database must be running on the same computer as the
migration scripts.
11) Install Activestate Perl (www.activestate.com), the Perl DBI, DBD::ODBC
modules on the Windows PC (or Virtual PC on MacOS X) along with the
FileMaker ODBC Driver. The installer for the FileMaker ODBC Driver is
located within the ODBC Client Driver Installer folder within the xDBC
folder on the FileMaker installer CD. (The DBI/DBD::ODBC modules can
be downloaded from www.cpan.org) or installed with Activestate PPM.
Install commands follow:
ppm
PPM> install DBI
PPM> install DBD-ODBC
PPM> quit
12) Run the example_create_table1.pl Perl script to create
the new database table in the FileMaker 7 database.
13) After creating the new FileMaker 7 database table it is necessary
to create a layout for the new table. A FileMaker layout is not created
automatically when creating a database table through an ODBC connection.
Make sure that you are viewing this new layout in browse mode when importing
data into the new database table in the next step.
14A) If the source database is a FileMaker 7 database file, you can
directly import the data from a FileMaker 7 database file by using the
Import Records menu item in the File menu. Select FileMaker Pro as the
type of file you will be importing.
14B) If the source databases is a FileMaker 3/4/5/6 database, then convert
the older FileMaker database file into a FileMaker 7 database file before
importing the data.
15) Use the information within the example_report_fmp file to copy the
calculation field formulas between databases. Carefully test each formula
to determine if any field name changes or new FileMaker 7 formula features
will invalidate each formula.
16) Use the Relationships tab of the Define Database window to define
relationships between database tables.
FileMaker Folder Tab - Field Descriptions
The FileMaker folder tab enables the entry of general info and FileMaker
specific info about the migration process.
Click the Browse button to select an existing folder which
will contain the migration scripts and reports created by FmPro Migrator.
You must have write access to the output directory selected, otherwise
an error dialog will be displayed.
Source Database ODBC DSN - Enter the ODBC DSN name for
the FileMaker 7 database and select your FileMaker database version
from the Destination Database menu prior to pressing the Refresh button.
All of the FileMaker 7 database tables within the FileMaker database
will appear in the Open Databases field. There needs to be at least
one database table available for the migration process to be performed.
This information is utilized within the generated migration scripts.
Source Database Username - field - If the FileMaker 7
database requires a username in order to access the data, enter a username
in this field which allows access to all of the database tables within
the FileMaker 7 database file. This information is utilized within the
generated migration scripts.
Source Database Password - field - If the FileMaker 7
database requires a password in order to access the data, enter a password
in this field which allows access to all of the database tables within
the FileMaker 7 database file. This information is utilized within the
generated migration scripts.
Destination Database ODBC DSN - field - Enter the FileMaker
7 database ODBC DSN within this field. This information is utilized
within the generated migration scripts.
Destination Database - menu - Select FileMaker 7 as the
database which will be the destination for the data.
Destination Database Username - field - The default username
for a new FileMaker 7 database is "Admin", therefore this
default value is automatically entered into this field whenever FileMaker
7 is selected as the destination database. Enter a different username
in this field if a different account is to be used to create and access
data within the FileMaker database. The username entered into this field
needs to have the extended ODBC/JDBC privilege within FileMaker. The
default Admin account is automatically granted this extended privilege
when the FileMaker 7 database is created. This information is utilized
within the generated migration scripts.
Destination Database Password - field - If the FileMaker
database requires a password in order to access the data, enter a password
in this field which allows access to all of the database tables within
the FileMaker database. The default password for the FileMaker 7 "Admin"
account is blank. This information is utilized within the generated
migration scripts.
Large Text Fields - field - FileMaker 3/4/5/6 text fields
are automatically converted to VARCHAR(64000) columns within a FileMaker
7 database. FileMaker 7 text fields are converted to VARCHAR(1000000)
columns within a destination FileMaker 7 database. Therefore the Large
Text Fields field does not require the entry of any information when
performing FileMaker to FileMaker 7 migrations. This field can be used
simply to designate certain columns which may need to grow more than
the default of 64000 characters.
Path to Perl - This field contains the path to the Perl
executable application for UNIX/Mac OS X servers. In most cases, no
change will be needed to this field because the Windows servers will
use the ".pl" extension in order to associate Perl.exe with
each Perl script.
Usage Notes
Time Fields
When FmPro Migrator uses an ODBC connection to retrieve field type info
from FileMaker databases, it is not possible to automatically determine
whether the destination field should be created as a date or time field.
Therefore FmPro Migrator assigns the field type as a time field if the
text "time" is contained within the field name. Otherwise
the field type is assigned to be a FileMaker date format field. If a
field is mistakenly assigned to be a time format field by FmPro Migrator,
then this automated behavior can be overridden by simply changing the
name of the field in Access so that the text "time" does not
appear within the field name.
Large Text Fields
FileMaker 3/4/5/6 text fields are automatically converted to VARCHAR(64000)
columns within a FileMaker 7 database. FileMaker 7 text fields are converted
to VARCHAR(1000000) columns within a destination FileMaker 7 database.
Therefore the Large Text Fields field does not require the entry of
any information when performing FileMaker to FileMaker 7 migrations.
ODBC driver and client software installation
The best way to transfer data within FileMaker text and container fields
is by using the example_fmpro_to_fmpro_xfer_odbc1.pl program. This program
is generated by FmPro Migrator based upon the specified source database
table structure in order to facilitate the transfer of data to FileMaker
7.
The example_fmpro_to_fmpro_xfer_odbc1.pl program transfers data for
all FileMaker field types including text fields up to 1000000 bytes
and image data from container fields. The program makes use of bind
variables to specify field names and ODBC field types. The example_fmpro_to_fmpro_xfer_odbc1.pl
program needs to be run on a Windows computer which has Perl, the Perl
DBI module, the Perl DBD::ODBC module, and FileMaker 7 ODBC driver software
installed. The example_fmpro_to_fmpro_xfer_odbc1.pl script will then
read the data from the source FileMaker database and then write that
data into a FileMaker 7 database running on the same computer. The FileMaker
7 database needs to be running on the same computer which is running
the example_fmpro_to_fmpro_xfer_odbc1.pl script if FileMaker Pro 7 is
being used. If FileMaker 7 Server is being used, the database may be
located on a different computer and accessed thru the network.
Container Fields
FileMaker uses container fields for storing binary data such as pictures,
sounds and movies. Data located within container fields is transferred
to FileMaker 7 container fields (BLOB column type).
FileMaker Pro 7 vs FileMaker Advanced Server 7 and ODBC
FileMaker Pro 7 enables ODBC/JDBC access only for programs running on
the same computer as the FileMaker Pro 7 database. The driver is configured
with hostname "localhost" or IP address 127.0.0.1 within the
DataDirect SequeLink ODBC driver DSN for a individual FileMaker database.
The default TCP/IP port number used for the DataDirect SequeLink server
is 2399.
FileMaker 7 Advanced Server allows ODBC/JDBC network connections from
other computers on a network, depending upon the security which has
been configured within the database.
Report Differences
FmPro Migrator for Windows generates a slightly different database report
compared to FmPro Migrator for MacOS X. FmPro Migrator for Windows utilizes
a ODBC connection to the FileMaker database since AppleScript is not
available for Windows. The only information available about the database
thru an ODBC connection is:
The Name of the Database
The List of Field Names
The Type of each Field
The Empty Ok attribute for each Field
The following information listed on the report consists
of default values used to fill in the report:
Field IDs (always an incrementing value)
Unique Values (always set to Not Unique)
Repeating Values (always set to No)
Global Values (always set to No)
Access Status for each field (always set to read/write)
Protection Status for each field (always set to formulas/protected)
Calculation for each field (always blank)
Script Names (none listed)
Layout Names (only 1 listed - named All Fields, containing all fields)
Troubleshooting
[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]database schema is locked by another user
(303) (SQL-HY000) [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]Query Failed (SQL-HY000)(DBD: st_execute/SQLExecute
err=-1) at example_create_table1.pl line 64.
Solution: This error occurs if the example_create_table1.pl
Perl program is being run to create the FileMaker 7 database table while
the Define Database window is open. Clicking either the Ok or Cancel
buttons will close the Define Database window and prevent this error
from occurring.
[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker
driver][FileMaker]duplicate name (12) (SQL-HY000) [DataDirect][ODBC
SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Query
Failed (SQL-HY000)(DBD: st_execute/SQLExecute err=-1) at example_create_table1.pl
line 64.
Solution: This error occurs if the FileMaker database
table example already exists within either the FileMaker Define Database
Tables window or the Define Database Relationships window. Delete the
example table from both of these locations, and then run the example_create_table1.pl
program again.
[Windows only issue] When performing a FileMaker 7 to
FileMaker 7 migration, FileMaker container fields are migrated as text
fields and every field is set to require "Not Empty" data
validation. This occurs when FileMaker 7.0v1 on Windows is used as the
source of the migration in FmPro Migrator.
FmPro Migrator for Windows makes an ODBC connection to
the FileMaker database which is being migrated in order to obtain database
structure information. When FileMaker 7 is used as the source of the
migration, the data types returned from the ODBC driver report that
Container fields are text fields and that all fields are "NOT NULL"
fields. There are two solutions to this issue:
Solution1: If a version of the database file is available within the
FileMaker 3/4/5/6 format, then use the older version of the file as
the source of the migration for FmPro Migrator. Versions of the FileMaker
ODBC driver prior to FileMaker 7 do not display this behavior. FmPro
Migrator will query the database and obtain the correct data types and
NULL/NOT NULL status for creating the example_create_table1.pl file.
Use of the older database file is only required when creating the migration
scripts. After the scripts have been created, use the FileMaker 7 version
of the file for the actual import of the data into the new FileMaker
7 database table.
Solution2: If an older version of the source database file is not available,
then use FmPro Migrator to create the migration scripts from the FileMaker
7 source database file. Then make changes to the field types and validation
settings within the Define Database Fields window prior to importing
the data into the new FileMaker 7 database table. Depending upon the
number of fields which are contained within the source database file,
it may be more efficient to update the create table SQL commands within
the example_create_table1.pl file prior to creating the new FileMaker
7 table. To make these change manually, change the VARCHAR(1000000)
field types to BLOB for each of the container fields. Then perform a
search and replace within a text editor to change all "NOT NULL"
text to "NULL". It is likely that very few database fields
will require "NOT NULL" validation so these few fields can
be updated manually.
The example_fmpro_to_fmpro_xfer_odbc21.pl file is not
created when performing a FileMaker to FileMaker 7 migration.
Solution: When performing a FileMaker to FileMaker 7 migration,
the example_fmpro_to_fmpro_xfer_odbc21.pl file is not required for the
data migration phase of the migration process. If a FileMaker 7 file
is being migrated to FileMaker 7, the source FileMaker 7 database file
may be used as the source for importing records into the new FileMaker
7 database table.
If a prior version of FileMaker is being migrated into a FileMaker 7
file, the original file should be converted into the FileMaker 7 format.
Then the converted FileMaker 7 file may be imported into the new database
table which has been created within the destination FileMaker 7 file.
[DataDirect][ODBC SequeLink driver][SequeLink Client]Network
problem, connection unexpectedly closed by peer (SQL-HY000)(DBD: db_login/SQLConnect
err=-1)
Solution: This error may indicate that FileMaker 7 ODBC/JDBC
file sharing is enabled at the same time an older FileMaker database
is also attempting to share a file with the Local/Remote Data Access
Companions. Only one FileMaker application can have ODBC file sharing
turned on at the same time on the same computer or they will interfere
with each other. Turn off sharing for one of the databases in order
to resolve this issue.
FmPro Migrator attempts to generate migration scripts,
but the "Generating Scripts" dialog box never disappears.
Solution: Database tables which contain no fields may
cause this symptom with FmPro Migrator for Windows. There is no reason
to try migrating a database table containing 0 fields, so delete the
table and the reference to the table in the Define Database Relationship
window of FileMaker 7.
Error: Can't call method "execute" on an undefined
value at example_create_table1.pl line 100.
Solution: There are multiple reasons why this error may
occur. One reason is that there is a FileMaker reserved word or illegal
name being used as a column name. FmPro Migrator contains a pre-defined
list of FileMaker SQL reserved words. Column names which are found to
consist of these words are renamed by FmPro Migrator by appending the
"_" character at the end of the column name. If additional
reserved words need to be added to this list, please send an email to
.com Solutions Inc. with your additions so that they may be added to
future revisions of FmPro Migrator.
This error may also occur due to almost any type of SQL syntax error
within the CREATE TABLE SQL statement. Using incorrectly spelled column
types, omitting a comma at the end of each statement line or having
a comma at the end of the very last line of the SQL code will cause
these errors. These types of errors generally occur after manually editing
the CREATE TABLE SQL statements. If the number of edits have been minimal,
it may be easier to regenerate the script again with FmPro Migrator,
then manually re-edit the file.
FileMaker 7 column names created via an ODBC connection to the database
cannot start with a number or an underscore "_" character,
and these conditions will also result in this error being displayed.
DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]Cannot Create Table (SQL-HY000) [DataDirect][ODBC
SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Query
Failed (SQL-HY000)(DBD:st_execute/SQLExecute err=-1) at example_create_table1.pl
line 832.
Solution: This error message is displayed whenever an
attempt is made to create a duplicate column name within the FileMaker
database through an ODBC connection. During the generation of the list
of column names, FmPro Migrator removes illegal SQL characters and replaces
spaces with underscore characters. This renaming process guarantees
that column names will be valid within any SQL statements, compatible
with web publishing and compatible when sharing data with other applications.
However duplicate column names may occur as a result of this renaming
process. FileMaker 7 and its associated ODBC driver do not report the
name of the duplicate column name which is contained within the CREATE
TABLE SQL statement. Generally it will be necessary to look through
the column names listed in the CREATE TABLE SQL statement in order to
determine which column is duplicated. Another option is to look through
the list of columns within the original FileMaker database, make the
changes within the original database, then regenerate the example_create_table1.pl
file with FmPro Migrator.
[DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]Maximum number of columns in a table is
1024 (SQL-HY000) [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC
FileMaker driver][FileMaker]Query Failed (SQL-HY000)(DBD: st_execute/SQLExecute
err=-1) at example_create_table1.pl line 1366.
This error is the result of having too many columns within
the CREATE TABLE SQL statement. This error only occurs when attempting
to create a FileMaker 7 database table through an ODBC connection to
the FileMaker database. There are several solutions to this problem.
Solution1: One solution is to replace global fields with the new FileMaker
7 script variable feature. This one change will often result in reducing
the field count below the 1024 maximum. It is recommended that you make
a copy of your original database, delete all of the global fields, then
create migration scripts with FmPro Migrator. Then you will be able
to move the structure of the database into FileMaker 7 and import your
data into the new FileMaker 7 table.
Solution2: The best (but most time consuming) solution to this problem
is to redesign the database structure using a more relational database
structure. Related information should be separated into different database
tables instead of putting all of the info into one single large table.
Information can be joined together for reporting purposes by grouping
as many fields as necessary onto a report layout. Once again you would
do the design work in the older version of FileMaker, then use FmPro
Migrator to generate the table creation scripts for the new FileMaker
7 database tables.
Solution3: The least desirable solution from a database design standpoint
is to simply keep the design exactly the same, but add additional columns
manually. The 1024 column limit is only enforced by FileMaker 7 when
issuing a CREATE TABLE SQL statement through an ODBC connection to the
database. If the number of additional columns is small, you can manually
add additional columns beyond the 1024 limit within the Define Database/Tables
window. To implement this solution a copy should be made of the original
FileMaker database file. Then remove all fields after the first 1024
fields. Use FmPro Migrator to generate the example_create_table1.pl
file, then create the new FileMaker 7 database table with this script.
Add the additional columns manually within the Define Database/Tables
window.
Error: FmPro Migrator doesn't complete generating scripts
for a FileMaker 7 database, with the "Generating Scripts"
window continuing to be displayed on the screen.
Solution: This error may occur when generating scripts
for a FileMaker database table which contains 0 fields. There is no
reason to migrate a database table containing 0 fields, so any tables
of this type should be deleted from the FileMaker database prior to
generating migration scripts.
[MacOS X only issue] FmPro Migrator gives a prompt requesting
that the FileMaker application be selected, even though the FileMaker
application name has been selected with the "Select" button
on the FileMaker tab.
Solution: FmPro Migrator may fail to find the correct
version of FileMaker installed on your computer if both FileMaker 6
and FileMaker 7 applications are installed on the same computer. FileMaker
7 shows up in the list of open applications seen by AppleScript commands
as "FileMaker", instead of "FileMaker Pro" or "FileMaker
Developer" as is the case with older versions of FileMaker. This
issue can be resolved by renaming the FileMaker application on your
hard drive with its actual version. So "FileMaker Pro" could
then be named "FileMaker Pro 6" or "FileMaker Pro 7".
Then type the actual name into the FileMaker App Name field within FmPro
Migrator instead of selecting it with the "Select" button.
The contents of this field will be used by the AppleScript commands
to send commands to the correct version of the FileMaker database application.
