This is called a cross tab report. They aren't the easiest thing in the world to do in FileMaker, but they can be done. My first FileMaker xtab was created with FileMaker 3. That said, FileMaker 11 introduced filtered portals and FileMaker 12 introduces the Execute SQL function--both can make building a flexible xtab report much easier.
That said, it's possible to set up a series of portals to organize your lesson plan data into columns. This assumes that each lesson plan for each subject (or period) is a separate record that either contains a date field or that links to a date record so that you can use that information to determine where on the grid of rows and columns it will appear.
You'll want to start with a table where each record represents one day of the school calendar--including holidays, inservice dates etc. You'll set up a list view layout based on that table to arrange your lesson plans in rows by date. Additional relationship info will limit a given column to a lesson plan for a given date and period to arrange the rows of plans into columns.
Here are possible relationships for this:
Days::Date = Per1Plans::Date AND
Days::constPer1 = Per1Plans::Period
Days::date = per2Plans::Date AND
Days::constPer2 = Per2Plans::period
and so forth for 5 remaining periods. Per1Plans, Per2Plans and so on will all be different occurrences of your Lesson Plans table.
ConstPer1, ConstPer2, are calculation fields that return a value that will match to the period field in the lesson plans table--but you can also consider whether it makes more sense to match by subject instead of period. (Depends on whether you can keep two periods with the same subject in synch throughout entire year given the vagaries of school scheduling.)
In your layout, you can then set up a one row portal to Per1Plans. Put a one row portal to Per2 plans next to it and so forth to get all 7 periods.
Wow! Thanks for the quick response...I am a relative noobie to the Filemaker world. The cross tab approach makes sense, but seems a bit over my head at the moment. I'm a very tech savvy person which is why this is frustrating me.
Let me ask you this...when you refer to starting a table where each record represents one day of the school yearare you saying that I have to create an additional table under the Manage database section, and that table has to have 1 entry for each school day? Basically a list of the 180+ school days of the school year? I would assume that at this point there would only be one field (column in the table), the date then, which will then be used in the relationship section, correct?
My lesson plan table currently does not have a date field but it does have a day of the week as a field...could I use that instead, or will I have to use a date. I ask because my plan is to use these entered lessons from year to year, and having the date as an entry in the database will automatically require me to change the dates. Also, I see multiple sections of the same grade over my weekly schedule. So, for example, let's say I am teaching a 6th grade class, I have one secion on Monday, one on Wednesday, and one on Thursday. I teach the same lesson to each section, so having the date on the original lesson plan would not be advantageous.
Like I said, I am a novice, so maybe I'm thinking too much, but I'm sure if I can wrap my head around what is required, I will eventually figure it out.
Thanks again for all your help...it's helping...
Let me ask you this...
Please note the other fields that you'll need due to not being able to filter your portals like you can in newer versions. In addition to that date field, you'll need calcluation fields that filter your relationships to match to a specific period.
And you may want one record for each week day between the start and end of the school year to give you a placeholder for those days when school is not in session on a week day.
A script can generate the records from a specified start date to a specified end date very easily so that you do not need to manually create the records for this.
You'll need a date field. Day of the week isn't specific enough. Otherwise, lesson plans for a Monday but on different weeks will appear in the same portal.
Actually a date field is advantageous as it can be used to show the same lesson plan in different portals on your layout at the same time. The challenge is that you need multiple dates for a given lesson plan. You can either format the date field as a repeating field or as a text field with returns separating a list of dates. I think this would be one of the few cases where I'd use a repeating field so that I can use date data validation and a pop up calendar to minimize data entry errors.
And just like you can use a script to generate the records in the Days table, you can update the dates in the first lesson plan for a given subject and then use a script or replace field contents to update all the other lesson plan records for that subject--this gets you most of the way there, but then you'd need to do additional adjustments to fit your dates around school holidays, minimum days and in service sessions.