6 Replies Latest reply on Feb 2, 2016 10:41 AM by paulanevaresw

    Tables and table-links needed for student registrations per semester

    paulanevaresw

      Hello,

       

      I'm using FileMaker Pro 8.5 (I know, it's an old version, but the main elements are there) and I'm trying to make a database that would allow me to register students in different courses, different programs and different semesters.

       

      So I have 2 tables so far:

      A table called "Programs", where I have the different Academic Programs that we offer.

      A table called "Courses", where I have the different Courses that we offer, available to all programs.

      A table called "Students", where I have the list of my students and their info.

       

      The problem I'm having is that I would like to register students to a specific course (or courses), inside a specific program and inside a specific semester, and I don't know how to set my tables (or the link-s between them) to do so.

      So far, I've done single table databases or multi-table databases, but never had to make something this complex.

       

      So I would like my table to allow me to, for example:

       

      Register student Jane Do to the course SPA101 inside her B.A. in Engineering, on the Fall 2015.

      Register the same student, Jane Do, to the course ENG310 inside her English Minor, on the Fall 2015 (let's say this student is doing 2 programs at the same time).

      Register the same student, Jane Do, to the course SPA101 (because she failed it on the Fall) inside her B.A. in Engineering, on the Winter 2016.

       

      Any ideas?

       

      THANK YOU SO MUCH !!!

        • 1. Re: Tables and table-links needed for student registrations per semester
          TomHays

          It sounds like you need a new table such as "Enrollments".

          A new record in the Enrollments table would signify that a specific student (Student_id) enrolled in a specific course (Course_id) to be classified inside a specific program (Program_id) in a specific semester (Semester).

           

          So your Enrollment table as at least these fields:

          Student_id

          Course_id

          Program_id

          Semester

           

          The *_id fields are keyed to the corresponding fields in the other tables.

           

          If you need to, you can add additional fields to the Enrollment table to document the grade the student achieved and other details of that event.

           

          -Tom

          • 2. Re: Tables and table-links needed for student registrations per semester
            paulanevaresw

            Hello TomHays,

             

            Thank you so much for your quick answer !

             

            I have as of now 4 tables:

            - Students

            - Programs

            - Courses

            - Enrollements (registrations)

             

            When you say that "the *_id fields are keyed to the corresponding fields in the other tables", do you mean that I should set them as equals on the table links?

             

            And when I create a new entry on "Enrollements", I assume I would have to use that table fields and then go look for corresponding fields on the other tables, right?

            i.e. I use the Student_Id field of the "Enrollements" table, but the Student_Name field of the "Students" table, that would correspond with the Student_Id.

             

            I have also created a new database on FileMaker using a template that seems similar to what I'm trying to do, but the amount of links on that template (which I use as an example to better understand the software) and all the scripts are driving me crazy =P

             

            I've tried searching for online tutorials that would allow me to better understand how all this works, with no luck. Do you have any recommendations?

             

            Thanks again for all your help !!

            • 3. Re: Tables and table-links needed for student registrations per semester
              TomHays

              That sounds right.

               

              One of the techniques to conquer a complicated relationship diagram is called "Anchor/Buoy".  If your graph gets too messy or you get too lost, look up how to use that technique.

               

              If it were me, I would use Anchor/Buoy at the very beginning since I use it for all of my projects.  Small projects usually turn into big ones over time, so having a system in place to keep things orderly is usually a good idea.

              • 4. Re: Tables and table-links needed for student registrations per semester
                paulanevaresw

                Hello Tom !

                 

                Thank you for all your help !

                I went ahead and read as many introduction tutorials an saw as many videos as I could find about graph modeling and the Anchor/Buoy diagram. Thank you for that info.

                 

                I ended up using one of FIleMaker's preset solutions and modified it all, created new tables and other relationships. It was better than starting from scratch as I was doing because it had some scripts that I could use and wouldn't know how to configurate them otherwise.

                However, some of those scripts now don't work (I'm guessing because of the -needed- changes I did on the tables) and I don't know how to fix it.

                 

                So here's what I have so far:

                 

                4 tables:

                - Contacts (Students)

                - Programmes (Programs)

                - Cours (Courses)

                - Inscriptions (Enrollements)

                 

                The "Contacts" table has the following (important) fields:

                - Matricule de l'étudiant (Student ID, which is unique)

                - Prénom (Student First Name)

                - Nom de famille (Student Last Name)

                 

                The "Programmes" table has the following (important) fields:

                - Code de programme (Program ID, which is unique)

                - Nom du programme (Program Name)

                 

                The "Cours" table has the following (important) fields:

                - Code du cours (Course ID, which is unique)

                - Nom du cours (Course Name)

                 

                The "Inscriptions" table has the following (important) fields:

                - Session (Semester, which is key)

                - Matricule de l'étudiant (Student ID)

                - Code de programme (Program ID)

                - Code du cours (Course ID)

                - And a few fields that go look for info on the other tables with calculations, such as:

                * Nom du programme (Program Name) has the calculation: Code de programme Programmes::Nom du programme (makes a link to the table)

                * Nom du cours (Course Name) has the calculation: ID code du cours Cours::Nom du cours (makes a link to the TO)

                * Prénom (Student First Name) has the calculation: ID matricule du contact Contacts::Prénom (makes a link to the TO)

                 

                Something that may be important: the Nom du programme (Program Name) calculation is made to the table itself because it's the table I added that wasn't already created on the FileMaker preset. The other calculations are made to table occurrences because that's how it worked on the original preset.

                 

                Another important thing: the 3 original tables on the preset (Contacts, Cours and Inscriptions) had:

                - A field named "Un" (as in "one") that equaled 1.

                - A global field (on Contacts, for instants, there is a "Matricule de l'étudiant global" field, as in "Student ID global")

                I duplicated the "Un" field on my Programs table, but not the global field.

                 

                And this is what my links look like (again, they're in French, I'm sorry):

                 

                As for my models, each model looks for the fields on ONE table. On my "Enrollement form" model, I use the Enrollements table for almost all my fields. There's only one field ("Remarques sur l'étudiant", which means "Notes about the student") that I go look for on the Contacts table (Students table) because I want people to be able to correct the Notes about the student from the Enrollement Form itself, so people don't need to go to another model for a quick change.

                I tested that, and it worked. I hope I'm not doing something wrong.

                 

                Ok, so now my problems:

                1) Not sure what the "Un" or "global" fields mean, neither if I'm using them correctly

                2) Not sure if I'm doing something wrong with my "Remarques sur l'étudiant" field on my Enrollement form model (below)

                3) This is the most important part: the scripts are not working.

                 

                About the scripts:

                Before, on the Student Form model, I was able to sign up the student to several courses, by clicking on the "Nouvelle inscription" button (see image below):

                This sent (and still sends) me to another model which shows a list of courses, but USED to allow me to click on the blue arrow (see image below) and sign up that student for a specific course.

                Now, when I click on the arrow, it creates another Course and gives me an error (see image below):

                 

                I have the same problem on my Course Information model. I was able to sign up a Student to that specific course, by clicking on the "Nouvelle inscription" button (see image below):

                This sent (and still sends) me to another model which shows a list of all students, but USED to allow me to click on the blue arrow and sign up a specific student for that specific course.

                Now, when I click on the arrow, it creates another Student and gives me an error (see image below):

                 

                Any ideas of what I'm missing?

                It's so complicated I'm getting really lost

                • 5. Re: Tables and table-links needed for student registrations per semester
                  beverly

                  This is similar to a database I work on. Semester and Year are important as they enrollments are going to be more than one year.

                   

                  And it may be that some courses are only offered different semesters (or terms) and years (Summer courses, perhaps).

                   

                  I cannot translate your error (my French is rusty), so don't see a solution for you. I wonder if it is validation (to prevent duplicates).

                   

                  beverly

                  • 6. Re: Tables and table-links needed for student registrations per semester
                    paulanevaresw

                    Hello Beverly,

                     

                    Thank you for your answer !

                     

                    I chose to have only one text field for the Semester as I complete it with the year as well ("Fall 2015", "Winter 2015" and so on). Hopefully one day I'll be able to configurate it so that it takes the current semester as default

                     

                    In our case, it doesn't really matter if some courses are not available on a specific semester because we just don't sign up the student for it.

                     

                    My problem was that I had to create a Programs table, and I think I'm missing links or TO's on my graph that messed with the scripts that were in place