12 Replies Latest reply on Jul 13, 2012 8:51 AM by petertj

    How do I create a school timetable in Filemaker 11?

    andrewmcc

      I want to create a database to track the absences of teaching staff and allocate colleagues to cover their classes when they are off. As part of this I want to include the master timetable in the database. I am having difficulty with this. I don't know how to set up the classes so they they repeat, say every Tuesday at 11.00am from now until 31 May 2013. Can someone help me?

       

      I am also planning the following tables in my database. I am looking for advice on whether this is sensible. Any advice gratefully received. I am new to this. I am working my way through the tutorials but I hope someone here can help!

       

       

      Table 1 = Staff

      __pkStaffID

      Title

      Surname

      Forename

      PostName

      Department

      ContractType

      NonContactAllocation

      NonContactActual

       

      Table 2 = Attendance/Absence

      __pkAbsenceID

      StartDate

      EndDate

      CategoryofAbsence

      Times

      Reason

       

      Table 3 = Classes

      __pkClassID

      _fkStaffID

      ClassName

      Department

      Room

      MeetingTimes

       

      Any advice gratefully received.

      Thanks

      Andrew

        • 1. Re: How do I create a school timetable in Filemaker 11?
          Claw'd

          There are quite a number of relationships between these groups of data.

           

          One teacher can teach many subjects

          One subject can be taught by many teachers

          Each class has only one teacher

          One teacher can teach many classes

          One class may have several hours (in a week)

          At one particular day and hour, there may be several classes

          A class is about one subject

          One subject may be taught in many classes

          One teacher can have many absences

          One absence can affect many classes

           

          So a many to many between Teachers and Subjects

          A one to many between Teachers and Classes

          A many to many between classes and class hours

          And a join back between Subjects and Classes

           

          Each many to many join has a joining Table with two foreign keys.

           

          So Classes would join to teachers TeacherID to TeacherId with the Primary key being in the Teachers File.

           

          Teachers would join to the Joining file (Teachers_Subjects) on the teachers side by TeacherId and on the Teachers_Subjects table also

           

          Teachers_Subjects and Subjects table would also have a SubjectId to link each other

           

          Same thing with Classes, there would need to be a joining table called say ClassHours_Classes linked together by primary and foreign keys.

           

          Then an absence table for the teachers this could be a many to many in between Teachers and Classes containing dates teachers are absent the link could be the date in classes and the date the teacher is absent.

           

          First shot.

           

          As far as populating the repeating events this could be scripted

           

          Not an easy first project:)

          1 of 1 people found this helpful
          • 2. Re: How do I create a school timetable in Filemaker 11?
            andrewmcc

            Hi, many thanks for this. I am going to make a start on this. Will no doubt encounter lots of difficulties along the way but thank you very much for your advice. Much appreciated and very helpful.

             

            Andrew

            • 3. Re: How do I create a school timetable in Filemaker 11?
              Claw'd

              Glad it was useful, it is often visually easier if you draw out the relationships as an ERD (entity relationship diagram) before you start, joining up all the tables and getting your head round how the data interacts.

              Good luck with the project:)

              • 4. Re: How do I create a school timetable in Filemaker 11?
                rob.speakman

                Hi Andrew,

                 

                user5178 last comment is an excellent idea.

                 

                The best way to start building even the simplest database is to draw a diagram. Although rather than building an ERD diagram first which can be a little challenging for someone not working as a developer I would suggest drawing an IA (Information Architecture) diagram. This is a simple diagram that allow you to visualise the interfaces (Layouts) you need and how they link to the data. I have attached an example of one to help you get started (It is was done quite quickly so please excuse the likely poor grammar).

                 

                School IA.png

                The is a little to small to read so I have put a full size copy up here http://weknowdata.net/school_ia.png

                 

                 

                Once you have refined that, you can then more easily work out how many tables and the structure you will need, in the diagram attached i would say there are roughly;

                 

                4 Main Table

                - Interface (to run the dashboard)

                - Teacher

                - Class

                - Subject

                 

                4 Join Table (These contain little data/fields, they mainly contain secondary keys to make links between table)

                - Absences (Contains keys and date from, date to and may a reason)

                - Lessons (Contains keys and a link between the teacher and lesson)

                - TeacherToClass (Contains keys and a link between the teacher and class)

                - TeacherToSubject (Contains keys and a link between the teacher and subject)

                 

                You would probably need a couple more join tables but I hope this helps to get you started.

                 

                Finally try to define small tasks within the project. By making a list of tasks the project will seem much less overwhelming. For example you can allocate yourself a task per day to fit in around your day job.

                 

                Good luck, you'll be a Filemaker Guru in no time!

                 

                Best,

                 

                Rob Speakman

                We Know Data

                1 of 1 people found this helpful
                • 5. Re: How do I create a school timetable in Filemaker 11?
                  andrewmcc

                  Many thanks Rob,

                  I have been overwhelmed by the responses from you and user5178. You have both taken time to help me understand the task I am trying to accomplish more clearly. I have no doubt I will have questions along the way but you have given me a more structured starting point.

                   

                  Thanks again,

                  Andrew

                  • 6. Re: How do I create a school timetable in Filemaker 11?
                    andrewmcc

                    Hi folks,

                    I have been thinking about my plans for using filemaker to manage teacher absences and allocation of class cover.  I have attached a file which outlines what I want to do.  I would be very grateful for any advice on what to do next.  I am struggling with this to be honest and might need to call in someone who knows what they're doing to help me out.  If anyone has any suggestions for how I could proceed, please let me know.

                     

                    Thanks

                    Andrew

                    • 7. Re: How do I create a school timetable in Filemaker 11?
                      BruceRobertson

                      Can't read it. What's an XPS file?

                      • 8. Re: How do I create a school timetable in Filemaker 11?
                        Claw'd

                        XPS is a Microsoft specific document format based on XML AFAIK, andrewmcc can you convert to PDF so that us ordinary mortals can have a look? I'm editing this from Linux Ubuntu and I'm pretty sure lots of others use MACS so a universal format is always best.

                        • 9. Re: How do I create a school timetable in Filemaker 11?
                          andrewmcc

                          Sorry!  Here is the file as a PDF.  Hope it makes SOME sense.  I know I have missed some big relationship links but this should at least give you an idea of what I want to achieve with it.

                           

                          Thanks again for looking at it.

                          Andrew

                          • 10. Re: How do I create a school timetable in Filemaker 11?
                            petertj

                            http://www.petertj.com/Staff_Absence_Management_plans_for_filemaker.pdf

                             

                            This is the aforementioned document in PDF.

                             

                            Forgive me for stating the obvious but isn't this project gasping for an experienced developer?

                             

                             

                            Peter

                            • 11. Re: How do I create a school timetable in Filemaker 11?
                              Claw'd

                              There is always the possibility of re-inventing the wheel so before you get too deep I would suggest having a look at what these guys are doing: http://www.cjmsoftware.co.uk/index.html I have no connection bu they appear to be in the same area.

                              • 12. Re: How do I create a school timetable in Filemaker 11?
                                petertj

                                 

                                Preamble

                                The project is aplty named since, in order to create an absentee record, it is necessary to have an effective table of arrangements made for teachers, classes and rooms aka A Timetable.

                                 

                                The Entities

                                 

                                teachers

                                classes - teachers teach (a class is a collection of pupils with an allocated name)

                                subjects - each teacher can teach specific classes/material

                                rooms - in which the teaching takes place

                                period - in this case there are 6 peiods per day

                                absence is created when a teacher is no longer able to teach an allocated class

                                 

                                The relationships between these entities are broadly :

                                 

                                teacher:subjects  1 to many

                                teacher:room 1 to many (time conditional)

                                teacher:period 1 to many

                                teacher:absence 1 to many

                                teacher: class 1 to many

                                 

                                If you create a hand drawn ERD showing these relationships then you can run through the various connections to see if the dependencies make sense.  When they do make sense then you can start creating tables and fields within the Graphical relationship Graph in FMP.

                                 

                                I'm UK based and I'd be happy to help and can be reached at fmpwhizz-peter@usa.net.  .

                                 

                                Given Information

                                Not unusually, some information necessary to make the system somewhat easier to develop has not been provided. 

                                 

                                The naming standard for a class of pupils is not clear in that Period 1 can have three classes with names such as 3M Geo, 2B3 Eng and 5E2 Art which does not identify the specific classroom needs but these names may contain information about streaming or the 'year' that the children who make up the class belong to.

                                 

                                Furthermore, the categories P, L, S and I are not explained. It would be most useful to be able to reproduce at least a week of allocated classes with classrooms, teachers, class names, subjects and period.  A sanctioned absence request by a teacher would then produce a vacancy which would need to  be filled from the remaining teacher 'float'.  Each teacher in the float would need to be flagged as having the capacity to teach the vacant class subject and level. All teachers would start off by being in the 'float' until they were allocated a 'slot'.  And so on....