12 Replies Latest reply on May 21, 2012 6:53 AM by Sorbsbuster

    Replicating Unique Field data from one FMP table to another

    MichaelWeisenberg

      Title

      Replicating Unique Field data from one FMP table to another

      Post

      HI

      I am a newbie attempting to develop an educational database in FMP 12 to use as a college gradebook. To link my tables, I am using a unique student ID field (issued by the colllege so it cannot be serialized) as my primary key. Since I have entered this unique Student ID into one of my tables, which is linked to other tables using this field, to save time, I am wondering whether FMP offers an easy way to replicate this student ID data from one table to another. Apparently, I may not be able to use the lookup table to auto enter this data as the unique Student ID is issued by the college and is not serialized. I come to FMP having previously used Microsoft Access where I was used to using an append query to accomplish my goal. I am unsure how to do this in FMP or even if it is possible to do so. I would appreciate any feedbak from others who may have done this before me. Thanks.

        • 1. Re: Replicating Unique Field data from one FMP table to another
          Sorbsbuster

          "Apparently, I may not be able to use the lookup table to auto enter this data as the unique Student ID is issued by the college and is not serialized" - not true, you can use it as a link in relationships.

          But I would seriously consider leaving the StudentID as it is, and creating your system's own serialised ID which you can have control of and guarantee is unique and is formatted and used the way you want.  Only your system need know it exists.

          "I am wondering whether FMP offers an easy way to replicate this student ID data from one table to another" - there are two ways to do it.  Both need you to create a relationship between the two tables, linked by (either of) the StudentIDs.  In a layout based on the first table you can, clearly, diaplay all the fields from the first table.  But you can also display any field from the second table, by dragging a filed on to it and sourcing it via the linking relationship.  It will display the 'live' data from the first linked record.  If you change the data in that field in the second table you will see it change immediately in the first table's layout.

          The second way is to capture the second table's data at the point of record creation.  It is called a 'lookup', and you can define any field to auto-populate with looked-up data.  You set that in the auto-enter data options in the field's definition.  Use the same relationship to copy information across from table 2 to table 1 - but note that it will not update then if you change the data in table 2.

          There are times that the first is the better option, and times when the second is what is required.

          • 2. Re: Replicating Unique Field data from one FMP table to another
            GuyStevens

            Hey Michael, there was another post with another question that had some similarities to your. I posted a little example file there that might be usefull:

            http://forums.filemaker.com/posts/3e06066d61?start=1&stop=10#195347

            If I where you I would also use a Number field with an Auto enter serial number and use that as the basis of your relationships.

            You can then just add a "Serial Number" field to your table and store the students serial number from school there.

            That number can then always be used but you don't need to base your relationships on it.

            As soon as you link a record on any table with a certain student you can get that students serial number on the other table. Nobody ever needs to see the Filemaker generated serial numbers.

            (but anyway, that's what Sorbsbuster already said)  :)
            P.S. Listen to him, he's really clever :)

            • 3. Re: Replicating Unique Field data from one FMP table to another
              MichaelWeisenberg

              Thanks for your reply Sorbsbuster. You come highly recommended through the posting by DaSaint!

              If I understand what you are saying, I believe your second solution would work for me...

              I would add an  auto generating number field to both table #1 & table #2 and make that the basis for my relationship. I would leave the unique serial number field in both tables alone and not make that the basis of my relationship. I would then use the Auto Enter Lookup function for my field in Table 2 to populate matched data from the unique serial number field in Table #1. Alternatively, I also already have a Class ID field common to Table 1 & 2 (which is also unique). I can also use that as the basis for my relationship between Tables & then still use the Auto Enter Lookup function in Table 2 to populate matched data from my unique serial number field in Table 1. I am also assuming that, even though it is not the best example of database design it is OK to use duplicated fileds in more than one table if it is for the purpose of auto populating fields.

              I would appreciate if you could confirm I am on the right track with the solution I propose above based upon your kind advice. Thanks again for your help!

              • 4. Re: Replicating Unique Field data from one FMP table to another
                Sorbsbuster

                (Oh, maybe I can just fool some of the people some of the time...)

                "I would add an auto generating number field to both table #1 & table #2 and make that the basis for my relationship".  I can't tell if that is right because you haven't told me what is in the two tables.  But I would doubt very much if this is what you want to do.  Yes: always create a unique serial number to track each record in every table.  But presumably you want that unique serial number to be the StudentID in the first table, and something else (like a ClassID) in the second table?  The second table will potentially have many instances where the field StudentID will have the same StudentID number in it.

                I don't think you are on the right track, but you need to tell us what you are trying to do with the two tables.  Is one a list of Student data, and the other a list of Classes?  Do you want to see a record for one Class, and a list of all the students in that class, with their details (name, address DOB etc) able to be displayed in the list for that class?

                If that is the case you would want only these fields in each:

                Student Table
                StudentID
                Name
                Address
                DOB
                ClassID

                Class Table
                ClassID
                ClassName
                ClassYear
                ClassTeacher (for example...)

                If have a relationship:

                StudentTable::ClassID = ClassTable::ClassID

                ...then in the Student Table you would be able to drop the ClassName, ClassYear, and ClassTeacher field on to the layout and show the details of the class that that student is attending.  Note that you do NOT have the same Class information repeated in the Student Table - the teacher's name only occurs once in the database.  That means that when the teacher changes halfway through the year you will only have to change the teacher name once (on the Class Record) and all student's records for that class will immediately show the new teacher name.

                But note that the student record only had space for one ClassID.  That is very limiting, and not very practical, as most students take more than one class.  Also, it ignores the obvious possibility that you would want to look at a Class Record and see all the students enrolled for it.

                You have here a common requirement for a Join Table - you need it when you have many-to-many relationships.  Here a class has many students, and a student can be in many classes.  So you need to have a third table with two fields in each record:

                StudentClasses Table
                StudentID
                ClassID

                You create one record for each student in each class.  If 10 students are each doing 5 classes you will have 50 records.

                Now your relationships will look like:

                StudentTable::StudentID = StudentClassTable::StudentID

                and

                StudentClassTable::ClassID = ClassTable::ClassID

                In diagram form:

                Students -----<StudentClasses >---------- Classes

                From the Students Table you can list all the classes they are in.  From the Classes table you can list all the students in each class, and show their details.  No information is entered or stored twice; that is good database practice.

                This is a very common project - search for students, enrollment, join table, classes on the forum and I'm sure you will even find some example files (maybe even a recent one from DaSaint.)



                • 5. Re: Replicating Unique Field data from one FMP table to another
                  MichaelWeisenberg

                  Hi Sorsbuster,

                   

                  I truly appreciate all your assistance. To be more specific as to what I am trying to accomplish...

                  I am in process of building a college gradebook database designed to capture student & class identification, absenteeism record by class date, student grades in each major assignment and, ultimately, student final grade. Currently, my database consists of 4 tables; Table 1 = Student and Class Identification and includes (among others) a unique (but not serial) data field for Student ID and another unique field for Class ID. I include the Class ID field so I can use the database for tracking multiple classes; Table 2 = Student Absenteeism and Participation credit and includes (among others) a similar unique Student ID field as Table 1 and a class date field used to track absenteeism and participation credit for each class; Table 3 = Student Final Grade computation and includes (among others) a similar Student ID field as Table 1 +  field for the grade for each major assignment (Midterm, etc.) and a calculated field for showing Final Grade for each Student; Finally, Table 4= Total Class Grading Criterion which includes  (among others) a similar Class ID field as Table 1 and fields for the Grading Criterion components for each major assignment...Table 4 consists of only 1 record and is used as part of the calculated Student Final Grade computation in Table 3. Currently, Tables 1, 2 and 3 are related by Student ID and Tables 1 and 4 are related by Class ID. Since I have already entered the unique Student ID's for each of my students in Table 1, my goal is to use the same data to populate the similar fields in Tables 2 & 3. 

                  I hope this detail helps. I am just trying to find an easier way to populate the unique Student ID fields in my Tables 2 & 3 with the equivalent data already entered into Table 1 without having to do manual data entry. When I (hopefully) reuse this database for another Class ID, this will, of course, be a recurring problem. I do not have the same problem in replicating Class ID because that data stays the same for many students in each class and because I can more easily use the last field auto data entry option in defining my Class ID field. I would appreciate any further insight you my have for what I am trying to accomplish. Thanks.

                  • 6. Re: Replicating Unique Field data from one FMP table to another
                    Sorbsbuster

                    The way you describe Table 1 suggests that you will not be able to track multiple classes.  You need a separate Join Table.  Your Table 3 should hold a separate record for each exam, I would suggest, and the results of averages or final grades calculated back in the Stucdent Table (drawing on linked results from the Table 3.)  You can do all the calculations of final grade and averages etc in the exams table, but it is more logical to have student data in the student table and exam data in the exam table.  (Each exam has one result.  Each student has one final grade.)

                    "my goal is to use the same data to populate the similar fields in Tables 2 & 3. " In the classic set-ups people have posted in the forum you will see that the data is almost never replicated, so if you have the same filed names in two tables then you should use that as a stimulus to re-consider your design.  One of the few places where genuinely copying exactly the same data is necessary is in creating invoice lines.  At the point of creation you want the invoice line to populate with the current price from the price list.  But when you update the price in the price list you absolutely do not want every invoice since the year dot changing its price as well.  In your case when you spot that a teacher's name was spelled wrongly, or a student gets married and chnages their name, you do not want to have to chase round your tables looking to find all the records where the original data was copied.

                     

                    David describes the required set up here:

                    http://forums.filemaker.com/posts/6685148e66

                    Useful links here:

                    http://forums.filemaker.com/posts/2b0727ae9e

                    • 7. Re: Replicating Unique Field data from one FMP table to another
                      MichaelWeisenberg

                      Sorbsbuster,

                       

                      Thanks for the advice! I will certainly take into consideration in redesigning my database.

                      • 8. Re: Replicating Unique Field data from one FMP table to another
                        MichaelWeisenberg

                        Sorsbuster,

                        Thanks again for your kind advice. I now understand the concept of using a join table for my application. Please note that, in my case, as I am an adjunct college instructor, I usually only teach one class at a time, sometimes two, but would want to use the same database to track classes I may have taught in the past. Two further questions though:

                        *From your description, the combination of my tables 3 & 4 become my exam table (linked by class as the exam criterion used in my calculations apply uniquely to each class) and my tables 1 & 2 are my student tables (linked by student as I need to track attendance & participation for each student in my table 2). Would I then place my join table between my tables 1,2 and 3,4 with students related to table 1 and class related to table 3?

                        *Since I have already populated part of 1 class of data in my database (1 class for tables 1,3 & 4 and 1 class date for 1 class in table 2); if I add the join table retrospectively you recommend, will I lose all the data I have already entered in existing tables? If so, is there any way to preserve the already entered data after adding the join table? 

                        Thanks again for your continuing assistance.

                        • 9. Re: Replicating Unique Field data from one FMP table to another
                          Sorbsbuster

                          "I usually only teach one class at a time, sometimes two, but would want to use the same database to track classes I may have taught in the past. - Filemaker is so useful that rarely does anyone start a database 'only wanting to use it for this' and not expand it hugely because they 'now realise they can use it for that and that too'.  So far there is no requirement that is removed or reduced because you only teach one class.

                          In your join table you may consider expanding the fields to include a Year, so you can see what students are in what class, for a certain year.  Note that for next year then you don;t have to re-create the existing student records - just add more students as they arrive.

                          I am honestly unclear about your existing structure.  You need a table of Students (which sounds like your table 1) and a join table between it and the Calss Table; I am unclear where you have the Class Table.  It should just have it in one record for one class, whether that be 'Spanish Level 2' or 'Spanish Level 2 Morning Session'.

                          You need a join table when you have a many-to-many relationship.  presumably your atendance table will have one record for each attendance by each student (whether you record every attendance or only record absences), so each student will have many attendance records, but each attendance record only relates to one student, so you don't need a join table there.

                          I don't really understand what the purpose of your single-record table 4 is.  I think you are going to use it to pull together some calculations from the other tables (and that is a common technique) but unless I know what those calculations will be I can't say if it sounds right.  But if there is only one record in it then your design, by definition, will not need a join table connected to Table 4.

                          Students, Classes, exams, attendance... they are one of the common solutions enquired about on the forum.  If you search for those terms you will find examples of good practice already discussed - davidanders provided links to some resources, including, I think, some sample files.

                          • 10. Re: Replicating Unique Field data from one FMP table to another
                            MichaelWeisenberg

                            Sorsbuster,

                            I am new using Filemaker Pro and I really appreciate your help in getting me started. Since you are unclear about my current structure, I am attaching a PDF of my existing table relationships. Please note that my PDF attachment does not reflect the JOIN table you recommend as it has not yet been added. 

                            My thought was to place your recommended JOIN table between my Student_Class identification table (which you are calling my STUDENT table) and my Student Final Grade Computation Table (Which you are calling my EXAM table). Student ID from my JOIN table would relate to my Student_Class Identification Table and Class Identification from my JOIN table would be added to and relate to my Student Final Grade Computation Table. Please note the Student Final Grade Computation field in this table is a calculated field and makes use (in part) of the data found in my Total Class Grading Criterion table (I would change the relationship of this table so it would point to the newly added Class Identification Field I would add to my Student Final Grade Computation Table as mentioned above). I would remove the Class Identification Field from my Student_Class Identification Table and only have that table relate to the Join table by Student ID. As stated above, the Join table would relate to the Student Final Grade Computation table by the newly added Class Identification Field. The Student Attendance_Class Participation table would also relate to the join table by Student ID. In that table, the Total Class Participation Score field would be a summary field totaling the Student Class Participation Score by Date field sorted by Student ID. The Total Class Participation Score Field (by Student) is used in the calculation of the Student Final Grade Computation referenced above.  

                            Please let me know of any comments you may have for my proposed structure. Thanks again.

                            • 11. Re: Replicating Unique Field data from one FMP table to another
                              MichaelWeisenberg

                              Sorsbuster,

                              I apologize. Being new to this forum, I inadvertently attached a PDF rather than JPG file of my database structure which was not uploaded. Please find the correct attachment of my table relationships to supplement my previous posting. Thanks.

                              • 12. Re: Replicating Unique Field data from one FMP table to another
                                Sorbsbuster

                                Michael,

                                You can't attach pdf to the posts (even though it may let you seem as if you are attaching something).  You need to take a screenshot as a jpeg, png, or gif, or you can upload the pdf (or a clone of the file, even) to dropbox, or a file-sharing website.

                                Have you read any of the links that davidanders posted, for example?  In terms of the existing help on the forum the wheel of students, classes, and enrolments has been invented quite a few times, so there are oodles of examples there.