How do I merge databases together?

[cpando1974]

Hi, not sure if this is right section, apologies if not.

I have in excess of 125 databases and of course all of them cannot be open at the same time on FM Server. What I would like to know is, how to amalgamate databases, bringing across the table and its layouts etc?

Nearly all of the databases at present are just one table (originating from Filemaker 5) and tend to relate to another database.

Cheers

Chris

[Fenton]

There are several topics involved, but I moved it to "Relationships", as much of what you'll be changing will be relationships.

What you want to do, for starters anyway, is pick tables which are entirely a child of a parent table (file in this case). Invoices and Line Items. People and Phones/Addresses. Etc.. In other words, create "modules" of parent/children. That should knock the number of files down (to about 1/3). Then you can consider going further.

Since you want to copy entire tables, you can use the Import Records... command. At the bottom of the "Target" drop-down for the source table is "New Table" (name of the table). So just point that at your other file, and Import. It wll bring in the fields and data, with some caveats.

Since you normally cannot Import into a calculation field, by default FileMaker does not have them lined up. It will do it in this case if you click the arrow on for calculations (and Summary fields, etc.). It will then bring them in, along with their calculations.

In some cases however the calculation itself will be commented out. This is likely because it has fields referenced it which are later in the creation order. In which case you just remove the comments and it works. It will also ${bracket} field names which are named poorly. Example:

"${StartStop g}" The "" is poor naming (very old file of mine, built in FileMaker 4)

Best idea is to fix the name at this point, in BOTH the new AND old files. So the scripts and layouts see them as the same.

"Start|Stop g" is OK. "z_gStart|Stop" is better. (My newer naming convention.)

Then rebuild the relationship graph (manually) to be exactly the same as the old file. You can Print the relationship graph of the old file. It is best to "print to pdf", which is built-in on Macs, but requires a driver on Windows (CutePDF?). You cannot have the Relationship Graph open in both files at the same time (unless you have another version of FileMaker; but the PDF is the easiest method).

There is also a function, in the Design section: TableNames ( Get (FileName)). Do this in the old file, result Text, Storage [x] Do not store. It will produce a list, which you can then copy/paste from. It is important to make the names exactly the same in the new file.

You will need to create then copy/paste layouts from the old file, naming them exactly the same (for scripts). But first (important) make sure the name of the new table on the relationship graph is exactly what the old one was named.

Then you'll need to Import Scripts. Then check them carefully for breakage. If all of the above was done perfectly they should be OK.

I don't know for sure how all the above works in FileMaker 8. FileMaker seems to get better at this "lining stuff up by name" in later versions.

If you got FileMaker Pro Advanced you could copy/paste Tables, Fields (some) and Scripts (some). But FileMaker Pro can do Import, as long as you want the whole thing, which you likely do.

All the above must be done in the order:

1. Import table (all fields)

2. Relationship Graph

3. Layouts

4. Scripts

I would advise only Importing one script, to see if things line up OK. Same for Layouts. If a layout's fields do not line up, because of a silly naming error of the relationship (table occurrence) on the relationship graph, you would have to retarget every single field, which is a PITA. Best to get the names right.

Edited November 23, 2010 by Guest

[Fenton]

P.S. Actually there are 2 steps to the layouts.

3. Create Layouts; but do NOT copy/paste

4. Scripts

5. Copy/paste layouts

Because otherwise all your buttons on layouts which run scripts will break. They will choose the wrong scripts, which can be a disaster if you don't fix them. If however the layout is created first, but left blank, the scripts will be happy. Then once the scripts are in, copy/pasting the layout objects (incl. buttons) will hopefully line up with the scripts. Check this carefully, because errors of button assignment are not visible.

[cpando1974]

Many thanks, will give it a try.