Tables in different files

[ctls]

I have tables in different files and I made relationships between them by adding File

References. How can I add the tables to a single file, which has the main table in it?

Thank You.

[Computer Geek]

There is no import function to import tables from one file to the next. In fact, you can't even duplicate a table in the same file. I did a 30 file solution. You need to recreate the table in the master file from scratch.

If you have a script that you are going to use based off of the reconverted file, you have a choice to make. Each field in a table has a sequential index number, based on the creation order. Scripts use the index number and not the name of the script. Therefore, if you don't recreate them in the exact same order, your scripts will get all screwed up. If you import a script, in the import process it looks for a name match, not the index. However, other things get screwed up, such as which layout to go to, etc.

Your choice is whether you are going to worry about the scipts or not. In my case, I had many complex scripts which would have been an absolute pain to edit. Therefore, I wrote a program that looked up the index numbers of the fields and recreated them in the master table with the exact same index number. I used the filemaker ODBC to pull this off. The only thing I had to do was go and enter the calculated fields. I have my layouts and scripts in one file and the tables and a few update scripts in the other.

I know that there is something from new millenuim that does something similar, but if you work for a place that is tight on funds, the poor man's solution works pretty good. Again, I did 30 files and it wasn't that bad.

I would suggest reading the tech briefs from the fm website on the conversion. I found them pretty helpful.

[Deep Thought II]

painful :-(

[ctls]

I guess its going to be a pain. Thanks for your help though. I appreciate it.

[ctls]

This is my first time with databases and filemaker pro and this might be a dumb question. But here it goes:

I have 11 files. Each with one table only. For example one file has Events table, the other file has People table and another file has Facilitators table, connecting People and Events.. What do I stand to lose if I keep them as separate file and relate them? I guess I am asking a very basic question here. What is the difference between having multiple tables in one file and relating them Vs having tables in different files and relating them?

Thanks in advance.

[Fenton]

This is complex question. But the simple answer is that it's OK to have separate files; but it will be a little bit more work to do things. It sounds as if you converted these files from 6 (since you already have 11).

I would take a hard look at some of those 11. Any join files, line items, value lists, or other "subsidiary" files should be brought into their "parent" file.

It will be more confusing to script things like the creation of new records, which involve fields from more than 1 file. You can include a Table Occurrence of the external file in the file's Relationship Graph. But you will likely have to do a similar thing in the other relevant files.

[This would be for "scheduling" type records; if you can add a schedule item from People, but also from Events, and also from Facilitators. You would probably need separate scripts even if it was all one file anyway; but there'd be less jumping around, and all the scripts would be in one place.]

Security is also more repetitive with multiple files. You can (and should) script the creation and deletion of Accounts. I have a sample file in the Sample files forum.

Definitely learn what you can about the relational and scripting "shortcuts" in version 7. The ability to reach across intermediate table occurrences is going to save much time, in many ways. The ability to attach many script steps directly to buttons, as well as pass parameters, will cut down on the number of scripts needed.

The necessity to "Commit Records" will cause problems unless you learn when to do it. Excellent article in "migration_foundations.pdf" (by Ilyse Kazar) available at FileMaker's support area.

Clean up your File References. And never play with matches :-)

[Computer Geek]

In my opinion, it will be well worth the time to bite the bullet and merge the tables into one file. All the reasons above point to exactly that and most of all, you will find that it is much easier to program with the tables in one file.

I would suggest that you read the migration documents from FMP. Also, I would do a series of stable points, where you move one table, verify that it and the scripts are working. Once that is done, save it in a folder labeled Stable Point 1. Then go onto the next table. When it is done, save that as Stable Point 2 ...

I did that and I did have to refer back to them from time to time. This was suggested in one of the FM migration documents and I found it very helpful.

You will thank yourself later for doing the migration...

[ctls]

Thanks Fenton, Computer Geek.

I guess I should put subsidiary tables in the same file as their parent table. But can I put ALL the tables in the same file? For example, can I put People table and the Events table in the same fle? I do have a table Facilitation, which has People ID(from People) and Event ID(from Events) relating the two tables. I thought I could put People, and Facilitation in one file and keep Events in a separate one.

I realize programming will be easier with tables in the same file. But is it okay to put ALL of them in the same file??

Thanks for your help.

[Fenton]

Yes, you can put them all in one file. It is easier to program, with the caveat that the Scripts menu can get kind of long, both for the developer and the user. If you have many files in 6, but each is reasonably simple, then "all in one file" is probably the way to go.

Each Table functions much like a file did in 6. It has its own Fields. They all share the same Relationship Graph. In "scheduling" type solutions that is plus, because there is so much interaction between the tables/files. They all share the same Value lists, which is also a plus. They all share the same Scripts, which can be a plus, but becomes less of one if there are too many. On the other hand you don't need as many scripts in 7. Many things can be done directly which required separate scripts in 6.

[ctls]

Hey Fenton. Thanks a million.

I read at some other places too about tables in FM7 and decided to put them all in one file. I've got to redo all the layouts, but I guess better now than later.

[Fenton]

You don't really have to completely redo the layouts, though you'll probably be making some changes (font sizes for example) and improvements. Convert your v6 files (this will take a while, and they will be a bit of a mess; you'll see why building from scratch is a good idea :-). Drop the entire folder of files on 7 to convert, not one at time.

Then you can copy/paste layouts from them. You MUST assign the layout to the correct Table.

Which means you must have first created the correct Table. Every base table you create will automatically be created as a Table Occurrence in the Relationship Graph. Most basic layouts will be assigned to one of these. It's best to leave their names close to what the base table is, so you know which they are.

Any further relationships will require more Table Occurrences of base tables. These may or may not have particular layouts assigned to them, usually not.

Sometimes you have to create what seems like a duplicate of a base table, out along the line. Because you cannot create a relational path that is a closed loop, ie., going out then coming back to the same TO. This is required, and FileMaker will tell you. Name it so you don't confuse it with the base table. Welcome to 7 :-)