Trying a class schedule.

[Himitsu]

well, all my employees are screaming again... they want me to design a way for them to show the students in a schedule type. I have created a list, which is great for roll calls and things, but they need something that shows a more graphical way, something like what the relationship tables on FMP7. Colums with the classes seperated by time, ie, column 1, 3:00 class, students in that class, 3:30 class, students in that one, and the columns would be the days of the week. Column 1, Monday, Column 2 Tuesday, ect... I know how to use portals but I can't see how this would work. I thought of a seperate field with a setfield command but as the classes are not the same, I can't simple set a new field to monday1, monday2. It would be like a list function sorted by day and class. But how to get that to go in colums? Would it be a repeating field? but I can't see that working either... any hints on how to tackle this beast?

[Ender]

Unfortunately, scheduling solutions are failry complex. In this case, you would likely need a relationship for each time slot for each day. So if you have 10 time slots, and 5 days, you will need 40 relationships.

Though you might be tempted to use repeating fields, you would lose the advantage of relational design. You would not be able to get up-to-date class lists or counts, and propagating changes to classes would be a big scripted mess.

To give you an idea of what's involved in a relational system, you can take a look at the ER Diagram of the first version of my Scheduling module (see attachment.)

[transpower]

Alternatively you could have a table with one record per day, with a field for each time slot. Have another table for students. Then have a join table for day_time_student, which would show in a portal in the first table for each time slot.

[Ender]

The Day_Time_Student table is close to what my Staff_Section is. But since some classes could be longer than one time slot (at least in my case,) it makes more sense to relate to a multi-key of time slots that the class uses, rather than having a join record for time slot for the same section.

So yes, you should break it down into the smallest parts (for the join table,) but that's only a small piece of the solution. How you frame the data entry, reports, and individual student schedules is the hard part. In my solution, I have our staff choose courses they would like to take (that's what the Course-Choice table is,) then a script will go through and slot everyone into sections based on their choices. The end result is class lists (a report in the Staff-Section table,) a master schedule (a report based on the Course-Section table,) and individual staff schedules (a form printed from the Schedule table, showing one day's schedule of related Staff-Section records.) There are of course, other ways to view and manipulate the data, but that the basic functionality.

[Himitsu]

that is what I first thought too, but considering that every year, the class change, it would seem hard. I already have a table that holds class records and the students who are in that class that school year. So I figure they have somethings in common. The class ID, which contains the time which has a field for that and the year. I am wondering if it is even possible to do what I am thinking. ahahaha.

[Himitsu]

Ok... I have been going through the layout in my head and on paper. Ender, I do understand how your layout is functioning. But mine is setup like transpower stated. I have a student table, class list table and a join table that stores each student in the class in it's own record then the class list table uses a portal to the join to show/add students for that class which has a day/time/year fields. What I have to figure out is how to combine all that info in a record and put it in a field. If I use a portal, and go through a join table, then I just basically re-made the class list table again just a little combined that's all. Any ideas on how to do this...

[Ender]

It's not clear how your scheduler is structured. Maybe you can post a screenshot of your Table Occurance Graph.

[Himitsu]

well, it isn't really a scheduler. I am just trying to make one with what I have now. The graph is hard to see because I first designed it in FMP4 then most of the work was rapped up in FMP6. That was before tables came around in FMP. So it consist of a lot of different DB's. Studentlist, classList, class_lineitems, payment, payment_lineitem, ect... but the classlist is designed to have records of each class with a portal that creates new records in the clss_lineitems where we add each student in that class. So the lineitem DB is probably the important file to work with. So I started to work on a new table in that file to make sorting a little easier. I don'T know if that helps at all, but the closest I can get to describing it.