Ideally, you should have more than two tables here:
Students contains one record for each student and should include a student ID field for linking to other tables.
Classes contains one record for each class that is taught with a class ID serial number.
Sessions contains one record for each time/date a class is taught (Biology is taught during periods 1 and 2 and thus has two enrollment records listing the classID number for Biology.) A field in sessions records the Faculty ID of its instructor.
Enrollment contains one record linking a given student to a given Session. Schedule/date data unique to that session would be recorded here
Faculty contains one record for each faculty member with a facultyID
Students---<Enrollment>----Sessions ---<Classes (----< means "one to many")
With this structure, a layout based on Sessions can list the assigned faculty member and use a portal to enrollment to list the enrolled students for that class session.
Note: Enrollment is a "join" table enabling you to link many student records to many different class sessions. If you might have two or more faculty team teaching a session, you may need either another join table, or might use Enrollment to list both Faculty and students for a given Session.
Thanks for that PhilModJunk.
I did manage to get this working by linking the correct fields - D'OH. There are lots of questions as a result of that and having trawled through the various resources on offer I was wondering whether you had any experience with the FileMaker Training Series book and DVD. I have several other books already but there is always something missing.
I have a copy sitting on my desk. And yes there is always something missing. I'm still scanning through it myself. (90% is old news for me, so the challenge is to pick out occaisonnal tidbits without missing anything and without having to read every single word)
Would you recommend it as a resource. I already have in my possession The Missing Manual for Filemaker Pro 8 & 11 and I have found them useful although the latter has lost clarity.
For the chapters I've skimmed through, it seems pretty decent. I argue with a few "best practice" statements, but only mildly. I can't really compare it to other publications, as I don't have any other FileMaker publications to compare it to as I'm a "self taught" FileMaker developer who started out with a degree in computer science and has since used FileMaker for many years.
I'm going to work through the lynda.com tutorials, even though I am well & truly over hearing about databases involving invoices & customers ;) Unfortunately the template that Filemaker provide for administering aspects of school life (MyClassPro - translating from the German) is far, far too complicated for me to get my head around at the present let alone adapt to my much simpler needs.
Ok, I've gotten my brain around how to deal with the above mentioned "problems" & have got it working, so I'm on to the next challenge. If I have a table that contains the names of all my teachers & some other details (but each in their own record), how do I create a portal that shows ALL the teachers' names in that portal i.e. as a list of names? The idea here is that with that portal I can click on a teacher's name (from that list in the portal) & that will take me to that teacher's details.
Create a link to the TeachersTable using the 'x' relationship type (not the '=' you are probably used to using). Or to achieve the same thing you can join it using a very useful Ultility Field in the tables - cConstant1, set to be a calculation =1. Every record will have that value, and so every record in the table will display in the portal every teacher record.
Could I suggest a slight modification (in principle):
Create in each table you have a similar calculation, but call it 'cConstantYes'. (I always create a 'cConstantNo' and a few other 'utility fields' as well.) Then in the Teachers' Table have a field 'TeacherActiveYseNo' and have it as a radio button field with the values auto set to 'Yes', but 'Yes' and 'No' as the options.
Then make your relationship SourceTable:cConstantYes::TeacherTable:TeacherActiveYesNo and draw a portal using that relationship.
That way as teachers leave you do not have to delete their record, but they will not constantly appear on your pop-up after they leave.
Many thanks, Sorbsbuster. I did stumble across that cConstant = 1 trick by trying out a number of things I had seen in the MeineKlasse Pro download from the Filemaker (German) Education site. Your suggested modification sounds really good & I will try it out tomorrow.
I have managed to get a lot further in creating this database (since my last post) but am still struggling with displaying classlists of all the classes a teacher teaches (I can only display one at a time via a portal). The assumption that most people, that I have asked, make is the one about a many to many relationship between classes & students. I'm only doing this for the Science department so a student is in only one Science class. Also my student table has which class they are in already embedded.
The other thing I want is a roll for each student for say a term. I am currently using Excel, where a column is a day & each student is assigned a row. I like the overview this gives me as I can see an entire term broken up into "weeks" (5 columns followed by a different coloured column representing the weekend. Haven't tackled that one yet.
They may all be Science Classes, but you still have 3 concepts: Students, Teachers, and Classes. Even if the Classes are Year 1, Year, 2... or 'Beginners', 'Improvers', 'Advanced', etc.
You would then (as already suggested) have a join table for the Teachers to be related to it, and it would show all the Science Classes that Teacher takes.
I assume you have only tables for Teachers and Students because you will guarantee that each Student only has one class. Therefore when you link Teachers to Students and draw the portal with the Class Field in it you will get the same Class repeated for as many students as are in that class. There are awkward tricks around that, but to be honest to make your solution robust and scaleable I think you would be better to create the join table, as 'received wisdom' suggest. With only one class per student it will be easy to populate. I don;t quite understand how you are getting only one class shown in the portal.
But if all you want to see is a list of the classes a Teachers takes, you can display it with a script that 'Goes To Related Records' from any Teacher's Record (select 'Related to current record only') and displays the resulting Students on a list which the script then sorts by Class. Make the layout show the Class Name in a Sub-summary part When Sorted By Class, and remove the Body part from the layout. This will show you one row for each class the teacher takes.
Thanks again, I really appreciate your advice. I'm new to Filemaker (obviously ;) ) & I'm working from 4 books on Filemaker Pro & 2 online video Tutorials trying to translate the usual Invoice/Customer/Products examples to my situation.
I have 2 tables, a teacher table (First & last names, class code) and a student table (First & last names, gender & a class code e.g. 7A because they are in Year 7 & the first of 6 classes A-E). So the students are already assigned to a class due to where I got this data from (A whole school database called First Class from Human Edge).
To link students to a teacher I created a relationship between the two tables (student & teacher) via the Class field. I then created a Portal in the teacher's table that links back to the students details. I then type a class code into the teacher's class field & the portal lists all the students in that class (Feels like magic to a newby like myself). This was great when I first got it to work but then I was stuck with having to type in a class code each time I wanted to call up a different class list.
I take your point about scalability although I will be the only one using my solution & it will be restricted to just Science classes. My issue with creating another table (or layout) that combines the students & the class they are in is that it seems like double-handling.
Thank you for your help so far & if this is all too much trouble (because I'm just too dense to get it ;) ) feel free to just ignore it.
I think you have not quite cleared in your mind the distinctly different tasks you are trying to achieve. For example, you said earlier that you want to see all the 'classes that a teacher takes', but then you say that when you type a class in the portal shows you all the 'Student's they teach' and you liked that. Maybe I am being too pedantic to the point of counfusion, but I would use the terms (and note I'm not in education) to mean the Teacher is Professor Brown, the Classes that he teaches are 4A, 6C, and 7D, and in Class 4A there are 12 Students, John, Sarah, Jim...blah blah.
So the various portals or alternative layouts (if you insist on not using a join table) that you could have are, for example:
Professor Brown 4A
Professor Brown Alan Brown 7D
Billy Black 4A
Carla Doe 6C
The way you have the tables and relationships set now you will be able to display the first listing I've shown, but the records it finds that match the relationship are 1 for every student, so you would see '4A' listed 12 times, one for every student in the class. In fact, the portal is really exactly the same as portal 2 I've listed, as it is the only relationship you have available.
Surely the join table you need only needs two fields (TeacherID and ClassID), and only one record for each Class, so it would be very easy to set up. By 'scaleabililty' I didn't just mean going from just you using it to all the staff using it, I also meant even going from one subject to 2, from a student being in one class to being able to be in 2 (say you start an after-school science club, so that guy in 4A can also attend the ASSC class.) A simple change now will open up all sorts of possibilities.
I think you have missed the wisdom that is in PhilModJunk's suggested structure, but it is the best way to do it. Would you feel happier trying this? (It is simple enough that I think you could try it as a little scratch file):
Teachers:TeacherID :: TeacherClass:TeacherID
Students:ClassID :: TeacherClass:ClassID
Now enter some data in the 3 tables, then on the Teacher Layout draw a portal using the relationship to the Students Table. Include in it the Student's name. You will see that it lists all of the Students of that teacher.
Still on the Teacher's layout draw a similar portal using the TeacherClass relationship, and include in it the field with ClassID. You will see there the much shorter list of all the classes the teacher takes.
This is a very simplifed version of the principle PhilModJunk was suggesting. I think you'll appreciate how flexible it is if you try this - his suggestion will be able to cope with all sorts of combinations of teachers, classes, students, and multiples of any.
Once again thank you for persevering! And you are absolutely right despite having spent weeks on this now it still hasn't clicked. I must be getting old. Physics at Honours level seemed easier ;)
I'm sure that PhilModJunk's suggestions (& wisdom) are spot on but between being new to relational databases & the jargon of sessions, enrolments, etc. I got lost very quickly. This is not a criticism of him it is just a reflection of how confused I am at this early stage. I wanted to start small & simple so I could get my brain around this whole thing. Hence your suggestion for the scratch file was exactly what I needed. It worked like a charm, thanks!
It seems that everybody out there (writing books & creating video tutorials) is convinced that the best way to teach this stuff is through this Customer/Products/Invoice example. I couldn't agree less.
Ultimately I want 2 things:
1. As the Head of Science I want to be able to click on a teacher's (Science) name & be presented with what classes (Science) they teach. Another click on a class code takes me to a list of students in that class. Done.
2. For myself I want to be able to do the same but then for each student I would like to be able to mark the roll & add notes about say incomplete homework for a particular day. At the present I mark the roll in DocumentsToGo(Excel) on my iPad but cannot add any notes & have to stuff around with iTunes.
Couple of questions then:
1. Is a portal just a join table?
2. I'm not a conscientious objector to join tables ;) I just thought they were reserved for many-to-many relationships.
3. I presume that classID should be an auto-generated number rather than a specialist code like 7A & 10C (for classes 7A & 10C).
4. What do I do for the next year when all the students move up a level. Do I have a 2011 file & the a 2012 file? Because 7A in 2011 will have different students in it than 7A in 2012.
By the way, are you a fellow cyclist? I saw your photo & comment about the St. Johann in Tirol (Sorry, the German in me could not resist) & your comment about getting on your bike & riding up it.
One other thing.
I import the student table in from an Excel file which contains not only the students' names but also which class they are in i.e. a code like 7A, 10C, etc..
The portal for the student information in the teacher table/layout remains blank because I have not entered a classID for each student & I would rather not at over 800 students. How can I use the existing class code (7A, 10C, etc) to save myself a lot of work (& mistakes).
I've gone and deleted the Students:ClassID :: TeacherClass:ClassID relationship & replaced it with a Students:ClassCode :: TeacherClass:ClassCode. This works "well" in that I now get a list of all the students taught by that teacher (I would like them in individual classes but that can wait). Can you see problems with this approach?