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:
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.
Thank you so much for your quick answer !
I have as of now 4 tables:
- 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 !!
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.
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:
- 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
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).
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