5 Replies Latest reply on Dec 6, 2010 3:41 PM by philmodjunk

    How to separate my data from a database which holds print layouts, scripts, data entry, etc.

    HowardRathbun

      Title

      How to separate my data from a database which holds print layouts, scripts, data entry, etc.

      Post

      My first database in FileMaker includes three tables (containing only data) and a bunch of layouts for data entry, data editing, print layouts, data display layouts and dozens of scripts for analysing the data and operating the layouts.  It works quite well but it is still a work in process.

      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.

      My problem is how to switch from one data set to another.  I tried adding Table Occurances from another file using File -> Manage -> Database -> Relationships Tab and selected a file where the table occured.  This table occurance (TO) appeared on the relations graph with the table title in italics and shows a list of the fields in that table.  So far so good. (It would be a big help if the icons at the bottom of this layout where titled or named when the cursor moved over them)

      I then looked at the Tables tab and discovered that the 3 TOs where not listed.  If I tried to add a TO to the list of tables, I discovered that a new table, TO 2, was displayed in the relationship graph but had nothing to do with the first TO I added.  I then went to Manage -> Layouts and was able to define layouts for each of the 3 TOs.  However, when I went to each of the 3 layouts, the display showed multiple records but the field names were missing and all the data was marked as missing.  Even so the record counter displayed the correct number of records for each of these tables.  In addition, none of the scripts would work because any references to these three TOs just said "missing table" or "missing field".

      I think I should use TOs for data separation but am not coming close to making it work.  I did this in my prior DB system (MS Access) using Attach and Detach which worked very simply so I hope I can do the same thing here.

        • 1. Re: How to separate my data from a database which holds print layouts, scripts, data entry, etc.
          philmodjunk

          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"?

          • 2. Re: How to separate my data from a database which holds print layouts, scripts, data entry, etc.
            HowardRathbun

            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.

            • 3. Re: How to separate my data from a database which holds print layouts, scripts, data entry, etc.
              philmodjunk

              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:

              1. No need move data back and forth with imports just to see the records for a given school.
              2. No need to add more tables everytime you add a school
              3. 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.
              • 4. Re: How to separate my data from a database which holds print layouts, scripts, data entry, etc.
                HowardRathbun

                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.

                Thanks again. 

                Howard

                • 5. Re: How to separate my data from a database which holds print layouts, scripts, data entry, etc.
                  philmodjunk

                  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