It can be done with just FileMaker but there are a lot of steps to be followed in exactly the correct order. FileMaker Advanced has tools that are very helpful in this and there is a third part tool, FMMigrator that claims to make this process much simpler. Depending on the level of complexity, you may find it easier to keep your tables in separate files due to the work involved in merging them.
1) Import records, if used with the New Table option selected in the target table drop down can be used to import a table from one file such that the data and field definitions are then imported into a new table. This pulls all the tables into the same file, but any layouts, scripts relationships and value lists still remain in the original file. Once you have imported a table like this, you next need to link the table occurrence this process creates in Manage | Database | Relationships to the other occurrences found there to create the same relationships between tables that you had with any occurrence that uses an external data source reference to refer to the original table in its separate file.
Table occurrences are the "boxes" found on this tab of Manage | Database. If you have any boxes here where the box's name is in italics, it refers to a table in another file. You can double click an occurrence to see what table and file is referenced by a given occurrence.
For each such occurrence that refers to this table in the external file, you can double click them to open a dialog box where you can specify that they refer to the newly imported table instead. It's helpful to copy the occurrence name to the clipboard before you make this change as "pointing" it at a different table automatically changes its name and if you have copied the original name, you can paste it back into the dialog to keep the name unchanged.
During the import, any calculation fields that refer to fields in a related table will likely be disabled, enclosed in /* comment brackets */. Once you have imported the needed tables and recreated any needed relationships, you can edit these calculation fields to remove the brackets.
2) Value lists cannot be imported, but you can copy a list of custom values from the original file into a new value lists of exactly the same name in the new file. Sinces both scripts and layouts may refer to a value list, it is best to do this before recreating any layouts or importing any scripts from the original separate file.
3) Layouts cannot be imported, but you can create a new blank layout and then copy all layout objects from one file and paste them into the new blank layout. However, any buttons or script triggers that are part of this layout will be broken unless you first import the scripts that they are set up to perform, but if you import the scripts first, any steps in the scripts that refer to layouts or objects on your layout will be broken. Here's how to minimize the damage and confusion:
Create a new blank layout in your target file. Make sure that the exact same name is referred to in Show Records From in Layout setup for this layout as the one from which you will be copying layout objects. This table name is actually the name of one of the table occurrence boxes in Manage | Database | Relationships. You may need to temporarily rename an occurrence in one file or the other to get the names to be exactly the same. Use the Inspector's postion tab and part set up to create each layout part (body, footer, sub summary, etc...) with exactly the same dimensions as the original layout. (You can click the units shown until you get pixels to make this simpler.)
Go to the layout in the original file (source file), enter layout mode. Select and copy all items on the layout to the clipboard. Return to your new layout and paste all objects. Don't worry about where on the layout you paste these objects, you are going to copy and paste the same objects all over again in just a moment. Save your changes to the layout.
Open Manage | Scripts in the file where you've imported your table (Target file). Use the Import scripts tool to import all scripts that are performed by buttons and script triggers on the source file's layout. If necessary, go back to the source file's layout can copy the objects back to your clipboard. Make a note of the top and left values shown in the position tab of the inspector. Return to the new layout, select and delete all the layout objects, then paste again to the layout. After pasting and with all pasted objects still selected, use the top and left boxes in the inspector to adjust the position of all layout objects to in one step so that they match the original layout. If necessary, double check all options specified in Layout setup in the original and make identical selections for the new layout that duplicates it in the target file.
Finally, the Database Design Report you can generate in FileMaker Advanced, while it has a few bugs, it is still very useful for checking your target file over for any broken or missing references created by the table merge process. After generating the DDR, you can text search it for "unkown" and "missing" to check for possible lose ends.
HIFirst of all, thank you so much for replying. It must have taken you ages to write all that. I have read through it and it makes sense, but i can't get of the starting blocks. I went to import records> file and then selected new table from the target menu, but then I get stuck. i have (hopefully) included a screen grab. The source fields and target fields arn't there and there doesn't seam to be an option to copy them across.I hope this makes sense.file://localhost/Users/weedonpaul/Desktop/Import%20Field%20Mapping.jpg
Can't see your screen shot. The filepath you've posted is the path to a file on your desktop so there's no way for me to access it from here. Did you use the upload an image control listed below the Post A Answer box?
First you select a file from which to import, then select a table from this file. WHen you then select "new table", you should see the exact same fields listed for both the source and (new) target tables.
Sorry, I told you i was an idiot.
Don't see how you got that. Are you sure that the Events table in your source file has defined fields?
How can i check that? i can send a screan shot of my relationships if that helps?
Relationships won't help you here.
Open the original file, the one you selected when you tried to import the table. Go to Manage | database | fields and select the events table from the table drop down. Do you see any field definitions listed here?
I have the screen you see (hopefully) and when i click on customers i get a drop down box that say's "local tables" and below it it say's "customers"
yes, but you were importing from events, not customers. Select events from this drop down instead of customers.
Event's isn't in the drop down menu. but i can open up the events file and repeat the process and I've got a list of 27 fields.
Events isn't in the drop down menu.
I think you used import records to attempt importing from a different file, one that does not have any fields defined in its Events table. That's the only explanation I can think of for your dialog that shows no fields listed.
i can open up the events file and repeat the process and I've got a list of 27 fields.
Open that file and make sure that you can see all 27 fields. Make a careful note of the file name and it's location. Then return to your file where you want to import a copy of this table and try again.
We got there in the end, thank you for your patience.