The bigger project consists of several sets of data where each set consists of three tables (table1, table2, and table3). All table1's are identical in field names and structure but differ only in the number of records and data contained. Tables 2 and 3 are similar.
Rather than a problem with Table Occurences, it appears you have a problem with too many data source tables storing the same type of data. In terms of data source tables, you should have exactly 1 Table 1, 1 Table 2 and 1 Table 3. You would then manipulate your data from within each table via Table Occurrences and Found Sets much like you can manipulate different recordsets in Access.
What problem are you solving by having so many different tables that differ only "in the number of records and data contained"?
Phil: OK, lets say I have 2 tables for the Lincoln School. One table has student data and a second has teacher data. The database file includes those two tables plus a bunch of data editing, viewing and printing layouts and analysis routines and a bunch of scripts to make it all happen.
Now I want to work on the Roosevelt School. For that school I also have student and teacher tables with identical fields as the first school but with different number of records and data.
So it sounds like I should swap the records between the two schools but keep the 2 tables intact in the database file. I can try that but since I am editing data from one school, in order to switch to the 2nd school, I first must export the 1st school records from the database, then erase the records in the database, and then import the records from the 2nd school into the database. I'll give this a try.
That's not what I am suggesting here. Keep in mind that I only have an extremely limited understanding of your system, so feel free to disagree with me...
What I am suggesting is that you put all the records for Roosevelt teachers and Lincoln teachers in the same table. A field in this table contains the ID number of the related School table record that identifies which school they teach at. A similar approach may be set up with the student table.
When you want to work with the Lincoln teacher records, you either perform a find to pull up a found set of only Lincoln teachers or use a portal designed via relationship or portal filter to only list Lincoln teachers. Scripts, access permission settings and layout design elements can be used to keep records from one school from being unintentionally mixed/confused with records from another. If I were creating such a system in Access, I'd use the same table structure, BTW.
This simplified design has many advantages. Here's a few:
- No need move data back and forth with imports just to see the records for a given school.
- No need to add more tables everytime you add a school
- Reports can now be easily set up that combine data from multiple schools when you need to create a report that spans more than one schools--say all the elementary schools in one schoold district just to give a possible example.
Phil, thanks for your comments. I went with your first comments and had success. I swapped (Import and Export) the data between the two schools without deleting or adding tables to the Relationships Graph. The various editing, display, printing layouts and controlling scripts in the database seemed to work just fine. I did this manually using the various File -> and Records -> commands without much trouble. I will now set up some scripts to do this more accurately and rapidly.
I do not want to use your second ideas to move all the data into two tables in the database: I NEVER need or want to combine all the 'teacher' data into one table; I do not want to add a 'school' id to every record in the tables; swapping the data does not take long and I don't need to do it very often anyway; I plan a similar application with about a dozen data sets and see no problem with using my new scheme; and, finally, I like the idea of keeping the data separate from the Database.
Hey it's your database and you can design it anyway that works for you, but what you are doing is not what I've recommended and in fact I recommend against that approach. Using one table for each type of data is a much safer, simpler, and more flexible approach.
finally, I like the idea of keeping the data separate from the Database
To keep the data separate from the data base, split your file into two files a front end and a back end. Put the data tables in the back end and put the scripts, layouts etc. in the front end. This works with your approach or mine. The Relationship graph in the front end would use table occurrences that use external data source references to link to the tables in the back end. Starting with two identical copies of a unified FileMaker database, it's fairly simple to convert one copy into the back end and the other into the front end.
If you insist on the import records approach, please note this long standing bug in FileMaker that can really cause problems unless you keep it in mind and design your system to avoid the issue: Script Import Picks Wrong Table
This is one of many acknowledged bugs that can be found in the Known Bug List here in the Report an Issue section of the forum.
It can also be downloaded as a database file from: http://www.4shared.com/file/8orL8apk/FMP_Bugs.html