Thank you for your post.
With the introduction of FileMaker Pro 8, you can import a database into a new table based on the schema of the imported data. This lets you consolidate tables from multiple files into a single database file without having to manually recreate the table schemas.
Since you are using FileMaker Pro Developer 7, you will have to create the table schema in the main file before importing from the secondary database file.
TSGal - I'm interested in the same, could you please walk me through the steps in FM10?
Warning: the more complex the two databases you want to merge, the more involved this process becomes.
Short answer first:
Select File | Import Records and select "new table" as the target table. Filemaker will import the table with all its field definitions plus the data it contains into the new table.
This operation did not import any relationships, scripts, value lists or layouts. Some calculation fields may be bracketed in comment symbols (/* ... */), because they refer to table occurrences that do not exist in the target file. Other field options may be broken as well. Scripts and Layouts can be imported separately. Value lists and relationships have to be recreated detail by detail and if you don't do this exactly the right way and in the right order, you can end up spending a lot of effort tracking down and fixing problems.
To merge a file that contains multiple relationships, scripts, layouts and value lists:
Study the relationship graph for both files. Print them out, lay them side by side and plan what changes you will need to make in order to merge the graphs into a single entity. You may want to rename certain table occurrences before you start importing things. You may want to change layout and/or script names too.
- Import your table
- Find all table occurrences that refer to this table in the external file and point them to the newly imported internal table.
- Very, carefully, reconstruct all necessary relationships from the source file that you need in your new file. Give all table occurrences exactly the same names as the original TO's in your source file.
- As needed, edit any field calculations and auto-enter options to remove comment brackets and restore working table references.
- Reconstruct all needed value lists in your target file so that they EXACTLY match the value list definitions in your source file.
- Recreate each required layout. (You can copy and paste entire layouts.) Give them exactly the same names as the originals.
- Import any scripts you need from your source file. Review and fix any errors in the scripts.
- Fix all buttons in your new layouts to perform the correct scripts. It may be easier to delete your newly pasted buttons and copy and paste them again now that you've imported the scripts.
Review everything for errors and test.
Using a copy of FMP advanced to print out Database Design Reports and searching for the keywords "Missing" and "Unknown" will help you find and correct any broken references you might have inadvertently caused.
I heard there was a utility that would do all of the things listed - is that just an urban myth?
I've seen references to an item called FMMigrator. It's third party software and I haven't used it, but I'd definitely google that term and check if out if I had to merge any major systems in the future.
(Note that there can be advantages to NOT merging your files in the first place.)
Thanks Phil!! This worked well.
"Recreate each required layout. (You can copy and paste entire layouts.) Give them exactly the same names as the originals."
How do you copy and paste entire layouts, i assuke you mean just the fields, but if your saying you can copy and paste all fields plus the layout parts, i would love to know how. Thanks!
It is possible to use AppleScript GUI scripting on MacOSX and WinAutomation on Windows to help automate the process of building the merged database file. This is the technique I use within FmPro Migrator. I have released these scripts under open source licensing for anyone who wants to use them. Included with the scripts is an open source LiveCode stack file which shows how to run these automation scripts with the FileMaker database. These scripts are available from within the demo version of FmPro Migrator, just select the File -> Export AppleScript or WinAutomation Scripts menu.When automating this type of processing, you can typically process hundreds of objects per hour. For instance, with layouts, you can create 200 - 800 layouts per hour, depending upon platform and whether you are performing Pass#1 (initial layout creation), or Pass#2 (replacing layout contents).
I had the same question where I needed to merge 4 data files into one new DB file.
Each input file had different fields but two common fields.
I did :
1. Import records for each file as a new table in the one new blank DB
(or just import the field definitions into new tables)
2. copied and pasted all the field definitions from each of the four tables definitions into a new blank table
3. opened the new empty table and imported the original data files a new record matching the keys and appending any non-match.
4. cleaned up the data in column format for any anomoliies
This all and only to recover the data not calculations or formats etc.
Hope this helped.