1 2 Previous Next 20 Replies Latest reply on Dec 9, 2013 9:33 AM by philmodjunk

    Timetable puzzle, looking for ideas

    appt

      Title

      Timetable puzzle, looking for ideas

      Post

           With the number of students in my school bringing iPads increasing daily I am looking at designing a database to replace the paper diary that they get issued with. 

           First on the list would be a timetable layout.

           But, our timetable works on a 6 day rotation. The first day of term 1 might be a Wednesday, for the timetable it would be Day 1. The following Wednesday would therefore be day 6 with Thursday then being Day 1. 

           I can see how to create a simple timetable so that students know what they have on each day but I'm struggling to see how I can get the system to look at a date and state which 'Day' it is without creating a record for every school day in the whole year. 

           I know it can be done as the school management system brings up your timetable/classes each day on my laptop. 

            

           Thoughts?

        • 1. Re: Timetable puzzle, looking for ideas
          philmodjunk

               but I'm struggling to see how I can get the system to look at a date and state which 'Day' it is without creating a record for every school day in the whole year.

               But why not create a record for each day of the school year? Such is frequently done for "calendar" type layouts and can be automated via script.

               You might find this calendar demo file of interest: https://www.dropbox.com/s/e8d03xvwe8vtz85/Calendar.fp7

               I can be opened from FileMaker 12's File menu to produce a copy converted to the .fmp12 file format.

          • 2. Re: Timetable puzzle, looking for ideas
            appt

                 Thanks Phil.

                 I have looked at calendar files before but I'm not sure they will quite solve my problem since I don't really want a calendar style view. I can easily set up a record for each day of the school year but how do I automatically populate that with the specific "Day' since it has also to take account of weekends, holidays and stuff like training days which may shunt the 'Days' backwards or forwards. It is not that simple case of 'if it is Monday period 2 then it is maths' since Monday period 2 will change each week.

                 I will do some work on it this morning and post a screen shot of what I want to achieve.

            • 3. Re: Timetable puzzle, looking for ideas
              philmodjunk

                   I think you'll need that calendar interface for setting up your overall school year so that you can record all holidays and other school closures. This doesn't absolutely require such an interface but I think you'll find that it's useful in that context.

                   Frankly, your six day rotation sounds like a recipe for sheer chaos in terms of school management, but working from that concept...

                   I'd set up a table of where you create that typical 6 day schedule. Then a script can assign a number to each day record for which school is open that links to one of these 6 typical days in order to access the correct daily scheduling information. And that  same master calendar can be used to identify days that you are open but for which the standard schedule does not apply--such as a minimum day schedule.

              • 4. Re: Timetable puzzle, looking for ideas
                appt

                     Hi Phil,

                     me again.

                     The 6 day rotation is a pain but it works ok. The school has had the system for years, but as you can imagine it does make a database solution a little trickier than Id like.

                     Have had a look at the calendar file and the code that creates the records. I am slowly going through line by line to try to get an understanding of how it all works. 

                     So before I needlessly set up a bunch of tables I will again ask your advice (if you have time).

                     Each day is made up of 5 periods. And each period will also have both a teacher and a room attached (the room and teacher may change from day to day)

                     Do I need to set up 6 separate tables, 1 for each day. And if I do what might be the pk and fk and the relationship to where?

                     I'm guessing that on that month view in the calendar that you have shared I could have a drop down that allows me to select Day 1, Day 2, Day 3 etc?

                     I would then need a week view on one layout so that a student can then see what 'Day' (Day 1, Day 2 etc) each day (Monday, Tuesday etc) is.

                     That view would not need to be editable it would simply be their timetable and would help them (and parents plan for the day or the week ahead).

                     Ideally I would have a setup page where you can set the 'Day' for each day for the whole year and provide the database with that info already entered. In following years they would need to be able to access that month view and set the days themselves (or a new copy could I suppose be provided pre-loaded each year).

                     They then need to go to a page that allows them to add in the lesson, teacher and room for each of the 30 periods in a 6 day cycle.

                     Finally they then need to access a timetable page that always starts on a Monday, shows 5 Monday to Friday and the lessons they have.

                     Would love to use your calendar as the starting point but as you can see I will need some help to set the rest up. 

                     This is a freebie that I would provide to my students.

                     No problem if it is too much work to help me on this.

                      

                • 5. Re: Timetable puzzle, looking for ideas
                  philmodjunk
                       

                            Do I need to set up 6 separate tables, 1 for each day.

                       No, that will really complicate getting the correct data into the correct day on your schedule. What you want are records in the same table numbered 1 through 6 to correspond with days 1 through 6 on your 6 day schedule. Those numbers 1 through 6 are then used as match fields to link them to the correct day on your school calendar or student schedule.

                       You can put in a record for each period, which makes for 30 records, 6 days x 5 periods in your 6 day schedule.

                       

                            I'm guessing that on that month view in the calendar that you have shared I could have a drop down that allows me to select Day 1, Day 2, Day 3 etc?

                       You could but why put the user to that bother? A script can add the needed number to match each school day to the correct day in the 6 day schedule.

                       

                            I would then need a week view on one layout so that a student can then see what 'Day' (Day 1, Day 2 etc) each day (Monday, Tuesday etc) is.

                       Once you have the correct links to your 6 day schedule in place, the existing layout in my calendar demo could be changed to form view and the layout could then be modified to use the additional space to show the week's schedule. It won't matter whether Monday is day 1 or day 6, the link to your 6 day schedule will show the correct data for that date. That should provide the needed schedule for adding in the additional student details. They shouldn't need to add in the teacher, that should be provided from the 6 day schedule as I understand your current procedures, but it would certainly provide a place to list assignments, etc.

                  • 6. Re: Timetable puzzle, looking for ideas
                    appt

                         Hi Phil,

                         I have added a new table to the calendar db called Days.

                         It has 6 records and each record contains a field for Periods 1-5 subject, teacher and room.

                         Can you point me in the direction of how now to add the day to a date on the calendar? Do i create a new field in the week table? Obviously I will need to adapt it slightly so that it cannot add days at the weekend or on days designated as holiday or truing days etc.

                    • 7. Re: Timetable puzzle, looking for ideas
                      philmodjunk

                           I would recommend that you not use individual fields for periods one through five. If you read my previous posts, I suggested one such record for each period on each day. Instead of 6 records, you'll have 30. This makes for a much more flexible structure to your data.

                           Also in previous posts, I mentioned numbering your records in this table from 1 to 6. With this number identifying the day. So in my suggested approach, you'd have 5 records with 1 in the day field, 5 with 2, and so forth up to 5 with 6 for the last day of the 6 day schedule.

                           In the table I called "events" in the demo file, you'd add a number field for these numbers of 1 through 6. By assigning 1 to an "event" record, it will link to records in the 6 day schedule table that also have a 1. You can further refine this by matching a Period and a a day field to the 6 day schedule so that a set of 5 event records can each match to a different record in the 6 day schedule all with a different period but the same day. This allows you to copy over data from this "master schedule", but then the users, if such is necessary, can edit the data to make entries specific to that day. (Perhaps there is a variance to the regular schedule, for that one day, for example) and can also record the other data that they need to record for that date in their schedule.

                      • 8. Re: Timetable puzzle, looking for ideas
                        appt

                             Ok, so as you can on the screen shot I have managed to set up my 30 records and by selecting a day from the drop down and using a portal the timetable for a day will appear, very happy with that so far. Obviously I only really want 1 record per calendar day and that record would be the timetable. The next step is to populate a whole year minus the weekends and specific 'blank' days, holidays, training days etc.

                             Unfortunately, being dense, I'm not following the next part

                        "You can further refine this by matching a Period and a a day field to the 6 day schedule so that a set of 5 event records can each match to a different record in the 6 day schedule all with a different period but the same day. This allows you to copy over data from this "master schedule""

                              

                        • 9. Re: Timetable puzzle, looking for ideas
                          philmodjunk

                               Is the list at the bottom a portal with 5 records in it.

                               What I am describing to you with that paragraph may or may not be useful. A lot of time and comments posted to other threads have gone by to make my memory hazy, but what I believe that I had in mind was the ability to look at Period 1 for all 6 days of the schedule by being able to specify a period number. if schedules frequently have the same subject in the same period of the day, that could be very useful as it lest you copy data from day to day for the same period when needed, but if your typical schedule doesn't have that kind of regularity, then this may be a feature that you don't need.

                               But then again, a number field for the period would make it easy to sort your records in order in each portal as well...

                               What's more important is to have two copies of your schedule data, the master week schedule of 6 typical days and then your day to day yearly schedule for your entire school year. The data from your master schedule should be copied to matching records in your day to day schedule so that edits made to those records after the basic schedule for the year is set up do not alter the data in the master schedule.

                          • 10. Re: Timetable puzzle, looking for ideas
                            appt

                                 Hi Phil,

                                 the list at the bottom is a portal. With our timetable each day is different, so if you have maths Period1 on Day 1 you will have it Period2 on Day 2 and so on. Day6 you would not have Maths at all. Plus some subjects might only appear on 3 days out of 6 to be replaced by other subjects on the other days.

                                 So they way I see it working is a student is given a copy of the DB on their iPad. The master schedule is already loaded so for each school day a Timetabled Day (1-6) is already defined. What the student will do then is take their printed timetable and enter the data for each day and period. 

                                 So it is the setting of the master schedule that I need the guidance on really.

                            • 11. Re: Timetable puzzle, looking for ideas
                              philmodjunk

                                   That would be done with a looping script that starts with the first day of school and repeatedly loops through the days of the school calendar skipping weekends, holidays and any other days (inservices?) when the school will be closed copying the data from the 6 day schedule into the master calendar. And you'll have to look over a typical yearly calendar for any other factors that might affect what you need to see for this schedule, things like minimum days, special assemblies, etc all or possibilities that I can imagine that could complicate this process if they are are part of how your school operates.

                              • 12. Re: Timetable puzzle, looking for ideas
                                appt

                                     Thanks Phil.

                                     Have no idea how to write that script as such.

                                     I assume I will need the script to create a new record for each day of the year. Then the script checks to see if the day is a Saturday or Sunday or one of a list of specific dates and misses that day out?

                                     I might have a go, but I reckon with my scripting skills it would actually be faster to create 365 new records and set the day by hand.

                                smiley

                                • 13. Re: Timetable puzzle, looking for ideas
                                  philmodjunk

                                       There are two parts to this problem. The first is to set up a table (I'll call it events) with at least one record for every day that the school is open from the first day of school to the last. Days where there will be no such schedule (Weekends, Holidays, Special Event days....)

                                       The days that have no schedule can be either deleted or marked as a day with no schedule. Chances are that you will need to run a script to create those records and then go through and mark or delete the holidays and special event days.

                                       Once you have those records in your table the script to assign each day to one of the 6 days in the master schedule will look like this:

                                       Enter Find Mode []
                                       Set Field [Events::Date ; Globals::gStartDate & "..." & Globals::gEndDate ]
                                       #This next code omits any records marked with a 1 in the NoScheduleToday field
                                       New Record/Request
                                       Set Field [ Events::NoScheduleToday ; 1 ]
                                       Omit Records
                                       Perform Find []
                                       Sort Records [Restore ; no dialog ] >>Sort by Date in ascending order
                                       Go to Record/Request/Page [First]
                                       Loop
                                          Set Variable [$ScheduleDay ; value: Mod ( $ScheduleDay ; 6 ) + 1 ]
                                          Set Field [Events::ScheduleDay ; $ScheduleDay ]
                                          Go to Record/Request/Page [ Next ;exit after last ]
                                       End Loop

                                  • 14. Re: Timetable puzzle, looking for ideas
                                    appt

                                           WOW!

                                         I managed to write the code that sets up the days of the year, then deletes all days that are weekends or fall during holidays. It's not very elegant or efficient code but it works!

                                         Then using your code (and setting certain days to 1 as suggested) I was able to populate the records with the correct school day - absolutely fantastic.

                                         All I have to do now is design the GUI and start adding options to record details of the lessons and I am away.

                                         Phil, as ever, you are amazing!

                                    1 2 Previous Next