Are you familiar with conditional value lists? That technique will handle some of what you are dealing with here. Let me know if you are not and then I can post some helpful links on the subject.
You'll also need to structure your data carefully in order to control the categories into which each lesson falls without losing track of what categories were selected for a given lesson in previous years and/or seasons.
Looks like you'd need a Seasons table, a Lessons table and a third table: Season_Lesson to link records from the Seasons and Lessons tables.
Seasons would have one record for each season each year. A season field would identify the season. A Year field could identify the year. A SeasonID field (auto-entered serialnumber) can be used to link a specific season record to a specific Lesson.
The Lesson table would have all the fields needed to document a lesson, and also a LessonID (auto-entered serial number) to uniquely identify each lesson.
Lesson_Season would have at least these fields: LessonID, SeasonID, Rank, Exemplar
They would be related like this:
Seasons::SeasonID = Lesson_Season::SeasonID
Lessons::LessonID = Lesson_Season::LessonID
With this many to many relationship implemented, you can change the season, exemplar and rank for any given lesson without losing the designations you selected for it in past years.
Hmmm, I just realized that I may have made an incorrect assumption here. The above set up allows you to assign the same lesson to more than one season of the same year. If this is never the case, then you can change Seasons, to Years and move the Season field into the join table which would then be named Lesson_Year instead of Lesson_Season.
I am not very familiar with any of Filemaker tools. :) I would like to read up on conditional value lists, if you'd be so kind to send me in the right direction.
And yes, the same lesson might be, but most probably not, assigned to more than one season per year.
Your response is already very helpful. I was concerned about the organizational aspect as well as the functionality. I will create the tables you suggest.
Oh also, am I correct in thinking if I portal each value to a list on a "lesson history" tab, it will update and display correctly? Do you think I should have the user confirm that the information is correct before it gets submitted? I'm worried about false records being created or incomplete records from someone changing information within the set of fields. Is a "submit" button common practice?
Conditional Value lists:
There's a fair amount of overlap between the first two links here. You may want to read one carefully and then skim the other.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
What I've diagrammed here is a many to many relationship. Here's a demo file for matching "contracts" to "companies" you may want to examine. If you renamed the tables "Seasons" and "Lessons", you get a basic outline of what I've suggested here:
Among other things, it shows some portals to the join table that enable the user to work with the related records.
Thanks! I'll check these resources out. I appreciate the help. I'm sure you'll be hearing from me again. :)
Again, thanks for the help but I'm still having some issues. I've looked at your demo files and I have set up the table relationships as you mentioned. I'm still confused on how to set up a history that displays information from both Seasons and lesson_seasons. Everytime the designation changes, I would like (on the lesson information layout) to note the new designation. I attempted to discern how to do this from your demo file, but I'm just not getting it. How do I show, or even, where do I show all the info? Just in case, I have posted my file to dropbox: http://dl.dropbox.com/u/27485067/Task%20Management.fp7 because I'm on a turn of the century windows machine that doesn't allow me to take reasonable screen shots. I feel as if I am not able to fully describe what I am doing. Do I need to set up two portals for each table? Can I display information from two tables in one portal?
The Lesson_Season records are your history. Each time you create a new record here for the current season, the records from previous seasons are retained. When you devise a layout such as one to show the past history like this, you are not limited to the fields from a single table. You are also able to include fields from other tables such as the seasons table.
Thus, you might add fields from the Seasons table to the sub summary part of a layout based on Lesson_Seasons with fields from Lesson_Seasons and Lessons included in the body. Select the seasonID as the "when sorted by" field, then sort your records by the Season ID then by Lesson ID and you'll a history of all your lesson-season links with additional relevant data all listed in a report grouped under a "sub header" of information from the seasons table.
Thanks! I'm going in. See you on the other side.